Financial model review
Working capital checks for financial models
Check receivables, inventory and payables in an Excel financial model. Use a worked example to trace working capital movements into the cash flow forecast.

Check working capital in a financial model by defining the included balances, reconciling their movements and testing how those movements affect cash. More revenue does not necessarily mean more cash in the same period. Customers can pay later, stock can build up and supplier balances can change.
This invented trading example uses credit sales and purchases, excluding taxes, write-offs, returns, foreign exchange and other adjustments. Real models need explicit treatment of their actual transactions.
Define working capital before selecting cells
The broad balance-sheet definition is current assets less current liabilities, as explained in the SEC's financial statements guide. Financial models often use a narrower operating measure to explain cash movements. Write down exactly what your model includes instead of treating these definitions as interchangeable.
For this example, operating working capital means:
Trade receivables + Inventory - Trade payables
Cash, borrowing, taxes and other balances are excluded. Receivables, inventory and payables are stored as positive balance amounts. An increase in either of the first two uses cash in this simplified bridge; an increase in payables provides a timing benefit to cash. This sign relationship assumes that the movements contain no excluded non-cash adjustments.
Work through the balance movement
Use these monthly balances:
- Trade receivables: EUR 50,000 opening; EUR 65,000 closing.
- Inventory: EUR 30,000 opening; EUR 42,000 closing.
- Trade payables: EUR 25,000 opening; EUR 32,000 closing.
Opening operating working capital is EUR 55,000. Closing operating working capital is EUR 75,000. The increase is EUR 20,000:
Opening = 50,000 + 30,000 - 25,000 = EUR 55,000
Closing = 65,000 + 42,000 - 32,000 = EUR 75,000
Increase = EUR 20,000
Cash effect = -15,000 - 12,000 + 7,000 = minus EUR 20,000
Compare that cash effect with the model's cash flow line. If it shows a positive EUR 20,000 contribution, inspect whether the increase was added instead of subtracted. If the payable balance is already negative in the workbook, adapt the bridge consistently rather than subtracting it twice.
Cross-check collections and supplier payments
Assume monthly credit sales are EUR 120,000 and cost of goods sold is EUR 72,000. With the same exclusions as above, customer receipts follow from the receivables movement:
Collections = Opening receivables + Credit sales - Closing receivables
= 50,000 + 120,000 - 65,000
= EUR 105,000
Purchases differ from cost of goods sold because inventory increases:
Purchases = Cost of goods sold + Closing inventory - Opening inventory
= 72,000 + 42,000 - 30,000
= EUR 84,000
Supplier payments = Opening payables + Purchases - Closing payables
= 25,000 + 84,000 - 32,000
= EUR 77,000
Receipts less supplier payments are EUR 28,000 before all other costs. Sales less cost of goods sold are EUR 48,000. Subtracting the EUR 20,000 working-capital increase from that EUR 48,000 gives the same EUR 28,000.
These two routes check a shared relationship. If the cash forecast already uses EUR 105,000 of receipts and EUR 77,000 of supplier payments, subtracting EUR 20,000 again would double count the timing effect. The cash flow guide explains why identifying the model's starting point is essential.
Inspect collection and payment assumptions
A days assumption is only useful when its denominator and period are defined. Does a collection-days calculation use credit sales, all sales or a tax-inclusive amount? Does a payable-days calculation use purchases or cost of sales? Inventory growth can make purchases and cost of sales materially different.
For a 30-day month with uniform EUR 120,000 credit sales, ten additional days of receivables imply EUR 40,000 more outstanding, using 120000/30*10. This assumes a steady sales rate. Uneven billing, seasonality and collection patterns may require an invoice or aging schedule instead.
Avoid mixing annual sales with a monthly day multiplier, or using a closing balance as though it were an average without labeling the approximation. The units and periods guide covers these alignment checks in more detail.
Check detail schedules before trusting totals
If the model aggregates customer or supplier records, compare a few included and excluded rows with the intended period and category. Check that payments are classified by payment date when that is the metric, rather than automatically by invoice date.
Microsoft's SUMIFS guide explains adding amounts that meet multiple criteria. A formula using that function still needs the right amount range, date field and boundaries. Inspect records on the first and last day of a period, and confirm that an item is not included in two periods.
Check that opening receivables and payables survive the forecast start. New sales and purchasing forecasts should not erase existing invoices or obligations.
Keep a short review workflow
- Define the included balances, currency and positive or negative sign convention.
- Reconcile each opening balance to its closing amount using actual model drivers.
- Compare the working-capital cash effect with collections and payment schedules.
- Check the denominators, periods and timing assumptions behind any days metrics.
- Test slower customer payment in a copy and explain the cash effect.
- Restore the base case and document exclusions or unresolved differences.
For this article's fixed closing-balance example, increasing closing receivables by EUR 5,000 with sales and everything else held constant reduces collections and cash by EUR 5,000. Real linked models can add financing or other effects, which need separate explanation.
Frequently asked questions
Does higher working capital always mean a mistake?
No. Growth, seasonality or deliberate stock purchases may explain it. Review the reason and cash consequence rather than treating an increase as automatically wrong.
Can a model use cost of sales for payable days?
It can be a stated approximation, but it differs from a purchases-based measure when inventory changes. Understand the intended definition and its limitations.
Will FinModelCheck validate these working-capital formulas?
No. It does not reconcile working capital, assess collection assumptions or recalculate Excel. It observes supported saved errors, broken references, external workbook links, embedded coefficients, broken names and saved manual-calculation metadata. Some observations are intentional review items.
Try the checker for those supported checks and see the example report. Online results show only the provisional score and type counts; a complete PDF is free in the current valid owning browser session without registration. Use those observations alongside this manual financial review.


