🏪Captain Store Reports Excel Dashboard Project
📋 Insert Pivot Tables
Use Pivot Tables to aggregate and analyze data.
🎴Pivot Table 1: Sales by Month
Insert > Pivot Table > From Table/Range
Place on new sheet, name it Sales Month
Rows: Month
Values: Amount (Sum)
Sort by values: descending
🎴Pivot Table 2 : Orders by Category
New Pivot Table
Rows: Category
Values: Quantity, Amount
🎴Pivot Table 3 : Sales by Channel
Rows: Channel
Values: Amount
🎴Pivot Table 4 : Sales by Country
Rows: ship-country
Values: Amount
🎴Pivot Table 5 : Top 10 Customers
Rows: Customer_Name
Values: Amount
Sort Descending by Amount
Filter Top 10
🎴Pivot Table 6 : Sales by Gender
Rows: Gender
Values: Count of Order Id
🎴Pivot Table 7 : Sales by Age Group
Rows: Age Group
Values: Count of Order Id
📈Insert Pivot Charts
🔛Convert Our Pivot tables into visuals for the dashboard.
🗺 Recommended Charts:
📉Line Chart: Sales by Month
🔴Pie Chart: Sales by Channel
📊Bar Chart: Sales by Country
🟢Donut Chart: Orders by Age Group
🔴Pie Chart: Sales by Gender
📊Bar Chart: Sales Status
🔴Pie Chart: Order by Quantity & Category
📘Captain Store Reports Excel Dashboard
🔰Excel Dashboard
🔰Sample Insight From Excel Dashboard
1️⃣ Men are more likely to buy compared to women(~ 54%)
2️⃣ Martinique, Jamaica, Monaco, Bhutan and Sri Lanka are the Top 5 Country
3️⃣ Adult age group(30 ~ 50 Yrs) is max contributing (~ 36%)
4️⃣ Online mode of channel are max contribution.
5️⃣ Snacks and Grocery Category have max sales contribution (~ 21%)
🛤 Final Conclusion to improve Captain Store Sales:
Target Men customers of Age group(30 ~ 50 Yrs) living in Martinique, Jamaica, Monaco, Bhutan and Sri lanka by showing ads/offers/coupons available Online Mode with Snacks and Grocery Item Category.
