Using pivots and pivot charts for interactive dashboards

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!

Thank you Dflak,

See attached sample data for the dashboard, and my initial thoughts of what it should look like. (Obviously the pictures of the graphs are purely for illustrative purposes, i just wanted to show what the overall page should look like).

Few points to note and to highlight the requirements I'm building towards:

Requirements:

1. I want to build a dashboard for the key metrics outlined in the spreadsheet attached (desired dashboard tab - traffic/cheque, dt times, operating margin, etc)
2. The dashboard must have the ability to summarize and get granular - i..e, if the vice president want to see how things are performing across his region he should see an average for each of his reports, if he drills into those reports he should an average across all of their reports, and so on until he can drill down to a single restaurant level
3. The dashboard should be analytical powerful yet user friendly and subtle - i think the real value will come from excellent presentation of the data, ease of navigation to slice by different regions and views such as store types, and the ease of highlighting outliers and trends in the data - i.e., poor performers, good performers, good/poor trends and perf vs same time last year.
4. The dashboard should have the ability to scale up - this is a small sample of data, for ~400 restaurants, the final file would have ~ 8000 restaurants

Notes:

1. I've prepared overviews for single metrics from the above, i.e., profitability by store, using pivot tables and a pivot line chart. This gave a good view of point in time performance by owner group, but what it was missing was the dynamism to span across a number of metrics and on a much larger scale, and it also didn't really deliver any insights, i..e, profitability might be low, but hard to understand why - hence including these metrics i thought would give a good opportunity to highlight root causes in a graph and simplify the process for the vice president right down to a manager responsible for a number of restaurants.
2. Of the available, i don't have historic data currently, but if you want to mock up a sample of illustration purposes I can then plug some real data in if it works. Obviously I'v scrubbed this data, so ignore if any of it looks funny to you. of the data I have available, the frequency at which it updates varies per below;
- Annual sales range = Annually
- Food/paper/labour = Monthly
- Operating Margin = Monthly
- Avg cheque and transactions = weekly and last year (2017) comparison
- Drive thru times = weekly
- Guest feedback = weekly
- Training completion = weekly
I'm a little unsure of how to best build up trend analysis for this. Maintain weekly data and after a few weeks i;ll have a trend and continue to do this and after a few months I'll have quarterly trends and so on? Open to your thoughts on this.


That's what I'm thinking in a nutshell, would love to hear your thoughts!