Cash Flow Forecast Template Ib Business

Enterprise Cash Flow Forecasting & Working Capital Tracking System

Target Framework: Investment Banking (IB) Corporate Finance / Financial Restructuring Standards
Version: 4.2-PROD

---

1. System Overview & Purpose

Purpose

To provide a rolling 13-week direct cash flow forecast combined with a monthly 3-statement integration bridge. This model is architected specifically to monitor liquidity burn rates, track working capital optimization (DSO, DPO, DIO), and identify short-term debt servicing capacity or cash deficits before they impact debt covenants.

Scope

• Operational Horizon: 13-week rolling weekly cash forecast (Short-Term Liquidity).
• Strategic Horizon: 12-month monthly cash forecast (Medium-Term Capital Planning).
• Consolidation Level: Multi-currency, multi-entity roll-up with intercompany eliminations.

Update Cadence

• Weekly Actuals vs. Forecast Variance Analysis: Every Monday at 09:00 EST.
• Rolling 13-Week Forecast Refresh: Every Thursday by close of business.
• Monthly Model Re-calibration: First business day post-month-end close.

---

2. Data Structure & Column Definitions Table

---

3. Complete Master Data Table / Tracker

---

4. Key Formulas & Calculation Logic

1. Ending Cash Balance (Rolling 13-Week)

Calculates the running liquidity balance by adding net cash flows to the prior period's ending cash.

=N(C5) + SUMIFS(MasterData[Amount_USD], MasterData[Date], "<="&B6, MasterData[Forecast_Actual], "<>Void")

2. Net Cash Flow

Aggregates inflows and outflows dynamically for a given weekly cohort.

=SUMIFS(MasterData[Amount_USD], MasterData[Category_L1], "Inflow", MasterData[Date], ">="&Start_Date, MasterData[Date], "<="&End_Date) + SUMIFS(MasterData[Amount_USD], MasterData[Category_L1], "Outflow", MasterData[Date], ">="&Start_Date, MasterData[Date], "<="&End_Date)

3. Burn Rate & Runway Calculation

Computes the average monthly cash burn based on historical actuals and projects the remaining runway in months.

=IF(AVERAGEIFS(MasterData[Amount_USD], MasterData[Category_L1], "Outflow", MasterData[Date], ">="&EDATE(TODAY(), -3), MasterData[Date], "<="&TODAY()) = 0, "N/A", ABS(Current_Cash_Balance / (AVERAGEIFS(MasterData[Amount_USD], MasterData[Category_L1], "Outflow", MasterData[Date], ">="&EDATE(TODAY(), -3), MasterData[Date], "<="&TODAY()) / 3)))

4. Forecast vs. Actual Variance (%)

Measures prediction accuracy for variance reporting decks.

=IFERROR((Actual_Value - Forecast_Value) / ABS(Forecast_Value), 0)

5. Working Capital Days Sales Outstanding (DSO)

Evaluates collection efficiency to inform A/R timing assumptions in the forecast.

=(Accounts_Receivable_Ending / Total_Credit_Sales) * 365

---

5. Summary KPI Dashboard

+-----------------------------------------------------------------------------------+
|                           EXECUTIVE LIQUIDITY DASHBOARD                           |
+-----------------------------------+-----------------------------------------------+
| Metric                            | Value / Status                                |
+-----------------------------------+-----------------------------------------------+
| Total Available Liquidity (Cash)  | $2,390,000.00                                 |
| Minimum Cash Threshold (Covenant) | $1,000,000.00                                 |
| Net Headroom                      | $1,390,000.00                                 |
| Monthly Operating Burn Rate       | -$300,000.00                                  |
| Calculated Runway                 | 7.96 Months                                   |
| 13-Week Net Cash Flow Projection  | +$1,605,000.00                                |
| Forecast Accuracy (Trailing 4W)   | 94.2%                                         |
+-----------------------------------+-----------------------------------------------+

---

6. Standard Operating Workflow

Step 1: Data Ingestion & Actuals Reconciliation (Monday Morning)

1. Export bank transaction statements via BAI2/MT940 formats for all active entities (US_OPCO, UK_SUB, HOLDCO).
2. Paste raw bank records into the staging tab and map transactions to MasterData.
3. Update the Forecast_Actual column from Forecast to Actual for all settled items matching the prior week.

Step 2: Variance Analysis & Root Cause Tagging (Monday Afternoon)

1. Run the automated variance pivot table comparing prior week's forecast to actuals.
2. Isolate any variance exceeding $\pm 10%$ or $$50,000$ (whichever is lower).
3. Document operational drivers (e.g., delayed customer remittance, accelerated vendor payment) in the notes register.

Step 3: Rolling Forecast Update & Driver Adjustments (Tuesday - Wednesday)

1. Update A/R collection schedules based on current aging reports and direct communications with the collections team.
2. Refresh accounts payable (A/P) disbursement schedules based on current open invoices and agreed payment terms.
3. Incorporate updated departmental opex projections signed off by FP&A business partners.

Step 4: Scenario Modeling & Stress Testing (Thursday Morning)

1. Apply downside sensitivity scenarios to the updated base model:
   • Downside Case A: 20% delay in top 3 customer collections.
   • Downside Case B: 15% unexpected spike in key raw material COGS.
2. Verify that liquidity headroom remains above the strict $$1,000,000$ minimum covenant threshold across all scenarios.

Step 5: Final Executive Sign-Off & Board Package Generation (Thursday COB)

1. Lock model versions, archive week-ending snapshots to the audit database, and generate PDF export of the Executive Liquidity Dashboard for the CFO and Private Equity Sponsor.