How do I analyse supermarket data in Excel?
This is a common question raised by salespeople, customer service team leaders and analysts from many departments. There are many approaches, but we can group these into a few similar ‘flavours’ and then look at typical approaches for each of these, before looking at what ‘best practice’ analysis looks like. Let’s start with the basic scenarios:
How do I analyse my supermarket sales performance using Excel?
Most supermarket suppliers want to understand how their products sell in their customers’ stores, so Sales Analysis is the typical starting point for everyone. This allows you to address questions like:
- How are my products selling week-on-week; are my sales growing or declining?
- How are my products selling before, during and after a promotion that I ran with a supermarket chain?
- Which of my products is selling best? Should it be available to shoppers in more stores?
- Which of my products are struggling to sell well – do they risk delisting?
- How do my products sell in different formats and regions?
To perform these types of analyses, you will need to:
- Ensure you have a valid account for the relevant ‘retailer portal’ (online reporting tool)
- Log into the retailer portal and navigate to the relevant report(s), typically:
- Sales by Item & Week
- Sales by Item & Day
- Sales by Item, Store and Day
- Run the relevant report(s) and download the results – opting for “XLS”, “XLSX” or “CSV” as the file format
- Open one of the downloaded files in Microsoft Excel
- Build summary tables, PivotTables, charts etc., which analyse the data to address the questions that you want to answer
If you struggle to find the right report(s) on the retailer portal, you may want to consider more automated data collection options.
How do I get the data in the first place?
Let’s start with the simplest (and smallest download) – analysing item sales by week. Follow steps 1-3 above, look for a report which contains Sales by Item & Week and then download the output to your computer. This report might be called any of the following:
- “Weekly Sales (by Item)”
- “Sales by Item and Week”
- “Item Sales Weekly” etc.
How can I check if I have the right data?
Once you have downloaded a file (in CSV or Excel format), open it in Excel; you will see a large table of data representing the sales of your items across the retailer’s stores. Typically, the first line of this file will contain ‘headers’ for the file, telling you what each column represents, and look something like:
Date (or Week#) | Product Code | Product Description | Sales Value | Sales Volume
You may have more or fewer columns (depending on the report and the retailer portal), but it probably looks like this. The alternative would be something like:
Product Code | Product Description | Date-1 | Date-2 | Date-3… etc.
This is referred to as “time-across” whereas the former example (with a single column for the date or week) is referred to as “time-down”. Retailers tend to offer either time-across or both time-across and time-down options.
In general, time-down offers more flexible analysis and is preferable; the rest of this article assumes that you have data in time-down format – if not, you will want to look into options to ‘unpivot’ the data.
How do I analyse time-down data in Excel?
The most straightforward Excel analysis tool is the PivotTable and it’s perfect for analysing time-down data. There are thousands (millions?!) of tutorials for PivotTables; when writing, searching “Excel pivot table tutorials” on Google generates over 16m results!
Whatever your start point or learning style, you can find a tutorial that works for you, with articles, videos and step-by-step training courses available online for everyone, from absolute beginners to advanced Excel users.
PivotTables can only go so far though; presenting data in tables is fine for small data sets, like monthly sales over a year, but once you want to deal with larger volumes of data, you need to be visualising your data to spot patterns.
Exploring patterns with PivotCharts
Excel provides PivotCharts to do this and, again, there’s a plethora of tutorial content available that will help you to create powerful visualisations. The best data visualisations, created with PivotCharts or other tools, summarise data that would be overwhelming in a table and help you identify and expose important patterns in that data that point to issues or opportunities.
Time-down data is often best presented in a bar chart or line graph – where the x-axis represents time – but which should you use when?
- Bar charts are usually best for displaying discrete, relatively aggregated, data points, for example:
- 13 weekly bars, summarising sales over the past quarter
- 12 monthly bars, showing the count of promotions activated in the past year
- Line graphs are better for displaying continuous, relatively detailed, data points, for example:
- Daily sales over the past year
- Comparing daily availability and service level metrics (as percentages) over the past quarter
These are not formal rules; the key is to ensure that the visualisation is an accurate representation of the underlying data, which itself is a representation of trading performance.
Combining representations in reports and dashboards
To present a more nuanced picture of trading performance, in its broader context, it’s often useful to combine multiple PivotCharts and/or PivotTables into a single report or an interactive dashboard. Effective dashboard design is both an art form and a science, with many books on the subject (and a good topic for a future article here).
Combining representations in reports and dashboards
To present a more nuanced picture of trading performance, in its broader context, it’s often useful to combine multiple PivotCharts and/or PivotTables into a single report or an interactive dashboard. Effective dashboard design is both an art form and a science, with many books on the subject (and a good topic for a future article here).
Where can I learn more about Excel analysis and dashboard design?
Some of the most popular YouTube videos for Excel come from Kevin Stratvert – a former Microsoft Product Manager who posts on Excel and other Microsoft products. His YouTube channel and website are worth exploring if you want to learn how to use Excel as an analysis tool. If you’re already confident in Excel and know your way around PivotTables and PivotCharts you may enjoy this tutorial for building interactive dashboards.One of the best starting points for dashboard design remains the seminal work of Stephen Few who maintained a thought-leading blog at PerceptualEdge from 2003 to 2017 which remains an excellent resource today, along with his many books on the subject.
Want to learn more? These articles can help:
