How to Build a 13‑Week Cash‑Flow Forecast in Excel (Free Template)

Cash is king. Every CFO knows this truth.

Yet most cash flow forecasts live in static spreadsheets that break when accounts change. They rely on manual data exports that become stale the moment you close the file. For fractional CFOs managing multiple clients, this creates a nightmare of constant updates and weekend work.

Ready to automate your cash flow forecasting? 

Download our free 13‑Week Cash‑Flow Forecast Template and follow along as we show you how to build a model that updates automatically with live data from your accounting systems.

Why a rolling cash‑flow forecast matters

Lenders demand weekly covenant reporting. Payroll hits every two weeks whether you’re ready or not. Your runway visibility determines whether you sleep well at night.

Most fractional CFO firms refresh their forecasts weekly—making automation essential. Manual updates across 10, 20, or 40 client companies will consume your weekends and delay critical decisions.

A 13-week rolling forecast gives you the perfect balance of accuracy and planning horizon. It’s detailed enough for immediate cash management yet extends far enough to spot potential shortfalls before they become emergencies.

Gather historical cash inflows & outflows

Your forecast is only as good as the data feeding it. Start by pulling the last 6–12 months of transactions from your core systems:

• Accounting platforms: QuickBooks, Xero, NetSuite
• Payment processors: Stripe, Shopify, PayPal
• Bank feeds: Direct connection to checking and savings accounts
• CRM systems: HubSpot for pipeline data

Here’s where most CFOs waste hours: manually exporting CSVs and copy-pasting into Excel. Coefficient’s connectors import these transaction lines directly into your spreadsheet—no downloads, no broken links, no version control nightmares.

With live data connections, your historical analysis stays current automatically. When a client processes a refund or receives a late payment, your cash flow model reflects the change immediately.

Categorize transactions by cash‑flow section

Not all cash movements are created equal. Tag each transaction line as one of three categories:

Operating cash flow:

• Customer payments and refunds
• Vendor payments and rebates
• Payroll and benefits
• Rent and utilities
• Marketing spend

Investing cash flow:

• Equipment purchases
• Software subscriptions and licenses
• Real estate transactions
• Investment portfolio changes

Financing cash flow:

• Loan draws and payments
• Equity raises and distributions
• Credit line usage
• Owner contributions

Pro tip: The template includes a pre-built “Transactions” tab with a category column. In Google Sheets, you can run Coefficient AI to auto-classify transaction lines based on vendor names and descriptions—saving hours of manual tagging.

![Screenshot showing the template’s Transactions tab with category classifications]

Build assumption drivers

Static assumptions kill forecast accuracy. Create a dedicated “Inputs” tab for key variables that drive your cash timing:

Collection assumptions:

• Average days to collect AR (typically 30-45 days)
• Percentage collected in each aging bucket
• Bad debt write-off rates

Payment assumptions:

• Vendor payment terms (Net 30, Net 15, etc.)
• Early payment discounts you typically take
• Payroll schedule and timing

Business drivers:

• Monthly recurring revenue growth rates
• Seasonal adjustment factors
• Pipeline conversion probabilities

Coefficient can sync AR/AP aging reports directly from QuickBooks to calibrate these assumptions with real data. Instead of guessing that you collect 80% in 30 days, you’ll know your actual collection pattern from the aging analysis.

Create the 13‑week forecast schedule

Now comes the heart of your model: the rolling 13-week forecast. This schedule shows your cash position week by week, incorporating both historical patterns and forward-looking assumptions.

Your forecast structure should include:

Beginning cash balance:

• Starting position for each week
• Separate lines for operating accounts vs. savings/reserves

Weekly cash inflows:

• Collections from AR based on aging
• New sales converted to cash
• Loan draws or equity infusions
• Other operating receipts

Weekly cash outflows:

• Accounts payable payments
• Payroll and benefits (bi-weekly timing)
• Rent and fixed expenses
• Variable operating costs
• Loan payments and interest

Ending cash balance:

• Net position after inflows and outflows
• Running total that becomes next week’s beginning balance

![Screenshot showing the template’s 13-Week Forecast sheet with weekly columns]

The template’s “13‑Week Forecast” sheet provides this structure pre-built. Simply connect your data sources and customize the categories for your specific business model.

Add scenario & what‑if layers

Reality rarely matches your base case perfectly. Build in scenario planning with Best/Base/Worst case columns by varying your key drivers:

Best case scenarios:

• Collections accelerate by 10 days
• Pipeline closes 20% faster
• Payment terms extend by 5 days

Worst case scenarios:

• Collections slow by 15 days
• 25% of pipeline pushes to next quarter
• Major customer requests payment delay

Link these scenarios back to your Inputs tab so you can quickly model different outcomes. When a major customer signals payment delays, you can instantly see the cash impact across all 13 weeks.

This scenario modeling becomes critical for covenant reporting and board presentations. Instead of delivering a single forecast number, you provide a range of outcomes with clear assumptions.

Visualize cash runway & variances

Numbers tell the story. Charts make it memorable.

Insert these visualizations to make your forecast actionable:

Cash runway chart:

• Line graph showing ending cash by week
• Color-coded zones (green = healthy, yellow = watch, red = critical)
• Minimum cash covenant line as reference

Variance analysis:

• Bar chart comparing actual vs. forecasted cash flows
• Week-over-week variance trends
• Rolling accuracy metrics

These charts update automatically when Coefficient refreshes your underlying data. Your executive dashboard stays current without manual chart updates or formatting fixes.

Automate refresh & alerts with Coefficient

Manual forecasting is dead. Set up automated data pulls that keep your model current:

Daily automations:

• Bank balances and cleared transactions
• New invoices and payments from QuickBooks
• Updated pipeline data from CRM systems

Alert triggers:

• Projected cash below minimum covenant levels
• Variance exceeding 15% from forecast
• Late payments pushing aging beyond normal terms

Advanced automation options:

• Schedule Slack notifications for covenant breaches
• Email weekly forecast summaries to stakeholders
• Write forecast commentary back to QuickBooks for audit trails

For fractional CFOs managing multiple clients, this automation scales infinitely. Set up once per client, then monitor exceptions rather than rebuilding forecasts manually.

Ready to transform your cash forecasting?

Building a 13-week cash flow forecasting model in Excel doesn’t have to consume your weekends. With the right template and live data connections, you can automate 90% of the manual work while improving forecast accuracy.

Download the 13‑Week Cash‑Flow Template now. Connect your accounting systems with Coefficient’s live data feeds. Transform your static Excel model into an automated forecasting powerhouse.

Your clients will thank you for the real-time visibility. Your weekends will thank you for the time back. And your business will thank you for the improved cash management.

Get started with Coefficient today—your first forecast automation is just 5 minutes away.

Frequently asked questions

How to create cash flow forecast in Excel?

Start with historical transaction data from your accounting system. Categorize cash flows as Operating, Investing, or Financing activities. Build assumption drivers for collection timing and payment schedules. Create a 13-week rolling schedule showing weekly inflows and outflows. Use Coefficient to automate data pulls and eliminate manual updates.

How do you create a cash flow forecast model?

A cash flow forecast model requires three components: historical data analysis, assumption drivers, and forward projection logic. Pull 6-12 months of transaction history to identify patterns. Build adjustable assumptions for collection timing, payment schedules, and business growth. Project future cash flows based on AR aging, sales pipeline, and payment obligations.

Can you create a cash flow diagram in Excel?

Yes, Excel supports multiple chart types for cash flow visualization:

• Line charts for cash runway and ending balances
• Waterfall charts showing inflow and outflow components
• Bar charts for variance analysis and scenario comparison
• Combination charts mixing actual vs. forecast data

The key is linking charts to your underlying forecast data so they update automatically when numbers change.

How to do a cash flow forecast construction?

Construction cash flow forecasting requires project-specific considerations:

• Progress billing schedules tied to milestone completion
• Material and labor payment timing based on delivery schedules
• Retention holdbacks that delay final payments
• Change order impacts on both revenue and cost timing
• Equipment rental and purchase timing

Use the same 13-week framework but customize categories for construction-specific cash flows. Track project completion percentages to trigger billing events automatically.