Dashboard insights and presentation

[jonnybegood]

I'm struggling to do a few things with regards to developing actionable insights and presenting in an easy to use dashboard for a wide user group.

The situation is as follows;

I want to Create a hierarchy dashboard across a number of kpis. I.,e start at the president's view of the average across a region, with the ability to drill into different parts of the region, or by type etc.
The dash board would be for the performance of a a number of restaurants (~1000) across a number of metrics:

Characteristics of each restaurant that I want to capture:
- Restaurant type (i.e., Standalone, Mall, etc)
- Annual sales volume (i.e., group by sales ranges)

KPI"s that I want to measure:
- Operating margin %
- Food, Paper, Labor %
- Drive Thru Times
- Customer feedback incidents per 100 transactions
- Training completion %

I have all of this data available, my question is twofold:

1. What insights can I gather from the above that will lead to action. I.e., If I have 500 normal restaurants, with an average operating margin of 10% but a range of 8 - 15%, how can i present the data in a dashboard format that will allow the president for example to see the whole view and easily understand where the outliers and problem areas lie. And similarly, if a problem here is identified, how can I easily pair up what might be driving this from the data available. I.e., a scan of the other metrics to assess for abnormalities

2. What is the best way to present the data for A) visual ease, and B) user friendliness (i.e., all users can easily navigate to helpful information for their own use)

Many thanks!

 

[frank_AL]

jonny,

Here's a few thoughts. The last 20 years of my 40 year professional career I was involved in massaging large amounts of data and creating Hierarchical data just as you are discussing. It is important to structure the reports to an appropriate level of detail for the person reading the report (e.g., President, Regional Manager, Store Manager, etc). I found that Pivot Tables and Pivot Charts can be used to tell a very effective story. These tools also allow for easy navigation to then drill down into the details assuming the people reading the report are willing to view the report in Excel. That may not be true for say the President who may want only a printed report.

For example at the President's level you might have:

[TABLE="width: 169"]
<tbody>[TR]
[TD="width: 169"][TABLE="width: 532"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2"]Restaurant Type - Standalone[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Region[/TD]
[TD]Northeast[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total Restaurants[/TD]
[TD]56[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sales Volume[/TD]
[TD]Average[/TD]
[TD]Stores <10% Below Average[/TD]
[TD]Stores > 10% Above Average[/TD]
[/TR]
[TR]
[TD]Operating Margin %[/TD]
[TD]10% [/TD]
[TD]6[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]
Food, Paper, Labor %[/TD]
[TD]
56%[/TD]
[TD]
7[/TD]
[TD]
8[/TD]
[/TR]
[TR]
[TD]Drive Thru Times[/TD]
[TD]45 Seconds[/TD]
[TD]4[/TD]
[TD]
6

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
With type of report being a Pivot Table individuals reading the report can then drill down to the details as desired to see the underlying facts driving the results.

Another approach is to create a weighted scoring system that would allow a comparison of stores based upon an overall value.
To do this you take all measurements and apply a number that represents the relative importance of a metric.

For example:
Sales Volume - 20%
Operating Margin - 50%
Food, Paper, Labor - 10%
Drive Thru Times - 10%
Training Completion % - 10%

Within your spreadsheet you would assign values based upon an individual result compared to the overall average.

Just a few ideas, hope this helps.

 

[jonnybegood]

Thanks Frank.

Couple of quick thoughts on your reply.

Ideally I’d like to do two separate things which ultimately will be linked.

1./ display the data visually with the ability to drill up or down as needed. I assume the best way to do this will be with multiple pivot tables all linking up to a hierarchy, I.e., the ability to see a regional average and then drill down into specifics if needed.

2./ this can come later, but I’d like to ability to do something along the lines of the second point you made, I.e., develop actionable insights. This may simply be another way of displaying the “bottom” performers or something like that, but this can be iterative and building on point one.


Any thoughts as to point 1 and if it’s even feasible?

Thanks!