Business Analytics with Excel
Certification Training
By:
Sonukumar Rameschandra Jaiswar
Business Analytics with Excel
sonukumarjaiswar@gmail.com
Designing a Sales Dashboard
in Excel
E-Commerce Sales Dashboard
DESCRIPTION
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 commonplace.
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 product category, so users can select a category and can see the
trend month-wise and product-wise accordingly.
Domain: E-Commerce
Dataset Description
We will be using E-Commerce Dashboard.xlsx Dataset here.
*E-Commerce Dashboard which covers Orders data for various Product Category;
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 Category Product 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
Analysis Tasks
• Use the Saved Sample – E-Commerce database.
• Create a histogram to analyse a number of shipping days.
Hints Steps Utilised :
Sample Step Create Histogram for Shipping Days(Aging)
To create a histogram, click the Data Tab, Under Analysis Group (Right Corner), Click Data
Analysis. Now, select Histogram and click ok. A histogram dialog box will appear.
In the histogram dialog box, first, click the Label’s Checkbox as we have labels in our data. After that,
In the Input reference box select the range (“Sales Data!D1: D51291”) of our data and in the Bin Range
Reference box select (“Working!K3: K7”).In the Output
section, select range “Working!N3” for a binning table, click Histogram checkbox and then ok.
Created Histogram Using Above mentioned Steps below :
30000 Histogram
20000
10000
Frequency
0
1 5 9 10 More
Prepare a table of Sales and Profit month-wise
Maximum in one sheet, named it as ‘Working Sheet’.
of Each bin
Second Table is linked with comb box and made by inserting pivot table using sales table data by
dragging appropriate data into various columns.
Code Used :
Sales = OFFSET(R15,ROW()-34,F140)
Profit = OFFSET(Z15,ROW()-34,F140)
Prepare the sales table region-wise in the working sheet.
Code Used :
Sales = OFFSET(AK42,ROW()-42,F140) and later linked with combo box
Create a User Control Combo box for Product Category.
Code Used :
Electronic Box Shown above values gets populated accordingly as 1,2,3,4
respectively from various categories as shown above =OFFSET(L33,F140,0)
Create Column Chart of a month-wise table and region-wise table
Steps Followed :
Select the column pertaining to month wise sales and profit and region wise sales
and then click on Insert -> Data - > Chart and click on clustered 2D Chart to get output as below.
Next Right Click on Chart and format it as per requirements by clicking format fields and axis
Region Wise Sales
2000000
1500000
1000000
500000
0 Total
Region Wise Sales
2000000
1500000
1000000
500000
0 Total
• Link the table with combo box.
Code Used : Using SUMIFS function and pulling data from sales table
Sales Box Value = SUMIFS('Sales Data'!$H:$H,'Sales Data'!$F:$F,Working!$F$141)
Quantity Box Value =SUMIFS('Sales Data'!$I:$I,'Sales Data'!$F:$F,Working!$F$141)
Profit Box Value = SUMIFS('Sales Data'!$K:$K,'Sales Data'!$F:$F,Working!F141)
•Create a dashboard.
Created as per requirements meticulously designed and it matches with the sample output.
Created As shown Below : Auto & Accecorries
Home & Furniture
Electronics
Fashion
Sample Output :
Note:
Below I have attached my excel sheet too where I have done all the workings and created a dashboard too with all the tables
shown as per requirements.
Thank You
