Sales Analysis by Brand

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: (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.