High Level Design
Overview
A Supply chain company’s decision to reduce spend must be driven by the data to consolidate suppliers and negotiate procurement contracts company-wide. By effectively utilizing their procurement data across all departments, a supply chain analysis can compare product spend across suppliers while analyzing their percentage of contract spend. Procurement can isolate expensive suppliers and negotiate contracts much more effectively by consolidating suppliers. Savings opportunities can be identified when looking at the minimum price paid and last price paid and comparing it to quantity.Dashboard Example (sample data)
Click on the image to open and interact with the dashboard:
Goal
Identify opportunities across all departments procurement is involved with to increase savings.
Objectives
- Identify savings opportunities where products are ordered in large volumes or there are large unit price discrepancies
- Increase contract spend in areas where a savings opportunity is present and suppliers currently are not using a contract
- Consolidate suppliers where departments use different suppliers for the same product
KPI’s
| Identify savings opportunities where products are ordered in large volumes or there are large unit price discrepancies | Potential Savings | ((sum([Discount Unit Price]),[MostRecent])*[Total Order Quantity])-(([Total Min Discount Unit Price],[Product ID])*([Total Order Quantity],[Product ID])) | Orders, Product AGGR |
| Quantity | sum([Order Quantity]) | Orders | |
| Max Price | sum([Max Unit Price]) | Product AGGR | |
| Last Price Paid | (sum([Discount Unit Price]),[MostRecent]) | Orders | |
| Min Price | sum([Min Unit Price]) | Product AGGR | |
| Current Savings | sum([Savings]) | Orders | |
| Decrease Overall spend by increasing contract spend | Spend | sum([Final Cost]) | Orders |
| Consolidate suppliers where departments use different suppliers for the same product | Average Parts Per Supplier | avg([Supplier],count([Product Name])) | Suppliers |
| Number of Suppliers | count([Supplier ID]) | Suppliers | |
| Increase Savings over time | Savings | sum([Savings]) | Orders |
| Savings % | sum([Savings])/sum([Sale]) | Orders |
Data Modeling (Elasticube Design)
The original data source contained only 3 tables; Suppliers, Orders_Old, and Products. However, in order for this dashboard to work some simple data manipulation was needed.
First, the Orders table was created from the Orders_Old table where a column was added to identify the most recent purchase per product. This was achieved using the RANK() Function.
Next, the Product table was created from the Products and Orders Table. The maximum price, minimum price, last price paid, minimum price with discount, and maximum price with a discount were calculated from the Orders table and joined to the Products table. The MostRecent flag is needed to identify the most recent price paid per product.
