This repository contains my work in Microsoft Excel.
Background
E-commerce is fast gaining ground as an accepted and used business paradigm. More and more business houses are implementing web sites providing functionality for performing commercial transactions over the web. It is reasonable to say that the process of shopping on the web is becoming common place.
Objective
An Online E-Commerce Company wants to design a Sales dashboard to analyze the sales based on various product categories. The company wants to add user control for Profit,Revenue,Quantity,COGS,Transaction. So users can select a options and can see the trend month-wise.
Domain: E-Commerce
Dataset Description
We will be using Ecommerce_Sales_Dashboard.xlsx Dataset attached.
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 | Sales Amount |
| 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 |
| Revenue | Total Revenue Generated |
| Profit | Profit Generated From Product Sale |
| City | Unique City Name |
| Sales Representative | Seller Name |
| Customer Name | Name of the Customer |
| Weekday | Product Purchase Day |
| Month | Product Purchase Month |
| Year | Product Purchase Year |
| Day | Product Purchase Day In Numeric |
Analysis Tasks
Create a histogram to analyze a profit,revenue etc. for every months.
Prepare a table of Revenue month-wise , named it as ‘Revenue Analysis’.
Prepare a table of Different Aspect in one sheet,named as 'Different Analysis'.
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. analysis
Create a dashboard.
Note: Sample output is given, please make it a meticulous as per the project statement.
Prerequisites : Basic Knowledge of Pivot Tables,Basic VBA,Charts and formatting in Excel
Sales Dashboard : I have utilized following items in building sales dashboard which analyzes sales by different aspects.
Pivot Tables
Different Chart
Slicers
VBA Basic