Inventory Forecast
Corporate Finance Financial Model (Free Excel Download)
Forecast inventory by SKU using sales, costs, target days on hand, purchase requirements, stock-out risk, and monthly working-capital investment.
professionals from Deloitte
Used by professionals from
About this model
An inventory forecast translates a monthly unit-sales plan into a 12-month projection of inventory units, dollars, the purchases needed to maintain target coverage, and the cost-of-goods-sold draw against ending stock. This template lays the full mechanic on six sheets: an Assumptions sheet with a panel of eight SKUs (unit cost, unit price, target days-on-hand), a 12-month unit-sales plan per SKU with mild seasonality, opening inventory units per SKU, and the DOH and coverage traffic-light thresholds plus a days-per-month constant; a SKU Master sheet that pulls each SKU's unit economics and computes annual units, annual COGS, % of panel, weighted-DOH contribution, and gross margin; a Purchase Plan sheet with a per-SKU 12-month purchase plan sized so ending inventory each month equals next-month sales times target DOH divided by days-per-month; an Inventory Schedule sheet that rolls aggregate opening, purchases, sales draw, and ending across 12 months in both units and dollars, with implied DOH and coverage ratio vs target each month and an identity check row; and a Dashboard sheet with weighted-average DOH, peak inventory value and the month of peak, lowest DOH (with stock-out risk status), minimum coverage vs target, ending value at M12, annual COGS and purchases, and a SKU value distribution block.
The purchase plan uses MAX(0, target ending - opening this month + sales this month) so it never goes negative even when opening inventory is already above target. The opening-this-month expression rolls forward from prior periods inside the same Purchase Plan row, so a single edit to a SKU's target DOH or to a month's sales reshapes the entire forward path. Aggregate dollar values are built via SUMPRODUCT against the Unit_Costs named range, so changing one SKU's unit cost ripples through to purchase value, COGS, ending value, and peak inventory. Weighted DOH is computed properly - each SKU's share of total annual COGS is multiplied by its target DOH and summed - so high-cost-and-volume SKUs dominate the headline number the way they would in a real working-capital plan.
CFOs, FP&A teams, operations leaders, and supply-chain managers use this template for inventory investment sizing (peak inventory value sizes how much working capital is tied up in stock), stock-out risk diagnostics (lowest DOH and minimum coverage flag the months where the plan is too thin), and purchase planning by SKU (the per-SKU monthly plan is a starting point for procurement schedules and a clean baseline for negotiating with vendors). The lead-time and safety-stock dimensions are intentionally left out - real procurement has both, but the trade-off is interpretability and one-edit responsiveness over the false precision of a daily order-and-receipt calendar.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Inventory Forecast
- SKU panel with unit cost, unit price, target DOH per SKU
- 12-month monthly unit-sales plan per SKU with seasonality
- Opening inventory units per SKU
- SKU Master with annual units, annual COGS, % of panel, weighted-DOH contribution, gross margin
- Per-SKU 12-month purchase plan sized to maintain target DOH coverage
- Aggregate inventory roll-forward in units and dollars
- DOH (days-on-hand) and coverage ratio vs target each month
- Identity check confirming ending = opening + purchases - sales
How the Inventory Forecast Template Models Demand, Replenishment and Cash
This inventory forecast template projects 12 months of stocking and purchasing for a panel of eight SKUs. It links a statistical demand forecast to per-SKU reorder policy, an auditable roll-forward, inventory carrying costs, and the cash-conversion cycle, so you can see how coverage, stockout risk and working capital move together.
What Drives the Forecast
The template starts from a statistical demand build rather than you typing unit sales directly.
- Each SKU's monthly sales are the product of five factors: a base level, a trend, seasonality, promotional lift and cannibalisation, with an additional scenario uplift applied on top.
- That structure lets you express why demand changes, not just that it does.
- Around it sit the operating inputs that shape replenishment: per-SKU unit cost and price, a target days-on-hand, lead time, demand variability, a service-level Z for safety stock, supplier payment terms, and holding, obsolescence, shrinkage, order and stockout cost rates.
How Purchasing Is Sized
Replenishment is driven by a target-position rule.
- For each SKU and month, the model compares a target stock position, built from expected sales over the effective lead time plus a target DOH buffer and safety stock, against what is already on hand and in transit.
- Cycle stock and safety stock feed a reorder point, while economic order quantity provides an efficiency reference, and min/max stock levels act as discipline bounds on the order.
- Lead time drives the timing split between when orders are placed and when receipts actually arrive, so a long-lead SKU ordered this month may only be received several months later.
The Roll-Forward and Its Outputs
Each SKU carries an explicit monthly roll-forward of opening units, orders, receipts, sales and ending units, with ending stock rolling forward to become the next month's opening and receipts pulled from orders placed ahead of the lead time.
- Ending units can go negative, which surfaces the size of a stockout rather than hiding it, and a flag block marks any month where opening plus receipts fall short of sales.
- These SKU lines aggregate into a panel-level schedule in both units and dollars, where days-on-hand, coverage against a weighted DOH target and an identity check confirm the roll-forward ties.
Costs, Cash and Scenario Use
Inventory economics bring the physical flow into money terms: average inventory value supports carrying, obsolescence, shrinkage, order and stockout costs, and a total cost of inventory expressed as a share of revenue.
- ABC analysis ranks SKUs by annual cost of goods to focus attention on the few that matter most, and the cash impact schedule applies supplier payment-term lags and a receivables lag to produce DIO, DSO, DPO and the cash-conversion cycle.
- A scenario toggle flexes several top-line drivers at once across the workbook, with two sensitivity grids and a checks sheet for integrity testing, and a dashboard collecting headline KPIs.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is an inventory forecast?+
An inventory forecast translates a forward sales plan into a month-by-month projection of inventory units and dollars, the purchases needed to maintain target coverage, and the cost-of-goods-sold draw against ending stock. It is the inventory side of a working-capital forecast and feeds the operating-cash leg of a 13-week or 3-statement projection.
How is the purchase plan sized?+
Each month the target ending inventory for a SKU is set as next month's unit sales multiplied by that SKU's target DOH divided by the days-per-month constant. The purchase is then MAX(0, target ending - opening this month + sales this month), so it never goes negative even when opening inventory is already above target.
What is DOH and how is it computed?+
DOH (days-on-hand) measures how many days of forward sales the current inventory covers. The aggregate DOH each month is ending units divided by that month's sales draw, multiplied by the days-per-month constant. The weighted-average target DOH is the COGS-weighted average across SKUs.
Does it model lead time and safety stock?+
No - this template assumes purchases land in the same month they are ordered. Real procurement has lead time and safety stock; users who need that should layer those on top of the purchase plan or extend the model with a lead-time offset column.
Can I add more SKUs?+
Yes. Extend the SKU panel on Assumptions, add the corresponding row on SKU Master and Purchase Plan, and extend the per-month named ranges and the Opening_Units, Target_DOH, Unit_Costs ranges to cover the new rows.
Have more financial modelling questions? Contact us
Related templates
Working Capital Model
Model cash conversion cycle with DSO, DIO, and DPO to forecast working capital needs.
AP Forecast
12-month accounts-payable timing forecast with vendor terms and DPO.
Cashflow Model
Detailed monthly and annual cash flow projections with sweep and liquidity mechanics.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
