# Inventory Forecast A 12-month inventory forecast: SKU panel with unit cost, unit price, target DOH, and a monthly unit-sales plan per SKU; a per-SKU purchase plan sized to maintain target DOH coverage; aggregate inventory roll-forward in both units and dollars with DOH and coverage ratio each month; dashboard with peak inventory, lowest DOH month, and stock-out risk status against user-set thresholds. - Canonical: https://finamodel.com/templates/inventory-forecast - Excel download: https://finamodel.com/templates/inventory-forecast.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Operations leaders, Supply chain - Tags: inventory, doh, working capital, purchase plan, cogs ## Overview 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's included - 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 - Dashboard with weighted DOH, peak inventory, lowest DOH month - Coverage and stock-out risk traffic-light status against user-set thresholds - SKU value distribution block - annual COGS by SKU and % of total - SKU panel with unit cost, unit price, and target DOH per SKU - DOH (days-on-hand) and coverage ratio vs target every month - Identity check row confirming ending = opening + purchases - sales - Dashboard with weighted-average DOH, peak inventory value, lowest DOH month - Stock-out risk and coverage traffic-light status against user-set thresholds ## 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. ## Built for inventory investment decisions When the question is "how much working capital is tied up in stock and is the plan too thin or too fat?", an inventory forecast is the cleanest answer. This template feeds the inventory leg of a working-capital build, a 13-week cashflow forecast, or a 3-statement projection without you having to stitch SKU-level math together by hand. ## Designed for one-edit responsiveness Every SKU input - unit cost, unit price, target DOH, monthly sales - is a named-range or named-cell input. Flex one number and the per-SKU purchase plan resizes, the aggregate roll-forward updates, the DOH and coverage ratios move, and the dashboard traffic lights flip. ## Honest about model scope Lead time and safety stock are intentionally not modelled - purchases land in the same month they are ordered. The trade-off favours interpretability and one-edit responsiveness over the false precision of a daily order-and-receipt calendar. Users who need lead time should layer it on top of the purchase plan. ## Built for inventory investment decisions When the question is "how much working capital is tied up in stock and is the plan too thin or too fat?", an inventory forecast is the cleanest answer. This template feeds the inventory leg of a working-capital build, a 13-week cashflow forecast, or a 3-statement projection without you having to stitch SKU-level math together by hand. ## Designed for one-edit responsiveness Every SKU input - unit cost, unit price, target DOH, monthly sales - is a named-range or named-cell input. Flex one number and the per-SKU purchase plan resizes, the aggregate roll-forward updates, the DOH and coverage ratios move, and the dashboard traffic lights flip. ## Honest about model scope Lead time and safety stock are intentionally not modelled - purchases land in the same month they are ordered. The trade-off favours interpretability and one-edit responsiveness over the false precision of a daily order-and-receipt calendar. Users who need lead time should layer it on top of the purchase plan. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: SKU panel, monthly sales, opening inventory, thresholds. - 8 SKUs with unit cost, unit price, target DOH - Monthly unit-sales plan per SKU with seasonality - Opening inventory units per SKU - DOH and coverage traffic-light thresholds (green / amber) plus days-per-month constant ### SKU Master Per-SKU unit economics, annual sales and COGS, weighted-DOH contribution. - Unit cost, unit price, annual units, annual COGS per SKU - % of panel and DOH contribution (% of panel times target DOH) - Gross margin per SKU - Panel total row drives Panel_COGS and Weighted_DOH named ranges ### Purchase Plan Per-SKU 12-month purchase plan sized to maintain target DOH coverage. - Target ending units = next-month sales x target DOH / days-per-month - Purchase = MAX(0, target ending - opening this month + sales this month) - Monthly opening rolls forward from prior period inside the same row - Column totals feed the aggregate inventory schedule via named ranges ### Inventory Schedule Aggregate 12-month roll-forward in both units and dollars. - Opening, purchases, sales draw, ending in units - Opening value, purchase value, COGS, ending value in dollars - DOH = ending units / sales x days-per-month - Coverage ratio = DOH / Weighted_DOH - Identity check row confirms balance integrity ### Dashboard Headline metrics with traffic-light status and SKU value distribution. - Weighted-average target DOH - Peak inventory value and the month of peak - Lowest DOH with stock-out risk status - Minimum coverage vs target with traffic-light status - Annual COGS and purchases - SKU value distribution: annual COGS by SKU and % of total ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: SKU panel, monthly sales, opening inventory, thresholds. - 8 SKUs with unit cost, unit price, target DOH - Monthly unit-sales plan per SKU with seasonality - Opening inventory units per SKU - DOH and coverage traffic-light thresholds (green / amber) plus days-per-month constant ### SKU Master Per-SKU unit economics, annual sales and COGS, weighted-DOH contribution. - Unit cost, unit price, annual units, annual COGS per SKU - % of panel and DOH contribution (% of panel times target DOH) - Gross margin per SKU - Panel total row drives Panel_COGS and Weighted_DOH named ranges ### Purchase Plan Per-SKU 12-month purchase plan sized to maintain target DOH coverage. - Target ending units = next-month sales x target DOH / days-per-month - Purchase = MAX(0, target ending - opening this month + sales this month) - Monthly opening rolls forward from prior period inside the same row - Column totals feed the aggregate inventory schedule via named ranges ### Inventory Schedule Aggregate 12-month roll-forward in both units and dollars. - Opening, purchases, sales draw, ending in units - Opening value, purchase value, COGS, ending value in dollars - DOH = ending units / sales x days-per-month - Coverage ratio = DOH / Weighted_DOH - Identity check row confirms balance integrity ### Dashboard Headline metrics with traffic-light status and SKU value distribution. - Weighted-average target DOH - Peak inventory value and the month of peak - Lowest DOH with stock-out risk status - Minimum coverage vs target with traffic-light status - Annual COGS and purchases - SKU value distribution: annual COGS by SKU and % of total ## Features - **Target-DOH-driven purchase plan:** Each SKU's monthly purchase is sized so ending inventory equals next-month sales times target DOH divided by days-per-month. A single edit to one SKU's target DOH reshapes the entire purchase plan and the inventory balance path. - **Weighted DOH not flat-averaged:** Weighted-average DOH is computed as the sum of each SKU's annual-COGS share times that SKU's target DOH, so high-cost-and-volume SKUs dominate the headline number the way they would in a real working-capital plan. No equal-weight averaging. - **Units and dollars side-by-side:** The inventory schedule rolls both units and dollars across the same 12-month grid. Units catch stock-out risk; dollars feed working-capital sizing and the operating-cash leg of a cash forecast. SUMPRODUCT against Unit_Costs converts every aggregate to its dollar mirror. ## Use cases - **Inventory investment sizing:** Read peak inventory value and ending value at M12 to size working-capital ties tied up in stock. Pairs directly with the working-capital and ap-forecast templates for the full WC build. - **Stock-out risk diagnostics:** Lowest DOH (any month) and minimum coverage vs target flag months where the purchase plan or opening inventory is too thin to meet sales. Traffic-light status against user-set thresholds turns this into a one-glance answer. - **Purchase planning by SKU:** Operations leaders use the per-SKU monthly purchase plan as a starting point for procurement schedules; flex target DOH on a slow-mover to see purchases compress, or raise it on a hero SKU to absorb a demand spike. ## Frequently asked questions ### 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. ## Related templates - [Working Capital Model](https://finamodel.com/templates/working-capital-model) - [AP Forecast](https://finamodel.com/templates/ap-forecast) - [Cashflow Model](https://finamodel.com/templates/cashflow-model)