Online Retail Store Sales Dashboard

Share Now


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.

Click Here To Download Template



Share Now
November 15, 2019

0 responses on "Online Retail Store Sales Dashboard"

    Leave a Message

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

    Tableau Training in Delhi

    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    Address

    NM 23, SECTOR 14, OLD DLF COLONY,
    GURGAON (HARYANA)
    0124- 4601426

    Featured Testimonial

    The experience...Read more

    Karan Jeena (HR Analyst)

    ZS Associates

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED