+91-8826547882

Online Retail Store Sales Dashboard

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.

Click Here To Download Template

October 24, 2017

0 Responses on Online Retail Store Sales Dashboard"

Leave a Message

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

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com