Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

Online Retail Store Sales Dashboard

Sales Dashboard in Excel Training

In this article, we will visualize one sales dashboard of an online retail store company. This dashboard will help the company to monitor the monthly numbers across markets. I have divided working of entire dashboard into 6 sections.

Context:

  • The Global manager of an online retail store wants to see the performance of company across markets.
  • He/she is also interested to see the top salesman’s and products.
  • Global Manager want to play with controls (Year, Customer Segment, Product Categories), so he can see numbers by different – different angels.
  • And he/she also interested to see total sales, quantity, profit, and cost.

Dataset:

Before we start our steps, first let’s get the understanding of data set.
This dataset keeps the track of sales, profit, discount, demographic info, customer info of an online retail store.
Data is saved in the datasheet in the attached sample dashboard workbook.

Designing Sketch:

Based on all his needs will design one sketch, (In your initial year of report visualization you can take reference from google also, google sample dashboards and you can pick any sketch from there as per your requirement.)

Here is our sketch based on the requirements.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Now let’s discuss Workings,
Step 1: Insert Controls and Create Input range
First, I have inserted four drop-down controls and prepared input range for them from the datasheet.

Step2: Extract selected values
We cannot pass values from the drop-down directly in excel, so next step is to extract them. Drop-down returns the position of the selected item in the cell link, offset formula returns value based on position, so here will use ffset formula.

 
 
 
 
 

Step3: Using Sumifs formula extract monthly numbers based on drop-down selection
Arrange your markets monthly numbers in a such a way, so that while creating chart program will automatically add one blank column between markets.

Step4:
Create a table for product category wise sales.



 
 


Step5:
Capture Selected year, previous year and change (in percentage) by each salesman and product.
Use below-mentioned macro to sort data based on the selection in the drop-down to look for top results.

Sub sortcalc()

Application.ScreenUpdating = False

Dim lastrow As Long

lastrow = Sheet3.Range("C1048576").End(xlUp).Row

Sheet3.Range("C40").Formula = "=SUMIFS(Rng,Data!$Q:$Q,Calculation!$C$15,Data!$Z:$Z,Calculation!$C$13,Data!$I:$I,Calculation!$C$14,Data!$G:$G,Calculation!B40)"
Sheet3.Range("C40").Copy

Sheet3.Range("C40:C" & lastrow).PasteSpecial xlPasteFormulas
Sheet3.Range("C40:C" & lastrow).Calculate
Application.CutCopyMode = False
Sheet3.Range("C40:C" & lastrow).Copy
Sheet3.Range("C40:C" & lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Sheet3.Range("H40").Formula = "=SUMIFS(Rng,Data!$Q:$Q,Calculation!$C$15,Data!$Z:$Z,Calculation!$C$13,Data!$I:$I,Calculation!$C$14,Data!$R:$R,Calculation!G40)"
Sheet3.Range("H40").Copy
Sheet3.Range("H40:H" & lastrow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
Sheet3.Range("H40:H" & lastrow).Calculate
Sheet3.Range("H40:H" & lastrow).Copy
Sheet3.Range("H40:H" & lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Worksheets("Calculation").Activate

Range("B39:C" & lastrow).Sort key1:=Range("C3:C" & lastrow), _
order1:=xlDescending, Header:=xlYes

Range("G39:H" & lastrow).Sort key1:=Range("H3:H" & lastrow), _
order1:=xlDescending, Header:=xlYes
Sheet2.Activate

Sheet3.Calculate
Sheet2.Calculate

Application.ScreenUpdating = True

End Sub

Click Here To Download Template

December 21, 2017

0 responses on "Online Retail Store Sales Dashboard"

Leave a Message

Your email address will not be published. Required fields are marked *

top
 

© LOKESH PALIWAL 1987 - 2017

Reporting Solutions | Data Consulting | Analytics Training