Sales Analysis

Project Title: USA Regional Sales Analysis

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About Dataset & Files

Dataset Source: Data.World

Introductory Key Points:

The Dataset is Contained with 7 tables. Those are pointed below:

➡️ 5 dimension tables:

Calander_Lookup (Primary Key Column Name: Date)
Customers_Lookup (Primary Key Column Name: _CustomerID)
Products_Lookup (Primary Key Column Name: _ProductID)
Regions_Lookup (Primary Key Column Name: StateCode)
Team_Lookup (Primary Key Column Name: _SalesTeamID)

➡️ 1 fact table, titled "Sales", has relationship with with 5 dimension tables; Calander_Lookup, Customer_Lookup, Products_Lookup, Locations_Lookup, and Team_Lookup)

➡️ There is 1 hybrid table, titled "Locations_Lookup", which acts as both dimensional & fact table. As a fact table, it is attached with the "Regions_Lookup" table containing with the primary key column "StateCode" . Again, it has relationship with the fact table "Sales" with the primary key columned titled "_StoreID".

The report is Contained with total 5 Dashboards.

Click Here to Download the Report File

Dashboard - 1: Sales Analysis by Dates

Visual Description:

The Slicer visual, titled "Select Date Range", contains all the dates reported in the "Calander_Lookup" table of the dataset. Report user should be able to select a range of dates from this slicer. Based on the date range selected by user, all the other visuals of this dashboard will change accordingly.
There are 2 Gauge Charts in this dashboard whose values vary based on the filter context of "Select Date Range" slicer. Those charts are briefly explained below:
- - 1. Current Month Orders vs Target: This chart shows the distance between the current month orders and monthly target orders. For each filter context of the "Select Date Range" slicer, the target orders of month change. Based on this assumption, it can be seen from the filling of the gauge chart about how close the current month order is on target.
    2. Current Month Revenue vs Target: This chart shows the distance between the current month revenue and monthly target revenue. This chart works exactly like the previous "Current Month Orders vs Target" gauge chart.
The Aria Chart, titled "10-days Rolling Profit", shows the calculated rolling profit for every 10 days plotted against the dates mentioned in the "Calander_Lookup" table.

A Table visual, titled "Cumulative Total Revenues", shows the normal totals & cumulative totals (based on years, quarters, and months) of the earned revenue. There are total 5 columns in this table:
- - 1. Start of Week: This column holds all the dates that represent the start of the week in the "Calander_Lookup" table of the dataset.
    2. Total Revenue: This column displays the total amount of weekly revenue earned, and the values are placed for every start of the week.
    3. YTD Revenue: This column shows the cumulative total Year-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every year.
    4. QTD Revenue: This column shows the cumulative total Quarter-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every quarter.
    5. MTD Revenue: This column shows the cumulative total Month-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every month.

Dashboard - 2: Target KPIs & Weekday Orders

Visual Description:

The Slicer visual, titled "Select Quarter-Year", holds all the individual Quarters reported in the "Calander_Lookup" table of the dataset. There is also an option of "Select all" by pressing which all the quarters can be selected together. Report user should be able to select any one quarter or all quarters together. Based on the selection, all the other visuals of this dashboard will change accordingly.
There are total 5 KPI Cards in this dashboard. The trend axis of all those visuals are changed by the Start of Months . In KPI visuals, when the measured value meets the target value, then the trend axis color becomes Green, otherwise it will be Red. Those cards are briefly introduced below:
- - 1. Monthly Order Target: This KPI shows whether the calculated average monthly order meets the preset target order or not. Here the big number represents the calculated average monthly order (based on the filter context of "Select Quarter-Year" slicer), and the "Target" holds the preset monthly target order. The percentage value in the bracket shows how far the average monthly order is far from the target order. For example, if this visual shows 1162 with red color, it means that the measured monthly order did not fulfill the target order.
    2. Monthly Cost Target: This KPI displays whether the calculated average monthly cost is smaller than the target cost or not. Here the big number represents the calculated average monthly cost (based on the filter context of "Select Quarter-Year" slicer), and the "Target" holds the preset monthly target cost. The percentage shows similar context like the previous "Monthly Order Target" KPI, but in this case the lower the cost the better. For example, if this visual shows $1.67M with red color, it means that the measured monthly cost is higher than the target monthly cost.
    3. Monthly Revenue Target: This KPI has similar visual representation and opposite context to the "Monthly Cost Target" KPI, where higher measure revenues (than the target) shows better result. For example, if this visual shows $2.31M with red color, it means that the measured monthly revenue is lower than the target monthly cost.
    4. Monthly Profit Target: This KPI has similar visual representation and context like the "Monthly Revenue Target" KPI. The only difference is, instead of "revenue", here the measured factor is "profit". For example, if this visual shows $637.23K with red color, it means that the calculated monthly earned profit is lower than the monthly profit target.
    5. Monthly Profit Margin Target: This KPI shows whether the calculated monthly profit margin (profit-revenue ratio) is better than the target monthly margin or not. For example, if this visual shows 27.57% with red color, it means that the calculated monthly earned profit margin is lower than the target.

The Stacked Aria Chart, titled "Total Order Placed in Weekdays", shows the total amount of orders placed in different weekdays for all transaction dates. Report user should be able to see different weekdays order while placing the cursor on the visual as tooltips.

Dashboard - 3: Sales Analysis by Location

Visual Description:

The single Slicer visual, titled "Select State", contains all the USA states reported in the "Locations_Lookup" table of the dataset. There is also an option of "Select all" by pressing which all the states listed in the slicer can be selected together. Report user should be able to select any one state or all states together. Based on the selection, all the other visuals of this dashboard will change accordingly.
There are 4 Card Visuals in this dashboard whose values differ based on the filter context of "Select State" slicer. Those charts are briefly explained below:
- - 1. Total Order: This card shows the total amount of order placed in the selected state/states.
    2. Total Revenue: This card shows the total amount of revenue earned from the selected state/states.
    3. Total Cost: This card shows the total amount of cost spent in the selected state/states.
    4. Total Profit: This card shows the total amount of profit made in the selected state/states.

The single Map visual, titled "Sale Scenarios in Different States", shows the geographical positions of all the USA Sates, listed in the "Locations_Lookup" table, in world map. The bubble size of this visual varies based on the total amount of orders confirmed in different states by the respective customers. Report user should be able to see the state name, total order, total profit, total revenue, and total cost of the corresponding state while putting the cursor on the city's bubble position. This visual changes based on the filter context of "Select State" slicer.

Dashboard - 4: Profit Margin by Individual Seller

Visual Description:

There is only a single Treemap visual, titled "Profit Margin by Individual Seller", in this dashboard. This viaual displays the comparison of all the listed sellers in the "Team_Lookup" table based on their earned profit margin. From this visual, it can be observed that there are total 28 seller name listed in the report dataset while each seller box in this visual is represented by different colorsa and box size. Every box size symbolizes the profit margin of the respected seller. Report user should be able to see the Seller Name, Profit Margin, and total profit of the corresponding seller while putting the cursor on each box.

Dashboard - 5: Customer & Product Analysis

Visual Description:

There are total 4 Slicer visuals in this dashboard, all are user-defined. In every slicer, Report user should be able to select either any one item, or all the items by pressing the "Select all" button. Those slicer visuals are shortly introduced below:
- - 1. Select Customer ID: This slicer contains all the customer IDs mentioned in the "Customers_Lookup" table as a dropdown list.
    2. Select Product ID: This slicer contains all the product IDs mentioned in the "Products_Lookup" table as a dropdown list.
    3. Select Month: This slicer contains all the Months in a year based on the "Calander_Lookup" table as a dropdown list.
    4. Select Year: This slicer contains three different sale years mentioned in the "Calander_Lookup" table as a horizontal list.

The Clustered Bar Chart, titled "Customer Order Chart", shows the list of customer names with their corresponding number of orders. The size of bars in this visuals vary based on the amount of orders. From the highest (Medline, 970 orders) to the lowest (WakeFern, 611 orders), there are total 50 unique customers (mentioned in "Customers_Lookup" table) placed in this visual. This chart changes its' context based on the filtering of three slicer visuals, i.e., "Select Product ID" , "Select Month" , and "Select Year".
The Stacked Column Chart, titled "Product Cost Chart", shows the horizontal list of products with their respective costs. The context of this visual is similar to the "Customer Order Chart" bar chart where the size of the bars change by the amount of cost spent. From the highest (Candles, costs $2.10M) to the lowest (Phones, costs $1.53M), there are total 28 various products (mentioned in "Products_Lookup" table) placed in this visual. Although the total number of products is 47, only 28 of them which have at least $1 cost are listed in the sales analysis. This chart changes its' context based on the filtering of three slicer visuals, i.e., "Select Customer ID" , "Select Month" , and "Select Year".

Page updated

Google Sites

Report abuse