lOMoARcPSD|63670505
Designing a Sales dashboard in Excel.
Project 1
DESCRIPTION
Use Excel to analyse the sales based on various product categories.
Description:
The dataset in file E-Commerce Dashboard [Link] contains sales data for different
product categories. The following are the features in the dataset:
Order ID Unique Order ID of a product
Order Date Order Placement Date
Ship Date Shipment Date of the placed order
Aging Used to Create Histogram Bin
Ship Mode Shipment mode of placed order
Product CategoryProduct Category
Product Name of the Product
Sales Sales Amount
Quantity The amount or number of a material
Discount A deduction from the usual cost of something
Profit A financial advantage or benefit
Shipping Cost The amount required to ship the placed order
Order Priority Precedence of placed order
Customer ID Unique Customer ID
Customer Name Name of the Customer
City Unique City Name
State Unique State Name
Country Unique Country Name
Region Especially the part of a country
Months The month of placing the order
The following project tasks are required to be performed in Excel:
Use the saved Sample E-Commerce database
Prepare a table of Sales and Profit month-wise in a working sheet
Prepare the sales table region-wise in the working sheet
Create User Control Combo box for Product Category
Create a Column Chart of the month-wise table and region-wise table
Link the table with a combo box
Create a dashboard.
Tools required: Microsoft Excel, Data Analysis Add-in
Expected Deliverables: Design a sales dashboard that analyses the sales based on various
product categories. The company wants to add user control for product category so that users
can select a category and can see the trend month-wise and product-wise accordingly.
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Contents
the file e-Commerce Dashboard Project............................................................................................3
the tables Sales and Profit month-wise and region-wise...................................................................3
Combo box for Product Category......................................................................................................4
Linkage of the tables with the combobox:.........................................................................................5
Dashboard.........................................................................................................................................6
another Dashboard............................................................................................................................8
The excel file used.............................................................................................................................9
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
the file e-Commerce Dashboard Project
the tables Sales and Profit month-wise and region-wise
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Combo box for Product Category
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Linkage of the tables with the combobox:
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Sales per each month
=SUMIFS('Sales Data'!$H:$H,'Sales Data'!$U:$U,Working!$B2,'Sales Data'!$F:$F,$J$9)
Profit per each month
=SUMIFS('Sales Data'!K:K,'Sales Data'!$U:$U,Working!$B2,'Sales Data'!$F:$F,$J$9)
Sales per each Region
=SUMIFS('Sales Data'!$H:$H,'Sales Data'!$T:$T,Working!$F2,'Sales Data'!$F:$F,$J$9)
Dashboard
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
Another Dashboard
I could create another dashboard using the pivot chart option as well as shown in the Dashboard2
worksheet.
I can go one by one on each of the graphs and select the product category.
However, I can be more flexible and select multiple products categories as well.
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])
lOMoARcPSD|63670505
The excel file used
Downloaded by Tamojit Chakrabarty (chakrabartytamojit@[Link])