Cashflow Waterfall Forecast with Scenario Toggle
โ Accountant
Sheets: Dates in A, OpeningCash in B, Inflows range C, Outflows range D; Scenario cell E1 = 'Base'/'Upside'/'Downside'Professional Guide
This Excel solution builds a rolling cashflow waterfall that supports scenario toggling and contribution breakdowns for Treasury teams. Use a master timeline (daily or monthly) and separate inflow/outflow detail tables. Key formulas: dynamic OFFSET/INDEX with MATCH to pull scenario multipliers from a scenario table; SUMIFS to accumulate inflows and outflows by date; cumulative cash = OpeningCash + running total of (Inflows-Outflows). Add helper columns for working capital changes and debt service. Implement data validation on scenario cell (E1) and use CHOOSE or XLOOKUP to switch scenario multipliers. Add conditional formatting to highlight negative daily balances, and include a final sensitivity table showing peak deficit by scenario. Lock formulas and protect structure before sharing with stakeholders.