EXCEL | Supermart online orders

Supermart Photo

About the project

Supermart is a grocery chain that focuses on its e-commerce website and collects its sales data through online orders. The goal of the project is to identify Supermart sales trends over 4 years and suggest new sales strategies to improve the outcomes.

Questions for the analysis

  1. What is the sales trend in 4 years (2015-2018)?

  2. Which category has the highest sales? Which category has the highest profits?

  3. What are the average profit margins?

  4. What region has the highest sales?

  5. What steps should the marketing department take in the future to increase sales?

Check out dashboard

Key takeaway from the dataset

  1. From 2015-2018, 2017 and 2018 sales exceeded the average sales of those 4 years. Sales in 2018 increased by 30.5% relative to 2017. Supermart's sales dropped in the first quarter of the year (especially January and February) and peaked in the last quarter of the year. This could be due to holiday shopping demands.

  2. Eggs, Meat, and Fish has the highest sales among all categories. However, Snacks bring the highest profit among all. In subcategories, health drinks and soft drinks have the highest sales and profits.

  3. Average profit margin is around 25%.

  4. West region has the highest sales while the North has the least sales. West's sales are 3,826% higher than North region sales.

Next steps

  1. Supermart's sales have increased significantly in 2017 and 2018. The company can further expand sales by focusing on the north region's marketing strategies. It is recommended that companies investigate reasons why North performs poorly and provide solutions accordingly.

  2. Company can also avoid stocking too much inventory in the first quarter and prepare for higher demand for the end of the year (September, November, and December).

  3. Even though Health drinks and Soft drinks bring most sales and profits, animal products like Eggs, Meat and Fish still bring the highest totals sales and Snacks has highest profits. Company should still focus on those two categories while stocking the inventories. For further analysis, number of units sold should be analyzed to gather additional information.

Tools used

  • Excel basic functions (Calculation, YEAR, MONTH, TEXT) to extract data for analysis preparation

  • Pivot Tables and Pivot Graphs to prepare reports and create breakdowns

  • Pivot Graphs for single graphs

  • Slicers with Macros to create an interactive dashboard

  • Tableau bar charts (with reference line), tables, and aesthetic color functions to highlight the highest intensity of data

** I created 2 dashboards using both Tableau and Excel slicers for different visualization approaches.

Check out my detail analysis here:

Download my Excel Dashboard:

Interact with my dashboard here:

Contact me