In this work I built an Interactive Dynamic Dashboard in Excel using Pivot Features, tables & slicers. This dashboard tells story about transactional historical data from 2015 to 2017. There are total 20,778 transactions. The data is contained in three worksheets and lookuped into one worksheet to create this analysis.
A web-based retailer wants to create a sales dashboard to analyse sales based on different product categories.For profit, revenue, quantity, cost of goods sold, and transaction, the corporation wishes to introduce user control. Users can thus choose settings and view the trend by month.
| Files/Folder | Description |
|---|---|
| Dataset | This folder provides you datasets for the transactional historical data from 2015 to 2017 |
| Dynamic Sales Dashboard | This file is an Excel Workbook in .xlxs format, in which the Dashboard has been made. |
The data is contained in three worksheets and lookuped into one worksheet to create this ananlysis.
Within this file you will find the following fields:
| Field | Description |
|---|---|
| Order ID | Product Order ID |
| Product ID | Unique Product ID |
| Location ID | Unique ID Of Location |
| Sales Person ID | Seller Unique ID |
| Customer ID | Unique Customer ID |
| Purchase Date | Product Booking Date |
| Quantity | Count Of Product Booked |
| Price | Price To Be Paid By Customer |
| Cost | Product Manufacturing Cost |
| COGS | Cost of goods sold |
| Revenue | Total Revenue Generated |
| Profit | Profit Generated From Product Sale |
| City | Unique City Name |
| Salesperson Name | Seller Name |
| Customer Name | Name of the Customer |
| Weekday | Product Purchase Day |
| Month | Product Purchase Month |
| Year | Product Purchase Year |
- Lookuped all the data into one worksheet
- Create a histogram to analyze a profit,revenue etc. for every months.
- Comparison of Revenue change With Previous Month
- Prepare a table of Quanterly Analysis
- Prepare the table of Top 5 Customers in term of sales and Top 3 Cities in term of sales
- Prepare the Daily,Weekly and Yearly Trends.
- Create a User Control Combo box for switching between Revenue,Profit,COGS etc.
- Create a dashboard
I have utilized following items in building sales dashboard which analyzes sales by different aspects.
- Pivot Tables
- Different Chart
- Slicers
