Working Capital and Cash Flow Model Preparation

Working capital and cash flow model preparation is where most financial models quietly fail. Revenue and margin assumptions get debated for weeks, while receivables, inventory, payables and tax balances are filled in as one percentage that never ties to the bank account. In India the gap is wider, because GST credit, TDS receivable and MSE payables sit in working capital and follow statutory clocks. This guide covers what belongs in the schedule, which forecasting method fits which business, a worked three-year example, the indirect cash flow link, and how the same model becomes CMA data for a bank.

What is working capital and cash flow model preparation?

Working capital and cash flow model preparation is the process of forecasting each operating current asset and liability from its own driver (days of revenue, days of cost, or a statutory rule), then deriving cash flow from the period-on-period change in those balances. A correctly built model reconciles closing cash on the balance sheet with the cash flow statement in every period, with no plug, in the format required by Ind AS 7 or AS 3.

Operating working capital vs net working capital: what belongs in the schedule

The schedule should forecast operating working capital only: receivables, inventory, payables and the operating tax and advance balances around them. Cash, short-term borrowings, current maturities of long-term debt and interest accrued are excluded, because the model derives them elsewhere. Including them double-counts financing and breaks the cash reconciliation.

Accounting net working capital (current assets less current liabilities, as presented under Schedule III to the Companies Act 2013) mixes operating and financing items. A model that forecasts that figure as a percentage of revenue will grow cash and bank debt with sales, which is circular and wrong. The first job in any build is reclassifying the trial balance into operating, investing and financing buckets.

Table 1: classification of balance sheet items for the working capital schedule

Balance	Include in operating working capital?	Where it belongs instead	Common misclassification
Trade receivables, unbilled revenue	Yes	Not applicable	Unbilled revenue left in “other current assets” with no driver
Inventory	Yes	Not applicable	Capital spares included in inventory
Trade payables	Yes	Not applicable	Capex creditors left inside trade payables
GST credit, TDS receivable, statutory dues	Yes, as separate lines	Not applicable	Netted into one “other current assets” line
Customer advances, deferred revenue	Yes	Not applicable	Treated as debt
Creditors for capital goods	No	Investing (capex schedule)	Inflates DPO and understates capex cash
Cash credit, WCDL, current maturities of term loans	No	Financing (debt schedule)	Shown as current liabilities that grow with revenue
Interest accrued, dividend payable	No	Financing	Included in “other current liabilities”
Security deposits, loans to related parties	No	Investing	Forecast as a % of revenue

The capex creditor point matters most in manufacturing. A plant expansion paid on 90-day terms inflates payable days in the year of purchase, and if left in trade payables the model carries that inflated DPO into every forecast year.

How to structure a cash flow model before entering a number

A cash flow model should run in a fixed sequence: inputs, historical analysis, driver schedules, statements, checks. Separating layers lets a reviewer change one assumption and trace its effect, and it is the structure lenders and investors expect in a data room.

Table 2: layer structure for a working capital and cash flow model

Layer	Contents	Common error	Review test
Inputs	Days, GST rates, credit terms, capex plan, minimum cash	Hard-codes scattered across schedules	Every schedule number traces to inputs
Historical analysis	3 audited years plus trailing 12 months of MIS and ageing reports	Year-end balances only, missing seasonality	Monthly days recomputed from MIS
Working capital schedule	One line per operating balance	Single “net working capital %” line	Each line has its own driver
Debt and interest schedule	Term loans, cash credit, WCDL	Circular reference left unmanaged	Circularity switch on inputs
Statements	P&L, balance sheet, indirect cash flow	Cash flow built separately from balance sheet	Closing cash matches across statements
Checks	Balance check, cash reconciliation, sign checks	Checks not linked to cover	Single pass or fail flag

Build months for the first 24 months and years thereafter. A bank sanctioning cash credit cares about the peak requirement, not the 31 March position, and annual models hide it. For weekly liquidity, keep a separate 13-week cash flow forecast rather than adding weekly columns to a five-year model. Use one colour for inputs and another for formulas, and never mix a hard-code into a formula row; reviewers test this first, as our guide to financial model validation before a data room sets out.

Which working capital forecasting method should the model use?

Use days-based drivers for receivables, inventory and payables in most operating businesses, line-item or ageing-based forecasts where a few customers dominate, and a per-period receivable-versus-received adjustment for project and infrastructure models. A flat percentage of revenue is acceptable only as a sanity check, never as the forecast itself.

Table 3: working capital forecasting methods compared

Method	How it works	Best fit	Weakness
Days (DSO, DIO, DPO)	Balance = flow for period x days / days in period	Most trading, manufacturing, services businesses	Hides concentration and seasonality if set annually
Percentage of revenue	Net working capital = fixed % of revenue	Long-range years 4 to 5, sanity checks	No link to terms; cannot be defended in diligence
Ageing or line-item	Collections forecast by customer or ageing bucket	Top 10 customers above 50% of revenue	Labour-intensive beyond 12 to 18 months
Per-period adjustment	Receivable in period less received in period, applied below EBITDA	Project finance, infrastructure, CFADS models	Less intuitive for operating management
Turnover (bank view)	Requirement as % of projected turnover	Bank assessment for smaller limits	An output for lenders, not a forecasting method

Many good models combine methods: ageing-based collections for the first two quarters (feeding the 13-week view), days for months 7 to 36, and a percentage check for outer years.

Building the working capital schedule line by line

Each working capital line needs a driver tied to the P&L line that creates it, computed from history and applied to the forecast. Receivables run off GST-inclusive revenue, inventory off cost of goods sold, payables off GST-inclusive purchases, and statutory balances off the rule that governs them. The ratio definitions themselves are covered in our guide to working capital management; this section covers how to model them.

How do you calculate days correctly for a model?

Four technical choices decide whether historical days are usable:

1. GST basis. Receivables and payables include GST; the P&L does not. Compute DSO on revenue grossed up for GST, or the forecast debtor balance will be understated by roughly the GST rate.
2. Purchases, not COGS, for payables. Where inventory moves materially, purchases equal COGS plus the change in inventory. Using COGS overstates DPO in a stock-building year.
3. Closing vs average balance. Closing balances are standard for forecasting because the model needs a closing balance sheet. Use average balances only for analysing history, and state which basis you used.
4. Seasonality. In a seasonal business, annual DSO on a December year-end is meaningless. Compute monthly DSO with the count-back method: subtract each prior month’s revenue from the debtor balance until it is exhausted, and count the months consumed. This gives a DSO that responds to the actual billing pattern.

Use 365 days (366 in a leap year if you model daily), or 30-day months consistently; mixing 360 and 365 across sheets is a frequent reconciliation break.

Statutory working capital lines: modelling treatment

The rules behind GST and TDS timing are explained in our working capital management guide. In the model, each needs its own line and mechanic:

• GST input credit: added with purchases and capex, utilised against output liability in the same month. Any surplus that will not be utilised operationally (inverted duty, heavy capex, exports) moves to a “refund receivable” line with a refund lag input, because refunds follow Section 54 of the Central Goods and Services Tax (CGST) Act 2017.
• GST output payable: output tax for the month, cleared in the following month (GSTR-3B due by the 20th for monthly filers).
• Credit reversal: where any vendor category is modelled beyond 180 days, the second proviso to Section 16(2) of the CGST Act requires reversal of credit until payment, so add a reversal line.
• TDS receivable: service receipts arrive net of TDS. Accumulate the deducted amount and release it against the tax liability or as a refund after a lag. For a loss-making company it is not a 12-month asset.
• TDS payable: deductions on vendor and salary payments, cleared the following month. The TDS provisions are re-enacted in the Income-tax Act 2025, mainly in section 393.
• Advance tax: for profitable years, outflows of 15%, 45%, 75% and 100% of estimated tax by 15 June, 15 September, 15 December and 15 March.

How does the MSME 45-day rule change the payables forecast?

Split trade payables into micro and small enterprise (MSE) suppliers and others, and cap MSE payable days at 45 (15 where there is no written agreement). Section 15 of the Micro, Small and Medium Enterprises Development (MSMED) Act 2006 sets the limit, and section 37(2)(g) of the Income-tax Act 2025 (formerly Section 43B(h) of the 1961 Act) allows the expense only on payment if it is breached (Ministry of Finance parliamentary reply, 21/07/2026).

Where the forecast leaves MSE dues unpaid at 31 March, add the amount back in the tax computation for that year so current tax and advance tax rise. Only the non-MSE bucket can carry a “stretch payables” scenario.

Worked example: a three-year working capital schedule

The example below shows a business growing from ₹100 crore to ₹218.4 crore revenue, with GST at 18%, COGS at 60% of revenue and EBITDA at 12%. Days are input assumptions; every balance is a formula.

Table 4: illustrative working capital schedule (₹ crore)

Line	FY 2025-26 (actual)	FY 2026-27	FY 2027-28	FY 2028-29
Revenue	100.0	140.0	182.0	218.4
DSO / DIO / DPO (days)	60 / 45 / 50	60 / 45 / 50	65 / 45 / 45	55 / 40 / 45
Trade receivables (revenue x 1.18 x DSO / 365)	19.40	27.16	38.24	38.83
Inventory (COGS x DIO / 365)	7.40	10.36	13.46	14.36
Trade payables (purchases x 1.18 x DPO / 365)	9.70	14.06	16.34	19.19
Operating working capital	17.10	23.46	35.37	34.00
Increase / (decrease) in working capital	Not applicable	6.36	11.91	(1.37)
EBITDA (12%)	12.0	16.8	21.8	26.2
Pre-tax operating cash flow (EBITDA less increase)	Not applicable	10.44	9.93	27.58
Cash conversion (operating cash flow / EBITDA)	Not applicable	62%	45%	105%

The reading is the point. In FY 2027-28 EBITDA rises 30% but operating cash flow falls, because DSO slips five days and payable days tighten five days at the same time. In FY 2028-29 a ten-day improvement in the cash conversion cycle releases cash even as revenue grows 20%. An investor will ask for the operational reason behind both movements; a model that cannot answer will have its days reset to history.

Building the full integrated model around this schedule? Our three statement financial model services page sets out how the schedules link.

How working capital behaves across business models

The same schedule mechanics apply everywhere, but the dominant line differs by business model, and several Indian models run negative working capital. The schedule should be built around the line that moves cash most.

Table 5: dominant working capital lines by business model

Business model	Dominant line	Typical sign	Modelling point
B2B SaaS, annual billing	Deferred revenue	Negative (customers fund you)	Forecast billings separately from revenue; deferred revenue = billings less recognised revenue
Marketplace	Payables to sellers	Negative	Settlement cycle drives cash; model payout days to sellers as a contractual input
D2C or consumer brand	Inventory	Positive, large	Model by SKU group or channel; marketplace receivables settle on platform cycles
Manufacturing	Raw material, WIP, finished goods	Positive	Separate inventory days by stage; capex creditors out of payables
Contracting and EPC	Unbilled revenue, retention money	Positive, lumpy	Model milestone billing and retention release dates, not DSO alone
Exporters	Export receivables, GST refund	Positive	Separate export DSO and a refund receivable line; realisation limits in our working capital management guide

Negative working capital is a funding source, not a problem, but it reverses when growth slows. A SaaS company whose billings growth falls below revenue growth will see deferred revenue shrink and cash fall even with stable profits. Run that scenario explicitly.

Linking the schedule to an indirect method cash flow

The indirect method cash flow starts from profit before tax, adds back non-cash items, then adjusts for the change in each working capital line. Because every adjustment is a balance sheet movement pulled by formula, the cash flow reconciles to the balance sheet by construction. Ind AS 7 and AS 3 both permit the indirect method for operating activities, and it is what auditors and lenders expect.

The operating section runs in this order:

1. Profit before tax.
2. Add back depreciation, amortisation, ESOP expense (non-cash under Ind AS 102) and non-cash provisions.
3. Remove finance costs and interest income (these sit in financing and investing).
4. Deduct increases in operating current assets; add increases in operating current liabilities, each pulled from the balance sheet as prior period less current period (assets) or current less prior (liabilities).
5. Deduct income tax paid, meaning advance tax and self-assessment tax, not the P&L charge.

The format of the statement itself is covered in our cash flow statement guide.

Why does a profitable model still show a cash shortfall?

Because growth absorbs working capital faster than profit replaces it. In Table 4, every extra rupee of annual revenue at 60 days DSO locks up about 19 paise in receivables before inventory. The model is working correctly when it shows this; the shortfall is the funding need to close with equity, debt or better terms.

Free cash flow and the normalised working capital peg

The same schedule feeds valuation and M&A. Free cash flow to the firm equals EBIT after tax, plus depreciation, less capex, less the increase in operating working capital; free cash flow to equity then adjusts for net borrowing and post-tax interest. Including cash or debt inside working capital here double-counts, which is why the classification in Table 1 comes first.

In an acquisition, the share purchase agreement usually sets a normalised working capital target (the peg), and the price moves rupee for rupee if closing working capital is above or below it. Model the peg as the trailing 12-month average of monthly operating working capital, adjusted for one-offs such as a delayed GST refund or a stock build ahead of a price rise. A peg built on year-end balances in a seasonal business can shift price materially in either direction.

Funding line, circularity and scenarios

Once operating, investing and committed financing flows are modelled, the model must solve for funding. A revolver (cash credit) line draws automatically when cash falls below the minimum balance and repays when surplus exists. Its peak drawing is the number a bank sanctions against.

The revolver creates a circular reference: interest depends on average debt, debt on cash, cash on interest. Either compute interest on opening balances, which removes circularity at a small accuracy cost on monthly models, or keep average-balance interest behind a circularity switch on the inputs sheet. Never leave iterative calculation on without a switch.

Set minimum cash as an explicit input, usually one to two months of fixed costs plus the next statutory payment cluster. Runway then equals cash above minimum divided by monthly net burn, as in our burn rate and runway guide.

Scenarios should move working capital, not only revenue. A 15-day rise in DSO often costs more cash than a 10% revenue miss. Minimum sensitivities:

• DSO plus 15 and plus 30 days.
• Largest customer paying one quarter late.
• Inventory build ahead of a festive season or price increase.
• Non-MSE vendors cutting terms by 15 days.
• GST refund delayed by two quarters.
• For negative working capital models, billings growth falling below revenue growth.

Report peak debt need and minimum cash under each case.

Working capital assessment for bank loans: CMA data from the same model

Banks assess limits on their own methods, so the model needs a lender view: projected current assets, current liabilities other than bank borrowings, the borrower’s margin, and permissible bank finance, presented as Credit Monitoring Arrangement (CMA) data. Build it as an output sheet reading from the same schedules, never as a separate file.

The Reserve Bank of India (RBI) withdrew the mandatory Maximum Permissible Bank Finance (MPBF) method in 1997 and left assessment to bank boards. Most banks still use one of three approaches:

Table 6: working capital assessment methods used by Indian banks

Method	Typical application	How the limit is computed	What the model must produce
Turnover method	MSE borrowers, smaller limits	Percentage of projected annual turnover, with borrower margin	Defensible next-year turnover
MPBF, second method of lending	Mid and large corporates	75% of (current assets less non-bank current liabilities); borrower funds 25% of current assets long term	Current asset and liability build by holding level
Cash budget	Seasonal and project businesses	Peak monthly cash deficit	Monthly cash flow with seasonality

For MSE borrowers, the RBI Master Direction on lending to the MSME sector has prescribed the turnover method at a minimum of 20% of projected annual turnover for limits up to ₹5 crore. For borrowers with aggregate fund-based working capital limits of ₹150 crore or more, the RBI Guidelines on Loan System for Delivery of Bank Credit (05/12/2018) require at least 60% to be drawn as a loan component from 01/07/2019, with a 20% credit conversion factor on undrawn cash credit.

What does CMA data contain?

CMA data is conventionally presented in six forms:

1. Form I: particulars of existing and proposed limits.
2. Form II: operating statement (past, current estimate and projections).
3. Form III: analysis of balance sheet.
4. Form IV: comparative statement of current assets and current liabilities, with holding levels in months.
5. Form V: computation of permissible bank finance.
6. Form VI: fund flow statement.

Forms II to VI should be direct reads from the model. Holding levels in Form IV are the model’s days expressed in months, so a mismatch between the two is visible immediately to a credit officer.

Which ratios do banks test in the model?

Banks commonly look for a current ratio of at least 1.33 (the outcome of the second method of lending), total outside liabilities to tangible net worth, debt service coverage for any term loan, interest coverage, and the trend in holding levels against history. Projected ratios that improve sharply without an operational reason are discounted. The projected first year must also reconcile to the last audited balance sheet and the latest stock statement filed with the bank.

Drawing power is the final link. Banks allow drawings against eligible stock and receivables after a margin (commonly 25%) and usually exclude receivables beyond a set age (often 90 days), related-party debtors and refund-dependent GST credit. Build drawing power from the ageing buckets in the receivables schedule, using the same ageing as Schedule III (amended by MCA notification dated 24/03/2021), so audited data, the model and the monthly stock statement agree.

Common mistakes in working capital and cash flow model preparation

Most defects are structural and surface in diligence or credit appraisal, when there is least time to fix them.

1. Forecasting accounting net working capital. Cash, cash credit and current maturities grow with revenue and the cash flow becomes circular. Forecast operating working capital only and let cash and debt fall out of the statements.
2. Leaving capex creditors in trade payables. DPO inflates in an expansion year and the inflated figure carries forward. Move them to the capex schedule.
3. Mismatched bases. Days computed on net revenue against GST-inclusive balances, or COGS used for payables in a stock-building year, misstate every forecast balance. Gross up and use purchases.
4. Annual days in a seasonal business. A year-end DSO hides the peak, and the bank limit is then undersized. Use monthly count-back DSO and a monthly model for the first two years.
5. A CMA file built separately from the model. Holding levels and ratios drift from the investor model, and credit committees compare them. Generate CMA forms as an output of the same schedules.

Working capital and cash flow model preparation: the build checklist

• Trial balance reclassified into operating, investing and financing items before any forecast.
• Forecasting method chosen per line and documented on the inputs sheet.
• Days computed on GST-inclusive revenue and purchases, with monthly count-back DSO where seasonal.
• MSE payables, GST credit (utilisable and refund-dependent), TDS and advance tax on separate lines.
• Indirect cash flow pulls every working capital movement from the balance sheet by formula.
• Closing cash matches across statements in every period, with no plug.
• Circularity switch and minimum cash input in place.
• Investor view, valuation view and CMA forms all read from the same schedules.

Frequently asked questions

Q: How is a cash flow model different from a cash flow statement?

A: The statement reports history; the model forecasts it. The model uses the Ind AS 7 or AS 3 indirect structure but derives each line from driver schedules, so the forecast statement reconciles to a forecast balance sheet.

Q: Should the model forecast net working capital or operating working capital?

A: Operating working capital. Cash, cash credit, current maturities of debt and interest accrued are financing items that the model derives from the cash flow and debt schedule; forecasting them as a percentage of revenue makes the model circular.

Q: How does the tax treatment of delayed MSE payments enter the model?

A: As an add-back in the tax computation. Amounts due to micro and small suppliers beyond the Section 15 MSMED Act limit and unpaid at 31 March are allowed only on payment under section 37(2)(g) of the Income-tax Act 2025, which raises current and advance tax in that year.

Q: Should a startup use the direct or indirect method?

A: Indirect for the multi-year model, direct for the 13-week liquidity forecast. The indirect method guarantees balance sheet reconciliation; the direct method shows weekly receipts and payments for operating decisions.

Q: How long does it take to prepare a working capital and cash flow model?

A: Typically two to four weeks with clean monthly MIS. The longest step is historical analysis: reclassifying the trial balance and rebuilding monthly days from ageing reports.

Q: What documents are needed to build the model?

A: Three years of audited financials, trailing 12 months of MIS, receivable and payable ageing, GSTR-3B and GSTR-2B, Form 26AS or the annual information statement, a vendor list with MSME status, a fixed asset and capex creditor register, and existing bank sanction letters and stock statements.

Q: How should foreign currency receivables and payables be modelled?

A: In foreign currency with an exchange rate input, then converted. Keep export receivables on their own DSO and model any GST refund on exports as a separate receivable; the applicable realisation limits are in our working capital management guide.

Q: How should a group with a holding company and operating subsidiaries be modelled?

A: Build schedules at each operating entity and consolidate. Intercompany receivables and payables must eliminate; leaving them in inflates current assets and liabilities and distorts the bank’s assessment.

Q: Can the model show negative working capital?

A: Yes, and for SaaS with annual billing or marketplaces it usually should. Model deferred revenue from billings and seller payables from settlement terms, and run a scenario where growth slows and the negative balance unwinds.

Q: What happens if the bank sanctions a lower limit than the model shows?

A: The peak deficit becomes a gap to fund elsewhere. Cap the revolver at the sanctioned limit, identify months where cash breaches the minimum, and plan bridge equity, invoice discounting or TReDS for receivables from large buyers.

Q: What will an investor test first in the working capital schedule?

A: Whether forecast days match history. Improvements must be tied to a specific action such as a collection process change or a customer mix shift, or the investor will reset days to historical levels and recut cash need.

Q: How is the normalised working capital peg set in an acquisition?

A: Usually as the trailing 12-month average of monthly operating working capital, adjusted for one-offs. The price moves rupee for rupee for any shortfall or excess at closing, so a peg built on seasonal year-end balances can shift value materially.

Q: How do ESOP exercises affect the cash flow model?

A: ESOP expense is non-cash and added back, but TDS on the perquisite value at exercise creates a payable the company deposits in the following month, and cash-settled awards or buybacks are financing outflows.

Q: How often should the model be updated?

A: Monthly against MIS actuals. Replace the oldest forecast month with actuals, recompute days, and roll the horizon forward; a model refreshed only at fundraising is stale when diligence starts.