Business Cash Flow Forecast Template

1. System Overview & Purpose

Purpose

The Rolling 13-Week Cash Flow Forecast Engine provides operational liquidity visibility, dynamic cash runway projections, and variance analysis between actual and forecasted cash flows. It is architected to prevent liquidity crunches, optimize working capital, and provide audit-ready financial reporting for executive stakeholders.

Scope

• Covers all operating, investing, and financing cash inflows and outflows.
• Granular weekly tracking over a rolling 90-day (13-week) horizon, backed by a 12-month historical actuals repository.
• Integrates Accounts Receivable (AR) aging schedules, Accounts Payable (AP) disbursement schedules, and fixed payroll/debt service obligations.

Update Cadence

• Actuals Reconciliation: Daily (reconciled against bank feeds every morning at 08:00 UTC).
• Forecast Roll-Forward: Weekly (every Monday, drop the oldest week, append a new forecast week).
• Variance Analysis: Weekly (post-reconciliation review of Actuals vs. Forecast).

---

2. Data Structure & Column Definitions Table

Validated Dropdown Lists:

• Category_Group / Line_Item:
  • Operating -> Customer Collections (AR), Payroll & Benefits, Vendor Disbursements (AP), Rent & Utilities, SaaS & Software Subscriptions, Tax Remittances
  • Investing -> CapEx & Equipment, Software Development Capitalization
  • Financing -> Debt Principal Repayment, Equity Injection, Interest Expense

---

3. Complete Master Data Table / Tracker

Transaction_ID,Date,Week_Number,Category_Group,Line_Item,Entity_Type,Counterparty,Currency,Amount,Probability_Weight,Risk_Adjusted_Amount,Notes
TXN-2023-1001,2023-10-02,40,Operating,Customer Collections (AR),Actual,Enterprise Corp,USD,45250.00,1.00,45250.00,Inv #1092 cleared via ACH
TXN-2023-1002,2023-10-02,40,Operating,Payroll & Benefits,Actual,Gusto Payroll,USD,-112000.00,1.00,-112000.00,Bi-weekly payroll execution
TXN-2023-1003,2023-10-03,40,Operating,Vendor Disbursements (AP),Actual,AWS Cloud,USD,-8450.50,1.00,-8450.50,Monthly infrastructure bill
TXN-2023-1004,2023-10-04,40,Operating,Rent & Utilities,Actual,WeWork Space,USD,-12500.00,1.00,-12500.00,HQ lease payment
TXN-2023-1005,2023-10-05,40,Investing,CapEx & Equipment,Actual,Dell Technologies,USD,-4200.00,1.00,-4200.00,New engineering workstations
TXN-2023-1006,2023-10-06,40,Financing,Debt Principal Repayment,Actual,Silicon Valley Bank,USD,-25000.00,1.00,-25000.00,Term loan monthly amortization
TXN-2023-1007,2023-10-09,41,Operating,Customer Collections (AR),Forecast,Global Logistics,USD,30000.00,0.90,27000.00,Expected invoice payment net-30
TXN-2023-1008,2023-10-09,41,Operating,SaaS & Software Subscriptions,Forecast,Salesforce,USD,-3500.00,1.00,-3500.00,Annual enterprise license auto-renew
TXN-2023-1009,2023-10-10,41,Operating,Tax Remittances,Forecast,State Department of Revenue,USD,-6200.00,1.00,-6200.00,Sales tax pass-through remittance
TXN-2023-1010,2023-10-11,41,Operating,Vendor Disbursements (AP),Forecast,Marketing Agency X,USD,-15000.00,0.75,11250.00,Q4 campaign spend (Pending approval)

---

4. Key Formulas & Calculation Logic

Implement these exact formulas in your spreadsheet calculation engine (Excel / Google Sheets) to automate the ledger and model. Assume data spans rows 2 to 1000 in a sheet named Master_Ledger.

1. Risk-Adjusted Amount (Column K calculation)

Calculates the probability-weighted cash impact for forecast items while keeping actuals at 100%.

=IF(F2="Actual", I2, I2 * J2)

2. Ending Cash Balance (Per Week)

Calculates cumulative liquidity by taking the prior week's ending cash and adding the current week's net cash flow.

=E1 + SUMIFS(Master_Ledger!$K$2:$K$1000, Master_Ledger!$C$2:$C$1000, A2)

(Assumes Column A in the Summary sheet is Week Number, and Column E is Ending Cash).

3. Weekly Net Cash Flow

Aggregates total risk-adjusted inflows and outflows for a specific ISO week.

=SUMIF(Master_Ledger!$C$2:$C$1000, A2, Master_Ledger!$K$2:$K$1000)

4. Actual vs. Forecast Variance ($)

Measures absolute variance between planned and realized cash movements for closed weeks.

=SUMIFS(Master_Ledger!$I$2:$I$1000, Master_Ledger!$C$2:$C$1000, A2, Master_Ledger!$F$2:$F$1000, "Actual") - SUMIFS(Master_Ledger!$I$2:$I$1000, Master_Ledger!$C$2:$C$1000, A2, Master_Ledger!$F$2:$F$1000, "Forecast")

5. Cash Runway (Months)

Calculates remaining operational runway based on current cash reserves divided by the 3-month trailing average monthly burn rate.

=Current_Cash_Balance / AVERAGE(Net_Cash_Flow_Wk-12 : Net_Cash_Flow_Wk) * 4.33

---

5. Summary KPI Dashboard

The executive dashboard layout aggregates data from the Master_Ledger tab to provide instant liquidity visibility.

+-----------------------------------------------------------------------------------+
|                   EXECUTIVE CASH FLOW KPI DASHBOARD                               |
+-----------------------------------+-----------------------------------------------+
| Starting Cash Balance (Wk 40)     | $ 1,450,000.00                                |
| Total Projected Inflows (13-Wk)   | $   585,000.00                                |
| Total Projected Outflows (13-Wk)  | $  -412,500.00                                |
| Net 13-Week Cash Change           | $   172,500.00                                |
| Ending Cash Balance (Wk 52)       | $ 1,622,500.00                                |
+-----------------------------------+-----------------------------------------------+
| Minimum Cash Point (Low Watermark)| $ 1,180,000.00 (Week 44)                      |
| Current Cash Runway               | 14.2 Months                                   |
| 30-Day AR Collection Efficiency   | 94.2%                                         |
| Forecast Accuracy (Trailing 4-Wk) | 96.8%                                         |
+-----------------------------------+-----------------------------------------------+

---

6. Standard Operating Workflow

Execute the following sequential procedure weekly to maintain absolute data integrity and forecast fidelity.

[1. Bank Reconciliation] 
       │
       ▼
[2. Actuals Lock-In] 
       │
       ▼
[3. AR/AP Schedule Refresh] 
       │
       ▼
[4. Probability Weight Adjustment] 
       │
       ▼
[5. Roll-Forward & Review]

1. Bank Reconciliation (Monday 08:00 UTC):

   • Export bank statements for all operational accounts.
   • Reconcile cleared transactions against Master_Ledger entries. Update Entity_Type to Actual and confirm exact settlement dates and amounts.

2. Actuals Lock-In:

   • Lock the prior week's data. Prevent manual edits to historical Actual rows to preserve audit trails for variance reporting.

3. Accounts Receivable & Payable Schedule Refresh:

   • Pull open invoice aging reports from the billing system (Stripe, NetSuite, QuickBooks).
   • Populate upcoming customer collections in the forecast rows, aligning expected payment dates with historical customer payment behavior (DSO).
   • Update AP aging for upcoming vendor disbursements.

4. Probability Weight Adjustment:

   • Review outstanding forecast line items. Apply Probability_Weight factors based on deal stage or contract status:
     • Invoiced / Contracted: 1.00
     • Verbal Agreement / PO Issued: 0.90
     • *Pipeline Proposal (Late Stage):0.50
     • Speculative Pipeline: 0.00 (exclude from risk-adjusted totals).

5. Roll-Forward & Executive Review:

   • Archive the completed historical week. Append a new forecast week at the tail end of the 13-week window.
   • Review the Minimum Cash Point (Low Watermark) metric on the KPI Dashboard. If cash drops below the target operating reserve threshold ($500,000), flag for immediate executive intervention regarding debt drawdowns or CapEx deferrals.