Full-year actual+budget forecast sheet

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:

  • 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 enter in column “B” all your spend categories by which you categorize your spending in the Categories sheet. This list needs to be limited to unique items (no repeats) and should also exclude items you hide elsewhere.
  • 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 * If you only have one year of budget data, I FY X+Y Estimate column D formula should work fine. If you have multiple years, you many need to adjust the columns referenced in the Categories sheet.

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.
  • 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 E.

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

This is my first share, be kind :blush:

I am not sure if I have the polarity of the cells correct. In my own worksheet, I track budget spend items as positive and income as negative. Here I reversed it. Please let me know if that is incorrect and I can adjust.

Any feedback always welcome.

This is awesome! Thanks so much for sharing @joern

I tried to access your sheet and it looks like you haven’t shared it publicly yet.

:point_up: Do you mean first row here?

:point_up: Does this mean “hide” using the “Hide from reports” column in Categories?

:wave:

1 Like

what a fabulous idea. i’m excited to see your sheet. thanks for this!

Thank you - I think I fixed the permissions - pls confirm? I also made the edits @heather suggested

1 Like

@joern, yes, the sheet is public now.

:point_up: this is intriguing. How does it work? Are the formulas looking for “on” before they will do something??

Under your “To configure” I would also include some steps on how folks should get it (e.g. step 1, open the example sheet below and make a copy from the file menu, step 2, right click the “FY X+Y Estimate” tab and choose “Copy to” and copy it to your Foundation template (assuming this is intended to work only on top of Foundation).

@heather

Thanks!

On/off - yes it uses a simple “if” check and doesnt run the formula unless set to “On”. Saves a ton of computation that is unneeded resulting in faster sheet response time. However, if you have lots and lots of IF checks, you will notice a slow down - wont happen with this sheet, but I did experiments with some matrixes of dates & categories that will quickly give you a couple hundred IF check on a single sheet and I can tell you that you will notice that.

I haven’t actually copied the sheet into a new workbook yet. I will copy you suggestions into the instructions.

1 Like

@randy I wonder if this is something we could consider for some of our calc intensive sheets - we’d have to coach customers on how to use it… but interesting idea.

1 Like

Hi @joern,
What a great sheet! Congrats.

I really like the way you converted your useful business metric dashboard into a personal one using Tiller.

Also, the “Calculations On/Off” concept is a clever idea. But I think the sheet can use some different formulas that will speed up the calculations.

Here’s a couple of suggestions.

  1. Fix the typo at the beginning of your post from “Is use” to “I use”.

  2. You might want to put the Total Spend row near the top of the sheet in Row 9. Then you don’t need to adjust that total formula if you add or subtract categories. Instead of the Total Spend formula of =SUM(C9:C25) in C26, you could just use =SUM(C10:C) if totals are in row 9. Some of the formulas I list below might need to slightly change if you do this.

  3. I looked into a couple of ways to improve the formula for column C. I was hoping to use a single ARRAYFORMULA() with a SUMIFS() for the entire column. But the date range needs to be filtered from the start of the year until the last day. And ARRAYFORMULA doesn’t work with SUMIFS().

    Instead, I came up with a formula that would need to be filled down for each row:

=if($C$5="On",IF(ISTEXT(B9:B),SUMIFS(Transactions!E$2:E, Transactions!D$2:D ,B9:B,Transactions!B$2:B,"<="&$E$5,Transactions!B$2:B,">="&DATE($E$6,1,1)), IFERROR(1/0)),"Calc off")

I agree with you that QUERY() is very useful, but I think the SUMIFS() is more efficient here. Also, you don’t need to add that extra Year column in the Transactions sheet. My proposed formula sums the amount for transactions where the category matches the name in Column B where the date range in the Transaction is less than or equal to the Last day included cell and greater than or equal to the first date of the selected year. =DATE($E$6,1,1)

  1. For the Total FY Estimate in column F, you could just use this one formula for the entire column:

={"Total FY Estimate";if($C$5="On",ARRAYFORMULA(IF(ISTEXT(B9:B),C9:C+D9:D+E9:E,IFERROR(1/0))),"Calc off")}

Any row with a spending category will get summed. If no spending category is listed, Total cell will be blank.

  1. Many of Tiller’s sheets use a hidden section where we lookup the column letters in the Transactions and Categories sheet. We use the results of those lookups in formulas so the sheet will work for any Transactions and Categories sheet. You can see how that’s done in the Monthly Budget or Insights sheets. This is NOT something you need to do, but it would make it work more universally.

Again, I think you did a great job on the sheet. You don’t need to make any changes. I’m just sharing this feedback in case it helps.

Jon

@jono

super useful feedback. I will implement this weekend and report back!

thank you very much!

1 Like

@jono

I made all the changes you suggested, works like a charm, thank you!

2 Likes

Hi @joern,
Nice job! And that was quick. I recommend folks give this a try.

I figured out one more improvement you might want to consider. It generates all the Category Actual YTD values in Column C with just one formula in one cell.

If you replace the formula in C10 with this and delete all the other formula in column C from row 10 down:

=IF($C$5="On",ARRAYFORMULA(IF(ISTEXT(B10:B),SUMIF(Transactions!D2:D&IF(Transactions!B2:B>=DATE(E6,1,1),1,0)&IF(Transactions!B2:B<=E5,1,0),B10:B&11,Transactions!E2:E),IFERROR(1/0))),IFERROR(1/0))

While ARRAYFORUMULA() doesn’t work with SUMIFS, it does work with SUMIF. I used SUMIF with some &s and internal IF statements to create the date range.

The calculations are pretty quick so the On/Off switch might not be needed.

Best,
Jon

1 Like

@jono

Awesome, I implemented the array formula. Works great.

Using the & combinations are a bit hard to comprehend, but it works.

Quick question: why does =ISTEXT(B10:B) provide TRUE in cell C10 but FALSE if I put it in cell C7? I was experimenting with different formula permutations and noticed that I couldnt get istext to work.

@joern,
Good deal on the updates. These new formulas make the sheet work very quickly for me.

Yes, the “& combinations” are pretty abstract. I’ll try to write a post to explain it better because I think it could be very useful.

Regarding the ISTEXT(B10:B), it will check if there is any text in each row from B10 to the botttom of the B column. Since B10 is “Charity”, is will be TRUE for row 10. Since B7 is an empty cell, it will be FALSE. All the empty Category cells in column B after your last entered Category will also be FALSE. The advantage of this is that when FALSE, no value is displayed in Column C. IFERROR(1/0) generates the no value.

I hope that made sense.

Jon

There is another way to use this Forecast. If you use “Groups”, you can duplicate this Sheet.

  1. Add Groups to your Transactions tab.
  2. Change the formulas to pick up the Groups column in the transactions tab.

You can forecast Groups. This might be useful to you if you prefer to manage expenses in buckets or cost centers.

This is absolutely great! I really like the X + Y idea. It sure beats looking at a spreadsheet with all 12 months of data. Thanks for sharing!

Hi @jono, thanks for the arrayformula. That’s a really useful improvement. Do you think the “Budget Remainder of FY” formulas in D10:D can also be turned into an arrayformula?

Hi @ed.w ,
I don’t believe INDEX and MATCH combinations work with ARRAYFORMULA. So there would need to be another way to get the SUM of the current and future months using ARRAYFORMULA.

I tried a few things but wasn’t quickly able to get it to work. But there is probably a way to do it.

There is a solution where you could turn your Categories sheet into a tall sheet with the headings for Category, Budget Month and Budget Amount. Doing an ARRAYFORMULA and SUMIF on that data would solve this problem. But that would take a good number of extra steps.

Anyone else have a suggestion?

Jon

@blarue001 I just updated the sheet to allow you to dynamically toggle between Groups and Categories. I also posted a new topic explaining the details, pending approval.

@jono and @ed.w I changed the formula in v1.1. for column D to be based on sumproduct so that I could do a a 2-dimensional sum of lookup. No idea if you can ARRAYFORMULA that or not.

1 Like

@joern,
Nice updates.

I’m pretty sure SUMPRODUCT() doesn’t work inside ARRAYFORMULA() based on some google searching.

@joern good job, exactly what I was thinking