Sales Dashboard in Excel Using Power Query and Power Pivot

Overview

This enhanced Excel-based dashboard provides a comprehensive overview of sales performance, utilizing Power Query and Power Pivot for data integration and analysis.

Data Source:

Sales Data in form of an excel sheet.

Data Preparation and Integration:

Power Query:

Step 1: Imported the 'Sales Data set' into Power Query.

Step 2: Cleaned and transformed the data as needed (e.g., handling missing values, data types, formatting).

Step 3: Established relationships between different tables using Power Pivot.

Key Metrics and Analysis:

1. Total Sales: Overall sales revenue for the year.
2. Total Profit: Net profit generated from sales.
3. Total Orders: Number of orders placed during the year.
4. Top Cities: Cities with the highest sales volumes.
5. Regional Analysis: Sales performance by region (Central, East, South, West).
6. Product Category Analysis: Sales and profit for different product categories (Furniture, Office Supplies, Technology).
7. Customer Segment Analysis: Sales and profit based on customer segment (Consumer, Corporate, Home Office, Small Business).
8. Shipping Method Analysis: Sales and profit for different shipping methods (Delivery Truck, Express Air, Regular Air).
9. Total orders and Returned Orders Analysis

Visualizations and Reporting:

PivotTables

1. Created PivotTables to summarize and analyze data.
2. Slicers and Timelines: Added slicers and timelines for interactive filtering and exploration.
3. Charts: Used various chart types (e.g., line charts, bar charts, pie charts) to visualize data effectively.
4. Dashboard: Design a comprehensive dashboard to present key metrics and visualizations in a cohesive manner.