Managing a sales pipeline is about more than just pushing late-stage deals across the finish line. In order to maximize your revenue and your team’s efficiency, you need to constantly keep an eye on every aspect of your sales process from target revenue to resource management and more.
In this guide, we’ll take a look at a modified version of Spreadsheet.com’s Simple CRM template and see how adding charts can give you new ways to visualize and analyze your sales pipeline. Take a look at our sample workbook and follow along, or create a copy for yourself to experiment.
Adding charts and graphs to your worksheets can help you visualize your data and gleam insights that may not be apparent when viewing data as rows in a spreadsheet. Data visualizations can help you generate important analytics, aggregate hundreds or thousands of rows of records, and give you more tools to present your data in a viewer-friendly format.
For our Simple CRM workbook, we’ll use charts to break down dozens of sales records by industry and by quarter.
To create a new chart, click the New chart button in the toolbar above the formula bar. Or, from the workbook menu, select Insert > Chart. Once you create a new chart, you can select a chart type and configure it from the Chart settings dialog on the right side of the screen.
Spreadsheet.com supports more than a dozen different types of charts. Learn more about selecting a type of chart for your data in our Help Center.
Before we dive into the financial side of our sales pipeline, we want to understand the distribution of deals across different industries. To do this, we’ll create a new pie chart. Take a look at the pie chart on the left – “Number of Deals by Industry” – in the image below:
This pie chart displays the distribution of deals in our pipeline across four different industries. It was configured in the Chart settings dialog on the right side of the image.
“Label” determines how the pie chart is divided into its slices. Because we want to visualize deals by industry, we’ve selected Column C - Industry for our label values.
“Value” – and its associated aggregation function – determines the size of each of the pie chart’s slices. Because we want to see a count of the deals within each industry, we’ve selected Column A - Name with the aggregation function count to calculate our values.
To the right of that chart, we’ve created a second pie chart – “Total Revenue by Industry” – that shows the total potential revenue for all deals in each industry.
Take a look at this chart’s Chart settings dialog. It uses the same “Label” values because it’s organized in a similar way as our previous chart, but the “Value” information is different. Because we want to use this chart to show revenue information and not a simple count of deals, our “Value” is selected as Column D - Tot. Revenue with the aggregation function sum.
Because we’re working with numerical, and not textual, data, we have more aggregation function options. Instead of showing a sum of revenue, we could choose to show the average or median revenue for each industry, or maximum or minimum values in each category.
Let’s take a closer look at our two charts.
By adding charts to our CRM and visualizing our data, we can gleam some insights that may not be as apparent when scanning through dozens of rows. For example, these two pie charts reveal that deals in the “Hospitality” and “Other” industries account for less than half of all deals in our pipeline, but contribute almost two-thirds of our total potential revenue.
Now that we’ve visualized our pipeline by industry, we can use more charts to take a closer look at the financial side of our CRM data. Take a look at the bar chart on the left – “Profit Forecast by Quarter” – in the image below.
This bar chart displays the total profit forecasted for each quarter of 2022. In the Chart settings dialog on the right side of the screen, Column I - Close Date has been assigned to the chart’s Y-axis labels and is aggregated by Quarters. We could choose a different aggregation period like Months or Weeks to drill down further into the data.
Column K - Profit Forecast has been selected for the chart’s series data and is aggregated with the sum function so we can visualize the total profit forecast for each quarter.
To the right of that chart, we’ve created a 100% stacked bar chart – “Profit Margin Forecast by Quarter” – to visualize a rough estimate of the expected profit margin for each quarter.
This chart is configured with the same Y-axis as our previous chart, but now has two series: Column J - Revenue Forecast and Column K - Profit Forecast, both aggregated with the sum function.
Instead of showing the raw values, 100% stacked bar charts display their series values proportionally. With this type of chart, we can visualize our revenue and profit numbers as a rough calculation of a profit margin instead of seeing the actual dollar values.
Let’s take a closer look at our two charts.
Just as we discovered with our pie charts previously, looking at these bar charts helps us understand our data in different ways than viewing our data as spreadsheet rows. With these charts, we can see that although our profit margin is stable across the entire year, our actual profit forecast differs greatly between Q1, Q2 and Q3, and Q4. Data like this can help us optimize our sales strategy and decide how to allocate resources more efficiently.
Ready to get started? Browse Spreadsheet.com’s Template Gallery to find ready-to-use CRM and sales templates, as well as templates for construction project management, education, finance, and more. Or, visit our Help Center to learn more about working with charts and graphs.