Working Capital Schedule in Excel: DSO/DPO Forecast (2026)

May 20, 2026 · VeloraAI Team
Data Analysis Financial Modeling Excel

A bad working capital forecast can silently erase 30% of a company's projected free cash flow. Most three-statement models I have audited miss this — analysts pour energy into revenue assumptions, then plug working capital with a flat 2% of sales and call it a day. The result: an operating model that looks right on the income statement but bleeds cash on the balance sheet.

This guide walks through how to build a working capital schedule in Excel the way buy-side teams and FP&A groups actually build it: using days-based ratios (DSO, DPO, DIO), historical normalization, and clean links back to the three statements. By the end, you will have a schedule that forecasts net working capital, computes the cash conversion cycle, and ties out to the change-in-NWC line on your cash flow statement.

What Is a Working Capital Schedule in Excel?

A working capital schedule is a supporting tab in a financial model that forecasts each operating current asset and current liability — accounts receivable, inventory, accounts payable, accrued expenses, and deferred revenue — by tying them to operating drivers (sales or COGS) through days-based ratios. It feeds the balance sheet and produces the change in net working capital that flows into cash flow from operations — the same linkage that makes or breaks a three-statement financial model in Excel.

The schedule sits between the income statement (which drives the ratios) and the balance sheet (where the ending balances live). Treat it as the bridge that converts an accrual-based forecast into a cash-based one.

ℹ️ Note: "Working capital" on the balance sheet equals current assets minus current liabilities. For modeling, you almost always want operating working capital — which excludes cash, marketable securities, and short-term debt. Mixing the two is the most common error in junior analyst models.

Why analysts get this wrong

Most templates project working capital as a single line — "NWC as % of revenue" — and call it done. That hides three problems:

  1. Receivables and payables move on different drivers. AR scales with sales; AP scales with COGS. Lumping them together breaks when gross margin shifts.
  2. Inventory does not scale linearly. A retailer adding SKUs builds inventory faster than revenue. A SaaS company has almost none.
  3. Seasonality disappears. Quarterly models that average annual ratios will systematically misforecast Q4 cash needs.

A proper schedule fixes all three.

How Do You Build a Working Capital Schedule in Excel?

To build a working capital schedule in Excel, link historical balance sheet line items (AR, inventory, AP) to the income statement, compute DSO, DPO, and DIO for each historical year, hold or trend those days into the forecast, then back-solve the projected balances. Net the operating assets against the operating liabilities to get NWC, and take the year-over-year change to feed cash flow from operations.

Here is the workflow in order:

  1. Pull historicals. Three to five years of AR, inventory, AP, accrued expenses, and deferred revenue from the balance sheet. Pull net revenue and COGS from the income statement.
  2. Compute historical days. DSO = AR ÷ Revenue × 365. DIO = Inventory ÷ COGS × 365. DPO = AP ÷ COGS × 365.
  3. Normalize. Identify any outlier years (acquisitions, one-time inventory writedowns, COVID shocks) and decide whether to average, take the most recent, or use a trend.
  4. Set forecast assumptions. Either hold days flat, trend them, or override based on management guidance.
  5. Back-solve forecast balances. Forecast AR = Forecast DSO × Forecast Revenue ÷ 365.
  6. Calculate NWC and the change. Sum the operating assets, subtract the operating liabilities, then take year-over-year deltas.
  7. Link to the cash flow statement. The negative of the change in NWC hits CFO.
graph LR
    A[Income Statement] -->|Revenue| B[DSO]
    A -->|COGS| C[DIO]
    A -->|COGS| D[DPO]
    B --> E[Forecast AR]
    C --> F[Forecast Inventory]
    D --> G[Forecast AP]
    E --> H[Net Working Capital]
    F --> H
    G --> H
    H -->|Year-over-Year Δ| I[Cash Flow from Operations]

Setting up the tab structure

I lay out the schedule in three blocks stacked vertically on one sheet:

  • Block 1 — Operating drivers: Revenue and COGS pulled from the income statement (link, do not retype).
  • Block 2 — Days calculation: DSO, DIO, DPO, days accrued, days deferred revenue, with historicals on the left and forecast on the right.
  • Block 3 — Balance back-solve: Each forecast balance computed from the days assumption.

Color convention matters here. Hardcoded historicals stay blue. Forecast assumptions stay yellow or another input color. Anything formula-driven stays black. If your audit trail breaks on this tab, the entire CFO line is suspect.

Calculating DSO, DPO, and DIO: The Three Core Ratios

These three ratios are the engine of the schedule. Get them wrong and every downstream number is wrong.

Days Sales Outstanding (DSO)

DSO measures how long it takes to collect cash after a sale. Lower is better.

DSO = (Accounts Receivable / Revenue) × 365

In Excel, if AR is in cell C12, Revenue is in C6, and you want days in a 365-day year:

=C12 / C6 * 365

For a quarterly model, replace 365 with 90 (or 91.25 for precision). For an average-balance approach — which smooths quarter-end spikes — use:

=AVERAGE(B12,C12) / C6 * 365

The average-balance method matches the way most credit analysts and rating agencies compute it. Stick with one approach across the whole schedule.

Days Inventory Outstanding (DIO)

DIO measures how long inventory sits before it is sold. Industry-dependent: software firms have DIO near zero, heavy industrial firms can carry 90+ days.

DIO = (Inventory / COGS) × 365

Excel formula assuming Inventory in C13 and COGS in C7:

=C13 / C7 * 365

Days Payable Outstanding (DPO)

DPO measures how long the company takes to pay suppliers. Higher DPO is generally better for cash — but extending payables too aggressively damages supplier relationships.

DPO = (Accounts Payable / COGS) × 365

Excel formula:

=C14 / C7 * 365

💡 Pro Tip: Always check whether AP includes only trade payables or also accrued expenses. Some 10-Ks lump them together, which inflates DPO. For a clean schedule, separate "trade AP" from "accrued liabilities" and model them with different drivers — accrued expenses typically scale with operating expenses, not COGS.

Putting them together: the Cash Conversion Cycle

The cash conversion cycle (CCC) sums the days a dollar is tied up in working capital:

CCC = DIO + DSO - DPO

A negative CCC — typical for Amazon, Walmart, and many marketplace platforms — means the company collects from customers before paying suppliers. That is structurally cash-generative and worth flagging in any model. Treasury teams that actively manage this dynamic also build a 13-week cash flow forecast in Excel to monitor the real-time impact of receivables and payables timing.

graph LR
    A[Cash Out: Pay Supplier] -->|DPO Days| B[Inventory Sold]
    B -->|DIO Days| C[Revenue Booked]
    C -->|DSO Days| D[Cash In: Collect from Customer]
    A -.->|CCC = DIO + DSO - DPO| D

Forecasting Working Capital: Percentage of Sales vs. Days Method

There are two acceptable methods for projecting working capital. Both are used in industry, but they behave differently when assumptions change.

Method 1: Days method (preferred)

Hold or trend DSO, DIO, and DPO into the forecast, then back-solve balances. The formula for forecast AR:

=Forecast_DSO * Forecast_Revenue / 365

This method scales correctly when margins change. If you forecast gross margin expansion (COGS shrinks as % of revenue), inventory and AP both naturally drop with the COGS denominator — preserving the underlying physical relationship.

Method 2: Percentage of sales method (simpler, less accurate)

Express each line as a percentage of revenue and hold that ratio flat:

=AR_Percent_of_Sales * Forecast_Revenue

Faster to build, but it conflates margin shifts with operational efficiency. Use it for back-of-envelope models or when historical data is too thin to compute reliable days.

Comparison: when to use which

Scenario Days Method % of Sales Method Notes
Stable margin business Either works Either works Pick days for precision
Margin expansion forecast Days Avoid % of sales over-projects AP
Limited historical data (1-2 years) Avoid % of sales Days needs 3+ years to normalize
Pre-IPO / private company Days Avoid Operating discipline matters
Sector with seasonal swings Days, quarterly Avoid Annual averages hide Q4 spikes
Quick LBO screening model Either % of sales Speed over precision

⚠️ Warning: Never blend the two methods within the same schedule. I have seen models where AR uses days but inventory uses percent-of-sales — when revenue scenarios run, the two lines move in inconsistent ways and the change-in-NWC sign flips erratically.

Holding days flat vs trending

Once you pick the days method, you still need to decide what the forecast days look like:

  • Hold flat at the most recent year. Defensible if last year was representative.
  • Three-year average. Smooths volatility, good for cyclical businesses.
  • Trend extrapolation. Use if management has stated a working-capital initiative — e.g., reducing DSO from 65 to 55 over three years.
  • Tiered convergence. Start at the most recent year, converge to an industry benchmark by the terminal year. Useful in DCF terminal-value setups.
=Recent_DSO + (Target_DSO - Recent_DSO) * (Year - Base_Year) / Convergence_Years

FP&A teams monitoring whether DSO and DPO targets are being hit each quarter will find the budget vs. actual variance analysis guide for Excel directly applicable — the same driver-based comparison logic governs working capital performance reviews in monthly operating reports.

How Do You Link Working Capital to the Three Statements?

The working capital schedule does not live in isolation — it feeds the balance sheet and the cash flow statement, and it pulls from the income statement.

The links:

  1. From the income statement: Revenue drives AR and deferred revenue. COGS drives inventory, AP, and accrued COGS.
  2. To the balance sheet: Each forecast ending balance from the schedule lands directly in the corresponding balance sheet line.
  3. To the cash flow statement: The negative of the year-over-year change in net operating working capital flows into CFO.

The CFO link formula, assuming current year NWC is C30 and prior year is B30:

=-(C30 - B30)

Negative sign because an increase in operating assets (AR, inventory) is a use of cash, and an increase in operating liabilities (AP, accrued) is a source of cash. The schedule should net these correctly so the single line into CFO is just the delta.

Example: If AR grows from $80M to $100M while AP grows from $50M to $55M, NWC change = ($100M − $80M) − ($55M − $50M) = $20M − $5M = $15M increase. Cash flow impact = −$15M.

Tying out: the audit check

After linking, run this check on a separate cell:

=SUM(CFO_NWC_Lines) - (-(Current_NWC - Prior_NWC))

The result should be zero. If it is not, you have either double-counted a line or missed one. The most common offender is deferred revenue — analysts forget it is a working capital liability when the company is subscription-based. Applying a verification cell like this to every supporting schedule is the core discipline behind a clean financial model audit in Excel.

What Are the Most Common Working Capital Modeling Mistakes?

The biggest mistakes are (1) using AP as % of revenue instead of COGS, (2) forgetting to exclude cash and short-term debt from "working capital," (3) ignoring deferred revenue in SaaS models, and (4) holding days flat through scenarios where management has guided otherwise. Each one quietly distorts the cash flow forecast by 5-20%.

The full list

  1. AP scaled to revenue. Already covered — but it happens constantly. AP scales with COGS; if your gross margin changes, AP needs the COGS-based driver.
  2. Cash included in "working capital." Cash is not operating. Models that include it create circular references with the revolver and break the cash sweep.
  3. Short-term debt mixed in. ST debt is a financing item. It belongs on the debt schedule, not here.
  4. Deferred revenue missed. For SaaS, deferred revenue is often the largest single working capital item. Drive it off annual contract value, not trailing revenue.
  5. No seasonality in quarterly models. Retailers build inventory in Q3, run it down in Q4. Holding DIO flat across quarters misforecasts cash by tens of millions.
  6. Days from year-end snapshot only. A single 12/31 balance can be anomalous. Use a four-quarter average if you have the data.
  7. Forecast days outside historical range with no justification. If DSO has ranged 45-55 for five years, forecasting 30 needs a documented assumption.
  8. No tie-out to cash flow statement. Without the audit cell, errors compound silently.

⚠️ Warning: Acquisitions distort historical working capital ratios. When a target with different terms gets absorbed mid-year, the post-deal year shows a hybrid ratio. Always pro-forma the historicals before computing days, or note the break in the schedule.

Industry Benchmarks: Working Capital by Sector

Days ratios vary enormously across industries. Use these as sanity checks for your forecast — if your projected DSO is 70 days for a SaaS business, something is off.

Industry Typical DSO Typical DIO Typical DPO NWC % of Sales
SaaS / Software 40-60 0-5 30-45 0-5%
Retail (mass market) 5-15 60-90 45-70 −5% to 5%
Consumer Packaged Goods 30-45 45-75 40-60 5-15%
Industrial Manufacturing 50-70 60-100 40-60 10-25%
Pharmaceuticals 60-90 90-150 50-80 15-30%
Aerospace & Defense 60-100 120-200 50-80 20-35%
E-commerce / Marketplaces 5-20 30-60 60-90 −10% to 0%
Utilities 40-60 15-30 40-60 0-5%

These are rough ranges based on aggregated 10-K data; always pull peer-specific values from the actual filings before benchmarking a model.

A Practical Build: SaaS Example

Take a $200M ARR SaaS business projecting to $350M in three years — a setup that closely mirrors the unit-economics structure in our SaaS startup financial model guide. The historical working capital looks like this:

  • DSO: 55 days (improved from 65 two years ago — collections team investment)
  • DIO: 0 (no inventory)
  • DPO: 35 days
  • Deferred revenue: 6 months of ARR (annual contracts billed up front)
  • Accrued compensation: ~$15M (one quarter of OpEx)

For the forecast:

  • DSO: hold at 55 — no further guidance from management
  • Deferred revenue days: hold at 180 — matches contract terms
  • Accrued comp: scale with OpEx, not revenue

The AR back-solve in Excel:

=55 * Forecast_Revenue / 365

For $350M revenue: =55 * 350 / 365 = $52.7M.

Deferred revenue back-solve:

=180 * Forecast_ARR / 365

For $350M ARR: =180 * 350 / 365 = $172.6M.

Notice deferred revenue is over three times AR. For SaaS, missing this line is the most expensive single modeling error you can make — it understates cash by hundreds of millions over a five-year forecast.

💡 Pro Tip: For SaaS and other subscription businesses, build deferred revenue off billings (or ARR-based billings), not GAAP revenue. The two diverge when growth accelerates, and deferred revenue moves with billings.

Frequently Asked Questions

What is the difference between net working capital and operating working capital?

Net working capital (NWC) is current assets minus current liabilities, full stop. Operating working capital excludes cash, marketable securities, and short-term debt — leaving only items that scale with operations (AR, inventory, AP, accrued expenses, deferred revenue). For financial modeling, always use operating working capital; including cash creates circular references with the revolver.

How do you forecast accounts receivable in Excel?

The standard approach is to compute historical DSO (AR ÷ Revenue × 365), hold or trend it into the forecast, then back-solve: Forecast AR = Forecast DSO × Forecast Revenue ÷ 365. For example, if DSO is 55 days and forecast revenue is $350M, projected AR = 55 × 350 ÷ 365 = $52.7M. Use the days method rather than percent-of-sales when gross margin is shifting.

Should I use average or ending balances for DSO and DPO?

Ending balances are simpler and match how the model is built (each year-end balance is one cell). Average balances — (Beginning + Ending) / 2 — match how credit analysts and rating agencies compute the ratios and smooth out year-end spikes. Pick one method and apply it consistently across all ratios; do not mix. For most operating models, ending balances are the convention.

Why is my change in working capital not tying to the cash flow statement?

Three common causes: (1) deferred revenue is on the schedule but missing from the CFO line, (2) the sign convention is flipped — increases in current assets are uses of cash, not sources, (3) cash or short-term debt was mistakenly netted into working capital. Add an audit cell that compares the sum of CFO working capital lines to the negative of the year-over-year NWC delta; it should equal zero.

What is a negative cash conversion cycle and is it good?

A negative CCC means a company collects cash from customers before paying suppliers. Amazon, Walmart, and most marketplace platforms run negative CCCs. It is structurally cash-generative — the business funds growth from supplier credit rather than from external financing. In a model, this typically shows up as negative operating working capital that becomes more negative as revenue grows, producing a cash source as the company scales.

Closing

A clean working capital schedule is the difference between a model that survives a CFO review and one that does not. The days method, paired with rigorous links into the three statements and an explicit audit tie-out, gives you a forecast that holds up when revenue scenarios change. For teams that surface DSO, DPO, and CCC trends in a weekly management view, our financial dashboard in Excel guide shows how to build KPI cards that auto-refresh from your working capital model outputs.

If you find yourself repeatedly building these schedules from scratch — pulling historicals, computing days, back-solving balances — that is exactly the kind of mechanical work that VeloraAI automates inside Excel, leaving you with the analytical judgment about what days assumption to use and why. Either way, build the schedule properly: it is the cash line in your model.

Next step: pull last quarter's 10-Q for any business you cover, compute the four working capital days ratios, and compare them to the same quarter a year ago. Anything moving more than 10% deserves an explanation in your model — and likely in your next earnings call note.