I’ve been doing the CFO thing for roughly 16 years for small to mid-sized businesses. Along the journey, I’ve seen some fascinating sales reports over the years.
Some are so-so. Some are just really, really bad. And others? Pretty okay.
Mine? I’m not a designer. But let’s take a peek at what I did for a recent client. We’ll call this a simple case study on creating meaning with limited data. So let’s get started.
We’re Keeping It Super Simple
Retailers, grocers, and food service providers report sales, gross profit, customer counts, and average transaction size in their daily flash reports.
For this discussion, I want to keep it super simple–just sales, that’s all.
Also, I’m doing my work in Tableau as I can build visuals must faster than I can with Excel.
Finally, we’re just working with one month of sales data. I know, a bit boring. Again, the focus is on presenting reporting ideas with limited data. You’ll be able to apply the concepts with a full year, or multiple years of data.
Since my favorite restaurant is Emmet’s Kitchen & Tap located in beautiful downtown Fayette, Missouri, let’s pretend we’re helping the owner to gain insights with a recent sales month.
1. Pulling in the Data
First, I extracted the sales data for July 2016. Needless to say, this was the easy part
The very first thing I do is add totals to the table. Meh, we can do that in a spreadsheet.
2. Adding Visuals
Now we need to do something with the data. The data table does not allow us to see how we did by day of the week. So that’s the first visual I created.
Also, notice I’ve created a filter which removes Week 27. We’re assuming Emmet’s does not operate on a 4-4-5 week calendar. Week 27 only had three days, so I’m excluding. If not, several days will be skewed higher than the others.
And then Tableau makes it easy to create simple dashboards. Since the visual above excludes the sales total for the month, a simple dashboard can be included to show the monthly visual like the one below.
Sales by Week
We’ve shown sales by day, and we clearly see the weekends are our best sales days. No surprise. But let’s turn our attention to sales by week. Again, I filtered out the stub week at the beginning of the month.
Not a lot of insight, yet. I’ve added gradients emphasizing the strongest from the weakest weeks. I think we’ll find later that Week 28 dropped as it was the week right after the holiday which I’m calling the holiday hangover.
Sales by Day
So far, we have not looked at sales by day. So I’ve added it below.
Typically, we don’t use bars for run charts. But I wanted to show the second dimension for my date area. I have the actual date. And since the date didn’t mean much to me, I added weekday. And that second dimension for time forced me to use bars.
Frankly, this is meaningless. We’re comparing apples and oranges. Spotting trends are really impossible.
Oh, so you’re blaming it on the bars? Well, here’s the line chart (without the weekdays).
I even added a reference line above which is the average sales per day for July. Again, meaningless. The average is including the big days and the slower-moving days.
So we’re going to group our sales per day by day of the week. Is this better?
I’ve also added reference lines showing exceptional behavior in the data. No, it’s not a XmR or SPC chart–overkill with this limited data.
Notice the very first data point appears to be an anomaly for a Monday. It’s not as it was a holiday.
I’ve added some other markers for the owner to investigate further.
The Calendar Presentation
The chart above helps a lot. But it can still be a shade hard to follow.
When it comes to visuals of any kind, I don’t want to have to think very long. I want to able to grab the big picture immediately, and then start acting on what the data is telling me.
So I thought I’d attempt showing sales on a calendar. Below is my first attempt.
Well, it’s a start. The sales data is shaded because I’ve clicked on one date of the calendar which triggers a tooltip, and that greys out the other cells.
The calendar still doesn’t pop. We want insights, and this view is making us work way too hard.
So the last thing I did was to create a calendar heat map. Now, I get it. I can clearly see my best days on the weekends along with the weekdays.
Now we can start asking questions on whether drops in certain days of the week were out of our control or not. If not, then something with our marketing needs to be addressed. For example, what happened on Sunday the 17th?
What about weekdays? The smallest part of our marketing dollars (probably 20%) yield the greatest impact on sales. We just don’t always know where. But I can bet that 20% is not helping on weekdays.
If so, we can be determining what controllable costs can be shaved during the week.
We’re merely scratching the surface, and that’s probably an exaggeration.
Hopefully, this gives you some ideas on what you can do with some of your daily, weekly, and monthly sales data when creating flash reports. Obviously, you’ll have the history where you can make comparisons to previous periods. In this exercise, I wanted to keep it simple.
When creating any flash reporting, remember this critical maxim–the goal of any data analysis is to gain meaningful insights that can be turned into actions.
In the meantime, happy flash reporting.
Photo Credit: Robert Bye