Procurement Optimization

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.

Goal

Identify opportunities across all departments procurement is involved with to increase savings.

Objectives

1. Identify savings opportunities where products are ordered in large volumes or there are large unit price discrepancies
2. Increase contract spend in areas where a savings opportunity is present and suppliers currently are not using a contract
3. 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.