What is the goal of your workflow? What problem does it solve? How does it help you?
I use this to very intuitively and quickly see where I have been straying from budget in terms of spending.
This sheet allows me to Actual Spending, Budget and Budget less Actual for either Categories or Groups.
I use this sheet for three things:
- Understand and drill-down where I am straying from budget
- Inform corrective action
- Quickly see how different categories or groups are trade-off each other (e.g., did I move spending from Restaurants to Groceries)
How did you come up with the idea for your workflow?
I find PIVOT tables very useful in understanding my data, esp. if paired with conditional formatting. However, they are finicky and hard to maintain over time. So I built my own hard-wired version of the data I am interested in seeing along with the toggles I care about.
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 Budget vs. Actual. 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 C13:N in the FY Budget vs Actual 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 Budget vs Actual” tab and choose “Copy to” and copy it to your Foundation template
- Ensure that the formula in C13:N 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 C13 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
- Select the calendar year
- Select whether you want to see Actual (spend), Budget (spend), or Budget less Actual (spend)
Please note that the sheet excludes Categories/Groups that you have marked as Hide from Reports.
Is it ok for others to copy, use, and modify your workflow?
Yes. Please share any interesting new uses.
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.