Inventory & Replenishment Planning

This use case explains how to represent retail inventory and replenishment planning inside Model Reef.

Model Reef does not have a dedicated inventory ledger module, but you can approximate inventory behaviour using COGS timing, drivers and working capital style variables. The aim is to forecast purchases, cost of goods and cash requirements for stock holding in a way that is good enough for planning and scenario analysis.

When to use this pattern

Use this pattern when:

• You have meaningful stock holdings and want to understand cash tied up in inventory.

• You need to plan purchases and replenishment cycles as part of cashflow and funding discussions.

• You are comfortable with an approximation rather than full inventory accounting.

If you need detailed SKU level stock control, you will generally run that in a specialist system and feed summary series into Model Reef.

Architecture overview

You will build four elements:

1

Demand drivers

• Expected sales volumes by category or store.

• Seasonality patterns and promotions.

2

Inventory policy drivers

• Target weeks or months of cover.

• Reorder points and lead times.

• Safety stock assumptions.

3

Purchase and COGS variables

• Purchases modelled as COGS or as Asset purchases depending on detail required.

• COGS driven by sales volumes and unit costs.

4

Working capital approximation

• Inventory related timing differences captured through COGS delays and, optionally, Asset style inventory balances.

1

Step 1: Create demand and unit cost drivers

In the Data Library, define drivers for:

• Sales volume by product category or store, for example:

  • Driver - Units - Store 01 - Category A.

• Unit cost per category, for example:

  • Driver - Unit Cost - Category A.

These drivers will be used to derive both revenue (via units and price) and COGS (via units and unit cost).

If detailed volume data lives in another system, import it as a CSV series and tag it clearly.

2

Step 2: Define inventory policy assumptions

Inventory policies can be approximated using:

• Target cover, for example:

  • Weeks of Cover - Category A.

  • Months of Cover - Category B.

• Lead times from order to receipt, for example:

  • Lead Time - Category A.

Create these as Data Library drivers, often as Modifier type, for example:

• Assumption - Weeks of Cover - Category A.

• Assumption - Lead Time - Category A.

Document the rationale in notes so that changes can be reviewed in future.

3

Step 3: Estimate required purchases

At a planning level, required purchases per period can be approximated as:

• Required Purchases = Forecast COGS plus Change in Desired Inventory.

You can represent this by:

• Using units:

  • Compute required units = forecast sales units plus change in target stock units.

  • Multiply by unit cost to get purchase cost.

• Or using value only:

  • Model COGS as a percentage of revenue and adjust timing and magnitude using working capital assumptions.

In Model Reef, it is usually enough to create COGS variables that:

• Are driven by sales volumes and unit costs.

• Use timing and delays to approximate when cash leaves the business for purchases.

For more detail, you can add an Asset variable for Inventory and manually link purchases and COGS to changes in that balance, acknowledging that this is an approximation rather than fully automated inventory accounting.

4

Step 4: Model cash timing for purchases

Use COGS timing and delay settings to represent payment terms with suppliers, for example:

• COGS - Merchandise Purchases with:

  • Occurrence based on expected purchase timing.

  • Delay representing supplier credit terms (for example 30 days or 60 days).

This creates Accounts Payable and captures the effect of inventory purchases on cashflow and working capital without tracking per SKU stock movements.

You can adjust the pattern of purchases to reflect known pre season buy ins or long lead time imports.

5

Step 5: Represent inventory build up and run down

If you want to see a notional inventory balance:

• Create an Asset variable named, for example, Inventory Balance.

• Use a simple rule such as:

  • Inventory balance equals a certain number of weeks or months of forecast COGS.

• Link this to cash by treating inventory changes as affecting working capital, for example:

  • Increase inventory when building up for peak periods.

  • Decrease inventory when running it down in off season.

This approximates the cash tied up in stock without implementing full inventory accounting rules.

6

Step 6: Build an inventory focused dashboard

Create a dashboard that shows:

• Purchases, COGS and notional inventory balance over time.

• Cashflow impact of inventory, for example in the Cash Waterfall via working capital changes.

• Key assumptions such as weeks of cover and lead times as prominent KPIs.

Use this dashboard in planning meetings to discuss inventory and cash strategies without diving into line by line stock details.

Check your work

• Demand and unit cost drivers align with how the business plans merchandising.

• Purchases and COGS patterns look reasonable with respect to known buying cycles.

• Cash outflows for inventory are captured with realistic timing in the Cashflow Statement.

• If using an inventory balance approximation, the values are in a plausible range relative to revenue and COGS.

Troubleshooting

Inventory balance looks implausible

Revisit your weeks or months of cover assumptions and unit cost estimates. Small changes can have large effects.

Cash crunch appears in the model but not in reality

Check payment terms and whether certain purchases are financed or deferred in ways not yet reflected in the model.

You need highly granular SKU behaviour

Keep detailed stock management in a dedicated system and feed only aggregated series such as total purchases, total COGS and category level volumes into Model Reef.

Related guides

• Build an Annual Planning Pack

• Build an Inventory Timing Model

• Authentication (OAuth)

• Drilldown Behaviour