Sign in or start a free trial to avail of this feature.
7. Sales Team Analysis
In this lesson, we'll analyse the performance of our salesforce, calculating the annual revenue generated by each sales manager.
Adding metrics on top of revenue
- A simple revenue chart can be combined with a related metric to provide additional insights
- For example, average revenue per customer identifies which sales managers focus on big clients
- Always consider adding a metric to a chart if it's just a simple bar-chart
Combining two charts in one
1 First select the series you want to convert into a line chart
2 Right-click on the series and Change Series Chart Type to Line chart
3 Select the line chart and go to Format Selection in the Layout tab
4 Select Secondary Axis under Series Options and click close
CTRL + SHIFT + →: Select all cells within data region
CTRL + F3: Open name manager
ALT + N: Create a new name
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
ALT + E , S , F: Paste formulas
ALT + N , C: Create column chart
ALT + A , S, S: Open Sort dialog box for selected data
Our current dashboard has three charts shown on screen that evaluate the sales performance for the company as a whole. The lower half of the dashboard will focus on the performance of individual sales managers. And in this lesson we're going to create a chart that will compare the revenue generated by each sales manager that will look something like this. Let's start by returning to our data set and naming the sales manager column. This will make the formulas using this column much easier to build. So I'll select the column with Ctrl + Shift + down arrow, Ctrl + F3 to open the name manager and Alt + N to create a new name and I will call this Sales Manager. I'll then press OK, escape from the name manager and move to the dashboard input sheet. Here, I've created a list of cells that correspond to each sales manager and a column for revenue. To calculate the revenue for each sales manager, you've probably guessed that again I'll use the SUMIFS function. So equals SUMIFS and open a bracket. The sum range will be the revenue column. The first criteria will be sales manager and the criteria for sales manager will simply be the name of the sales manager. For the last criteria, we'll check the status column and we'll make sure that the status is not lapsed because I only want to show 2013 revenue in this chart. I'll then close the bracket and press Enter. We can now copy and paste for the remaining sales managers. So I'll copy with Ctrl + C, select the cells and Alt + E S F to paste formulas. I'll now select my data and Alt + N C to create a chart and I'll select the stacked column chart. This creates a chart that shows a revenue for each sales person, but it would be a lot easier to read if we sorted the revenue from largest to smallest. I'll return to my data set and again select the data and this time I'll use the shortcut Alt + A S S to bring up the Sort dialogue box. And I'll sort by revenue from largest to smallest. I'll then press Ok. And as you can see that makes our data a lot easier to read. We can now move this chart to our dashboard. However revenue charts like this can always be combined with an additional metric and provide further insights. For example, we could show up percentage of revenue as new, what lapsed revenue rate each sales manager has etcetera. After discussing this with the CEO he's eager to find out which sales managers are focusing on larger clients as he has directed them to do. To find this out, we can simply compare the sales manager by the average revenue per customer. To calculate this metric, we'll simply divide revenue by the number of customers for each sales manager. So I'll delete the chart and entitle a new column, Average Revenue per Customer. And here I'll take the revenue and divide it by the number of customers for each sales person and I'll calculate this using the COUNTIFS function. The first criteria will be the sales manager and I'll make sure that the sales manager, in this case, is Hall. And the second criteria would be status. And I'll make sure that the customer is not lapsed. I'll then close the bracket and press Enter. Again we can copy and paste for the remaining cells. And I'll do a little bit of data formatting off-camera. Let's now create our chart again so I'll select the data, Alt + N C and create a column chart. And unfortunately, because average revenue per customer is such a small value compared to the total revenue, we can't see this value on our chart. To solve this problem, I'm going to convert average revenue per customer into a line chart and give it a separate axis. Let's select the series, then I'll right click and change series chart type. I'll then select a line chart and press OK. I'll select the line chart again and for the series options, I'll select a secondary axis. This now makes it much easier to view our average revenue per customer. Off camera, I'll make some formatting changes and transfer this chart to the dashboard. From the chart, it's easy to pick out the sales managers that are generating more revenue from large customers. The CEO can also identify which sales managers are not targeting large customers and encourage them to do so.