Sales Executive Dashboard Report

Summary

This project was initiated to fulfill a critical business need for the Adventure Works Bike Shop's Sales Department. The primary objective was to design and deliver an end-to-end data analytics solution from scratch, providing the executive team with a comprehensive overview of sales performance.

The solution, a robust Power BI dashboard, was developed by first preparing and cleaning raw CSV data from 2021-2023 using Power Query. A star schema data model was then created to establish clear relationships between key tables such as fSales, dCustomers, dProducts, dCalendar, dTerritories, dProductSubcategories, and dProductCategories. The final dashboard provides a single, intuitive interface for monitoring critical Key Performance Indicators (KPIs) such as total revenue, profit, and profit margin.

The result is a visually appealing and highly interactive tool that not only summarizes historical data but also provides actionable insights. The report successfully identifies top-performing products and sales territories, and highlights key trends that will enable the executive team to make data-driven decisions to drive future growth.

Project Goals & Objectives

The core objectives were:

• Single Big Number KPIs: Provide a high-level overview of Revenue, Quantity Sold, Average Selling Price, Profit, and Profit Margin.

• Insights KPIs: Visualize and track trends, including Weekly Sales Trend, and monthly comparisons for Revenue, Units Sold, and Profit.

• Supporting Details: Present a breakdown of sales data by Category and Country, including revenue, units sold, profits, and profit margin for each.

Data Source and Methodology

The End-to-End Analytics Process

A. Data Sources: The project utilized the following datasets in CSV format from 2015-2017:

• Customers

• Products

• ProductCategories

• Product Subcategories

• Territories

• Calendar Table

• Sales Data

B. Data Preparation & Transformation (ETL):

• Extraction: Data was loaded into Power BI Desktop.

• Transformation: Power Query was used to perform data cleaning and shaping tasks. This included handling missing values, standardizing date formats in the Calendar Table, and merging and appending tables as needed to create a unified view.

• Modeling: A star schema data model was implemented to create a single source of truth for the analysis. Relationships were established between the central fSales table and dimension tables such as dProduct, dCustomer, and dTerritories and more. This normalized structure ensures efficient data querying and accurate calculations.

Dashboard Deep Dive

A Visual Analysis of Key Performance

The final dashboard is designed to provide both a strategic overview and the ability to drill down into specifics.

• Card KPIs: The dashboard prominently displays the following key metrics:

  • Revenue: $24.91M

  • Units Sold: 84.17K

  • Avg. Selling Price: $295.99

  • Profit: $10.46M

  • Profit Margin %: 41.97% These were calculated using DAX measures to ensure accuracy and reusability.

• Weekly Revenue Performance Overview: The line chart effectively visualizes long-term trends, highlighting a clear upward trajectory in sales and identifying periods of growth.

• Units Sold Breakdown by Category: A bar chart displays the distribution of units sold across different product categories, quickly identifying the top performers. The figures are:

  • Bikes: 14K

  • Accessories: 58K

  • Clothing: 12K

• Sales by Country: A table provides a detailed, sortable breakdown of key metrics by country. The top countries by revenue are:

  • United States: $7,938,999.42

  • Australia: $7,416,456.20

  • United Kingdom: $2,902,562.09 The table also highlights that Canada has the highest profit margin at 42.83%.

• Most Ordered Product & Revenue Driver: Dedicated cards highlight the "Water Bottle - 30 oz." as the most ordered product and the "Mountain-200 Black, 46" as a key revenue driver, giving the executive team a clear focus point.

Insights & Recommendations

From Data to Actionable Insights

Based on the analysis of the dashboard, the following key insights and recommendations were derived:

• Insight 1: The "Bikes" product category is the most significant driver of both revenue and units sold, indicating a strong market and customer preference for these core products.

  • Recommendation: Continue to invest in the Bikes product line and explore opportunities for new models or accessories to further capitalize on this market segment.

• Insight 2: The United States, Australia, and the United Kingdom are the top three performing countries.

  • Recommendation: Allocate additional marketing and sales resources to these high-performing regions to maximize their potential. Investigate a targeted approach to replicate this success in other territories.

• Insight 3: Canada has the highest profit margin, despite not being a top-tier country in terms of raw revenue.

  • Recommendation: Conduct further analysis to understand the factors contributing to Canada's high profitability. Examine product mix, pricing strategies, or sales channels in Canada to see if similar approaches can be applied to other countries to improve overall profit margins.

Insight 4: The dashboard identified a strong upward trend in weekly revenue, demonstrating the company's consistent growth over the period.

• Recommendation: Use this trend to inform future financial forecasting and inventory management, ensuring the company is well-prepared to meet growing demand.

Conclusion

The successful delivery of this Power BI dashboard for Adventure Works demonstrates my comprehensive understanding of the data analytics lifecycle, from translating a business problem into technical requirements to providing actionable business recommendations. The dashboard serves as a foundational tool that empowers the executive team to monitor performance and make strategic decisions with confidence. This project showcases my proficiency in data preparation, modeling, visualization, and strategic communication of data-driven insights.

Materials

This project explored sales trends from the year 2021 to 2020 to identify patterns and find business opportunities in today’s market.

The dataset can be found and downloaded for your reference on Kaggle

Tools Used for this Project: Power BI, Power Query, Data Modeling, and DAX