In this inventory forecast, I share a quick and easy way to model stock balances and purchases. But take note — there’s lots of nuance to be aware of.

A roll-forward inventory forecast can be approached in two ways, depending on the availability and reliability of data.

1) Using Purchase Schedules from Procurement:

This method bases the forecast on detailed purchase plans provided by the procurement team. It’s effective when procurement data is precise and aligned with sales forecasts.

2) Forecasting Ending Balance & Backing into Purchases:

Here, you start with forecasting the ending inventory balance, a COGS forecast, and then back into the required purchases to complete the accounting equation.

This method is particularly useful when there’s high confidence in COGS forecasts and you need to determine the necessary inventory level to meet sales demands.

However, there are lots of nuances that may cause either approach to be wrong:

A) Raw material inventory purchases don’t often take place weekly. So using a weekly reconciliation is likely going to be in conflict with reality on the ground.

B) Timing issues. The timing of raw material inventory purchases, sales, and A/R collections is crucial for cash flow management. These approaches don’t effectively consider the lag between inventory purchases and sales.

C) Inaccuracy and erroneous valuation due to the commingling of inventory types. Inventory consists of raw materials, WIP, and finished goods. Lumping them all into a single inventory roll-forward can invite a highly imprecise analysis. It also ignores the capitalization of labor and overhead as conversion costs into WIP inventory.

There’s a whole lot more than what I can cover in this post. So…

⚠️ Be cautious about applying textbook approaches

⚠️ Be wary of using formulas that may not reflect reality.

⚠️ Substantiate and justify the methods you choose.

Watch the walkthrough video. 👆