High Level Design
Overview
To maintain profitability in any industry (and especially highly competitive ones), keeping cost of goods as low as possible is critical. Too often businesses lose sense of how their costs compare to others in the industry and in what areas they are overpaying. This dashboard allows a customer to get a sense of how their costs stack up again others by date, product area and region. It provides actionable information that a business can use to immediately lower costs.
Goals
The goal of this dashboard is to provide an understanding of the how a customer’s cost of goods compares to competitors and identify opportunities to lower costs. After identifying the lowest cost suppliers and the potential cost savings if the business were to switch to those suppliers, the decision makers can evaluate if the switch is suitable for the business.
Objectives
- Understanding overall business costs and how it compares to competitors
- For the categories I spend the most on, identify where I am paying a per unit price above the average
- For the categories I spend the most on, find out the lowest cost supplier and what they charge
- For the categories I spend the most on, calculate potential cost savings of switching
- Evaluate offline the business case for switching and ultimately lower costs wherever possible
KPIs Architecture
|
Objectives |
KPIs |
Measures |
Data Source |
|
Understanding the a business costs and how it compares to competitors |
Avg Order Cost
|
(avg([Cost]), all([Region Name])) |
Fact Purchases, Dim Region |
|
My Avg Order Cost as a % of Customer Base Average |
([Average Cost], all([Region Name]))/avg([Avg Cost]) |
Fact Purchases, Fact Benchmarking, Dim Region |
|
|
Avg Order Price Paid
|
(avg([Cost]), all([Region Name])) |
Fact Purchases, Dim Region |
|
|
My Avg Order Price Paid as a % of Customer Base Average |
([Average Applied Unit Price], all([Region Name]))/avg([Avg Applied Unit Price]) |
Fact Purchases, Fact Benchmarking, Dim Region |
|
|
Avg Order Volume
|
(avg([Quantity]), all([Region Name])) |
Fact Purchases, Dim Region |
|
|
My Avg Order Volume as a % of Customer Base Average |
([Average Quantity], all([Region Name]))/avg([Avg Quantity]) |
Fact Purchases, Fact Benchmarking, Dim Region |
|
|
Identify lowest cost suppliers for top spending categories and see how I compare to the average |
Quantity Purchased for 10 Categories by Total Cost
|
(sum([Quantity]), all([Region Name])) |
Fact Purchases, Dim Region |
|
Average Price Paid by Category |
(avg([Unit Price]), all([Region Name])) |
Fact Purchases, Dim Region, Dim Category |
|
|
Average Price Paid Overall |
avg([Avg Unit Price]) + 0 |
Fact Benchmarking, Dim Category |
|
|
For my top 10, identify the lowest cost supplier and calculate potential cost savings of switching |
Total Cost Savings |
sum([Category Name],((avg([Applied Unit Price]), all([Region Name]))-[Price of Cheapest Supplier])*([Total Quantity],all([Region Name]))) |
Fact Purchases, Dim Region, Dim Category, Fact Benchmarking |
|
Lowest Supplier Cost by Category |
min([Supplier ID],[Average Avg_Applied_Unit_Price]) |
Dim Category, Fact Benchmarking, Dim Supplier |
|
|
Potential Savings by Category |
((avg([Applied Unit Price]), all([Region Name]))-[Price of Cheapest Supplier])*([Total Quantity],all([Region Name])) |
Fact Purchases, Dim Region, Dim Category, Fact Benchmarking |
|
|
Potential Per Unit Savings by Category |
(avg([Applied Unit Price]), all([Region Name]))-[Price of Cheapest Supplier] |
Fact Purchases, Dim Region, Dim Category, Fact Benchmarking |
Entities Relationship Diagram
Data Connectors
