This project involves creating a comprehensive sales dashboard using an 8-year historical dataset. The dashboard provides key insights into the company's sales performance, shipping intervals, and regional sales distribution. The data is handled using Power Query and Power Pivot in Microsoft Excel to manage millions of rows efficiently. The dashboard is designed to help the company make informed decisions about future shipping strategies and overall sales performance.
The dataset contains 1,000,000 rows and 13 columns, capturing 8 years of sales data. Key columns include order dates, shipping dates, transaction details, revenue, COGS (Cost of Goods Sold), and profit. The data is stored in a single table, and Power Query is used for data connections and calculations before transferring to Power Pivot for efficient handling and analysis.
- Power Query: Utilized Power Query to extract and transform the data from raw, unstructured formats into a structured, tabular format.
- Custom and Conditional Fields: Created custom and conditional fields within Power Query to enhance data quality and prepare it for analysis.
- Power Pivot: Transferred the data to Power Pivot for further analysis and to store the data for future exploration, especially if the number of rows increases into the millions.
- Excel Functions: Implemented various Excel functions, including OFFSET and COUNTA, to prepare reports that interpret transaction, order, COGS, revenue, and net profit on a monthly and quarterly basis, by sales channel, by sales regions, and by item types sold over time.
- Pivot Tables: Generated pivot tables to compile and analyze key financial data.
- Interactive Dashboard: Designed and implemented an interactive dashboard in Excel to visualize key metrics and trends.
- Key Performance Indicators (KPIs): Incorporated KPIs to provide quick insights into total transactions, orders, revenue, COGS, and net profit.
- Dynamic Views: Created views to analyze weekly and monthly performance, and identified top expenditure categories.
- Option Buttons: Integrated option buttons to allow users to select different metrics (transaction, order, COGS, revenue, profit) and interact with sales regions, item types, months, and quarters.
-
KPIs: Easy-to-read cards displaying import KPIs like transactions, orders, revenue, COGS, and profit for rapi decision-making.
-
Transaction Analysis: Monthly and quarterly transaction trends.
-
Shipping Interval Analysis: Distribution of orders shipped within a week, within a month, and after 30 days.
-
Sales Region Analysis: Total transactions by different sales regions.
-
Sales Channel Analysis: Comparison of online and offline transactions.
-
Item Type Analysis: Total transactions by various item types sold over time.
-
Filters: Country, year, and quarter filters to interact with and analyze specific subsets of the data.
- Data Analysis: Power Query for data connection and transformation.
- Data Management: Power Pivot for handling large datasets and efficient data storage.
- Dashboard Design: Excel for creating interactive and visually appealing dashboards.
- Data Visualization: Various chart types and KPIs for insightful data representation.
- Attention to Detail: Ensured accuracy and consistency in data calculations and visualizations.
- Problem-Solving: Addressed challenges related to handling and analyzing large datasets.
- Communication: Presented complex data in a clear and understandable manner through the dashboard.
- Project Management: Managed the project from data preparation to final dashboard creation, ensuring timely completion.
This sales dashboard project showcases the effective use of Power Query and Power Pivot in Excel to handle large datasets and create an interactive, insightful dashboard. The dashboard helps the company make data-driven decisions regarding their sales and shipping strategies, contributing to overall business success.