High Level Design

Overview

In sales data analysis, it’s common to use Gross Profit Margin (GPM) to understand efficiency. Since the GPM basically represents the revenue generated from sales deducted by the costs of making said sales (cost of goods sold). As a rule of thumb, a high GPM would indicate that our company/brands are likely to make a reasonable profit as long as we keep the remaining costs (Manufacturing overhead, operating expenses, etc.) under control. A low GPM would indicate that the production costs are too high and changes need to be make to production processes. We’ll use this indicator to understand which of our brands is best utilized and study its behavior over time, compare our brands across different locations (countries) and more. This will give us a good indication regarding which brand to allocate more resources and invest in.

Dashboard Example (sample data)

Click on the image to open and interact with the dashboard:

Sales Analysis by Brand Dashboard

Objectives

Increase our company’s profitability by 5% compared to last year by effectively utilizing our resources across the brands we produce.

KPI Architecture

Objectives KPIs Measures Data Source
Increase company’s profitability Company’s Gross Profit Margin

Formula:
(Revenue – Cost of goods sold) / Revenue

 Brands Gross Profit

Formula;
Per Brand: (Revenue – Cost of goods sold) / Revenue

 fact_Sale_Orders, fact_Purchase_Orders, dim_Brand
Growth Rate of Gross Profit Margin

 

Formula:

Current Period Gross Profit / Past Period Gross Profit

 fact_Sale_Orders, fact_Purchase_Orders
Sales Revenue

Formula:
SUM of Net Revenue

 fact_Sale_Orders

Data Requirements

# Source Table Name Table Details (Type, #Rows, Key field/s)
1 [CRM System] fact_Sale_Orders Contains the general, or parent, sales order information alongside the specific products associated with the sales order.
2 [CRM System] fact_Purchase_Orders Contains the general, or parent, purchase order information alongside the specific products associated with the purchase order.
3 [CRM System] dim_Brand Contains the list of Brands associated to each Product.