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.
The following resources will enable you to design your dashboard and data model with sample
data and then apply it to your own data. Note that you will need to have a previously
installed version of Sisense (you can use the free trial version if you’re
not a customer).
