Working capital formula: net and operating working capital in Excel

The formulas

• Net working capital (NWC) = current assets − current liabilities
• Operating working capital (also non-cash or trade working capital) = (receivables + inventory + other operating current assets) − (payables + accrued expenses + other operating current liabilities)
• Change in working capital = this year's working capital − last year's
• Working capital ratio = current assets ÷ current liabilities, the same as the current ratio

Current means due or expected to turn into cash within twelve months. Current liabilities include the part of long-term loans that must be repaid within the next year, which is easy to miss when the loan is shown as one line.

The two versions answer different questions. Net working capital is a liquidity cushion: are there more short-term assets than short-term obligations? Operating working capital measures how much money the business model itself needs to keep goods and invoices moving, regardless of how it is financed. That is why forecasts, valuations and cash flow statements use the operating version.

Step by step in Excel

1. List the current assets in rows 2–5 and the current liabilities in rows 7–9, one column per year.
2. Totals with =SUM(B2:B5) and =SUM(B7:B9).
3. Net working capital: =B6-B10.
4. Operating working capital: =B3+B4+B5-B7-B8, the operating lines only, without cash in row 2 and debt in row 9.
5. Change column: =C2-B2, filled down.
6. Ratio to revenue: =B12/B13.

Keep the operating formula as an explicit list of lines, not as a total minus a few items. If a new balance sheet line is added later, an explicit list makes you decide consciously whether it is operating or financing.

Worked example: working capital of a wholesaler over two years

Two year-end balance sheets of a fictional wholesaler (yellow). Net working capital uses all current items; operating working capital leaves out cash and short-term debt. The block below turns the change into its cash flow effect.

Worked example: working capital of a wholesaler over two years

	A	B	C	D
1	Balance sheet item	FY2025	FY2026	Change
2	Cash	120,000	85,000	-35,000
3	Accounts receivable	310,000	395,000	85,000
4	Inventory	280,000	360,000	80,000
5	Prepaid expenses	20,000	25,000	5,000
6	Current assets	730,000	865,000	135,000
7	Accounts payable	210,000	240,000	30,000
8	Accrued expenses	60,000	70,000	10,000
9	Short-term debt and current portion of loans	100,000	150,000	50,000
10	Current liabilities	370,000	460,000	90,000
11	Net working capital	360,000	405,000	45,000
12	Operating working capital (without cash and debt)	340,000	470,000	130,000
13	Revenue for the year	2,400,000	2,700,000	300,000
14	Operating working capital as % of revenue	14.2%	17.4%	3.2%
15	Current ratio	1.97	1.88
16
17	Cash flow effect in FY2026			Value
18	Change in operating working capital in the cash flow statement			-130,000
19	Operating working capital at FY2025's % of revenue			382,500
20	Extra cash tied up by the higher ratio			87,500

Fictional figures for illustration only. This is a calculation guide, not accounting, financing or investment advice.

Reading the example

Revenue grew 12.5%. Receivables grew $85,000 and inventory $80,000, faster than sales. Payables and accruals rose by only $40,000, so operating working capital climbed from $340,000 to $470,000, from 14.2% to 17.4% of revenue.

Net working capital hardly shows this. It rose by only $45,000 because two financing lines moved the other way: cash fell by $35,000 and short-term debt rose by $50,000. In other words, the wholesaler paid for its growing receivables and stock partly from its bank account and partly with a bigger overdraft. The current ratio slipped from 1.97 to 1.88, which looks harmless. The operating view tells the real story.

Working capital in the cash flow statement

In the indirect cash flow statement an increase in operating working capital is a use of cash and is subtracted from profit; a decrease releases cash. Here the change is −$130,000: profit may be healthy, but $130,000 of it is sitting in customers' unpaid invoices and on the warehouse shelves. In Excel the sign is handled by =-(C12-B12). The full statement is built in the indirect method guide.

Part of the increase is simply growth: more sales need more receivables and stock. To separate growth from slippage, apply last year's ratio to this year's revenue. At 14.2% of $2.7 million the wholesaler would have needed $382,500. The other $87,500 is cash tied up only because collection and stock control got worse. The days behind that slippage are the subject of the cash conversion cycle.

Forecasting, negative working capital and benchmarks

Financial models usually forecast operating working capital as a percentage of revenue, or line by line in days: receivables from DSO, inventory and payables from days of cost of goods sold. The change then flows into the cash flow forecast, as in the three-statement model.

Negative working capital is not always a warning sign. Supermarkets and subscription businesses collect cash before they pay suppliers, so their customers and suppliers finance operations. For a manufacturer or wholesaler, though, negative net working capital usually means short-term obligations exceed liquid assets and needs a closer look. As with every ratio, compare with the same company over time and with similar businesses, and read the liquidity side in current ratio vs quick ratio.

Common mistakes

• Mixing net and operating working capital. Including cash and short-term debt hides how much the operations themselves absorb. State which version you use.
• Getting the cash flow sign wrong. An increase in receivables or inventory is a cash outflow, an increase in payables a cash inflow.
• Leaving out the current portion of long-term debt. Repayments due within twelve months belong in current liabilities for net working capital.
• Comparing amounts across company sizes. Express working capital as a percentage of revenue or in days before comparing years or companies.
• Reading one balance sheet date. Seasonal businesses can show very different working capital at different month-ends. Use averages or several dates when it matters.