Inventory Turnover & Working Capital

This use case shows how to approximate inventory and working capital for manufacturing and production businesses in Model Reef.

Model Reef does not implement formal perpetual inventory accounting. Instead, you can use drivers and timing to approximate:

• Inventory levels by product family or plant.

• Inventory turnover and days on hand.

• Working capital tied up in raw materials, work in progress and finished goods.

• The impact of changes in stock policy, lead times and production plans.

When to use this pattern

Use this pattern when:

• Inventory is a significant use of cash in your business.

• You want to understand how production and purchasing decisions affect cash.

• You need a planning view of inventory rather than detailed batch level tracking.

You will usually connect this with:

• BOM and Unit Cost Modelling for unit costs.

• Capacity and Production Planning for volumes.

• Multi Plant Consolidated Forecasting for group views.

Architecture overview

The inventory and working capital structure includes:

1. Inventory policy and turnover drivers

   • Target days on hand or turns per year for each product family or plant.

   • Lead times for materials and finished goods.

2. COGS and volume drivers

   • COGS per period by product family or plant.

   • Production and sales volumes.

3. Inventory level approximations

   • Average inventory based on COGS and days on hand or turns.

   • Separation into raw materials, WIP and finished goods where useful.

4. Working capital and cashflows

   • Inventory value as part of working capital.

   • Changes in inventory feeding the Cashflow Statement and Cash Waterfall.

1

Step 1: Define inventory groups and plants

Choose a manageable level of aggregation for inventory, for example:

• By product family (for example A, B, C).

• By stage (raw materials, WIP, finished goods).

• By plant or warehouse.

In the branch structure, you can:

• Represent plants as branches and track inventory at plant level.

• Or keep inventory modelling central and use drivers to apportion between plants if needed.

The key is to avoid modelling each SKU individually unless necessary.

2

Step 2: Set inventory policy drivers

In the Data Library, create drivers such as:

• Target Days on Hand - Raw Materials - Product Family A.

• Target Days on Hand - Finished Goods - Product Family A.

• Or, if you prefer turns:

  • Target Inventory Turns per Year - Product Family A.

You can also define:

• Supplier lead times for raw materials.

• Production lead times for WIP and finished goods.

These assumptions describe how much inventory you plan to hold relative to usage or sales.

3

Step 3: Link COGS and volume to inventory

Using outputs from BOM and Unit Cost Modelling and your revenue model, determine:

• COGS per period by product family and plant.

• Production and sales volumes per period.

Then approximate inventory levels using formulas such as:

• If using days on hand:

  • Average Inventory = COGS per Day × Target Days on Hand.

  • Where COGS per Day = COGS per Period ÷ Number of Days in Period.

• If using turns:

  • Average Inventory = COGS per Period ÷ Turns per Period.

Create variables for inventory levels, for example:

• Inventory - Raw Materials - Plant North.

• Inventory - Finished Goods - Plant North.

These variables should be typed as Asset to ensure they appear on the Balance Sheet.

4

Step 4: Separate raw materials, WIP and finished goods (optional)

If you need additional detail, you can split inventory into stages using shares or more detailed drivers, for example:

• Raw Materials Share of Inventory.

• WIP Share of Inventory.

• Finished Goods Share of Inventory.

Then compute:

• Inventory - Raw Materials = Average Inventory × Raw Materials Share.

• Inventory - WIP = Average Inventory × WIP Share.

• Inventory - Finished Goods = Average Inventory × Finished Goods Share.

This allows more nuanced analysis of where cash is tied up and how policies differ by stage.

5

Step 5: Model changes in inventory and working capital

The Cashflow impact of inventory comes from changes in inventory levels over time.

Model Reef will compute this automatically when you treat inventory as Asset type variables with appropriate timing, but conceptually:

• An increase in inventory uses cash.

• A decrease in inventory releases cash.

Use the Cashflow Statement and Cash Waterfall to see:

• Net change in working capital related to inventory.

• Contribution of inventory changes to total cash movement.

You can also compare inventory levels and changes to trade debtors and creditors to get a full picture of working capital.

6

Step 6: Build inventory and working capital dashboards

Create dashboards that show:

• Inventory levels by product family and plant.

• Inventory days on hand and turns over time.

• Inventory value as a share of total assets or revenue.

• Cash impact of inventory changes.

Use these views to:

• Evaluate the impact of changing inventory policies (for example reducing days on hand).

• Assess whether improvements in capacity or forecasting could reduce stock without increasing risk of stock outs.

7

Step 7: Use scenarios for policy and supply chain shocks

Clone the base model into scenario models that reflect different inventory strategies or supply conditions, for example:

• Reduced days on hand through better planning.

• Higher safety stock due to supply chain risk.

• Changes in lead times from key suppliers.

Adjust inventory drivers and compare:

• Inventory levels and working capital tied up.

• Cashflow volatility and minimum cash positions.

• Ability to meet sales plans given production and supply constraints.

Check your work

• Inventory policy drivers reflect realistic operational practice, not just theoretical targets.

• Inventory levels and days on hand for historical periods resemble what is seen in actual stock reports when you calibrate the model.

• Changes to policy or volumes produce expected directional changes in inventory and cash.

• The complexity of the structure is proportionate to the business size and decision needs.

Troubleshooting

Inventory levels appear unrealistically high or low

Check that COGS per period is correctly calculated, that days on hand or turns are realistic, and that unit conversions are handled correctly.

Inventory metrics are too noisy

Consider smoothing inputs or using a slightly more aggregated view by product family or plant.

Difficult to reconcile to accounting inventory balances

Treat this pattern as a planning tool. Use it for forward looking decisions and policy analysis rather than expecting exact historical reconciliation without detailed calibration.

Related guides

• Build a Working Capital Model

• Build a Capital Raise Model

• Working Capital

• How to Enter Drivers