Updated "Yearly Budget" sheet that does not rely on "Monthly Budget" sheet and added features

[Cowboy13]

Overview

I had replaced the Tiller foundation Monthly Budget sheet with this sheet- Timeframe Actuals vs Budget Tracker; ability to exclude Groups and/or Categories. It allows you to exclude Categories or Groups and you can choose different time frames. I then deleted the Monthly Budget sheet since I was not using it any longer.

What I didn’t know is that the Yearly Budget sheet uses the Monthly Budget sheet, so it stopped working!

So, I rewrote the Yearly Budget to remove its dependency on the Monthly Budget sheet. I also added YTD running budgeted and actual cash flow.

This looks pretty much the same as the original Yearly Budget, but with the added cashflow…

You can download it here.

Installation

Simply copy the sheet to your Tiller workbook. It uses the 4 core foundation sheets- Categories, Transactions, Balance History, and Accounts.

Setup

If you want to track cash flow using the starting balance of certain accounts, you need to choose the accounts to include in columns AY and AZ…

Usage

Set up your budget in the Categories sheet. FYI- I insert additional columns each year and add the new year’s budget to the Categories sheet…

You can also condense the layout by selecting to either show or hide blanks lines between Groups.

Permissions

It is absolutely ok for others to copy, use, and modify your workflow.

Notes

You can continue to use the Yearly Budget from the Tiller foundation sheet as long as you have the Monthly Budget sheet. If you don’t use the Monthly Budget sheet and want to save some processing power and reduce the number cells your workbook uses, you can use this.

Enjoy!

[twalane]

Thank you @Cowboy13 , good approach!

[pkrug539]

This looks great! Thanks for doing it!

[pkrug539]

I just gave it a spin and it does not seem to be reading any of my transaction history. I love the idea, hope I can get it running!

[Cowboy13]

@pkrug539

Thanks for the feedback. I;d like to make sure yours gets working. When you say…

it does not seem to be reading any of my transaction history

does that mean the actuals for each month is 0? If so, which “type” of category is having this error- all Categories, certain Categories, Groups, Category type (Income or Expense), or something else?

What does BB16-19 say? It should look something like this…

Lastly are the budget numbers correct?

Thank you.

[pkrug539]

Yeah, I just get this:

Here’s what I get in BB:

It does seem to be reading my budgeted numbers fine, just not transactions.

[Cowboy13]

Thanks for the screenshots.

I think I may have put a link to an older version, before I fixed some errors. Download this file, which should have this in A2…

I also updated the link in the initial post.

Try this and if it doesn’t fix it, I see a few others things that may be causing the issue.

Thanks again.

[PCB]

Hello!

Thanks for the support. I downloaded the new file but it’s still not pulling actuals. The group indirect from the transaction sheet shows a #num error probably because I do not have a group column on my trancations sheet.

[pkrug539]

Still no luck. It’s pulling budgeted amounts just fine, just not totaling actuals.

[Cowboy13]

@PCB

I have never had to deal with the scenario of not having a Category Group in the Transaction sheet so this is a first for me! Because of this, I believe the formula to fill in the Actuals is producing an error and returning 0s for all. I am pretty sure I can fix this for the actual Transactions Category and Type, but it may still show 0s for Category group as I’m summing this from the Transaction sheet, not in this sheet.

Give me some time to dive in deeper and see how to handle this case.

Thanks.

[Cowboy13]

@pkrug539

Are you and @PCB the same? I am a bit confused now.

If not, do you have Category Group in your Transaction sheet? If not, probably the same issue as PCB. If you do, do you have the same name for any of the Categories and Category Groups? The calculations assume you do not, but it may cause issues if you do. I don’t, so I would need to do some more testing to see what happens when there are.

Thanks.

[PCB]

I removed the reference to the transactions sheet group from the query for each month and they are pulling now.

But only for the Total, income and budget lines. Groups and categories line still show 0

[pkrug539]

Nope, that’s someone else.

[pkrug539]

Sorry, realized I didn’t respond to the actual question part. I do not have a category group in my transactions sheet.

[Cowboy13]

Thanks @pkrug539. Now I know there is a common thread that neither you or @PCB have a Category Group in your Transactions sheet. This is scenario I didn’t know existed, but there are at least 2 of you! This helps a lot. I’ll see if I can come up with a solution that handles this case over the next few days.

I am curious- do you not use Category Group at all and do you still only have 2 “Types” of Transactions- Income and Expense? If you don’t use Category Group, do you group Transactions any other way or are they “flat” under Type?

[PCB]

No, I think I might have explained it poorly. I have a category column on the transaction sheet. But the core transactions sheet does not have a group column that the reference in BB19 of the sheet is looking for.

I use the categories from the Categories sheet and the Categories sheet has the groups.

[Cowboy13]

Thanks. That makes more sense.

Please send me a screenshot of AY16 through BB19 values.

Also, can you type in the whatever columns the Categories Group is into cell BB19? Something like ‘Transactions’!G2:G?

If you hard type this in and it works, then the issue is with extracting the Categories columns from your Transactions sheet, which should be an easy fix.

Thanks.

[PCB]

When I force it by pasting the code as value, the transactions pull but the subtotal for the actuals on each category line is still 0

[PCB]

[pkrug539]

I use category groups and rely on them a lot in Savings Budget.

I never thought about grouping transactions, I just have them show up chronologically?