Sign in or start a free trial to avail of this feature.
9. Link Sales KPIs to Buttons
In this lesson, we'll learn how to link our sales KPIs to the input button. Now when we select a sales manager, his KPIs will update automatically on the dashboard
Linking KPIs to form control buttons
- Give KPIs for each sales manager a separate column in your lookup table
- Make sure the number format of your KPI column is correct once calculated
- Use cell link from the button as the lookup value for each KPI column
- Note: INDEX/MATCH could also be used instead of VLOOKUP function
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select next cell
CTRL + C: Copy selected item(s)
ALT + E , S , F: Paste formulas
CTRL + SHIFT + %: Convert cell format to percentage
In the previous lesson, we created a dropdown list that contained all of our sales managers. When we selected the sales manager the output was a number which we used a LOOKUP to find the total revenue for Parrish. In this lesson, we're going to do the same for the new revenue rate and the lapsed revenue rate for each sales manager. These metrics will tell us how well our sales managers are doing at finding new customers and holding on to existing ones. The formula for new revenue rate is equal to new revenue divided by the 2012 revenue. And the formula for lapsed revenue is the amount of lapsed revenue divided by the 2012 revenue. So in my dashboard input sheet, I've created three new columns. The first will calculate the 2012 revenue for each sales manager because this value will be used in both our formulas for the new revenue rate and the lapsed revenue rate. So let's calculate the 2012 revenue using the SUMIFS formula. The sumrange will be the revenue column. The first criteria range will be the status and the 2012 revenue will include all revenue except revenue that's new. The next criteria would be the sales manager column and here I'll just make sure that the current sales manager is selected. I'll then close the bracket and press Enter. I can now copy and paste for the remaining cells. Now let's calculate the new revenue rate. So this will be equal to the new revenue generated by the sales manager divided by the 2012 revenue. To calculate the new revenue, again, I'll use SUMIFS so the sumrange would be revenue. The criteria range would be status and I'll want only the new revenue. And finally, the sales manager criteria will, of course, be the current sales manager. And this is then divided by the 2012 revenue. I can now copy and paste for the remaining cells. I'll convert these values to percentage with Ctrl + Shft + &. The lapsed revenue rate formula will be very similar. Again I can use the SUMIFS formula, my sumrange will be revenue. My first criteria range will be status and this time my criteria will be lapsed. My second criteria will be the sales manager, and I'll make sure that the current sales manager is selected. I'll then close the bracket and again divide by the 2012 revenue. I'll copy and paste this for the remaining sales managers. Again, I'll convert to percentage with Ctrl + Shift + % sign. With these values in place, we can now return to our dashboard and connect our drop-down list to these two metrics. So I'll write VLOOKUP, that will take the cell link as my lookup value, my table array will be my full lookup table. My callindexnum for new revenue rate will be 6 and the range lookup will be FALSE for an exact match. And similarly for lapsed revenue rate, we'll write a LOOKUP again, the LOOKUP value will be the same which is simply the cell link, which is output from the dropdown list. Table array will again be my lookup table, but this time, the callindexnum will be 7. And again the range lookup will be FALSE. I'll then close the bracket and press Enter. And this leaves us with a new revenue rate and our last revenue rate for each sales manager. When I change the sales manager the numbers update accordingly. The CEO is very pleased with this functionality because he can now compare the new revenue rate and the lapsed revenue rate for each sales manager against the company average. The CEO would like us to go one step further however and rank the performance of each sales manager with these three metrics. We'll do this in the next lesson using the RANK function.