B&J-Biscuits-Sales-Dashboard 🍪

📌 About This Project

Overview

This project analyzes the sales and profit performance of B&J Biscuits, a U.S.-based FMCG company.
Using Excel as a full analytics environment, I transformed raw transactional data into three interactive dashboards that reveal:

• Who buys our products → Customer demographics & age segmentation
• Where revenue and profit come from → Location, brand, and sales rep performance
• How our business performs over time → Monthly, quarterly, and seasonal trends

The dashboards are powered by VBA macros that allow users to:

• Toggle between absolute values and percentages
• Quickly clear filters using a slicer-reset macro
• Interactively analyze sales by quarter, brand, and location

---

📊 Dashboard 1: Sales Overview

🎯 What is selling, where, and to whom?

KPIs:

• Units Sold: 3.1M
• Total Revenue: $61.6M
• Average Order Value: $5.1K
• Customer Count: 12K

Key Visuals & Insights:

• Revenue by Product Category: Shortbread leads with $14M (29.3%)
• Revenue by Location: San Antonio highest $7.3M, Chicago lowest $5.7M
• Revenue by Age Group: 30–44 and 60–74 are top buyers (~24% each)
• Revenue by Gender & Payment Method: Even split across payment types; male buyers dominate revenue

Key Takeaway:
Revenue is geographically concentrated in 2–3 cities and driven by loyal, older customers.

---

📊 Dashboard 2: Profit Analysis

🎯 Where are we making the most money?

KPIs:

• Total COGS: $34.8M
• Total Profit: $26.8M
• Profit Margin: 43.5%
• Most Profitable Brand: Shortbread

Key Visuals & Insights:

• Monthly Revenue Trend:
  • Peak in Jan & July: $7.9M
  • Sudden drop Aug–Dec: ~$3.2M/month
• Profit by Brand & Top Customer Leaderboard: Revenue highly concentrated in top 5 customers
• Weekday vs Weekend Split: 72.6% weekdays, confirming weekday-heavy purchases

Key Takeaway:
Q4 underperformance is critical. Revenue collapsed after July due to either:

• Seasonal behavior or market saturation
• Lack of Q4 campaigns
• Potential missing or incomplete transaction data

---

📊 Dashboard 3: Revenue Dynamics

🎯 How efficiently are we selling?

KPIs:

• Profit per Transaction: $2.2K
• Revenue per Sales Rep: $7.7M
• Top Sales Rep: Travis Doyle (17.6% of revenue)

Key Visuals & Insights:

• Sales Rep Leaderboard: Travis Doyle dominates; Elizabeth Guerrero lowest at 2.7%
• Price Range Contribution: 90% revenue from expensive products
• Quarterly Revenue Split: Q1: 35.9% | Q4: 10.3%
• Brand Preference by Age (Heatmap):
  • Vanilla Wafers & Shortbread dominate
  • Chocolate Delight consistently underperforms

Key Takeaway:
Revenue is heavily reliant on premium products and one top-performing sales rep.
If Travis Doyle exits or premium demand drops, revenue risk is high.

---

📌 SDD: Summary, Decisions, and Recommendations

Summary

• Sales are strong in Q1 & Q2, but collapse in Q4 threatens annual stability
• Revenue is concentrated in premium products and top cities
• Customer base skews older (30+) with weekday-heavy purchases

Decisions to Consider

• Launch Q4 promotional campaigns to stabilize seasonal sales
• Train or incentivize underperforming reps
• Expand into new cities to reduce geographic concentration
• Introduce mid-priced products to capture younger buyers

Recommendations

• Investigate Q4 revenue drop → Check campaign history & distribution data
• Rebrand or discontinue Chocolate Delight
• Implement performance incentives to reduce reliance on Travis Doyle

❓ Key Questions for the Business Owner

1. Were Q4 holiday campaigns or discounts launched last year?
2. Are there distribution gaps in low-performing cities like Chicago?
3. Why is Chocolate Delight consistently underperforming?
4. Should we target younger demographics with smaller or cheaper packs?
5. What is our contingency plan if Travis Doyle exits or premium demand slows?

---

📚 What I Learned

• Data storytelling is powerful - executives care about why, not just what
• Macro automation (toggles & slicer resets) dramatically improves UX
• Identifying concentration risks is key to actionable recommendations
• Excel + Power Query + VBA can rival dedicated BI tools for decision-making

---

🛠 Skills Used

Technical Skills:

• Microsoft Excel (Advanced)
• Power Query & Power Pivot
• Pivot Tables & Charts
• VBA Macros & Dashboard Automation
• Data Cleaning & Transformation
• Interactive Dashboard Design

Soft Skills:

• Analytical Thinking
• Business Intelligence Storytelling
• Problem-Solving & Critical Thinking
• Attention to Detail

---

📌 Simulated Business Impact

• ⏱ Save 25+ hours/month through automation
• 📊 Identify seasonal risks & concentration issues
• 📦 Enable data-driven sales & product strategies
• 💼 Provide actionable insights to reduce revenue volatility

---

✅ This project demonstrates how Excel dashboards, when combined with Power Query, Pivot Tables, and VBA, can deliver executive-ready insights and support data-driven decision-making.