The Cash Flow Forecast (Excel) Template attempts to provide close to full parity with the original Google Sheets version. Thank you, @jono, for the amazing original work, and for permission to migrate to Excel and share this version with the community.
This is fully compatible with the Retirement Planner Template, which has also been migrated to Excel.
The following overview has been copied and pasted from the original post:
This solution allows you to test out different income/expense scenarios and view the impact of those changes over the long term.
This sheet looks at the long-term picture. Think years, not months.
Some of the benefits of using this sheet include:
- Easy to set up
- Baselines your cashflow with the current-year budget (other ranges can be used)
- Outputs both chart and tabular data
- Provides workspace to manually add life events (e.g. children going to college, retirement, etc) with starting/ending years, starting amounts, and growth rates
- Life events can be activated/deactivated to run scenarios and quickly see long-term impacts
- Ability to set growth rates for your core income/expenses and life events
- Download the Cash Flow Forecast (Excel) Template
- Follow this guide to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
Please refer to the original Google Sheets post for detailed usage instructions. When I first started using the sheet, I found it very intuitive. Note that some cell references have slightly change from the Google Sheets version (e.g., the Chart Options drop-down is in cell E4, not G4).
You can enhance the chart interactivity by making it responsive to the number of years you enter in cell
A1 with just a few clicks, or a quick macro.
- Right-click on the chart and select “Select Data”
- Under Legend Entries (Series), click “Edit” and change the formula to
='Cash Flow Forecast'!CashFlowChartSeriesData
- In the Horizontal (Category) Axis Labels, click “Edit” and change the formula to
='Cash Flow Forecast'!CashFlowChartYears
Alternative automated configuration:
If you’re comfortable with macros, you can also copy this simple macro as a new module into your workbook, run it once, and then remove the module it so you can continue saving your workbook as an .xlsx:
Sub UpdateCharts() ' Update Cash Flow Forecast chart With ThisWorkbook.Sheets("Cash Flow Forecast").ChartObjects(1).Chart .SeriesCollection(1).XValues = "='Cash Flow Forecast'!CashFlowChartYears" .SeriesCollection(1).Values = "='Cash Flow Forecast'!CashFlowChartSeriesData" End With End Sub
Explanation: The sheet has “Named Ranges” for the series data, which point to dynamic arrays that respond to the value in
A1. However, when copying or moving a sheet, Excel removes Named Ranges from a chart’s definition, replacing them with absolute ranges. It’s an unfortunate limitation of Excel that requires a few manual steps each time you copy the sheet.
I originally migrated this for my own use, and I’m just trying to match the Google Sheets version of this template. Please let me know if you have any issues with installation or missing functionality.