KPI Dashboard Training
KPI dashboard – The all-time classic! These days the BI systems have dashboard support. With their help we can create KPI dashboards in which we can place our most important indicators, the KPIs. 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 weigh 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 KPI 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.
KPI 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 KPI 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.
Everything wants KPI
Everything wants KPI! These days the business intelligence systems have dashboard support. With their help we can create kpi dashboards in which we can place our most important indicators, the KPIs.
We can give management a 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.
Is it easy to create a KPI?
From the technological view, yes.
A KPI 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.
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!
Create Sales KPI Dashboard
Our excel sales kpi dashboard is divided into five 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.
Sales kpi dashboard 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 KPI dashboards 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 excel 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. Use the following link to download this sales kpi dashboard!
Build a Customer service KPI dashboard
We’ll show you how to create in Excel a simple customer kpi dashboard for monitoring and reporting the actual status of customer center key performance indicators. The Main Objective building a one-page excel kpi dashboard for tracking the actual status of key performance indicators.
In the situation of tracking customer performance, a KPI is necessary.
Key Performance Indicators for customer service
Using these key performance indicators we can track and trace the overall service performance.
Time to answer: this performance dimension usually expressed in second this is the time from when a call is started until it is responded by a customer service agent. Using the average call time to answer telephone call is the base of calculation.
Abandon Rate of incoming calls: we will handle this key performance indicator as a % of the number of callers who are cut off, before they touch an agent who answers their call. We can measure this indicator the following method: [numbers of abandoned calls while the customer waiting] / [total incoming phone calls]
We can use this formula to calculate FCR value = [issue solved by first call] / [total issues] this is a very important dimension (my opinion it is hard to tracking), expressed as a percentage, the number of those calls and that they will be responded to at the first try during the call.
In this case do not require the customer to call back.
Customer Data visualization
Check the actual status of
• Time to answer for the selected week
• Abandon rate for the selected week
• First call resolution for the selected week
• The period (average of the branch) from the actual week to actual week + 3 months
Put the results into our kpi dashboard in Excel
We are divided this excel model into three separate worksheets for the excel dashboard, the main dashboard sheet, the input data and the calculation area. It is one of the easiest ways to build a clear and structured model. The implementation of the raw data and calculated value is very easy:
The main worksheet with the all figures is linked directly to the data table.
We will use spin button to display the results week by week. When defining a key performance indicator (KPI) it is appropriate to notice the characteristics of the company.
These indicators are perfect in areas of sales, marketing and IT. KPIs are based on business goals.
A business goal is such a high level objective, which is measurable and expressible in quantities that can be translated to a KPI with which a company can measure the aspects of a process to a predefined target.
In the next article we discuss the main indicators and the level of success can be determined by comparing the actual results.
Customer level performance tracking
With fixed duration the calculated KPI is based on a single duration. You must specify the start and end dates – for example from 1 January 2015. to 31 January. If you specify only a start date the KPI will be calculated from the specified date to the current date.
If you specify only the end date the KPI will be calculated from the introduction of the process to the specified end date.
The clear indicators of the company’s performance are the changes in the sales volumes. Management information systems provide to these changes’ availability in time and in proper structure.
When the exploration of relationships between sales data is typically a company’s inside task, the customer kpi dashboards mostly are capable to evaluate the performance only retrospectively.
The evaluation has two key objectives: evaluate, classify, rank and develop, change. In addition it can serve as a basis for promotion, pay raise, relocation or prehaps dismiss.
You can download this customer kpi dashboard for free.
Create Healthcare KPI dashboard
Healthcare KPI dashboard template is our first attempt to develop infographics style dashboard in Excel using male and female infogaphics elements. Our goal is tracking and analysing the most important healthcare quality information of human body.
Dashboard Design & Functions
We’ll use an exclusive visual design and style which helps you to identify the key performance indicators and the overall performance. The first question: How to effectively track and accomplish our goals?
We divided the male and female vector shapes into nine shape groups. The first 8 slicer display the health of a body part (percentage), the last one visualize the overall health status. How to visualize data on a human map?
The value based color mapping method (using our well-tried vba map engine) will perfect for us!
Healthcare KPI Dashboard – Color Panel
On the Sheet ’calc’ fill in a line for each dimension value you want to set up. On the right side of the picture you can find the values area.
Here is a list of some parts of the body that have been included both male and female heatmap: Brain, Lungs, Heart, Liver, Stomach, Knee, Leg and Foot. You can change these elements of a human chart if you want.
Check on the middle of the picture! We prepared three bulit-in color themes for you: white to blue, white to green and the classic red to green. But you can override the predefinied settings for colors without a trouble.
Finally on the left side of the screenshot see the scale and the currently selected colors.
The color scale divided into 16 sections using PERCENTILE function. The default parameter of minimum value is 0, the default parameter of maximum value is 100.
Download our healthcare kpi dashboard!
Advanced Performance Tracking – Cube Dashboard
We are crazy but in our opinion it’s not impossible to visualize more indicators on one kpi cube dashboard. We mapped a cube using VBA. We used the same engine as Premium Excel Power Maps.
When our first idea was born to display more key performance indicator we didn’t know ourselves if we are able to create such a special dashboard. The Rubik cube gave us the idea and after this we have stopped development for a long time because in the beginning we had a lot of trouble with spatial visualization.
If you are interested in how can you learn Excel shape-coloring technic and use it efficiently than look around in our Excel maps articles and take your pick! We had made more country’s interactive maps available for educational purposes.
We will use excel shapes as a way of identifying and organizing visual information like key performance indicators.
Zoom to KPI Dashboard!
The cube area divided into 27 color coded regions. There are 9 people on each sales team so we’ll measure three different teams. The 27 separated area in the cube represent the target versus actual value by each person.
We’ll measure performance! Just set targets for the kpi settings to monitor your sales teams!
As a result of the development of the kpi dashboard you can provide a powerful presentation in 5 minutes. In creating the dashboard we have applied previously introduced solutions, namely using some simple programming tricks we have colored geometrical objects based on the previously assigned values to given objects.
This method is similar to conditional formatting but can be widely used.
Under the color changer area there is a drop-down list. We displayed the drop-down list with white color. The drop-down menu list is known for those who handle Excel, its key task to manage the listed data.
On the right side of the picture there is the interactive cube. Based on the presented results you can quickly show how popular the selected sales rep in the team.
Setting up KPI and color settings
In column Scale values can be found that divide the scale for 16 equal parts and in column Display Label displayed the intervals. The Color Templates part shows that six color scale that colors the cube depending on the radio buttons modifications. And this is all!
Based on the presented results you can quickly show how popular the selected sales rep in the team.
Additional KPI Dashboard resources
KPI Tracking Template is a metro UI style template. Namely this is an Excel add-in where conditional formatting function finally expands. No experience needed to use the add-in. Fortunately it automatically does all the calculation tasks.
What is the clearly and simply method? Your task is only to define the limits of the key performance indicators. Set the arbitrarily picked color and value zones and your dashboard is done!
A VBA code ensures that when the values of the cells change the elements of the dashboard also change dynamically.