How to Do a Cash Flow Forecast Template

Cash Flow Forecasting & Liquidity Management System (CF-FMS v2.4)

---

1. System Overview & Purpose

Purpose

To provide a deterministic, rolling 13-week and 12-month direct cash flow forecast model. This system monitors operational liquidity, detects working capital bottlenecks, prevents insolvency events, and standardizes variance analysis between projected cash flows and bank statement actuals.

Scope

Covers all operating, investing, and financing cash inflows and outflows across enterprise bank accounts, merchant accounts, and credit facilities. Excludes non-cash accruals (e.g., depreciation, amortization, stock-based compensation).

Update Cadence

• Operational Execution: Weekly rolling update (every Monday before 09:00 local time).
• Reconciliation: Daily matching of actual bank transactions against projected items ($T-1$).
• Strategic Review: Monthly variance report analyzing forecast error rate (% variance).

---

2. Data Structure & Column Definitions Table

---

3. Complete Master Data Table / Tracker

---

4. Key Formulas & Calculation Logic

1. Variance Amount Calculation

Calculates the absolute variance between projected and actual cash metrics. Placed in column Variance_Amount.

=IF([@Status]="Cleared", [@Amount_Actual] - [@Amount_Projected], 0)

2. Ending Cash Balance (Rolling)

Computes the running cash balance dynamically by adding net cash flow to the preceding period's ending balance.

=N(C$2) + SUMIFS(Master_Data[Amount_Projected], Master_Data[Date_Scheduled], "<="&B$5)

3. Burn Rate (Trailing 30 Days)

Calculates net cash depletion over the immediate historical trailing window.

=ABS(SUMIFS(Master_Data[Amount_Actual], Master_Data[Category_Level_1], "Outflow", Master_Data[Date_Actual], ">="&(TODAY()-30), Master_Data[Date_Actual], "<="&TODAY()))

4. Runway Calculation (Months)

Computes absolute runway remaining based on current cash reserves divided by average monthly burn.

=IF(B12>0, B10 / B12, "Insolvent / Infinite")

(Where B10 is Current Total Cash Reserves and B12 is Trailing Monthly Burn).

5. Forecast Accuracy Error Rate

Computes the mean absolute percentage error (MAPE) between projected and actuals for closed periods.

=AVERAGEIFS(Master_Data[Variance_Amount], Master_Data[Status], "Cleared", Master_Data[Amount_Projected], "<>0") / AVERAGEIFS(Master_Data[Amount_Projected], Master_Data[Status], "Cleared")

---

5. Summary KPI Dashboard

+-----------------------------------------------------------------------------------+
|                        EXECUTIVE CASH FLOW DASHBOARD                              |
+-----------------------------------+-----------------------------------------------+
| 1. Total Liquid Reserves          | $1,425,800.00                                 |
|    (Sum of all active bank accounts)|                                             |
+-----------------------------------+-----------------------------------------------+
| 2. Trailing 30-Day Net Burn       | -$132,400.00                                  |
+-----------------------------------+-----------------------------------------------+
| 3. Calculated Runway              | 10.76 Months                                  |
+-----------------------------------+-----------------------------------------------+
| 4. Next 30-Day Projected Inflows  | $270,000.00                                   |
+-----------------------------------+-----------------------------------------------+
| 5. Next 30-Day Projected Outflows | -$148,900.00                                  |
+-----------------------------------+-----------------------------------------------+
| 6. Forecast Accuracy (Last Period)| 98.42%                                        |
+-----------------------------------+-----------------------------------------------+

---

6. Standard Operating Workflow

1. Data Ingestion & Bank Sync (Daily - 08:00):
   • Export daily transaction feeds from all banking and merchant gateways (JPM, SVB, Stripe).
   • Paste raw transactions into the staging tab and match against unassigned Transaction_ID records.
2. Actuals Reconciliation (Daily - 08:30):
   • Update Date_Actual and Amount_Actual for items that have cleared.
   • Change transaction Status from Projected to Cleared.
   • Review Variance_Amount. Any variance exceeding $\pm 5%$ or $$5,000$ requires a mandatory note in the audit log.
3. Rolling Forecast Updates (Weekly - Monday 09:00):
   • Extend the forecasting horizon by 1 week to maintain the strict 13-week or 12-month forward window.
   • Review unbilled receivables (Category_Level_2 = Revenue); downrate Confidence_Score if client payment delays are identified by account executives.
   • Add newly contracted capital expenditures or operational expenses.
4. Variance Analysis & Reporting (Monthly - Day 1 of Month):
   • Calculate forecast error rates using the Master Calculation formulas.
   • Distribute the Summary KPI Dashboard and Variance Report to the CFO, CEO, and Board of Directors.