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.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: |
Brands Gross Profit
Formula; |
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: |
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. |