I hope you are doing well. We need to improve our internet sales reports and want to move from static reports to visual dashboards. Essentially, we want to focus on how much we have sold of what products, to which clients and how it has been over time. Seeing as each salesperson works on different customers it would be beneficial to be able to filter them also. We measure our numbers against budget so l added that in a spreadsheet so we can compare our values against performance. The budget is for 2021 and we usually look 2 years back in time when we do analysis of sales.
- Necessary Systems: PowerBI, CRM system
Provide a dashboard in PowerBI, including 2 pages:
- Page 1: Sales Overview, including:
- KPI chart
- Filters by Year and Month (Slicer)
- Filters by Customer City, Sub-category, Category, Product Name (Slicer)
- Sales by Product Category (Donut chart)
- Sales and Budget by month (Line chart)
- Sales by top 10 Customers, top 10 Products (Bar chart)
- Page 2: Customer Details, including:
- Filters by Year and Month (Slicer)
- Filters by Customer City, Sub-category, Category, Product Name (Slicer)
- Sales and Budget total sales (Card)
- Sales and Budget by month (Line chart)
- Sales by top 10 Customers (Bar chart)
- Sales per each customeres by month (Table)
Suggest to add a map showing Sales by Customer City so that stakeholders have better understanding of sales by region
(Data range from Jan 2019 - Jan 2021)
- Mountain Bikes brought back highest sales overall.
- Mountain Bikes sales was on top 1-6. Each product was sold more than $1M. Bikes products in general comprised 95% of total sales.
- Overtime, trend was switched from Road Bikes to Mountain Bikes.
- Road Bikes sales always appeared on top 1-5 (Jan19- May19), before they were gradually replaced by Mountain Bikes (Jun19 - Feb20), then completedly surpassed by Mountain Bikes in Mar20. From Jan21, Accessories products were on top (data cut-off to end of 2021).
- Sales peak time was from Oct - Dec
- Average sale amount in Q4 2019 was ~24% more than it in Q1,2,3. Gap in 2020 was ~45%.
=> Suggest:
- Enhance selling Mountain Bikes products to increase sales amount.
- Conduct further analysis to see if Accessories will be trending in replace of Bikes or they're seasonal products.
- Create campaign to push sales from Oct - Dec.
- Jordan Turner was top 1 customer overall.
- Jordan Turner's sales was $15.999, exceeded $3k compared to no.2.
- Overtime, top 5 customers fluctuated monthly
- Each month, top 5 customers were replaced by different names.
=> Suggest:
- Enhance customer service for top 10 customers like Jordan.
- Conduct further analysis on customer churn rate and retention rate.
- Gap between Sales and Budget were quite close in 2020, except for significant differences in June and Q4 2020.
- Difference between Sales and Budget was usually around $50k, except for ~$500k in June, and >$100kin Q4 2020.
=> Suggest:
- Take futher investigation on sales in June and November to see if there was any cause to significant increase.