KPI Sales Dashboard with Fuel Gauge Part 2 of 3

Continuing with last week’s post…. Wait you didn't read last week’s post???? Well here it is detailing how to create this super cool Fuel Gauge dashboard.

I want to create the calculations for this dashboard so this post will detail how to create the data that displays on this dashboard.

It starts with just some basic calculations to determine the Top and Low values of predetermined KPI’s. The calculations are displayed below the results. Remember that my raw data is on the Data tab and these calculations will reference this tab.

DATA TAB

CALCULATIONS TAB

% Sales to Target
=VLOOKUP(B2,Data!G:H,2,FALSE)	=MAX(Data!G2:G17)	Top
=VLOOKUP(B3,Data!G:H,2,FALSE)	=MIN(Data!G2:G17)	Low
="Top "& A6 & "/ Low " & A7 & " Sales"
=VLOOKUP(B6,Data!C1:H17,6,FALSE)	=MAX(Data!C2:C17)	Top
=VLOOKUP(B7,Data!C1:H17,6,FALSE)	=MIN(Data!C2:C17)	Low
Top / Low Items Sold
=VLOOKUP(B10,Data!E1:H17,4,FALSE)	=MAX(Data!E2:E17)	Top
=VLOOKUP(B11,Data!E2:H18,4,FALSE)	=MIN(Data!E2:E17)	Low
Total Sales To Target Bar Chart
Target Sales	=SUM(Data!D2:D17)
Total Sales	=SUM(Data!C2:C17)

Now it is important to note that the calculations used are all basic.

=Vlookup

=IF

=Max for Top values

=Min for Low values

 =Sum calculations as well as basic addition, subtraction and division.

There are also a few =Concatenate functions to pull names and values together.

Let’s look at columns A B and C. Here are the calculations…

Here are the calculations for columns E – J.

Not get too wrapped up in all these calculations; at the end of the post I will have a link to the worksheet for your review.

The take away from this (if there is one) is that you should keep your calculations on a separate tab from your data for easy reference. You should also take your time and break down what seems complex into smaller manageable components.

Dynamic Dashboards Using Dynamic Charts Part 3 of 3

To continue with the dashboard in the prior two posts, I want to provide a summary on the dashboard. On my data table I create a summary section based on my entire data set. I could create this directly on my dashboard but I prefer to keep my data on separate tabs.

Since I have multiple records for each item in my dataset, I need to summarize them in this section. I use =SUMIF(G:G,R3,I:I) to get a total for the sale $. Remember that in a previous post

I showed how to use the SUMIF function. If I apply this to my original dataset my results will not be dynamic (change with the checkboxes selected on my dashboard. Instead I point the SUMIF to my dynamic data on my data tab. You didn’t read my post on creating dynamic data? (Click here)

I repeat the SUMIF formula for both the Profits and Units Sold column.

Finally I copy the ITEM name to column V.

Now with this information I can go back to my Dashboard and create a summary area.

The Most, Least and Average Sales in Column K is done with basic formulas. I use a min, max and average formula to populate the three cells.

=MAX(Data!S2:S18)

=MIN(Data!S2:S18)

=AVERAGE(Data!S2:S18)

To pull back the Item name, I use a Vlookup formula (I can use Vlookup because in a previous step I copied the ITEM name to column V in my data tab.

Next I will use conditional formatting to provide my data bars.

To start I use a simple cell reference =Data!R3 to bring back the Item. For sales $ Profits and Units Sold I repeat the process (=Data!S3,  =Data!T3, =Data!U3). I copy that down for each item.

Next I highlight the Sales $ data (cells L10 – L25).

From the home tab, I select Conditional Formatting, Data Bars and select the blue color.

I repeat the process for both the Profits and Units Sold columns.

Now if you didn’t want the numbers to show behind the data bars, you can go into the Conditional Formatting rule (Home tab, Conditional Formatting, Manager rules). Click the edit rules button and then check Show bar only.

When done the dashboard starts to really take shape.

And since we based all the data on the dynamic data setup, when I check or uncheck my checkboxes in the (switch board), my data changes.

Best Dashboard

In a previous post I demonstrated a simple but effective dashboard but did not go into how to create it. 

Sales Dashboard

I will do so in the following post.

As with any dashboard, it all starts with the data you have and what you are trying to convey. A good dashboard will be easy to understand, (clear and concise). It will convey the data in as few steps as needed without losing key points. It will also not obfuscate key points by surrounding them with superfluous information.

For my demonstration, I will create almost the entire dashboard from one key data series (Sales per Month).

Month	Sales
Jan	$  20,947
Feb	$  45,494
Mar	$  35,173
Apr	$  20,249
May	$  47,792
Jun	$  42,511
Jul	$  28,343
Aug	$  28,343
Sep	$  28,000
Oct	$  27,161
Nov	$  17,864
Dec	$  29,987

Now to start my dashboard I first need to set a color theme. I personally don’t like dashboards that use dozens of colors. In my opinion they tend to end up looking like something from a circus. I prefer a sleek look and try to minimize my color pallet. For this example I will use variations on shades of blue.

So now that I have my data, know what my key points are, and color tablet; I can now begin.

I am going to stop for a moment and vent a bit… Quite often managers don’t know how long it actually takes to create a useful dashboard. A good dashboard is so simple to understand that the development side is often overlooked. A general rule of thumb is that the design and planning of the dashboard will take about 70 percent of the time it takes to create it. The other 30 percent is the actual creation. A good dashboard is almost always used over and over so it needs to be easily updated (so be sure to plan ahead).

So we plan what we are going to design and then design what we plan.

Now having said that, don’t allow yourself to be pigeonholed by your initial plan. Some times as you actually create the dashboard, inspiration strikes.  But I digress….

To start I first highlight the entire worksheet and change the background to white. This removes the gridlines. I then enter in my data In cells C3..D15 and then format the data to be more visual appealing. I then tweak the data by merging cells B2, C2, D2 and E2. I enter the Sales in this merged area. I also add the Quarter for each Month in column B. In my dashboard I am using the Calendar quarters so Jan Feb and March are Q1, Apr May and June are Q2, etc…

I use the Boarders tool to outline my cells and fill ever other cell so they are shaded light blue. I bold and shade the Header cells grey and am pleased with the following result. If your data has cents, you may want to remove them to help streamline the look.

.

So far the data looks nice but it does not easily tell a story. I want to quickly know how the data is trending. I don’t want to have to visually compare each row so I want to add trending arrow indicators in column E.

What’s a trend indicator? Well it’s just like it sounds. Some text or graphic to indicated a direction of increase or decrease for a particular metric.

To add these trend indicators, I use a technique I explained in a prior post. Click on the link for instructions on how to accomplish this.

Trend Indicators

Don’t want to click on the link??? The formula you need in cell E5 is:

=D3 & " " & IF(D2<D3,"▲", IF(D2=D3,"*","▼"))

Copy this formula down through E15. Use conditional formatting to change the color. What’s that, you don’t know how to do that? Click on the above link (go on, it won’t hurt)…

Next I want to work the second portion of my dashboard, Sales by Quarter. If you have followed my instructions for the above, then this is just a repeat only with a consolidated result.

Q1 Q2 Q3 and Q4 in cells B 18 – B 21. In D18 I sum the first three months of my above data (cells D4, D5 & D6). For cell D19 (Q2) I sum (D7, D8 and D9), etc…

I then merge Cells B17 and C17 adding the title “Quarter”.  I also merge cells D17 and D18 and add the title “Sales”. I format the same as the above (blue background fill, outlining the cells and bolding / shading my headers grey).

I again use my trend line technique to compare the Quarter results.

Now I have displayed sales by Month but also summarized by Quarter including trend indicators for quick reference on how sales are trending by month and quarter.

Next up is the Summary section of the Dashboard. This is where I place my key points. I group them in one area for easy data access. I also center these key points toward the top. Now perhaps I could apply some heat map logic used for websites to optimize this focal point but I feel they best are represented in this dash board where I placed them(above and to the left of the graphics)

To create the summary I just repeat the formatting of the first two parts of the dashboard and use simple formulas to create the lookups. You will also notice that to create the Summary, I have a row for Sale Target for Year. This is hard coded but could be a lookup to a budget worksheet.

Again all of the formulas use basic Excel functions….

=AVERAGE(Sheet1!D4:D15)

=MAX(Sheet1!D4:D15)

=MIN(Sheet1!D4:D15)

=MAX(Sheet1!D18:D21)

=MIN(Sheet1!D18:D21)

=SUM(D4:D15)

For the lookups I could have used a combination of Indirect and Match as described in the following post…

Vlookup To The Left

However I opted to use helper cells. In the below image I have changed the font color from White to dark red for the helper cells to help you better understand the formulas. Now all of the helper cells are done by a function. For example cell F4 has the formula =C4. This way if I sort my data, the helper cells also sort.

Next on the dashboard, I create the Sales By Quarter bar chart and size it to fit.

Same with the Sales by Month bar chart (with trend line). No great mystery on creating a bar chart. To add the trend line (Excel 2007) from the ribbon select the Layout tab and under Analysis, select Trendline.

I format the Trendline as Polynomial with Order 4 to give it a smooth look.

Now all that’s left to create is the sweet little Sales by Month gauge / summary board in the upper right corner.

WARNING HERE COMES THE CONTROVERSY!!!!

People either love or hate the “Fuel Chart” or “Speedometer Chart”. I will concede that it can be misused and is usually not my first choice in graphs. However, used correctly it can be a powerful tool. 

Besides a billion automobiles can’t be all wrong, if they were, we would all have bar charts on our car displays….

Now I won’t go into detail on how to get the Current, Trend, Goal info. That’s all covered above and done with simple lookups. But the speedometer graph is done by overlaying a donut chart with a pie chart.

To create this graphic, see my next post on creating a speedometer graph.