I used a dataset of 2020 sales data to generate pivot tables and data visualizations. Then, I combined the charts into a summary dashboard to answer stakeholder questions.
- Microsoft Excel
- Data Analysis
- Pivot Tables
- Data Visualizations
- Dashboards
The questions asked by stakeholders included:
- What were the total monthly sales in 2020?
- How did each salesperson perform during 2020?
- How many items of each product were sold in 2020?
- What percentage of total items sold in 2020 were from each product category?
- What percentage of total items sold in 2020 were from each city?
I created a table from the sales data. I then created separate pages with pivot tables to answer each stakeholder question. I used the pivot tables to generate an appropriate data visualization. For example, percentages were best visualized with a pie chart and time series data was best visualized with a line chart. Finally, I combined the visualizations into a summary dashboard for stakeholders.
Further questions that could be answered include:
- Which product generated the most sales in dollars?
- How do grocery sales fluctuate in response to weather and holidays?
- How could a sales forecasting model help assist with staffing and preparing our inventory levels?
The project could be improved by adding more interactivity to the dashboard, such as the drill-through behavior and slicers in PowerBI. The dashboard could also be modified to update in real time to new sales data. A dashboard that incorporates live data along with historical data in forecasting models could quickly adjust to changing consumer environments. In 2020, the Covid-19 pandemic began and the retail environment changed as demand for certain items surged and people switched to contactless delivery. Dashboards could quickly alert store leadership to emerging consumer trends and help them adjust market strategy accordingly.