Full-year actual+budget forecast sheet v1.1

NOTE: I made some updates to this sheet, principally adding the ability to toggle between Categories and Groups. I retained the older version as a hidden sheet for those not interested in this version. This version requires you to add a column in Transactions that maps Groups to Categories. For the life of me, I couldn’t find a way to do this without that helper column - any Sheets whiz kids with better ideas, I am all ears.

What is the goal of your workflow? What problem does it solve? How does it help you?

I use this to better estimate my total full-year (FY) spending by category, which in turn informs my forecast for how much I can save this year and what my spending budget for next year should be.

It allows me to see how much I have spent up to a certain date (actual) and how much I expect to spend over the rest of the year (forecast) by combining my remaining annual budget with manual adjustments.

Every year I develop a directional soft-budget for the year that I measure my actual spending against. Combined with an income forecast, I use this to estimate how much I can save in a given year and how I expect that to extrapolate in the future. This in turn allows me to project my networth and forecast when I could retire.

I use this sheet for three things:

  • Predict actual vs. budgeted spending for a calendar year
  • Predict actual vs. budgeted savings for a calendar year
  • Inform next year’s budget with more accurate recent spending behavior

How did you come up with the idea for your workflow?

In my day job - running a P&L in corporate America - we have a monthly report that tells us how much we have spent and earned to-date in our fiscal year, and how much we expect to spend and earn for the remainder of the year. The remainder of the year is driven by the budget plus adjustments that correct the budget expectation to be closer to expected reality. In the end, we can see what our fiscal year profit forecast will be and what drivers are impacting that negatively or positively. Usually, we call this a X+Y FY forecast, where X is the number of completed months, and Y is the number of outstanding months. For example, in October I will get the 9 +3 FY report, in November the 10+2 and so on.

Personally, I have always found these the most useful reports in informing how to run a business tactically, I wanted something comparable for my personal finances.

What are the sheets included with your template? Does your workflow use any custom scripts or formulas?

There is one custom sheet, which I call FY X+Y Estimate. It feeds off the Transactions and Categories sheets.

To configure:

  • Make sure your categories, groups and budgets in Categories sheet are set up properly
  • Add a column in Transactions that maps Groups to each row, the template expects that column to be “G” in transactions. If different, you need to change the reference in cell to C10:C in the FY X+Y Estimate later on
  • Copy and paste the ARRAYFORMULA from my Transactions!G1 to your Transactions!G1 or corresponding cell if it does not already exist
  • Open the example sheet below and make a copy from the file menu
  • Right click the “FY X+Y Estimate” tab and choose “Copy to” and copy it to your Foundation template
  • In FY X+Y Estimate ensure that the formula in column C is pulling correctly. In your transactions sheet your date should be in column B, the category in D, the amount in E ; if your columns are different, just adjust B/D/E references in cell C10 accordingly and then copy/paste the cells all the way down for all your categories

To use:

  • As with other sheets that are more calculation intense, I included an “On/Off” switch to reduce calculation load when not actively using this page. Simply flip to off when not in use
  • Select whether you want to see Categories or Groups
  • Next manually enter the last date of actual data you want to use. I recommend entering the last day of the most recent fully completed month. Today is October 8th 2020, I would enter Sept 30th 2020. The sheet then pulls actual spend and project budget spend from Transactions and Categories.
  • Finally, add manual adjustments for the remaining months of the year that you are already aware of in column I to L. I is the category, K is the expected amount, and L is the calendar month you expect that amount to hit. If the month is same or less than the cutoff date you selected, the adjustment will be excluded.

Is it ok for others to copy, use, and modify your workflow?

Yes. Please share any interesting new uses.

I personally don’t use Tiller to track my income in detail, I focus just on spending. This might have other uses for folks tracking their income.

If you said yes above, please make a copy of your workflow and share the copy’s URL in your post

Comments

Any feedback always welcome.