Jan 282013
 

The image below is an Excel Dashboard section generated from 847 rows and 11 columns of data related to classroom and book sales. In the range D1:N5 we summarize Monthly Sales Totals by Sales Rep. So much easier to look at this instead of scrolling around in the source data.

Let’s quickly look at 4 points (click the image for a larger view in a new window):

Excel Dashboard Snapshot

Point A: Crossfooting & Data Quality

  • The total of all the 847 transactions: $645,699.33
  • Total attributed to the Sales Reps: $602,430.13
  • Why do we have a difference of $43,269.20?
    • Our formulas might be wrong.
    • Some transactions might not have Reps assigned to them.
    • Maybe there’s a good reason for this mismatch and it needs to be made explicit.
  • A good dashboard includes “crossfooting” so that we can trust what we see. Before we do anything else, we need to reconcile the numbers or have an explanation for the difference.

Point B: Easy to See

  • A decent stacked bar graph that shows JUL, AUG, SEP and clearly Larissa and Opal are kicking butt!
  • Because the graph is stacked, we can see that Opal had an ok July compared to the rest of the Sales team, and she came on strong in August and September.

Point C: OMG!

  • Too busy! August was the best of the 3 months. Clear. But the stacking has too many levels and too many colors. It tells a story but it’s too ugly and too much much work to dig it out.

Point D: Fine Line Between Love & Hate

  • Looks cool but it takes just a few of these 3-D graphics to slow down a spreadsheet’s performance. Be careful.

 

 

Dashboards and reports are fantastic tools for summaries, analysis and decision-making. We just have to be very clear about what we need to see and how useful the result is.

Share Button

Oz du Soleil

I am Oz du Soleil. Data Mercenary and Excel Trainer. My courses have been described as informative, fun and they get people to relax about using Excel. I'm based in Chicago, a veteran of the U.S. Navy, and have a passion for custom-made hats, good bourbon, and spicy food.
0 comments
Follow

Get every new post delivered to your Inbox

Join other followers: