Working Capital Schedules in a 3-Statement Model: AR, AP, Inventory That Actually Tie

📦 Quick Summary

• Working capital is where most three-statement model builds break down because timing logic is scattered across tabs instead of owned by schedules.
• A reliable schedule outputs two things per line: closing balance and change vs prior period (the change is what drives cash).
• AR should be driven by revenue and collection timing; AP by COGS/opex and payment timing; inventory by purchasing/COGS timing.
• Don’t hard-code “days” inside statements-keep days/turns assumptions in one driver layer so your financial model stays auditable.
• The cash flow should reference schedule deltas, not recalculate them, or your financial statements will drift over time.
• Inventory is the most common offender: lead times and reorder cadence create cash timing that simple “days” math can miss.
• Treat exceptions explicitly (deferred revenue, accruals, prepaids), so your finance statement outputs don’t hide timing gaps.
• Build one check: working capital deltas in cash flow must equal the balance sheet movement (every period).
• If you’re short on time, model collections and inventory timing explicitly before you debate templates.

⏱️ Introduction to core concept, what the topic matters

In a 3-statement financial model, working capital is the bridge between “we sold it” and “we got paid,” between “we consumed it” and “we paid for it.” That’s why AR/AP/inventory schedules are less about accounting and more about operational timing. When teams try to shortcut that timing with a few lines on the cash flow statement, the model might tie once, but it won’t stay tied after the second reforecast.

A schedule-first approach fixes this: assumptions live once, balances roll forward predictably, and the cash impact is the delta, no debate, no hidden logic. If you anchor working capital assumptions to clear drivers (volumes, terms, cadence), it becomes easier to run scenarios and explain cash outcomes without rewriting formulas every cycle.

🧾 Simple framework that you’ll use

Use the same repeatable structure for each working capital line:

1. Balance build – calculate closing AR/AP/inventory from operational drivers (revenue, purchases, COGS, terms, lead times).
2. Delta calculation – compute change vs prior period (closing − opening).
3. Cash mapping – map the delta into operating cash flow with the correct sign.
4. Tie-out – confirm the balance sheet line equals the schedule closing, and cash flow uses the schedule delta (not a second calculation).

This framework matters because it prevents “logic duplication,” the #1 cause of drift in three-statement model builds. If you’re aligning your cash flow to balance sheet movements more broadly, this framework plugs directly into the cash proof method.

🛠️ Step-by-step implementation

Step 1: 🧮 Decide your approach: days-based, turnover-based, or bucket-based

Pick the simplest approach that matches your business reality. Days-based (DSO/DPO/DIO) works well for stable terms and steady volumes. Turnover-based is useful when inventory dynamics are better expressed as turns. Bucket-based (e.g., 30/60/90-day ageing) is best when collections or payments are lumpy or when you want realistic cash timing.

The mistake is mixing methods without noticing: using days for AR, buckets for AP, and a hard-coded “inventory plug” for the rest. Choose deliberately, document the rationale, and keep assumptions in the driver layer so updates don’t require rewriting schedule math.

If AR collections are a major cash driver, modelling receipts explicitly (rather than implied by DSO) often improves accuracy immediately.

Step 2: 📈 Build AR from revenue and collections timing (not from “sales ÷ 365”)

AR should be driven by what you invoice and when you collect. For a simple days-based model: AR = revenue × (DSO/days in period). For bucket-based: allocate revenue into collection buckets (current month, +30, +60, +90) and roll forward the unpaid portion into AR.

The key is consistency: AR schedule outputs closing AR and AR change. Then the cash flow references AR change with the correct sign (AR increase is a cash outflow). Avoid calculating the AR delta in two places-use the schedule output only. This keeps your financial statements stable when assumptions change.

If you use Model Reef, keeping AR logic modular helps teams update terms without breaking downstream statement references across versions.

Step 3: 🧾 Build AP and inventory as a paired system (purchase timing matters)

AP and inventory are linked through purchasing and payment timing. If you model inventory as “COGS × DIO,” but never model purchases, you’re missing the mechanism that creates AP and cash outflows. A cleaner build is:

• Start with a purchase schedule (often derived from COGS + inventory change).
• Set payment timing (DPO or buckets) to generate AP closing balances.
• Roll inventory forward using purchases and COGS timing.

This approach reduces the biggest inventory cash timing error: assuming inventory is a simple ratio when lead times and reorder cadence create lags. If inventory is material, use an explicit cash timing approach (PO timing, lead times, reorder cycles) rather than only “days” math.

Step 4: ✅ Map deltas to cash flow and add a tie-out that fails loudly

Once AR/AP/inventory schedules output closing balances, compute deltas and map them to operating cash flow:

• AR delta: increase = cash outflow; decrease = inflow
• Inventory delta: increase = outflow; decrease = inflow
• AP delta: increase = inflow; decrease = outflow

Then add one tie-out check: the cash flow working capital lines must exactly match the schedule deltas, and the balance sheet working capital lines must exactly match the schedule closing balances. This check should be unmissable.

If you’re already using a checks framework across your 3 statement financial model, incorporate working capital tie-outs alongside balance sheet and cash reconciliation checks.

Step 5: 🔁 Stress-test working capital under scenarios (without duplicating files)

Working capital assumptions are scenario-sensitive: DSO can deteriorate in a downturn, DPO can tighten under supplier pressure, and inventory can rise under demand shocks. A robust three-statement model should let you stress these assumptions without rebuilding schedules.

Keep scenarios at the driver layer: toggle terms, turns, and timing assumptions, then let schedule outputs flow into the financial statements automatically. This is where controlled scenario logic matters more than extra complexity, especially if multiple stakeholders review the model. A downside toggle framework helps you run base/upside/downside cases without creating separate “scenario spreadsheets” that drift.

For teams using Model Reef, this is also where governance helps: scenarios branch cleanly while the core schedule logic stays consistent across versions.

🏭 Examples and real-world use cases

In growth periods, working capital is often the first constraint, not EBITDA. Revenue can rise while cash falls because AR and inventory absorb cash faster than AP releases it. A schedule-driven approach makes that dynamic visible: you can show leaders exactly how terms and inventory policy translate into cash needs and funding timing.

This is particularly useful in operating reviews: sales sees bookings, operations sees supply, and finance sees cash. A clean working capital schedule reconciles these views into one story inside the 3 financial statements. If you’re modelling growth and want working capital drag, capex, and financing needs to tell a coherent cash story, connect the schedule outputs into a growth-focused cash flow view.

🚧 Common mistakes and how to avoid them

1. AR/AP/inventory deltas computed twice (once in schedule, once in cash flow) → drift over time. Use schedule deltas only.
2. Inventory without purchase timing → AP becomes arbitrary, and cash timing is wrong. Model purchases explicitly.
3. DSO/DPO/DIO treated as constants → no scenario sensitivity. Make them drivers with scenario toggles.
4. Missing timing items (accruals, deferred revenue, prepaids) → operating cash looks unexplained. Schedule them where material.
5. Wrong sign conventions → increases show as inflows. Lock sign rules and test them with a simple one-period example.

If you’re building the indirect cash flow statement, working capital timing errors show up immediately when you add deltas back to net income.

❓ FAQs

➡️ Next Steps

Once AR/AP/inventory schedules tie, operationalise how they get updated: decide who owns the assumptions (terms, turns, buckets), how often they’re reviewed, and what triggers a scenario refresh (sales mix changes, supplier term changes, inventory policy changes). Then keep those assumptions in one driver layer so reforecast cycles don’t turn into formula rewrites.

If you’re scaling collaboration, approvals, and version control around these schedules, connect the workflow to a structured product workflow layer, so updates remain governed and traceable across reviewers.