13-Week Cash Flow Forecast in Excel: Treasury Playbook (2026)

A mid-market manufacturer with $180 million in revenue ran out of operating cash on a Tuesday in February — eleven days before payroll, six days after the CFO assured the board liquidity was "fine through Q2." The monthly cash flow statement had shown $14 million of cushion. The problem was timing: a $9 million customer payment slipped two weeks, a quarterly insurance premium hit early, and the revolver covenant test was due that Friday. None of it would have been a surprise on a 13-week cash flow forecast in Excel, which is exactly why every restructuring banker, turnaround consultant, and treasury team builds one before they look at anything else.

This guide walks through the full model — workbook architecture, direct-method receipt and disbursement forecasting, liquidity roll-forward, variance tracking, and the lender-grade output that DIP credit committees actually expect. Every formula is real and works in Excel 365.

What Is a 13-Week Cash Flow Forecast and Why Does It Matter?

A 13-week cash flow forecast (sometimes called a "TWCF" or "weekly cash forecast") is a rolling, weekly-granularity projection of every cash receipt and cash disbursement a business expects to make over the next quarter. It uses the direct method — actual money in and out of bank accounts — rather than starting from net income. The output is a week-by-week ending cash balance and the minimum liquidity required to survive the period.

Thirteen weeks is not arbitrary. It maps to one fiscal quarter (which lenders, boards, and auditors care about), it is short enough that operational visibility is genuinely high, and it is long enough to catch a quarterly tax payment, a debt service date, or a seasonal AR drought before they become a Friday-afternoon emergency.

Three audiences rely on the model daily:

Audience	Primary Use Case	Update Frequency
Treasury / FP&A	Working capital management, covenant tracking	Weekly
Restructuring & turnaround	DIP financing, bankruptcy court reporting	Weekly, with daily updates near a liquidity cliff
Private equity portfolio CFOs	Liquidity covenant compliance, dividend recap timing	Weekly to bi-weekly
Corporate development	M&A diligence on a target's near-term cash position	One-time, then weekly post-close

ℹ️ Note: Restructuring lenders typically build a ±10–15% variance covenant on both receipts and disbursements into DIP credit agreements. A model that misses by 25% in week three is a credit event, not just a forecasting miss.

Direct vs. Indirect Method: Which Should You Use?

For a 13-week forecast, always use the direct method. The indirect method (start with net income, add back non-cash items, adjust for working capital changes) is fine for the cash flow statement in your annual report, but it cannot tell you what your bank balance will be on April 19th. The direct method projects each receipt and disbursement at the bank-account level, which is the only granularity that matters when you are managing liquidity.

Dimension	Direct Method	Indirect Method
Starting point	Cash receipts and disbursements	Net income
Granularity	Weekly, by transaction category	Monthly or quarterly
Best for	Short-term liquidity, restructuring	Long-range capital planning
Reconciles to	Bank statements	Income statement and balance sheet
Lender acceptance for DIP	Required	Not accepted
Build effort	High (line-item detail)	Low (formulaic from financials)

💡 Pro Tip: The first 13-week model you build in a turnaround should be a "blank slate" direct-method model populated from bank transactions, not from the GL. Distressed companies frequently have GL coding errors that mask the real cash picture, and the bank statements are the only ground truth.

How Do You Build a 13-Week Cash Flow Forecast in Excel?

Build a 13-week cash flow forecast in five steps: (1) set up the workbook with separate input, calculation, and output sheets; (2) project weekly receipts by customer or category using historical collection patterns; (3) project weekly disbursements (AP, payroll, debt service, taxes, capex); (4) calculate net cash flow and roll it into a weekly ending balance; and (5) build a variance tracker that compares forecast to actuals each week.

The remainder of this guide walks through each step with working formulas.

Step 1: Workbook Architecture

A clean 13-week model has five sheets, in this order:

1. Cover & Assumptions — version, date, currency, minimum cash policy, contact owner
2. Inputs — historical AR aging, AP aging, payroll calendar, debt schedule, capex plan
3. Receipts — weekly cash inflow projection by category
4. Disbursements — weekly cash outflow projection by category
5. Summary & Variance — net cash flow, liquidity position, forecast vs. actual

Use the standard model-color convention so reviewers can audit at a glance:

• Blue font — hard-coded inputs (assumptions, historical data)
• Black font — formulas referencing the same sheet
• Green font — formulas referencing a different sheet
• Red font — external file links (avoid these in a 13-week model)

⚠️ Warning: Never embed assumptions inside formulas. A formula like =B5*0.15 for "15% of revenue" hides the assumption from reviewers. Put the 0.15 in a labeled input cell and reference it. Lenders reject models with hard-coded assumptions buried in formulas.

Step 2: Set Up the Weekly Date Header

In the Receipts and Disbursements sheets, columns C through O hold weeks 1 through 13. Anchor the dates with a single formula that propagates:

=Cover!$B$5+(COLUMN()-COLUMN($C$1))*7

Where Cover!$B$5 is the "week 1 ending date" (typically a Friday). The formula adds 7 days for each subsequent column. Format the row as a short date (mmm-d) so it reads cleanly in lender packages.

graph LR
    A[Bank Statements<br/>+ AR/AP Aging] --> B[Inputs Sheet]
    B --> C[Receipts Forecast<br/>13 weeks]
    B --> D[Disbursements Forecast<br/>13 weeks]
    C --> E[Net Cash Flow<br/>per week]
    D --> E
    E --> F[Ending Cash Balance<br/>+ Min Cash Test]
    F --> G[Lender Package<br/>Variance Report]

Forecasting Weekly Cash Receipts (Direct Method)

Cash receipts are not revenue. Revenue is what you billed; receipts are what you collected. The single most expensive mistake in a 13-week forecast is treating an invoice date as a receipt date. To project receipts correctly, you need an AR aging report and the empirical collection curve for each customer cohort.

The Collection Lag Approach

Pull the last 12 months of invoice and payment dates from your ERP. For each major customer or customer tier, calculate the weighted-average days sales outstanding (DSO) and the percentage that pays in each 7-day bucket:

Customer Tier	Avg DSO	Week 0–1	Week 2	Week 3	Week 4	Week 5+
Tier 1 (Top 10)	38 days	5%	35%	40%	15%	5%
Tier 2 (Mid)	47 days	2%	20%	35%	30%	13%
Tier 3 (Long tail)	62 days	0%	5%	15%	35%	45%

Accurate DSO estimates depend on clean AR aging data — for a deeper look at how DSO and the cash conversion cycle connect to the working capital balance on your balance sheet, see our working capital schedule guide for Excel.

Once you have the collection curve, the forecast for week W from a given billing cohort B is:

=SUMPRODUCT(($Billing_Week:$Billing_Week=W-LagWeek)*$Collection_Pct*$Billing_Amount)

For a simpler implementation, use a SUMIFS-driven approach with a helper column for "expected collection week":

=SUMIFS($Billing_Amount, $Customer_Tier, "Tier 1", $Expected_Collection_Week, C$3)*0.40

Where C$3 is the week-1 ending date and 0.40 is the share of Tier 1 invoices expected to collect in that week.

The Open AR Run-Off

For invoices already on the books, a simpler bucket-aged approach works:

=SUMIFS($AR_Aging[Amount], $AR_Aging[Days_Past_Due], ">"&((C$3-TODAY())-7), $AR_Aging[Days_Past_Due], "<="&(C$3-TODAY()))

This pulls every open invoice whose expected collection date falls inside the 7-day window of week W. Layer this on top of new billings for a complete picture.

Example: A SaaS company with $4.2M in AR aged 0–30 days, 80% historical collection in days 30–60, expects roughly $4.2M × 0.80 = $3.36M of receipts spread across weeks 4 through 8. A retailer with daily card settlements would model receipts at a 2–3 day lag and treat them as deterministic.

Other Cash Inflows to Forecast

Receipts go beyond customer collections. A complete 13-week receipts schedule includes:

• Customer collections (the bulk for most companies)
• Customer prepayments / deposits (subscription renewals, project mobilization fees)
• Asset sale proceeds (planned divestitures, scrap, inventory liquidation)
• Tax refunds (federal, state, R&D credits)
• Insurance recoveries (claims in progress)
• New financing draws (revolver borrowings, DIP funding tranches, equity injections)
• Interest income on cash balances

Each gets its own row in the Receipts sheet, populated only in the weeks the cash actually arrives.

Forecasting Weekly Cash Disbursements

Disbursements are easier than receipts because most are contractually fixed (debt service, rent, payroll) or predictable (utilities, insurance). The art is in the AP run-off — projecting when invoices already received but not yet paid will hit the bank.

Payroll: The Calendar-Driven Disbursement

Payroll is the most variable mid-quarter expense because pay periods do not align cleanly with calendar weeks. Build a 13-row payroll calendar with the actual check dates and reference it directly:

=SUMIFS($Payroll_Calendar[Amount], $Payroll_Calendar[Pay_Date], ">="&C$3-6, $Payroll_Calendar[Pay_Date], "<="&C$3)

This pulls any payroll run that lands inside the 7-day window ending on the week's date marker. For semi-monthly payrolls (15th and end of month), some weeks will have zero payroll and some will have two — that lumpiness is exactly why a weekly model exists.

AP Run-Off and Vendor Payment Strategy

Open AP rolls off based on payment terms and management's payment strategy. In a turnaround, "stretching AP" — slowing payments to non-critical vendors — is the fastest source of liquidity. Build a vendor classification column:

Vendor Class	Payment Strategy	Forecast Treatment
Critical (utilities, raw materials)	Pay on terms (net 30)	Disburse at terms
Strategic (key suppliers)	Pay on terms or +15 days	Disburse at terms + 15
Non-critical	Stretch to 60+ days	Disburse at terms + 30
Disputed / under negotiation	Hold pending resolution	Excluded from base forecast

Forecast each vendor class with a SUMIFS that respects the payment date:

=SUMIFS($AP_Open[Amount], $AP_Open[Vendor_Class], "Critical", $AP_Open[Expected_Pay_Date], ">="&C$3-6, $AP_Open[Expected_Pay_Date], "<="&C$3)

Fixed and Periodic Disbursements

Hard-code the calendar items in their own input rows:

• Debt service — interest and principal; the amortization build and revolver draws are detailed in our debt schedule and credit analysis guide for Excel
• Rent — typically first business day of the month
• Insurance premiums — quarterly or annual; check the policy calendar
• Income and payroll taxes — federal due dates (Apr 15, Jun 15, Sep 15, Dec 15 for estimated tax) plus state schedules
• Capex — milestone-driven, not straight-lined
• Professional fees — legal, audit, restructuring advisor retainers (significant in distressed situations)

⚠️ Warning: A common, expensive miss is the quarterly estimated tax payment. A profitable company that has paid $2M of estimated taxes in each of the prior three quarters will owe another $2M in the current quarter — and it lands on a single day. Model it. Do not smooth it.

Calculating Net Cash Flow and the Liquidity Position

Once receipts and disbursements are forecast, the summary roll-forward is mechanical. On the Summary sheet, build the following structure with one column per week:

Beginning cash balance         (Row 5)
+ Total cash receipts          (Row 6) = Receipts!$Total$Row
- Total cash disbursements     (Row 7) = Disbursements!$Total$Row
= Net cash flow                (Row 8) = SUM(C6:C7) — disbursements entered as negative
+ Net financing activity       (Row 9) (revolver draws, paydowns, DIP)
= Ending cash balance          (Row 10) = C5 + C8 + C9
- Minimum cash policy          (Row 11) = Cover!$B$8
= Excess / (shortfall)         (Row 12) = C10 - C11

The beginning cash balance for week 2 onward links to the prior week's ending balance:

=B10

Where B10 is the prior column's ending cash. This single-cell roll forward is the spine of the model and the most-audited line in any lender package.

Revolver / Borrowing Base Logic

If the business has an asset-based loan (ABL), the revolver availability changes weekly with the borrowing base. Add a borrowing base block that calculates eligible AR and inventory and applies the advance rates:

=MIN(Revolver_Limit, AR_Eligible*0.85 + Inventory_Eligible*0.50) - Revolver_Outstanding

A 13-week model that ignores borrowing base availability misses the most important constraint in a credit-stressed business.

graph TD
    A[Beginning Cash<br/>Week N] --> E[Ending Cash<br/>Week N]
    B[Receipts<br/>+ AR collections<br/>+ Refunds<br/>+ Financing draws] --> E
    C[Disbursements<br/>- AP<br/>- Payroll<br/>- Debt service<br/>- Taxes<br/>- Capex] --> E
    E --> F{Above Min<br/>Cash Policy?}
    F -->|Yes| G[Operate normally]
    F -->|No| H[Trigger:<br/>Draw revolver<br/>Stretch AP<br/>Lender call]
    E --> I[Beginning Cash<br/>Week N+1]

Variance Tracking: Forecast vs. Actual

A forecast that is never reconciled to actuals is theater. Every Monday, the treasury team should drop the prior week's actual receipts and disbursements into the model and compute the variance — both in dollars and as a percentage. Lender DIP covenants typically require this reconciliation in writing within 3 business days of week-end.

The Variance Calculation

Add three rows below each forecast line:

Row N:   Forecast      ($ in week W)
Row N+1: Actual        ($ in week W)
Row N+2: Variance ($)  =Row N+1 - Row N
Row N+3: Variance (%)  =IFERROR((Row N+1 - Row N) / Row N, 0)

For the cumulative variance through week W, use a running total:

=SUM($C$Forecast_Row:C$Forecast_Row) - SUM($C$Actual_Row:C$Actual_Row)

Conditional Formatting for Out-of-Tolerance Weeks

Highlight any line where the variance breaches the lender tolerance. With your variance percentages in row 25, columns C through O:

1. Select the range
2. Home → Conditional Formatting → New Rule → Use a formula
3. Formula: =ABS(C25)>0.10
4. Format with red fill

Now any week that drifts outside ±10% lights up before your CRO has to ask. For a deeper treatment of this technique, see our conditional formatting guide for financial models.

💡 Pro Tip: Build a "permanent variance" line that captures structural misses — for example, a customer that has consistently been paying 10 days slower than the model assumes. Do not reset the assumption inside the variance week; correct it forward starting next week's forecast and document the change in a model log.

How Often Should You Update a 13-Week Cash Flow Forecast?

A 13-week cash flow forecast should be updated weekly at a minimum, with the prior week's actuals reconciled and weeks 2 through 14 re-projected. Companies in active restructuring or near a liquidity cliff update daily during the critical period. The cadence is a function of risk: stable mid-market companies refresh weekly; companies inside a forbearance agreement or DIP financing update at least weekly with daily ad-hoc views.

The discipline that separates good treasury teams from great ones is the rolling roll-forward: each Monday, drop week 1, advance everything by one week, and add a new week 13. The model is always 13 weeks looking forward, never a fixed-end calendar.

Common Mistakes That Sink 13-Week Forecasts

Five mistakes account for the majority of forecast failures, and every one of them is avoidable.

1. Confusing revenue with cash. Booking a $1M deal does not mean $1M arrives next Friday. Forecast off the AR aging and historical collection curve, not the sales pipeline.
2. Ignoring quarterly and annual disbursements. Estimated tax payments, insurance premiums, audit fees, and bonus accruals do not show up in monthly run rates. They land in single weeks and crush forecasts that smooth them.
3. Forecasting at month-level granularity inside a weekly model. Dividing $400K of monthly rent by 4.33 to get a "weekly rent" is wrong. Rent is paid in week 1 of the month and zero in the others.
4. Stale assumptions. A collection curve built six months ago does not reflect a customer that just hit financial stress. Refresh the cohort assumptions quarterly.
5. No variance discipline. A forecast model that is not reconciled to actuals every week is decoration. Lenders, boards, and turnaround consultants all judge the model by the variance, not the projection.

ℹ️ Note: For complex, multi-entity businesses, the consolidation of weekly forecasts across legal entities introduces FX and intercompany timing issues. A standalone 13-week model per entity, rolled up centrally, is more accurate than a single consolidated model with embedded FX assumptions.

Where AI-Assisted Excel Fits

Building the first version of a 13-week model is largely mechanical: parse an AR aging file, classify vendors, build a payroll calendar, lay out the weekly columns. This is exactly the kind of work where AI-assisted Excel — including VeloraAI — collapses hours of data prep into minutes by translating natural-language descriptions of your inputs into the SUMIFS, SUMPRODUCT, and conditional-formatting rules above. Treasury professionals still own the assumptions and the variance review; the AI just removes the boilerplate. For a broader look at how AI is changing analyst workflows, see our AI for financial modeling workflows post.

Frequently Asked Questions

Why 13 weeks specifically and not 8 or 26?

Thirteen weeks equals one fiscal quarter, which aligns with covenant tests, board cycles, and tax payment dates. It is short enough that operational visibility on receipts and disbursements is genuinely high, and long enough to surface a lumpy quarterly outflow before it lands. Restructuring practice standardized on 13 weeks in the 1980s with firms like Alvarez & Marsal and AlixPartners, and lender DIP requirements ossified the convention.

What is the difference between a 13-week cash flow and a cash flow statement?

A cash flow statement is a historical, GAAP-compliant report that reconciles net income to cash using the indirect method, presented monthly or quarterly. A 13-week cash flow forecast is forward-looking, weekly, and uses the direct method to project actual receipts and disbursements. They serve completely different purposes — the cash flow statement looks backward at accounting; the 13-week looks forward at liquidity.

Can I build a 13-week cash flow forecast without an ERP?

Yes. The minimum inputs are: bank statements for the last 6 months (to establish receipt and disbursement patterns), an AR aging, an AP aging, a payroll calendar, and the debt schedule. Many small and mid-market companies build the entire model from a downloaded bank CSV plus AR and AP exports from their accounting system.

What variance tolerance is acceptable for a DIP-financed 13-week forecast?

DIP financing agreements typically build in a ±10–15% variance tolerance on both total receipts and total disbursements, measured weekly and on a rolling cumulative basis. Larger variances trigger a covenant default. The narrower the tolerance, the more conservative your collection assumptions need to be — most distressed companies set their forecast 5–7% below their honest expectation to preserve covenant headroom.

Should net cash flow ever be positive in week 13 of a turnaround model?

Often no, and that is fine. The point of the model is to show the funding need, not to show a happy ending. A turnaround 13-week forecast frequently shows a cumulative shortfall that justifies the DIP facility size or the equity injection. Lenders are not surprised by negative numbers; they are surprised by numbers that understate the need and trigger a re-trade three weeks later.

Closing

A 13-week cash flow forecast is the most operational financial model a finance team builds — and the one most often built badly. The ones that work are short on assumptions, long on bank-account reality, reconciled every Monday, and trusted by the people who write the checks. Build yours from real receipts and disbursements, classify your vendors, anchor it to a borrowing base, and put the variance tracker in row 1 where the CFO sees it first.

Once the spine of the model is in place, the next step is sensitivity: stress the receipt curve by ±15%, model a customer concentration loss, and walk the board through the funding need under each case. That is where the 13-week forecast stops being a treasury report and starts being a strategic document.