KPI Dashboard

KPI stands for Key Performance Indicator. KPIs are goals or targets that measure how well a company is doing with achieving its overall operational objectives. KPIs need to be clearly defined in order to provide a quantifiable and measurable indication of progress towards achieving goals.

Since they are defined and measurable, dashboards are an excellent tool for tracking KPIs. Let’s examine how to create a simple, effective KPI dashboard for the ACME Company. Here are the products that the ACME Company sells (primarily to coyote’s).

Item
 Units Sold
 Unit Sold Price
 Per Unit Profit
 Cost To Make Unit
 Total Sale $
 Total Cost $
 Total Profit $
Coyote First Aid Kit
             4,050
             16.89
               1.79
             15.10
     68,404.50
     61,155.00
       7,249.50
Blank Signs
             1,046
               2.45
               2.23
               0.22
       2,562.70
           230.12
       2,332.58
Knife and Forks
             1,181
               1.98
               1.76
               0.22
       2,338.38
           259.82
       2,078.56
Road Paint
             2,330
               1.15
               0.89
               0.26
       2,679.50
           605.80
       2,073.70
500 Lb. Anvil
             2,093
               1.15
               0.98
               0.17
       2,406.95
           355.81
       2,051.14
Hypno Ray gun
             1,859
               1.23
               0.99
               0.23
       2,286.57
           427.57
       1,840.41
Giant Round Boulder
             1,358
               1.37
               1.26
               0.10
       1,860.46
           135.80
       1,711.08
Parachute
                832
               2.02
               1.85
               0.17
       1,680.64
           141.44
       1,539.20
Paint Brushes
             1,815
               0.97
               0.70
               0.26
       1,760.55
           471.90
       1,270.50
Giant Sling Shot
             2,175
               0.70
               0.54
               0.16
       1,522.50
           348.00
       1,174.50
Giant Bow and Arrows
             2,086
               0.86
               0.54
               0.32
       1,793.96
           667.52
       1,126.44
1000 Lb. Anvil
                878
               1.38
               1.27
               0.10
       1,211.64
             87.80
       1,115.06
Wings
                588
               1.89
               1.65
               0.24
       1,111.32
           141.12
           970.20
Tunnel Paint
             1,670
               0.87
               0.56
               0.31
       1,452.90
           517.70
           935.20
Strap On Helicopter
                868
               1.25
               1.01
               0.24
       1,085.00
           208.32
           876.68
Hole Paint
             1,125
               1.32
               0.78
               0.55
       1,485.00
           618.75
           877.50
Ball Bearings
             1,164
               0.70
               0.68
               0.02
           814.80
             23.28
           791.52
Rocket Launcher
             2,000
               1.25
               0.38
               0.88
       2,500.00
       1,760.00
           760.00
Sign Paint
             1,152
               0.70
               0.51
               0.19
           806.40
           218.88
           587.52
Giant Spring
                722
               1.07
               0.80
               0.27
           772.54
           194.94
           577.60
Aviator Goggles
             1,011
               0.80
               0.70
               0.10
           808.80
           101.10
           707.70
Large Hammer
             2,263
               0.39
               0.24
               0.16
           882.57
           362.08
           543.12
Metal Bird Seed
             1,850
               0.78
               0.28
               0.49
       1,443.00
           906.50
           518.00
Roller Skates
             1,000
               1.56
               0.33
               1.23
       1,560.00
       1,230.00
           330.00
Red Rocket
             1,375
               1.11
               0.23
               0.87
       1,526.25
       1,196.25
           316.25
Helmets
                    5
               1.17
               1.08
               0.09
               5.85
               0.45
               5.40

Now we need to define our KPIs.

If I look at my data, I have 7 KPIs to start with. I could also add other KPI criteria such as target sale goals, length of time the product sits on the display shelf, etc. but for this example let’s keep it simple and use what we already have in our data.

KPI 1
 KPI 2
 KPI 3
 KPI 4
 KPI 5
 KPI 6
 KPI 7
 Units Sold
 Unit Sold Price
 Per Unit Profit
 Cost To Make Unit
 Total Sale $
 Total Cost $
 Total Profit $

To start I need to create two tabs in addition to my data tab, Calculations and DASHBOARD.
On the Calculation Tab I want to add two sections, one for Top 5 Values by KPI and the second, Top 5 Product Names by KPI.
















Right about now you’re saying to yourself… Now hold on there! How did he do that?

The Top 5 Values by KPI is simple using the LARGE function
LARGE(array,k)

Array     is the array or range of data for which you want to determine the k-th largest value.
K     is the position (from the largest) in the array or cell range of data to return.
If array is empty, LARGE returns the #NUM! error value.

If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

Cell B4 value is =LARGE(Data!B$3:B$28,1)
Cell B5 value is =LARGE(Data!B$3:B$28,2)

























As for the Top 5 Product Names by KPI…

Well a while back I showed you have to do a VLookup to the Left using a combination of Index and Match. Don’t recall seeing how it works, insert link here. If I did not want to use Index and Match, I could order my data with ITEM in the last column instead of the first or I could add a helper column to the right that has the same value as the Item column. I could then use VLOOKUP to get my data (but where is the fun in that??? And how would you learn Index Match???).

I use the the Top 5 Values by KPI as my matching value. Here is the first row each KPI column.

=INDEX(Data!$A$3:$B$28,MATCH(B4,Data!$B$3:$B$28,0),1)
=INDEX(Data!$A$3:$C$28,MATCH(C4,Data!$C$3:$C$28,0),1)
=INDEX(Data!$A$3:$D$28,MATCH(D4,Data!$D$3:$D$28,0),1)
=INDEX(Data!$A$3:$E$28,MATCH(E4,Data!$E$3:$E$28,0),1)
=INDEX(Data!$A$3:$F$28,MATCH(F4,Data!$F$3:$F$28,0),1)
=INDEX(Data!$A$3:$G$28,MATCH(G4,Data!$G$3:$G$28,0),1)
=INDEX(Data!$A$3:$H$28,MATCH(H4,Data!$H$3:$H$28,0),1)

For the subsequent rows, I just change the value of the highlighted red cell reference. For example, =INDEX(Data!$A$3:$B$28,MATCH(B4,Data!$B$3:$B$28,0),1)  Would become
=INDEX(Data!$A$3:$B$28,MATCH(B5,Data!$B$3:$B$28,0),1)














When done, I have the following…



























Now I want to create my Dashboard tab.

I format the entire worksheet white background and then add the Top 5 Products by KPI.

I use a simple concatenate to lookup each columns data against the calculations tab. For cell C5 I use the following formula… =CONCATENATE(Calculations!B12," - ",Calculations!B4).







So the top of my KPI dashboard gives the user a quick high level overview of the top products in each KPI (with value) that I am measuring.

Now let’s look at comparing two KPI values.

Back on the Calculation tab I add my KPI Values and assign them the name KPI Values.



I want to use this on my dashboard for KPI comparisons.

On the dashboard tab in cell B12 I type KPI Value 1. In cell C12 I apply data validation to allow the user to only select the values on my list. From the Ribbon, I select the Data tab then Data Validation. I format the cell as follows.


I repeat the process in cells B14 and B 15 for my second KPI value. Now when a user clicks on one of these cells, they will be prompted to select a value.





On my calculations tab I add in cell J1 through P1 the following…

KPI 1
 KPI 2
 KPI 3
 KPI 4
 KPI 5
 KPI 6
 KPI 7

I want to return the appropriate values whenever a user enters one of the 7 KPIs in either KPI Value 1 or KPI Value 2.

On the Calculations tab in cells C19 and D 19 I add two Column Headers
Selected KPI 1
 Selected KPI 2

Underneath these Headers I want to return the values for each of the selected KPIs. In this example the user on the Dashboard tab has selected the following two KPIs to compare. To return the value selected, I simply call the cell value

Cell C20 =Dashboard!C12
Cell D 20 =Dashboard!C14

Selected KPI 1
 Selected KPI 2
Unit Sold Price
Total Profit $

Now using an IF OR combination formula I can determine which KPI the user has selected.
For Units Sold I would use the following formula in cell J2 to check of the user has selected Units Sold in either data validation cell.

In cell J2 I use 

=IF(OR(Calculations!$C$20 = "Units Sold", Calculations!$D$20 = "Units Sold"),Data!B2,NA())
Column K Units Sold Price formula

In cell K2 I use =IF(OR(Calculations!$C$20 = "Unit Sold Price", Calculations!$D$20 = "Unit Sold Price"),Data!C2,NA())

I then copy this formula down to complete my array ensuring that I change the KPI name and the cell return reference highlighted in red . When done I have dynamic data based on my users selection.

KPI 1
 KPI 2
 KPI 3
 KPI 4
 KPI 5
 KPI 6
 KPI 7
#N/A
 Unit Sold Price
#N/A
#N/A
#N/A
#N/A
 Total Profit $
#N/A
16.89
#N/A
#N/A
#N/A
#N/A
7249.5
#N/A
2.45
#N/A
#N/A
#N/A
#N/A
2332.58
#N/A
1.98
#N/A
#N/A
#N/A
#N/A
2078.56
#N/A
1.15
#N/A
#N/A
#N/A
#N/A
2073.7
#N/A
1.15
#N/A
#N/A
#N/A
#N/A
2051.14
#N/A
1.23
#N/A
#N/A
#N/A
#N/A
1840.41
#N/A
1.37
#N/A
#N/A
#N/A
#N/A
1711.08
#N/A
2.02
#N/A
#N/A
#N/A
#N/A
1539.2
#N/A
0.97
#N/A
#N/A
#N/A
#N/A
1270.5
#N/A
0.7
#N/A
#N/A
#N/A
#N/A
1174.5
#N/A
0.86
#N/A
#N/A
#N/A
#N/A
1126.44
#N/A
1.38
#N/A
#N/A
#N/A
#N/A
1115.06
#N/A
1.89
#N/A
#N/A
#N/A
#N/A
970.2
#N/A
0.87
#N/A
#N/A
#N/A
#N/A
935.2
#N/A
1.25
#N/A
#N/A
#N/A
#N/A
876.68
#N/A
1.32
#N/A
#N/A
#N/A
#N/A
877.5
#N/A
0.7
#N/A
#N/A
#N/A
#N/A
791.52
#N/A
1.25
#N/A
#N/A
#N/A
#N/A
760
#N/A
0.7
#N/A
#N/A
#N/A
#N/A
587.52
#N/A
1.07
#N/A
#N/A
#N/A
#N/A
577.6
#N/A
0.8
#N/A
#N/A
#N/A
#N/A
707.7
#N/A
0.39
#N/A
#N/A
#N/A
#N/A
543.12
#N/A
0.78
#N/A
#N/A
#N/A
#N/A
518
#N/A
1.56
#N/A
#N/A
#N/A
#N/A
330
#N/A
1.11
#N/A
#N/A
#N/A
#N/A
316.25
#N/A
1.17
#N/A
#N/A
#N/A
#N/A
5.4

Now you are looking at a lot of #N/As and that’s okay. When I create my chart, the #N/A will be ignored.
On the Calculations tab I highlight my array of data that is dynamically linked and create a scatter with only markers chart. I delete the legend and then cut the chart and paste it into the Dashboard tab.

A little tweaking with the marker options for each KPI (changing to circles and giving each a different color) and I am almost done.

The last thing I want to do is conditional format the KPI Value 1 and KPI Value 2 text on the Dashboard to match the selected KPI. I select cell B12 and then conditional format based on a formula. Example. =$C$12= "Cost To Make Unit". I then change the font to match the color of the marker on the scatter graph. This way the reader will understand which KPI is being represented on the scatter graph.


Now this is just a basic graph. I have not added target goals and projections. I only wanted to show how to create a basic KPI report. In another blog I will create a complete KPI dashboard with all the bells and whistles.
Posted by
Labels: , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)