What is the goal of your workflow? What problem does it solve, or how does it help you?
As I work toward setting up a usable budget, the anal-retentive part of me wants to see where every dollar is going. I have health related bills in health categories that are totaled up in a ‘Health’ group, which is a good start, but doesn’t cover everything. In my mind, Health Insurance is part of my ‘Health’ costs, but for me, that is paid through a paycheck deduction, so I don’t see that expense in my transactions. That’s what pushed me to setup a ‘Paycheck’ sheet in my Foundation Template, which I’ve documented here.
With my paycheck deduction data now available, the next step is to include it in budgeting. Though it mostly stays the same every month, my bi-weekly paychecks occasionally throw a third pay period into a month, which messes with budgeting a bit. Thankfully, my HR department gave us a schedule for when these occur, allowing me to project my paycheck deductions into the future. Having that info, I can now use a QUERY to lookup each months deductions, so that my budget will be more accurate.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
This workflow assumes you’ve setup a Paychecks sheet the way I described here.
In your Categories sheet, instead of typing in a dollar amount for each month for a category that relates to one of your deductions, you can instead use a variation of this formula:
=QUERY(Paychecks!$W$36:$AL,"Select SUM(X) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(X) ''",1)
Lots to explain here:
-
We’re doing a QUERY, which looks up data from another location
-
Paychecks!$W$36:$ALrefers to the range of data that we’re pulling from, in this case the Paychecks sheet starting with column W row 36 and going over to column AL -
The next part that is in quotes,
"Select SUM(X) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(X) ''", is the ‘Query’ we’re doing
The query is basically saying this:
In the range we specified (Paychecks!$W$36:$AL), add up (SUM) all paychecks that happened in the month and year that can be found in the top cell of this column (in this case, G1), and set the LABEL for this number to ‘’ (nothing, we don’t want a label).
So in the Categories sheet, I put the above formula in the left-most month of my ‘Paycheck’ category, and then fill the formula to the right, and all of the months fill in the total projected income I’ll make, based on the data I included in the Paychecks list. You can do the same thing for each of the deductions that you made a category for. Just put the formula in the left-most month of the category, change the SUM(X) parts of the formula to use the column heading of the deduction you want to include, then fill that formula to the right. In my case, I have a category for ‘Life Insurance’, but multiple deductions related to Life Insurance. No problem. There’s probably a better way of doing this with one QUERY, but it’s easy to just add them together by using a ‘+’ between multiple QUERY statements like this:
=QUERY(Paychecks!$W$36:$AL,"Select SUM(AG) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(AG) ''",1)+QUERY(Paychecks!$W$36:$AL,"Select SUM(AJ) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(AJ) ''",1)+QUERY(Paychecks!$W$36:$AL,"Select SUM(AK) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(AK) ''",1)+QUERY(Paychecks!$W$36:$AL,"Select SUM(AL) where month(W)+1=" & MONTH(G$1) & " and year(W)=" & YEAR(G$1) & " LABEL SUM(AL) ''",1)
Now I’ve got more reliable data for all those categories, and it’s visible in my budgets, reports, etc. I change the background color to a shade of yellow for the cells where I’ve put the formulas as a reminder that I shouldn’t change it manually, it’s formula based. I also add a ‘note’ to the cell that holds the category name (right-click, ‘Insert Note’) and explain it’s a query of the Paychecks sheet (I have other queries of other sheets as well, so the note helps jog the memory on those early Monday mornings).
Is it ok for others to copy, use, and modify your workflow?
Yes, please! And I hope everyone can give feedback and possibly suggest improvements to the workflow.