High Level Design
OverviewA 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 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:
Identify opportunities to increase savings across all departments procurement is involved with
- 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
|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|
|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|
|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|
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.