Skip to main content
Inventory represents goods held for sale or use in production. Common examples include raw materials, work in progress, and finished goods. Balance sheet forecasting is always a function of last period’s value, additions, and detractions. See BS fundamentals. For inventory, this means inventory last period + new purchases in period − cost of goods sold in period.

​
What approach to use

Driver-based is the right default. Either express the closing balance as a percentage of revenue for a simple setup, or build a category-level supporting sheet that tracks purchases and sales by category. The category approach lets inventory movements feed into the cost of sales line on the P&L. Use statistical when inventory levels are stable and seasonal patterns are consistent. A rolling average or prior-year reference is often accurate enough without rebuilding the purchase and sales link. Hardcoded is acceptable when you have a known purchase plan or a specific target balance in mind. Enter the expected closing balance directly.

​
Where to forecast inventory

  • Supporting sheet: right when inventory is split by category. Track purchases and sales separately within each category. The sales rows can feed the cost of sales line on the P&L, keeping inventory and cost of sales in sync.
  • Directly on the balance sheet: right for simple single-category setups or when using the % of revenue approach.

​
Forecasting approaches

% of revenueThe simplest approach. Add an Inventory % assumption and apply it to monthly revenue:
= "Revenue"[0]
* "Inventory %"[0]
Set the percentage to reflect your typical inventory-to-revenue ratio. This works well for businesses with stable turnover rates and a single product category.Supporting sheet by categoryFor businesses with multiple inventory categories, build a supporting sheet. Create one section per category. Categories can follow a process-oriented structure (Raw materials, Work in progress, Finished goods) or a SKU-oriented structure (individual product lines or families). Choose based on how your business tracks inventory and what level of P&L detail you need. Within each section, add four rows.Opening balance: hardcode the first period, then reference the prior period’s closing balance:
= "Closing balance"[-1]
Purchases: hardcode the expected purchase amount in the month it arrives. This is the addition to inventory and flows to cash flow as an operating outflow.COGS: the cost of inventory sold in the period, entered as a negative value. The direction can go either way: forecast COGS here and reference the total on the P&L, or take COGS from an existing P&L forecast and reference it here to drive the closing inventory balance.Closing balance: the roll-forward:
= "Opening balance"[0]
+ "Purchases"[0]
+ "COGS"[0]
The balance sheet references the closing balance total across all categories. The P&L references the COGS total.
Linking inventory and COGS through the supporting sheet keeps both in sync automatically. If COGS is forecast here, changes flow to the P&L. If COGS comes from the P&L, changes flow to the inventory balance. Either way, the two statements move together. This is the main advantage of the category approach over % of revenue.
When setting up the supporting sheet, pull the opening inventory balance with a formula, so the starting value sits in the actuals layer and feeds the forecast automatically. As you move the Forecast Start date forward, periods that were forecast become actuals, and the formula keeps sourcing each opening balance from the actuals layer with no manual update.

​
FAQ

Use the supporting sheet approach. The COGS row within each inventory category feeds directly to the corresponding P&L line. Reference the COGS total from the supporting sheet into the cost of goods sold row on the P&L. Any change to inventory assumptions flows through automatically.
Model a write-down as a detraction in the period it occurs. Add a separate write-down row in the relevant category section and enter the amount as a negative value. The write-down flows to the P&L as a cost and reduces the closing inventory balance.