Excel for Supply Chain Management: 10 Use Cases With Formulas You Can Check
Copyable formulas, the assumptions behind them, and where each one stops working.
Excel is not the future of supply chain management: dedicated platforms such as SAP IBP, Kinaxis and o9 Solutions do things a spreadsheet cannot. But spreadsheets remain common in planning teams because a well-built workbook is fast, portable, needs no procurement cycle and can be handed to almost anyone. The problem is rarely Excel itself. It is that workbooks are often built in a hurry, with unstated assumptions and untested formulas. This guide shows ten use cases with formulas you can copy, the assumptions behind them, and the limits.
How to read the examples. Code blocks marked "Excel" contain only formulas that you can paste into a cell, using the cell layout stated above them. Explanations are in the text, not inside the formulas. Every worked example with numbers is hypothetical, chosen to show the mechanics; none describes a real company.
TL;DR — 10 Use Cases
- Demand Forecasting — moving averages, exponential smoothing and FORECAST.ETS, tested on a holdout
- Safety Stock & Reorder Points — a statistical calculation with its assumptions stated
- ABC–XYZ Classification — illustrative thresholds for value and demand variability
- Supplier Scorecards — weighted metrics with traffic-light formatting
- PO Tracking — SUMIFS and date logic for open orders and overdue flags
- Landed Cost Modelling — freight, duty, insurance and financing cost per unit
- S&OP Data Consolidation — Power Query for repeatable data ingestion
- KPI Dashboard — sparklines and conditional formatting on one page
- BOM Explosion & Cost Rollup — lookups and SUMPRODUCT, and where they stop
- Scenario Planning — Scenario Manager, Data Tables and a simple Monte Carlo model
1. Demand Forecasting with Moving Averages & Exponential Smoothing
Every supply chain starts with one question: how much will we sell? Too high and you carry excess stock; too low and you lose sales. When you have no dedicated forecasting tool, Excel and a clean sales history can give you a transparent baseline.
Simple Moving Average (SMA)
Average the last N periods. It suits stable, low-volatility items. Layout: dates in column A, actual demand in column B, forecast in column C. To forecast row 5 from the previous three actuals, put this in C5 and copy it down:
=AVERAGE(B2:B4)
Weighted Moving Average (WMA)
Use it when recent periods should count more. Put the weights in F2:F4, oldest to newest (for example 0.2, 0.3 and 0.5), and check that =SUM(F2:F4) equals 1. Then, in C5:
=SUMPRODUCT(B2:B4,$F$2:$F$4)
A weighted average is not automatically better than a simple one. Which performs better depends on the series, so compare them on data the models have not seen (see the holdout test below).
Exponential Smoothing
Exponential smoothing sets the next forecast to a weighted mix of the latest actual and the previous forecast: F(t+1) = α × A(t) + (1 − α) × F(t). You choose α between 0 and 1; a low α reacts slowly and smooths noise, a high α reacts quickly but follows noise. Name a cell alpha and put 0.3 in it. Start the forecast column with the first actual, then smooth:
C2: =B2
C3: =alpha*B2+(1-alpha)*C2
Copy C3 down. Worked check with α = 0.3 and actuals 100, 120, 80: the forecasts are 100 (initial), 0.3 × 100 + 0.7 × 100 = 100, then 0.3 × 120 + 0.7 × 100 = 106 and then 0.3 × 80 + 0.7 × 106 = 98.2. Plain exponential smoothing has no trend or seasonality, so it suits level series.
FORECAST.ETS
Excel's FORECAST.ETS function (introduced in Excel 2016; check availability for your platform on Microsoft's page) fits an exponential smoothing (ETS) model that can include trend and seasonality. The complete documented signature is:
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
timelinemust be numeric with a constant step between points (for example monthly dates); a non-constant step returns an error.seasonality: a positive whole number is the length of the seasonal cycle in timeline steps.12means a twelve-observation cycle, which is annual seasonality only if your timeline is monthly and you have reason to expect a yearly pattern. The default (1) lets Excel detect seasonality automatically, and 0 means no seasonality.data_completion: how gaps are handled. The default (1) interpolates missing points; 0 treats them as zeros. For demand data, decide deliberately whether a blank means "not recorded" or "confirmed zero sales", because the two give different forecasts.aggregation: how several values with the same timestamp are combined. It is optional; see Microsoft's documentation for the options.
A copyable example with 24 months of history in B2:B25 (dates in A2:A25) and the next date in A26:
=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12,1)
Test the forecast on a holdout
A model that fits history well can still forecast badly. Hold back the most recent periods, forecast them using only earlier data, and compare the error with a simple benchmark such as the last observed value. With 36 months in rows 2–37, use rows 2–25 for training and rows 26–37 as the holdout; in row 26, copy down:
=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12,1)
The benchmark in the same rows is =$B$25 (the last training value). For more than one test window, use time series cross-validation with a rolling forecasting origin; see sections 5.8 and 5.10 of Hyndman and Athanasopoulos, Forecasting: Principles and Practice (3rd ed.).
For one observation, the absolute percentage error is ABS(Actual - Forecast) / Actual. MAPE is the average of that error across observations. With actuals in B2:B13 and forecasts in C2:C13, put the error in D2 and copy down, then average it:
D2: =IF(B2=0,"",ABS(B2-C2)/B2)
MAPE: =AVERAGE(D2:D13)
Percentage errors are undefined when actual demand is zero and explode when it is near zero, so MAPE is a poor fit for intermittent or low-volume items. A common alternative is a weighted error, total absolute error divided by total actual demand: =SUMPRODUCT(ABS(B2:B13-C2:C13))/SUM(B2:B13). Worked check: actuals 100, 120, 80 and forecasts 90, 130, 100 give errors of 10%, 8.33% and 25%, a MAPE of 14.4%, and a weighted error of 40 / 300 = 13.3%. There is no universal "good" MAPE; compare against a benchmark on the same data. See section 5.8 of Forecasting: Principles and Practice for scaled errors such as MASE.
2. Safety Stock & Reorder Point Calculation
Safety stock is a statistical buffer against demand and lead time variability. The standard calculation below rests on stated assumptions, so check them before trusting the result.
Assumptions. Demand per period is roughly normal, independent from period to period and stationary (no trend or seasonality in the history you use). Lead time is independent of demand. All quantities use consistent time units: if demand is per week, lead time and its standard deviation are in weeks.
Which service level? Z × σ gives a cycle service level: the probability of not stocking out during a replenishment cycle. It is not the same as a fill rate, the share of demand met from stock. They can differ a lot, so state which one your target means.
Layout (hypothetical inputs in column D): D2 average weekly demand 200; D3 standard deviation of weekly demand 40; D4 average lead time in weeks 2; D5 standard deviation of lead time in weeks 0; D6 target cycle service level 0.95. Then:
D7: =NORM.S.INV(D6)
D8: =SQRT(D4*D3^2+D2^2*D5^2)
D9: =D7*D8
D10: =D2*D4+D9
D7 is Z (about 1.645 for 95%), D8 is the standard deviation of demand during lead time, D9 is the safety stock and D10 is the reorder point. For the inputs above, D8 is √(2 × 1,600) ≈ 56.6, safety stock is about 93 units and the reorder point is about 493 units. If lead time varies by 0.5 weeks (D5 = 0.5), D8 becomes √(3,200 + 10,000) ≈ 114.9 and safety stock rises to about 189 units, which shows how much lead time variability can matter.
Use =AVERAGE(range) and =STDEV.S(range) on your history to populate D2 and D3, and keep the history window consistent with the time unit.
Fill rate. To estimate the fill rate for an order quantity Q in D11 (hypothetical 400), compute the unit normal loss function L(z), the expected shortage per cycle, and the fill rate:
D12: =NORM.S.DIST(D7,FALSE)-D7*(1-NORM.S.DIST(D7,TRUE))
D13: =1-D8*D12/D11
With the first set of inputs, L(z) is about 0.0209, expected shortage is about 1.18 units per cycle and the fill rate is about 99.7%. The same 95% cycle service level can therefore correspond to a much higher fill rate, which depends on Q.
3. ABC–XYZ Inventory Classification
Not all SKUs deserve equal attention. ABC–XYZ analysis turns that into a rule.
ABC classifies by value contribution. A-items are the few SKUs that account for most of the value, often cited as roughly 20% of SKUs and 70–80% of value, though the actual split varies by business. B-items sit in the middle and C-items are the long tail.
XYZ classifies by demand variability using the coefficient of variation (CV = standard deviation / mean). X is stable, Y is moderately variable and Z is erratic or intermittent. A high CV does not by itself tell you why demand varies: seasonality, trend, promotions and random noise can all produce one, so examine the pattern before choosing a forecasting method.
The thresholds below (80% and 95% cumulative value; CV of 0.2 and 0.5) are illustrative. Set them from your own data and review them periodically.
Layout: SKUs sorted by annual revenue, highest first, in rows 2 to 501; revenue in column B; 12 months of demand in D2:O2. Then, in row 2, copied down:
C2: =SUM($B$2:B2)/SUM($B$2:$B$501)
P2: =IF(AVERAGE(D2:O2)=0,"n/a",STDEV.S(D2:O2)/AVERAGE(D2:O2))
Q2: =IF(C2<=0.8,"A",IF(C2<=0.95,"B","C"))
R2: =IF(P2="n/a","none",IF(P2<0.2,"X",IF(P2<0.5,"Y","Z")))
C2 is the cumulative share of revenue, P2 the coefficient of variation (returning "n/a" when average demand is zero, which avoids a divide-by-zero error), Q2 the ABC class and R2 the XYZ class. Variants differ on whether the item that crosses 80% is A or B.
An AX item (high value, stable demand) can justify tight reorder points and close supplier collaboration. A CZ item (low value, erratic demand) may suit a simple min-max policy and infrequent review. Use a PivotTable to count SKUs by class and conditional formatting to colour the 3×3 grid.
4. Supplier Scorecard & Performance Tracking
A scorecard gives supplier conversations a shared set of numbers. Common metrics are On-Time Delivery (OTD), fill rate, quality rejection rate, lead time consistency and price compliance. The weights below are illustrative and should reflect your priorities; they must sum to 1.
Layout: OTD in B2, fill rate in C2, rejection rate in D2, lead time consistency in E2 and price compliance in F2, all as fractions from 0 to 1. One illustrative definition of lead time consistency, with that supplier's lead times in H2:M2, is one minus the coefficient of variation, floored at zero:
E2: =MAX(0,1-STDEV.S(H2:M2)/AVERAGE(H2:M2))
G2: =100*(0.30*B2+0.25*C2+0.20*(1-D2)+0.15*E2+0.10*F2)
G2 is the score out of 100. Use conditional formatting for a traffic-light view; thresholds such as red below 60, amber from 60 to 79 and green from 80 are examples, not standards. Review scores with suppliers on a regular schedule so that the numbers inform the conversation rather than replace it.
5. Purchase Order Tracking & Open Order Management
Purchase orders are raised, acknowledged, partly shipped, delayed, amended and cancelled. A structured tracking sheet shows what is outstanding. Useful columns are PO number, supplier, line item, quantity ordered, quantity confirmed, quantity received, expected date, revised date, status and comments.
Layout: supplier in B2, status in E2, expected date in F2 and line value in G2, with data in rows 2 to 500. Then:
H2: =IF(AND(E2<>"Received",F2<TODAY()),"OVERDUE","")
I2: =IF(E2="Received","",MAX(0,TODAY()-F2))
K2: =SUMIFS($G$2:$G$500,$B$2:$B$500,J2,$E$2:$E$500,"<>Received")
H2 flags overdue lines, I2 gives days delayed, and K2 totals open value for the supplier named in J2. TODAY() recalculates every time the workbook opens, so saved values change. Add conditional formatting to colour overdue lines red and lines due within seven days amber. XLOOKUP (Excel 2021 and Microsoft 365) can pull supplier details into the tracker, and summary tables built with SUMIFS can feed your S&OP pack.
6. Landed Cost Modelling
Buying on unit price alone can hide freight, duty, insurance, port charges, inland haulage and the cost of tying up cash in transit. A landed cost model puts these side by side for each sourcing origin. The components are unit cost, freight, customs duty (by HS code), insurance, port and handling, inland transport and the financing cost of in-transit inventory.
Layout (hypothetical inputs in column B): B2 unit cost 10.00; B3 freight per container 2,000; B4 units per container 1,000; B5 duty rate 5%; B6 insurance rate 0.5%; B7 port and handling per unit 0.30; B8 inland transport per unit 0.20; B9 lead time in weeks 6; B10 annual cost of capital 8%. Then:
B11: =B2+B3/B4+B2*B5+B2*B6+B7+B8+B2*(B9/52)*B10
Checked by hand: 10.00 + 2.00 + 0.50 + 0.05 + 0.30 + 0.20 + 10 × (6/52) × 0.08 (about 0.09) = 13.14 per unit. The financing term converts weeks to years, so the cost of capital must be an annual rate. Duty is often calculated on a customs value that includes freight and insurance (the rules differ by country and incoterm), so check your own duty basis rather than assuming it is the unit cost.
Build one column per origin and compare total landed cost per unit. Then test sensitivity with a Data Table (What-If Analysis): what happens to the decision if freight rises by 20%, or the duty rate changes?
7. S&OP / IBP Data Consolidation
Sales and Operations Planning brings finance, commercial and supply chain together, often with conflicting numbers. Even organisations with planning platforms such as SAP IBP or Anaplan commonly use Excel for data input, review packs and ad hoc analysis.
A useful S&OP workbook has separate tabs for the sales forecast (by SKU and region), the supply plan (with constraints), inventory projection (forward stock cover), gap analysis (where supply cannot meet demand) and a one-page executive summary populated from the working tabs.
Power Query can pull data from databases, SharePoint, CSV files and many system exports, so you build the transformation once and refresh it, instead of copying and pasting by hand. Availability of Power Query and Power Pivot depends on your Excel version and licence.
If some of your data starts as barcode scans, capture it carefully before analysing it. Our barcode scanner for Excel inventory guide covers keeping leading zeros and checking stock movements with a small worked example.
Combine Power Query for ingestion, Power Pivot for the data model and pivot charts for presentation. This can act as a lightweight reporting layer, but it still needs ownership, documentation and version control.
8. KPI Dashboard with Conditional Formatting
Leadership usually wants one page that shows whether the supply chain is healthy. Define each KPI, its numerator and denominator, and its time window, so that the number means the same thing every month. Common KPIs are:
| KPI | What It Tells You |
|---|---|
| OTIF (On Time In Full) | Delivery performance to customers; define "on time" and "in full" before you measure |
| Inventory Days of Cover | How long current stock will last at the forecast rate of demand |
| Forecast Accuracy | Quality of the demand signal; state the error measure (see MAPE limits above) |
| Supplier Fill Rate | Share of ordered quantity delivered |
| Stockout Rate | How often you fail to fill customer demand |
| Freight Cost per Unit Shipped | Logistics cost efficiency |
| Purchase Price Variance | Actual versus budgeted or standard unit price |
Use sparklines (available since Excel 2010) for trend direction and conditional formatting icons to show performance against target. Add a date-selection dropdown linked to the charts for interactivity.
Every number on a dashboard should answer a question. If you cannot state the question, remove the metric.
9. Bill of Materials (BOM) Explosion & Cost Rollup
A BOM lists the components and quantities in a finished good. When a material price changes or a component is substituted, a BOM model shows the cost impact. In Excel, store component costs in an Excel Table named Cost_Table with columns ID and Unit_Cost.
Layout for one finished good: component ID in A2, quantity per unit in D2, then:
E2: =D2*XLOOKUP(A2,Cost_Table[ID],Cost_Table[Unit_Cost])
E8: =SUM(E2:E7)
E2 is the cost of one component line and E8 the total material cost (XLOOKUP requires Excel 2021 or Microsoft 365). If you already hold unit costs in column F, =SUMPRODUCT(D2:D7,F2:F7) gives the same total. To go from material to a loaded cost, keep units consistent. With material cost in B2, labour cost in B3, an overhead rate applied to labour in B4 and packaging cost in B5:
B6: =B2+B3+B4*B3+B5
Here the overhead is a rate multiplied by its base (labour), not added directly. Your own costing may absorb overhead on a different base. A single-level BOM is straightforward; multi-level BOMs need each sub-assembly's cost calculated before its parent, which in Excel means working bottom-up level by level (or using Power Query or recursive functions), not a single lookup.
Combine this with the landed cost model from use case 6 for a more complete product cost, remembering that every input is an assumption you should keep current.
10. Scenario Planning & What-If Analysis
Supply chains face demand spikes, port closures and supplier failures, and freight rates can move sharply. Modelling "what if" scenarios before a crisis is a useful skill, and Excel supports it well.
Excel's Scenario Manager (Data tab, What-If Analysis) stores named sets of input assumptions, such as "Base", "Freight Crisis" or "Key Supplier Down", and recalculates the model when you switch. Data Tables (one- or two-variable) show how an output, such as total landed cost or inventory cover, changes as inputs vary.
A simple Monte Carlo model: put average demand in B1 and the standard deviation in B2, then fill C5:C1004 with 1,000 draws and read a percentile:
C5: =MAX(0,NORM.INV(RAND(),$B$1,$B$2))
D1: =PERCENTILE.INC(C5:C1004,0.95)
C5 draws one simulated demand from a normal distribution, truncated at zero because demand cannot be negative; copy it down for 1,000 rows. D1 is the 95th percentile of simulated demand, meaning 95% of the draws are at or below it (it is not a "worst case"). RAND() recalculates every time the sheet changes and Excel has no seed, so paste values if you need a fixed result. The normal distribution is an assumption: check it against your history.
Python in Excel is available in some Microsoft 365 plans and runs with limits set by Microsoft; availability and limits change, so check Microsoft's current documentation before relying on it for larger simulations or models.
The Excel Skills That Make These Use Cases Work
These use cases do not require you to be a developer, but they do need solid knowledge of Excel's core toolkit:
| Skill | Why It Matters |
|---|---|
| XLOOKUP / VLOOKUP | Cross-reference two datasets |
| SUMIFS / COUNTIFS | Conditional aggregation for exception management |
| Power Query | Repeatable data ingestion |
| PivotTables | Summarise large tables without formulas |
| FORECAST.ETS | Statistical forecasting with optional seasonality |
| Conditional Formatting | Visual exception management |
| Data Tables | Sensitivity analysis on one or two inputs |
| Named Ranges / Tables | Readable, maintainable models |
| IFERROR / ISBLANK | Formulas that handle bad data deliberately (use IFERROR carefully, since it can hide real errors) |
| INDEX / MATCH | Flexible lookups where XLOOKUP is not available |
Should You Replace Excel?
For some processes, eventually. Excel struggles when many people edit one file, when datasets reach millions of rows, and when you need controlled, real-time integration across a network. Those are the cases for dedicated tools.
Before signing a multi-year software contract, though, ask whether your current process is documented and well understood. Teams that know their data, their processes and what a good result looks like tend to specify and test new tools better. Whatever you use, keep inputs and assumptions visible, document formulas and test them on known cases.
Frequently Asked Questions
Is Excel good for supply chain management?
It is widely used for analysis, planning and reporting because it is fast, portable and flexible. It can handle forecasting baselines, safety stock calculations, scorecards, landed cost models and scenario analysis. Its limits show up with large datasets, many simultaneous editors, version control and live system integration.
How do you calculate safety stock in Excel?
Safety stock = Z × σLTD, where Z is the normal quantile of your target cycle service level (=NORM.S.INV(0.95) is about 1.645) and σLTD is the standard deviation of demand during lead time. If demand varies and lead time varies, σLTD = SQRT(avg_LT × σ_demand² + avg_demand² × σ_LT²), with demand and lead time in consistent time units. This assumes roughly normal, independent demand and lead time independent of demand. A cycle service level is not the same as a fill rate.
What is FORECAST.ETS and how is it used in supply chain?
FORECAST.ETS fits an exponential smoothing (ETS) model that can include trend and seasonality. The complete syntax is =FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]). The timeline needs a constant step. A seasonality of 12 means a twelve-observation cycle, which suits annual seasonality in monthly data when that pattern is justified; the default lets Excel detect it. Test the result on a holdout against a simple benchmark before using it.
What is ABC-XYZ analysis in inventory management?
ABC-XYZ combines value contribution (A, B, C, using cumulative revenue share) with demand variability (X, Y, Z, using the coefficient of variation). Typical thresholds, such as 80% and 95% cumulative value and CV of 0.2 and 0.5, are illustrative and should be set from your own data. A high CV does not by itself show seasonality or trend, so examine the demand pattern before choosing a policy.
What is the difference between MAPE and an absolute percentage error?
The absolute percentage error is ABS(actual − forecast) / actual for one observation. MAPE is the average of those errors across observations. It is undefined when actual demand is zero and unstable when it is near zero, so weighted or scaled error measures are often better for low-volume items.
Can Excel replace supply chain software like SAP IBP?
Not at scale or where controlled multi-user planning and integration are required. Excel can complement such platforms as an analysis and reporting layer, and it can be a sensible choice for small or one-off analyses.
Spotted an error or an outdated fact? Suggest a correction — corrections are reviewed by the author.