3. Calculating Headline Values

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Most dashboards will have some headline values such as total revenue and growth rate. Here I'll show you how to calculate these values and include them in the dashboard

Lesson Notes

Calculating headline values

- Headline values should be calculated on a backup sheet
- Once calculated, link the headline value on the backup sheet to the correct cell on the dashboard
- This keeps the dashboard neat and hides any complexity associated with the calculations

Revenue, growth, new and lapsed users

- For subscription businesses, growth is comprised of new users - lapsed users
- Always insist on lapsed users being included in the database
- New customer rate will tell you how well the company acquired new customers
- Lapsed customer rate will tell you how well the company satisfied existing customers

Keyboard shortcuts

SHIFT + F11: Create a new sheet
ALT + O , H , R: Re-name sheet
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + F3: Open name manager
ALT + N: Create a new name
CTRL + SHIFT + : Select all cells within data region

Transcript

Let's start off building our dashboard by creating a new sheet in the same workbook as our sales data. To do this, I can click on this button underneath the spreadsheet, or use the keyboard shortcut, Shift + F11. To change the name of this sheet, I'll use the shortcut Alt + O H R, and I'll type Dashboard. For the dashboard, I won't be using the gridlines that we normally have on the spreadsheet, so I'll go to the View tab in the ribbon, and then hit the checkbox for Gridlines. Next, I'll type the title of our dashboard, which is simply Sales Dashboard for Bloom Acre Software. There's no need to worry too much about formatting for now as this is easier to complete once the dashboard has been built. Now let's create our headline metrics which will run along the top of the dashboard. The first of these will be Target Revenue. The CEO has told us that the target revenue is $48 million dollars and I've formatted this cell blue because the figure is an input that can be changed by the user. Next to target revenue, I'll have Total Revenue for 2013. Again I'll add the label off camera to save some time. The remaining headline metrics will focus on growth and the ability of the company to retain existing customers as the CEO has requested. To make sure we select the correct metrics, we'll first need to understand the components of growth for a subscription based company. We start with revenue for the previous year, which was 2012 and then add the revenue from new customers that joined in 2013. We then subtract the customers that left during 2013 which I have called Lapsed leaving us with a total revenue for this year. Comparing 2012 and 2013 will give us the overall revenue growth. The New Customer Revenue number will tell us how good the sales team were at attracting new customers. And the Lapsed Customer Revenue number will tell us how well the sales team served the existing customers. Both are vital to growth and should be included in the headline metrics. Returning to our sales data set, we can see that we don't have lapsed customers included, only new customers which joined in 2013 and existing customers which joined before this year. After asking Bloom Acre for this information they have now included the Lapsed Customers in the sales data set. We can now calculate the four bars from our waterfall diagram. Before we do this, however, let's first name our revenue column by selecting it and Ctrl + F3, Alt + N and I'll call it Revenue. And I'll also name Status column. And I'll simply select it with Ctrl + Shift + down arrow. Naming these arrays will make our formulas much easier to write and to understand. On a new sheet called Dashboard Input, I'll calculate the four revenue bars we had in our diagram. And these are 2012 Revenue, New Revenue, Lapsed Revenue and 2013 Revenue. New Revenue is quite easy to calculate. It's just a SUMIF formula. The range will be Status and we'll check that the criteria is simply New and the SUMRANGE will be Revenue. I'll then close the bracket and press Enter. Similarly, Lapsed Revenue would be SUMIF. Again the range would be Status. And we'll check if a customer has lapsed and in the SUMRANGE will be Revenue. I'll then close the bracket and press Enter. 2013 Revenue will simply be all revenue except Lapsed Revenue. So I'll write equal SUM, and I'll sum the total revenue. And then I'll subtract the Lapsed Total. And finally, 2012 revenue will simply equal to the sum of the total minus our new customers. Let's now include these values in our dashboard. So for Total Revenue 2013, I'll simply link this cell to our $50 million figure. For New Revenue and Lapsed Revenue, I don't want to give the absolute amount because that doesn't tell us a lot about performance. Instead, I want to compare the new revenue and lapsed revenue to the 2012 figure and that'll tell us what percentage of customers are lapsed and what percentage of the total new customers represent. So back in my dashboard, I have added two new metrics. New Revenue Rate, which is simply equal to the New Revenue divided by the 2012 Revenue and Lapsed Revenue rate equal to the Lapsed Revenue divided by the 2012 Revenue. The final metric I'll show in my headlines will be Annual Growth Rate. I'll leave this for you to calculate as an exercise.