USA_Beverage_Sales_Analysis

Beverage sales analysis project for the U.S.A market, built with Excel & Power BI. Features data transformation, DAX measures, KPI dashboards, maps, top/bottom states insights, and advanced profitability metrics. Designed to visualize performance trends, enhance decision-making, & demonstrate analytical storytelling through interactive dashboards.

📌 Overview

This project presents an end-to-end Business Intelligence (BI) analysis of beverage sales across the United States, developed using Excel and Power BI. It demonstrates the full data workflow — from cleaning and transformation to visualization and insight generation — turning raw data into actionable intelligence. The analysis focuses on key business metrics such as total sales, operating profit, cost ratio, and efficiency, along with regional and retailer-level insights.

All data used in this project is open-source and intended solely for learning and training purposes.

🎯 Project Objectives

The main goal of this project is to demonstrate the application of Business Intelligence (BI) techniques to transform raw beverage sales data into clear, data-driven insights that support decision-making.

Specific objectives include:

• Data Cleaning & Transformation – Preparing raw data in Excel using formulas, calculated columns, and structured relationships.

• Analytical Modeling – Building dynamic Pivot Tables and DAX Measures to calculate KPIs like profit efficiency, cost ratio, and operating margin.

• Interactive Visualization – Designing insightful dashboards in Power BI featuring maps, time trends, and top/bottom performance analysis.

• Performance Insights – Identifying the most profitable regions, retailers, and beverage brands across the U.S. market.

• BI Skill Demonstration – Showcasing practical skills in data modeling, visualization, and storytelling for real-world business intelligence scenarios.

📊 Dataset Description

The dataset used in this project is open-source, designed for educational and business intelligence training purposes. It initially contained 12 columns and over 3,800 rows, representing U.S. beverage sales across multiple states, retailers, and product categories. After data cleaning and transformation, the dataset expanded to 23 well-structured columns, including calculated fields for profitability and performance metrics.

🧩 Source & Scope

• Open-source / fictional dataset prepared for learning and analytical modeling.

• Represents U.S. soft drink retail transactions (target region: United States).

• Initial raw dataset: 12 columns

• Final dataset after processing: 23 columns

🧹 Cleaning & Quality Process

• Removed duplicates and empty rows.

• Normalized numeric and currency formats.

• Converted percentage and text fields into numeric types.

Created derived columns for:

• Total Sales

• Total Cost

• Operating Profit

• Profit Margin

• Efficiency Ratios

• Verified consistency and data integrity across all columns.

📝 Note

• To maintain privacy and encourage creative flexibility, a new logo, brand name, and product line were designed specifically for this project — avoiding the use of any real commercial identities.

✅ Result

A clean, analysis-ready dataset optimized for Excel PivotTables and Power BI dashboards, enabling accurate reporting and interactive insights.

🛠️ Tools & Technologies

• Microsoft Excel → for initial data exploration, cleaning, and pivot-based summaries

• Microsoft Power BI → for advanced dashboards, data modeling, and interactive reports

• DAX Language → for creating KPIs, ratios, and dynamic performance measures

⚔️ Challenges & Solutions

• Challenge: Large, unstructured dataset with inconsistent data formats. Solution: Applied Excel cleaning tools and Power Query transformations to standardize and enrich the dataset.

• Challenge: Presenting both summary and deep-dive insights. Solution: Developed a multi-page dashboard combining maps, Top/Bottom 10 charts, KPI cards, and interactive filters.

💡 Key Insights

The analysis revealed strong performance patterns and profitability indicators across the U.S. beverage market, supported by advanced Business Intelligence techniques and DAX analytics.

Overall Performance

• Total Sales: $8,684,027.50 generated from 17,148,250 units sold.

• Total Cost: $5,510,395.63, resulting in an Operating Profit of $3,173,631.88.

• Average Operating Margin: 36.31%, with a Profit Efficiency of 59.59%.

• Overall, U.S. sales reached $8.68M, reflecting a strong and balanced market performance.

• Top-Performing Brand — Purevia: Led the portfolio with $655.7K profit and the highest profit efficiency (68.23%).

• Demonstrated optimal pricing and cost control strategies.

• Most Profitable Retailer — Sodapop with total profit ($1.65M).

Seasonal Sales Trend

• Sales and profit increased sharply from May to August, peaking in July ($384.7K profit) — consistent with summer beverage demand.

• The lowest profits occurred between January–March, averaging $177K, suggesting off-season challenges.

Regional Insights

• Top 5 States: Florida, New York, California, Texas, and Georgia contributed ~29% of total U.S. profit.

• Bottom 5 States: West Virginia, Minnesota, Nebraska, North Dakota, and Iowa generated less than 3% combined, highlighting areas for market optimization.

• Top-performing regions consistently maintained higher margins, while low performers indicate potential for supply and pricing adjustments.

Performance Distribution

• High Performers: 39.5% of total sales records.

• Low Performers: 60.5%, reflecting uneven profitability across states and product categories.

Brand-Level Observations

• Sparkivola (flagship brand) achieved strong profitability ($793K) despite lower volume.

• SlimSpark and Zestila showed moderate efficiency (~54–55%), suggesting room for improvement in cost and margin structures.

Retailer Insights

• Smaller retailers like DreamCo had limited sales and lower efficiency.

• Major distributors (Sodapop & FizzySip) together drove over 70% of total unit sales.

Strategic Takeaway

Seasonal peaks, regional demand variations, and brand-level disparities indicate significant opportunities for targeted marketing, optimized distribution, and improved product portfolio management through data-driven Business Intelligence practices.

📋 Tables Preview

📈 Statistical Preview

🖥️ Dashboard Preview

All State Sales Chart In Excel:

-Note: You can review the chart in more detail and with greater clarity by referring to the attached Excel file.

Microsoft Power BI Dashboard:

🧭 Overview:

📊 Sales Analysis:

🗺️ Sales Distribution Map:

🏆 Top 10 States:

⚠️ Lowest 10 States

💰 Sales & Profit Breakdown

📊 Next Steps / Recommendations

Based on the insights above, the following strategic actions are recommended to enhance profitability, efficiency, and data-driven decision-making across the beverage portfolio:

1. Optimize Seasonal Performance

• Increase stock and marketing efforts during May–August, the peak demand period.

• Develop summer-focused promotions and dynamic pricing models to capture higher margins.

2. Improve Low-Performing Regions

• Conduct regional market analysis for West Virginia, Minnesota, Nebraska, North Dakota, and Iowa to identify distribution and demand gaps.

• Reallocate resources toward high-potential urban centers and optimize logistics to reduce transportation costs.

3. Strengthen Retail Partnerships

• Deepen collaboration with top retailers (Sodapop, FizzySip) to expand product placement and joint campaigns.

• Provide incentive-based programs for smaller retailers to boost sales participation and efficiency.

4. Enhance Brand Profitability

• Continue cost control and premium positioning for Purevia and Sparkivola.

• Review pricing and supply chain strategies for SlimSpark and Zestila to improve their margins by at least 5–7%.

5. Leverage Business Intelligence Tools

• Integrate real-time dashboards in Power BI for tracking sales, cost, and profit KPIs by region, brand, and retailer.

• Use forecasting and what-if analysis to simulate pricing or cost scenarios and optimize future business strategies.

6. Data & Reporting Improvements

• Maintain dataset automation pipelines to ensure clean, analysis-ready data.

• Incorporate predictive analytics and clustering models to identify emerging demand trends and customer segments.

📈 Summary Conclusion

• This project demonstrates a complete end-to-end business intelligence workflow — from raw data preparation and transformation to insightful visualization and strategic interpretation. Through detailed sales, cost, and profit analysis across multiple brands, retailers, and U.S. states, the study identified clear performance patterns, seasonal demand peaks, and regional optimization opportunities.

• The results highlight the power of Excel, DAX, and Power BI in uncovering actionable insights that drive profitability and efficiency. With continuous data refinement and advanced analytics, this project serves as a scalable foundation for future market forecasting, performance benchmarking, and data-driven business decisions.

🗂 Files in This Repository

Original data.xlsx → The Excel file containing data before cleaning contains

USA Sales Analysis.xlsx → The Excel file containing data, Tables & dashboard

BI_Ready_Dataset.xlsx → Curated Excel Dataset for Microsoft Power BI Analysis

USA Sales Analysis BI.pbix → Power BI File Project

🔗 Interactive Dashboard

You can explore the fully interactive sales dashboard online without needing Power BI Desktop. Click the link below to view the report:

http://bit.ly/3Jr2kzQ

Note: The dashboard works best on desktop browsers. Mobile browsers may have limited functionality.