Supermarket Sales Data Analysis

GOAL

Analyse the supermarket sales data, transform the raw data set and build a dynamic dasboard in Excel to enable visualization, tracking and monitoring of the key KPI metrics

METHODOLOGY

1. I first imported the data into Excel and then carried out data cleaning
2. I created 3 tabs in the workbook - raw data, chart(pivot table), and dashboard
3. In the chart tab, I carried out data analysis using pivot tables, power pivot, and conditional formating which informed the visuals I would use for my dashboard. The visuals have to be representative of the metric being measured.
4. Then I created the dashboard by combining the various visuals and connected them with slicers which made the dashboard interactive. The slicers enable one to slice and dice the data by gender, customer type and date

I went further to do some simple coding by inserting Excel VBA code to get the exit, about and update data tab. The return to dashboard tab was done using a hyperlink

For the coding go to developer, then VBA/ view code. After the macro code is written and saved, it can then be attached to certain triggers in the Excel model. The macro can be activated at the push of a specific button on the worksheet, or when certain cells are modified, for example. The easiest way to implement a macro is to attach it to a button like I did with the exit, about, update data tabs.

KEY INSIGHTS

1. Most sales were made by 7 pm
2. Goods were paid for mostly by cash followed closely by e-wallet
3. Food and beverages brought in the most sales and Branch C made the most sales by a wide margin.

These insights have influence on marketing campaigns and the company can tailor this to the gaps identified.