Using sales activity template we will create a good looking sales presentation.
The sales activity and its visualization is a particularly rewarding subject!
You can find several excel dashboards on the website if you’re serious interested about this field.
In today’s tutorial we’ll show you how to create a complex Excel presentation.
4 products, 3 regions, yearly and detailed data and all of this is on one screen!
This kind of article was long due!
What data will be on the dashboard?
The left-side section contains a column chart. This displays the chosen product’s yearly sales evolution.
On the right side there’s the breakdown by regions with the use of three gauges.
What kind of tools will we use?
First and foremost advanced Excel formulas like: MATCH, INDEX and VLOOKUP.
We calculate partial results on an additional worksheet.
When we’re done we display the results on the sales activity template.
Data Sources and Worksheets
Let’s see the starting (basic) data! The data worksheet contains the starting data and all the calculations that we need.
On the gauge worksheet stored is the basic information of the speedometers (minimum, maximum and actual values).
On the dashboard worksheet there are the operational elements and the charts.
Let’s see in detail the tables found on the data worksheet!
In the first range you can see the matrix contains the four products and the three regions.
On the second table we didn’t use breakdown by regions, here the emphasis is on the sale values by months.
On the third table, on the dashboard worksheet the name of the chosen product appears. With this value we’ll make further calculations.
The fourth table contains the yearly sales of the chosen product in monthly breakdown. In the fifth table we calculate the actual values of the gauges that’s also depends on the chosen product.
Dashboard Formulas – How to create column chart?
After this introduction let’s see all the steps of the calculations!
For the region breakdown we’ve used percentage values. This is the simplest way to unfold the numbers of yearly sales. First let’s see the example of Product A / Region I combination.
In the table the 35% value means that the share of the year’s sale was 35%. You can interpret the other elements of the matrix based on this logic easily.
We portray the values of the second table on a column chart! These are fixed values we don’t have to make any calculations. And now the trick comes!
In the fourth table (calculation for monthly data charts) we have to display the yearly data for the chosen product.
On the picture you can see that this is now Product “B”. The value of K2 cell is 3. With the help of the VLOOKUP formula we’ve calculated the values belonging to this product.
And we are done with the first half of the interactive excel dashboard!
Prepare Gauges for Sales Activity Template
The fifth table is really interesting!
Excel offers two solutions for lookup type searches. The first one is the old, but widely used VLOOKUP. The other is the combination of the INDEX and MATCH formulas.
Advanced users prefer to use the latter solution.
With the help of the MATCH formula we calculate the relative position of the chosen product. The INDEX will give the precise position of the searched value.
Finally we link the gauge charts and the gotten values. The interactive excel dashboard will become truly spectacular with the use of the gauge charts!
After download check the calculations! We have built the formulas so beginner users can understand all the steps.
Conclusion and Downloads
In the future we’ll alternately publish easier and more complicated tutorials.
To understand the operation of the sales activity template you need patience and attention.
Your knowledge will be soon exceptional if you spend time on the more complicated excel dashboards also.
Our goal remains the same in the future: free kpi dashboards to everybody!