Create KPI Dashboard
KPI dashboard – The all-time classic! A KPI can have a goal which is the exact value that it should reach, and ranges for tracing purpose.
The business world for some time now is heading in the direction that we weigh every possible thing. To quote a classic phrase: we weight it, it gets better. So we got the idea for the key performance indicator from here. And how true is the before quoted sentence!
Build Business Performance dashboards from scratch!
There can be mapped multiple departments and significant related business process within a company. These days we can apply 50+ indicators at a moderate sized company. We would like to show everybody the world of performance measuring with the help of the articles of the KPI category.
Performance dashboard can be effective tools in reaching business goals. Was this enough reading for you? Excel dashboard templates allow you to see trends and key performance indicators. It’s an awesome tool for creating visual reports. The goal of this article is to show you how to build better reports.
As is usual key performance indicator can be linked to three different statuses.
If the indicator is red, we know at once that something is not going well with the examined process and we have to intervene right away.
The yellow sign alerts us that – although we are not too far from the plan – the reviewed procedure, in our case the marketing, needs increased focus.
The green light needs no explanation when we see this on the dashboard than all is in the best order.
Is it easy to create a key performance indicator?
From the technological view, yes.
A metric has four characteristics:
• Current value, namely where we stand now.
• Plan value, namely where we plan to stand now.
• Deviation value, namely by how much and what direction we differ from the plan.
• And a trend value that shows the trend of fact values.
We have to give these values to create a KPI and from then on the tool is doing everything for us. If we want it displays the KPI on a speedometer, or on a diagram or a map. It does it all depending on the content, i.e., according to given period, branch, product group or other criteria.
But the challenge does not lie here but in the choosing of KPI. Could you tell which one is that 5-10 KPI, or we can call it performance indicator by which you can monitor the achievements of your own field? Could you create 5 or 10 KPI with your field’s success criteria, your department’s value saving ability?
What are the most important KPI ?
Let’s say that you are an IT designer. You are a very busy man with not much time on your hand. Wouldn’t it be good to have a dashboard that you can look at every morning and could see the values that your department has added to the success of the company.
You can see that all IT procedure is running fine, where you can track the IT expenditure compared to plan, where you can see the levels of the IT projects and see how the goals and expectations of your department be realized, etc.
It would be good if there would be only a few indicators on your dashboard and it could be clearly arranged. But what should these indicators be? And what would be those indicators you would put on your boss dashboard?
Today, thanks to the business intelligence solutions, not a great challenge to display the KPIs, but to choose those KPIs is a hard and time consuming task.
How to Create Sales KPI Dashboard? – Tutorial
Our primary goal is to show the main key performance indicators and provide a comparative view of other KPIs or products using interactive visual elements.
In this tutorial, I will show you how to create a sales dashboard in Excel. This is an updated and improved version of a dashboard that I created in 2013.
Let us see how the final interactive sales dashboard looks like:
Our excel sales dashboard is divided into four parts by products.
It will be easy to compare the main indicators. To measuring performance and creating sales dashboard in Excel is a simple procedure, we’ll apply the good old offset function and conditional formatting.
This template allows user to select the dimension of data and shows sales efficiency right away. This dynamic template let us check sales by consumer type or location and examine values with averages too.
The OFFSET function helps to determine the ranking and grouping as soon as possible and with help of formatting we can analyze the high and low differences.
The most used excel sales reports inform the sales team and management which products are selling well.
We should rank the highest and poorest performing products to determine which products are failing to boom with your customers.
The three units of our kpi dashboard:
• KPI is the main Excel worksheet; we summarize and visualize the results here.
• Now go to the data sheet, here are the main data.
• On the calculation sheet can be found the calculation area.
- We have the raw KPI data for 100 salesreps and for 4 pruducts. The data looks as shown below.
2. First create a named ranges for the further calculations. Select the cell or range, in this example we’ll use both of them. Select the =data!$E$10:$E$109 range on the data sheet.
Go to Formulas tab! In the Defined Names group, click Define Name. Enter a Name (menu) for the range. Select cell E6 and add enter a name (sort_order)
See the results on the picture below!
3. Use the OFFSET function to build a dynamic named range for further calculations.
On the calculations sheet type:=OFFSET(data!$E10;0;sort_order).
Explanation: How to get the correct name from the unsorted list? OFFSET(name of the salesrep from data table; 0 = same row; 1st position)
4. Use the =E10+$C10/1000000000 formula to create unique list!
5. To get the proper value in a range, you can use the CHOOSE function together with the LARGE and SMALL functions.
The CHOOSE formula returns a value from a list using a given position or index. For example, CHOOSE(3,”KPI1″,”KPI2″,”KPI3″) returns “KPI3”, since KPI3 is the 3rd value listed after the index number.
The LARGE function retrieves numeric values based on their position in a list when sorted by value.
The SMALL function retrieves numeric values based on their position in a list when sorted by value.
6. To get the position of an item in an array apply the MATCH function.
7. Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for Product 1.
8. Jump to KPI sheet! You can insert option buttons to make data entry easier.
Option buttons are perfect when you have just one choice.
To add option button, click the Developer tab, click Insert. Finally under Form Controls, click option button icon.
9. To add linked cell, right-click the option button, and then click Format Control.
Jump to the Control tab. In the Cell link box, enter a cell reference that contains the current state of the option button.
Use the same linked cell (E6 on the calculation sheet) for Product 1 to Product 4.
10. To assign a macro to the inserted picture (icon), execute the following steps:
Right-click on the picture. Choose the Assign Macro command. Add a macro from the list.
11. The Macro is very simple. I think it’s not necessary to further explanation.
12. You can combine the conditional formatting tool with your KPI settings.
13. To manage all conditional formatting rules in this worksheet, use the Conditional Formatting Rules Manager.
14. On the Home tab, click Conditional Formatting, and then click Manage Rules.
Select the ‘Format all cells based on their values’ rule type option. This is the easiest way to create kpi based conditional formatting.
15. To insert a slider in Excel go to Developer Tab –> Insert –> Scroll Bar).
Click on Scroll Bar button and insert it in the worksheet.
16. Right click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.
Go to ‘Control’ tab, and make the following changes to create scrolling list:
- Current Value: 1
- Minimum Value: 1
- Maximum Value: 76
- Incremental Change: 1
- Page Change: 10
- Cell Link: $E$5 on the calculation sheet.
Finally it’s done!!!
Conclusion: Everything wants KPI
Everything wants KPI! These days the business intelligence systems have dashboard support. With their help we can create performance dashboards in which we can place our most important indicators, the KPIs.
We can give a management tool by which they can oversee the fields under their responsibility, they can see all of its movement and notice right away if it is differ from the balance course.
This is a strong weapon what the Business Intelligence Solution providers do utilize.
They make people believe that if they buy their product they will be able to create dashboards consisting of these KPIs and afterwards they have nothing to do but look at the dashboard during the morning coffee and make sure everything is going well.
We, as in charge of business intelligence help in the systematical gathering of existing KPIs, to text the requirements of the dashboards, unify the KPIs, but in the future too, it is the task of the management to choose the most important KPIs.
Don’t forget: everything wants KPI!
Additional KPI Dashboard resources:
- How to create professional kpi dashboard in minutes? Check our gauge chart templates and add-in!
- Call Center Performance Dashbord – Detailed Tutorial
- Are you interested in human resource management? Visit the HR Scorecard Template!
- Quick KPI Charts in Excel? No problem!
- Advanced data visualization – Healthcare KPI Dashboard