# Working Capital Model Example Build a working capital model to optimise your cash conversion cycle, stress-test supply chain scenarios, and evaluate financing strategies like factoring or dynamic discounting to maximise free cash flow. - Canonical: https://finamodel.com/examples/working-capital-model - Excel download: https://finamodel.com/templates/working-capital.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Beginner - Audiences: CFOs & FP&A, Founders & operators, CFOs, Treasurers, Credit analysts, Corporate planners - Tags: DSO, DIO, DPO, cash-cycle, optimization ## Overview Track the cash conversion cycle (CCC) using Days Sales Outstanding (DSO), Days Inventory Outstanding (DIO), and Days Payable Outstanding (DPO) to forecast working capital needs and optimize cash flow. AR balance is calculated as (Revenue / 365) × DSO; inventory balance as (COGS / 365) × DIO; AP balance as (COGS / 365) × DPO. The CCC measures how many days elapse between paying suppliers and collecting from customers; negative CCC (customers pay before suppliers are due) is a source of cash. The workbook includes a roll-forward schedule for each working capital component, calculates the cash impact of changes in AR/Inventory/AP on the cash flow statement, and expresses working capital as a percentage of revenue. Sensitivity tables show how 5-day improvements in DSO, 10-day reductions in DIO, or extended DPO terms impact free cash flow and cash conversion efficiency. Typical manufacturing CCC ranges from 30–90 days; subscription businesses often achieve negative CCC (10–30 days). Key metrics: CCC trends (positive = cash tied up, negative = cash generated), cash impact quantification, and optimization levers ranked by impact (DIO improvement typically yields largest cash release). This model is essential for working capital optimization, supply chain finance programs, and cash flow forecasting in operating businesses. ## What's included - DSO, DIO, and DPO driver-based forecasting - Cash conversion cycle and operating cycle analysis - Accounts receivable ageing and bad debt modelling - Inventory safety stock and lead-time calculations - Supply chain finance and receivables factoring impact analysis - Days sales outstanding (DSO), days inventory outstanding (DIO), and days payable outstanding (DPO) - Accounts receivable (AR), inventory, and accounts payable (AP) calculations - Cash conversion cycle analysis and trends - Working capital as a percentage of revenue - Cash impact of working capital changes in cash flow statement - Scenario analysis on collection improvements, inventory optimization, and supplier terms ## Working Capital Model: How the Template Works This working capital model is a five-year management tool for a mid-market wholesale distributor funded by an asset-based revolver. It answers how much facility the business needs, when, and against what collateral, by linking operating drivers to net working capital, borrowing-base availability and cash flow. The public download is a values-only preview. ### Operating drivers behind the working capital balances 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. ### From operating drivers to borrowing-base availability 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. ### Calculation flow and the circularity break 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 and practical use 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. ## Built for liquidity optimisation Use this model when you need to identify where capital is tied up in the operating cycle and quantify the impact of releasing it. ## Goes deeper than a standard cash flow forecast A working capital model drills into balance sheet drivers to improve the velocity of cash, not just track its movement. ## Useful for M&A and operational improvement This gives you the framework for normalised working capital analysis in transactions and for designing internal cash acceleration programmes. ## Built for liquidity optimisation Use this model when you need to identify where capital is tied up in the operating cycle and quantify the impact of releasing it. ## Goes deeper than a standard cash flow forecast A working capital model drills into balance sheet drivers to improve the velocity of cash, not just track its movement. ## Useful for M&A and operational improvement This gives you the framework for normalised working capital analysis in transactions and for designing internal cash acceleration programmes. ## Features - **DSO/DIO/DPO mechanics:** Calculate AR, inventory, and AP based on these key metrics, so working capital changes flow through automatically as revenue, COGS, and purchasing change. - **Cash conversion cycle tracking:** Monitor DSO + DIO - DPO to see the cash flow implications of collection delays, slow inventory turns, or fast payment of suppliers. - **Optimization scenarios:** Model the cash benefit of improving DSO (faster collections), reducing DIO (better inventory management), or negotiating longer DPO (slower payables). ## Use cases - **Credit facility sizing:** Use peak working capital requirements to determine the size and terms of revolving credit facilities. - **Operational improvement initiatives:** Identify working capital optimization opportunities (faster collections, inventory reduction, extended payables) and model their cash impact. - **Strategic planning and forecasting:** Include working capital changes in cash flow forecasts to show the true cash generation of the business after funding growth. ## Frequently asked questions ### What is a working capital model? It is a model focused on the efficiency of operating assets and liabilities, analysing DSO, DIO, DPO, and the cash conversion cycle to identify liquidity improvement opportunities. ### Who uses working capital models? CFOs, treasurers, private equity operating partners, and financial controllers use them for liquidity management and transaction due diligence. ### What should a working capital model include? It should include receivables, inventory, and payables analysis, cash conversion cycle outputs, and scenario testing for supply chain or credit policy changes. ### Can I evaluate early payment discounts? Yes. The model includes ROI calculations for dynamic discounting, helping you determine whether early payment is more cost-effective than alternative uses of cash. ### Does it support segmented analysis? Yes. You can assign different DSO, DIO, and DPO assumptions to different business units or product categories to reflect diverse operating realities. ## Related templates - [3 Statement Model](https://finamodel.com/templates/3-statement-model) - [Cashflow Model](https://finamodel.com/templates/cashflow-model) - [Unit Economics Dashboard](https://finamodel.com/templates/unit-economics-model)