Budget and Actuals Charts for Each Category

What is the goal of your workflow? What problem does it solve, or how does it help you?
This solutions creates a side-by-side comparison of budget and actuals for each category by month. It provides a visual snapshot of activity within a category to see how the budget plan is working (or not!)

How did you come up with the idea for your workflow?
I wanted to get a quick look at the activity in my categories for planning the coming year’s budget. Looking back helps me see if my expectations were on target or where changes need to be made.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
The solution is its own tab and draws data from the standard form of the Categories sheet and the Yearly Budget sheet. The formulas assume these sheets are being used as provided by Tiller.

Anything else you’d like people to know?
The sheet takes a second or two to respond to the change in the drop-down menu when the Category is selected. So while the title of the chart changes immediately, the data take a bit to show up.

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

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

Thanks for sharing this @Brad.warren

I hooked it up in my demo sheet and it looks awesome! :slight_smile:

I would recommend making the sheet view only though so that others who check it out don’t make accidental edits to it.

Thanks. Heather. Good catch on the permissions. I have made the change.

1 Like

New here and I’m looking for visual charts and graphs and interested in trying this one. :slight_smile: How… do I add it to my own Tiller sheet? Lol. :grimacing:

In the original posting, click on the shared spreadsheet at the bottom of the post, the part I highlighted in yellow below:


It will open in a new windows. At the bottom left corner, click on the triangle to the right of the ‘Budget and Actual Charts’ sheet name:
Screenshot 2021-11-06 170442
In the menu that opens, choose ‘Copy to’, then ‘Existing’ and point to your Tiller spreadsheet. It will then add a copy of the sheet to your Tiller spreadsheet!

2 Likes

Ahhhhh thank you!!! I tried so many things; I can’t believe I missed that obvious one. :woman_facepalming: Perfect!!

Do you - or anyone else - know of a similar report that uses the overarching groups instead of individual categories? Which I guess requires a way to budget for groups instead of categories?

…I can start a new thread since is highly off topic, lol. :smiley:

I took a peak at the formulas to see if it could easily be adjusted, but it gets it’s data from the ‘Yearly Budget’ sheet which is category based, so that sheet would have to be converted to groups as well, so not an easy tweak. Yea, a new thread would be best to get the appropriate eyeballs on the topic.

2 Likes

Hi @EstherO:

@jpfieber challenged me (the benefit of working in community :grin:), and I spent a little more time around your request for charts that return data by Groups instead of Categories. Though this solution is not elegant, it should work…

  1. In the Budget and Actuals Chart sheet, click into B7. Then in the menus, select Data, then Data Validation.

  2. In the Criteria window, you’ll see a formula that looks like this: =Categories!$A$2:$A

  3. Replace the As with Bs, like this: =Categories!$B$2:$B

In the drop-down box, this then calls the Groups list from the Categories sheet’s column B instead of the Categories list from Column A.

BUT…the Query function that gathers the data for the charts is case sensitive. So the charts won’t work unless the Group names in column B of your Categories sheet are also in all caps to match the all-caps group names in the Yearly Budget sheet.

Does this work for you?

2 Likes

That looks like it does work! Thank you!

1 Like