Working Capital Model Example

The model is built around the operating choices that tie up cash rather than a simple P&L forecast. Revenue growth, COGS percentage, and three day-count levers — DSO, DIO and DPO — drive the trade balances.

• Those levers are split between opening actuals and plan bases, so improving DSO does not reduce the opening balance and the cash release is not self-cancelling. A settlement-discount blend adjusts applied days: customer discount uptake pulls days out of DSO, while supplier discount participation reduces DPO.
• The receivables ageing mix and per-bucket loss rates feed an allowance and bad debt charge, so net receivables, not gross, drive net working capital. This structure makes the balance sheet the binding constraint.

The model converts gross receivables and inventory into a collateral-backed borrowing base. Receivables are aged into buckets, with haircuts for past-due, cross-age and concentration excess, then an advance rate.

• Inventory is adjusted for slow-moving and in-transit stock, then valued at NOLV and an advance rate, capped by a sublimit. Reserves for dilution — write-offs plus settlement discounts over revenue — and rent are deducted.
• Availability is the lower of the net borrowing base and the facility commitment. This is the cap on drawdown, so the model sizes the facility against eligible collateral rather than the commitment alone.

A seasonal peak block then tests intra-year needs against year-end availability.

The model records a five-year P&L, a working capital schedule with a Year 0 opening column, a borrowing base, a cash conversion analysis, a funding analysis, and a summary. A key design choice is breaking the revolver circularity structurally rather than with iterative calculation.

• Cash flow before financing uses pre-tax working capital movement, depreciation, capex and EBIT struck above the financing line, so interest cost never feeds back into the cash need. Interest is then charged on an average facility balance derived from an indicative draw, which is determined before financing.
• This keeps the workbook acyclic and avoids iterative settings, while still charging interest on a realistic average balance.

Outputs include net working capital and its change, DSO, DIO, DPO and the cash conversion cycle, borrowing-base availability and drawn revolver, free cash flow, liquidity ratios, and covenant tests such as FCCR and leverage.

• A terms-sensitivity block prices one day of each metric in cash and interest against programme targets, and a settlement-discount decision compares the implied APR of a supplier discount with the revolver rate. The seasonal peak block exposes the intra-year facility need that annual balances hide.
• A dashboard, summary and validation checks support review. The model is best used to explore how operating terms and collateral shape funding needs, not to promise live formulas in the public preview.