Excel data analysis project
Dashboard Creation for B&J Biscuit Business Analysis
Creating a comprehensive business analysis dashboard for B&J Biscuit.
Project Details :
Dashboard that provides insights into various aspects of the business, including revenue distribution, customer demographics, geographic performance, and profitability
Project Overview:
Dashboard that includes the following key metrics and visualizations:
1. Revenue Distribution:
- By product price category
(high-priced vs. low-priced), (Low >=10,High<10)
- By age group and gender.
- By payment method.
2. Profitability Analysis:
- Most profitable brand, location, customer, and salesperson.
- Overall profit margin.
3. Customer Insights:
- Top 5 customers by revenue contribution.
- Total number of customers acquired.
4. Geographic Revenue Distribution:
- Revenue share across key geographic locations.
5. Sales Performance:
- Metrics such as quantity sold, total revenue, total cost of goods sold (COGS), and total profit.
Specific Requirements:
Design Preferences:
-Dashboard to be user-friendly,
-visually appealing,
-interactive, with filters for (location, payment method, and age group)
Goals of the Dashboard:
- Enable quick and informed decision
-making by providing key business insights at a glance.
- Identify profitable segments and customer demographics to optimize marketing and sales strategies.
- Monitor sales performance and track customer acquisition.
This category details the step-by-step approach taken during the project, including Data preparation , Pivot table creation, Dashboard design.
Data Preparation:
1-Data Integration:
Fetched biscuit brand, cost, and unit price columns from the product sheet to the transactions sheet.
2-Derived Columns:
Created additional columns in the transactions sheet using Excel functions:
Age: Calculated from buyer birthdate.
Age Group: Grouped buyers into categories.
Revenue:
Quantity Purchased * Unit Price.COGS (Cost of Goods Sold):
Quantity Purchased * Cost.Profit:
Revenue - COGS.Price Range: Categorized as "High-priced" or "Low-priced" using an IF formula.
Weekday: Identified the day of the week.
Week Day Type: Classified as "Weekday" or "Weekend"
Pivot Table Creation
Created several pivot tables to summarize and analyze data:
KPIs: Summarized key metrics like total quantity purchased, revenue, COGS, and profit.
Top Performers: Identified the highest profit contributors by:
Biscuit brand.
Buyer location.
Customer name.
Sales representative.
Age group.
Revenue Distribution:
By age group (used to create a column chart).
By price range as a percentage of total revenue (used for a column chart).
By payment method (used for a column chart).
By location (used for a bar chart).
By top 5 customers (used for a bar chart).
By gender (used for a card-like visual).
Dashboard Design
Visual Components:
KPI cards displaying total quantity purchased, revenue, COGS, and profit.
Column charts for revenue by age group, price range, and payment method.
Bar charts for revenue by location and top 5 customers.
Card-like visuals for gender and top performers.
Interactivity:
Added slicers for filtering by payment method, location, and age group.