Project Overview

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.

Dataset Used

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.

Key Steps in the Project

Data Extraction and Transformation

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.

Data Compilation and 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.

Dashboard Development

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.

Conclusion

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.

Sales Analytics Dashboard

Useful links

Company

Information

Content Writers

Freelancers

Freelance Jobs

Online Jobs

Designers

Developers

Translations