50 Excel Formulas Supply Chain Professional Should Know

50 Excel Formulas Every Supply Chain Professional Should Know.

Inventory Management

EOQ: =SQRT((2DS)/H)
Safety Stock: =Z * STDEV(Demand)
Reorder Point (ROP): =Average Daily Usage * Lead Time
Total Inventory Cost: =Ordering Cost + Holding Cost + Purchase Cost
Inventory Turnover: =COGS / Average Inventory
Days of Inventory: =365 / Inventory Turnover
Holding Cost: =Average Inventory * Holding Cost Rate
Average Inventory: =(Beginning + Ending Inventory)/2
Stockout Rate: =Stockouts / Total Orders
Fill Rate: =Orders Fulfilled / Total Orders

📈 Demand Forecasting

Simple Moving Average: =AVERAGE(B2:B6)
Weighted Moving Average: =SUMPRODUCT(B2:B4, C2:C4)
Exponential Smoothing: =(Alpha * Current Demand) + ((1 – Alpha) * Previous Forecast)
% Forecast Error: =(Forecast – Actual)/Actual
Mean Absolute Deviation (MAD): =AVERAGE(ABS(Forecast – Actual))
Mean Absolute Percentage Error (MAPE): =AVERAGE(ABS((Forecast – Actual)/Actual))
Tracking Signal: =Cumulative Forecast Error / MAD
Linear Regression: =FORECAST(x, known_y’s, known_x’s)
Trendline Equation: =LINEST(y_values, x_values)
Seasonal Index: =Period Demand / Average Demand

💰 Costing & Finance

Total Cost: =Unit Cost * Quantity
Cost Variance: =Actual – Budgeted
Freight Cost per Unit: =Freight Cost / Total Units
Contribution Margin: =Revenue – Variable Cost
Break-even Point (units): =Fixed Cost / (Selling Price – Variable Cost)
Net Profit: =Total Revenue – Total Cost
Average Unit Cost: =Total Cost / Total Units
Total COGS: =SUM(Product Costs * Units Sold 👍 )
Markup %: =(Selling Price – Cost Price)/Cost Price
ROI: =(Net Profit / Investment) * 100
📊 Performance Metrics
OTIF (On-Time In-Full): =On-Time Deliveries / Total Deliveries
Perfect Order Rate: =Perfect Orders / Total Orders
Lead Time: =Delivery Date – Order Date
Cycle Time: =Completion Date – Start Date
Backorder Rate: =Backorders / Total Orders
Service Level %: =Fulfilled Orders / Total Demand
Capacity Utilization: =Actual Output / Max Capacity
Supplier Defect Rate: =Defective Units / Total Received Units
Scrap Rate: =Scrap Units / Total Units Produced
Efficiency Ratio: =Actual Output / Standard Output

Excel Utilities for SCM

VLOOKUP: =VLOOKUP(Item, Table, Column, FALSE)
XLOOKUP: =XLOOKUP(Item, Range, Result Range)
INDEX + MATCH: =INDEX(Column, MATCH(Value, Range, 0))
IF Statement: =IF(A2>100, “Bulk”, “Standard”)
SUMIFS: =SUMIFS(Sales, Region, “East”, Product, “A”)
COUNTIFS: =COUNTIFS(Region, “West”, Status, “Delayed”)
TEXT Function: =TEXT(Date, “YYYY-MM-DD”)
NETWORKDAYS (working days): =NETWORKDAYS(Start, End
DATEDIF (Lead Time): =DATEDIF(Order Date, Delivery Date, “D”)