🏆 Cash Flow Forecast (Excel)

[bdunks]

Overview

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

Installation

1. Download the Cash Flow Forecast (Excel) Template
2. Follow this guide to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.

Usage

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).

Excel Specific Instructions

Dynamic Charts

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.

Manual configuration:

1. Right-click on the chart and select “Select Data”
2. Under Legend Entries (Series), click “Edit” and change the formula to ='Cash Flow Forecast'!CashFlowChartSeriesData
3. 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:

VBA Macro

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.

Notes

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.

[jono]

Thanks for doing this @bdunks

[bdunks]

Thank you @adekunledauda for reporting an issue when the Categories table has non-numeric values.

I’ve updated the sheet with a little extra error handling so any non-numeric cells in your Categories sheet will be treated as “$0” for cashflow forecasting.

[henryb]

thanks for your great work @bdunks

[randy]

Wow! … Just wow!

First off, @bdunks, thanks for taking the initiative to port this popular template to Excel.

Second, I’m really impressed with the fidelity to the Sheets version. Not only the UX, but I added test data and it all lined up to the decimal point to values output from the Sheets version.

Finally, you’ve done a great job with the build quality, using spill ranges and LAMBDA functions throughout the calculations. The interoperablity with your Retirement Planner is also welcome and well done.

Overall, the worksheet is working great. If you’re open to making a few small changes, a few minor things that I noticed:

• The chart is a little wonky when year-range is updated The x-axis doesn’t grow or shrink. Should empty cells as “gaps”? Or can the data range be more dynamic?
• The range on the chart-type data validation includes a ton of blanks (=$AB$38:$AB202)… consider tightening this up.
  -The Learn & Discuss link should point to https://community.tillerhq.com/tag/tcs-cf-forecast?

If you’re familiar with the Tiller Builder Rewards Program , you know that we award a sliding scale of cash prizes based on:

• Value of concept to the personal-finance community
• Value & quality of Tiller integration
• Uniqueness of concept vision
• User experience & usability

As the Community’s first long-term planning template available in Excel, this solution with its create build quality is a homerun and we are excited to award you $750 for building and documenting this Cash Flow Forecast template-port to Excel.

Thank you @bdunks!

P.S. You might notice a similarly effusive award and recognition in the thread for your Retirement Planner port for Excel.

[2fromthenorth]

The cash flow forecast is great. I have a favor to ask. For my purposes, I would like to start with the YTD actuals. How might I edit the Categories budget formulas to do this?

[bdunks]

Hi -

The current sheet does look at actual transaction postings at all, so this could get complex.

I’d recommend adding one or more life events with a start and end date of the current year to true up income and expenses with actuals manually, and you can adjust as needed.

I know it’s not a “real-time” solution, but I hope it helps.