For Google Sheets: I’m doing something that manipulates two things in the Savings & Debt solution (Docs: Savings Goal & Debt Tracking Sheet), as well as adds a new sheet to the workbook (full explanation below). I’ve never offered a solution to the community before, and don’t know how to go about it. I know for users that want to implement this (might be none, as it’s just fixing an issue that bugs me personally), the easiest way is to add the new sheet, then copy the two formulas I manipulated and drag it to the bottom of the Savings & Debt sheet to fill the replace the rest of the formulas. But do I upload a solution with the Savings & Debt sheet already fixed as well as the new sheet, or just the new sheet? I know when I integrate some community solutions into my own, I just hit a button and the sheets download into my solution. But with other solutions I’ve come across, I’ve had to download a separate workbook and manually add the sheets/rename to remove “Copy of”. Just want to get some insight into how it’s all done. Thanks!
Full explanation of what I’m doing. No real need to read below unless interested:
I really like the ‘Savings & Debt’ template, but I do not like that it shows that the full amount set for the current month is available right at the beginning of the month (and thus the full amount is added to the ‘allocated to goals’ total for the account its tied to). It’s accounting for ‘future money’ that I do not have yet. It makes sense on the Savings Budget sheet where you are tracking what you have available and trying to stay under that amount. But not as much so on Savings & Debt, where you’re trying to see if you’re on track with a budget goal.
I set savings goals to be funded from savings accounts. When I get paid, I put what I need into the savings account from my checking to make the budget that I set for the month. (So with a monthly budget goal of $600: If I get 2 checks in a month, I move $300 to savings. If I get 3 checks, I move $200, etc…). When I get my final check of the month and transfer the final amount, the savings goal finally says I’m current on funding. But I show as underfunded until that final transfer. Then, on the 1st of the month, it shows underfunded again until I meet the monthly goal. It’s a vicious cycle that robs me of the serotonin boost of seeing that I’m on track. I built a home page that displays my savings account balances, and the amount allocated to goals assigned to that account, displaying a red value if it’s underfunded. So the issue is even more in-my-face.
To fix this, I’ve added a new sheet called ‘Income Settings’ where you enter in your 1st pay date (or any past true pay date), and pay frequency, as well as choose the category that you categorize your paycheck under in Transactions (and some other stuff that I have for other custom solutions).
In the hidden section on this sheet, it calculates how many paychecks you should expect to get in the current month (monthly = 1, bi-weekly = 2 or 3, and weekly = 4 or 5, depending on how the days fall), as well as queries against the transactions table to get a count of how many paychecks you’ve categorized under the above category this month.
I’ve changed two of the formulas on the Savings & Debt sheet to essentially assess the current month’s total that is ‘available’ based on paychecks expected & accrued this month, instead of being a ‘through end-of-month’ balance. The first formula I manipulated was in column AC7 (‘total budget’ under the Savings Categories (through period) section).
I changed the formula in cell AC7 from:
=if(ISBLANK($Z7),iferror(1/0),iferror(SUM(OFFSET(Categories!$A$1,match($Z7,INDIRECT("Categories!$"&$AX$27&"$2:$"&$AX$27),0),$AX$7-1,1,$AX$8-$AX$7+1)),0))
To:
=if(ISBLANK($Z7),iferror(1/0),iferror(SUM(OFFSET(Categories!$A$1,match($Z7,INDIRECT("Categories!$"&$AX$27&"$2:$"&$AX$27),0),$AX$7-1,1,$AX$8-$AX$7)) + (($AB7/'Income Settings'!$U$26) * 'Income Settings'!$U$27) ,0))
The only part that is different is at the end. I am removing the +1 to remove the current period’s budget from the calculation. I am then taking the monthly total, dividing it by the expected paychecks this month, and then multiplying that value by how many checks have been received this month.
$AX$8-$AX$7+1)),0))
To:
$AX$8-$AX$7)) + (($AB7/'Income Settings'!$U$26) * 'Income Settings'!$U$27) ,0))
That assigns the ‘to date’ value for the savings goal, and only updates once you have marked that you have received the income to do so. You would have to enter the calculation into AC7, then drag it all the way down to change the rest of the sheet.
This does mess up one value in the Savings & Debt sheet, which is in cell J6, the req’d budget calculation. This value is calculated by taking the remaining balance, adding the current period budget, and dividing by the periods left. Since the ‘amount remaining’ in Column F changed (due to part of the current month balance moving to the ‘Available’ balance), the calculation was thrown off.
={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F+I7:I)/(H7:H+1),2))))}
To fix this, I replaced it with this formula:
={"REQ'D BUDGET";arrayformula(if(isblank(A7:A)+ISBLANK(D7:D)+(E7:E>=C7:C),iferror(1/0),Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$U$26) * 'Income Settings'!$U$27))/(H7:H+1),2))))}
Again, the end is the only thing different. The new formula essentially rebalances that calculation above by adding back in the value that was taken out and moved to ‘Available’
Iferror(ROUNDUP((F7:F+I7:I)/(H7:H+1),2))))}
vs.
Iferror(ROUNDUP((F7:F + I7:I + (($I7:$I/'Income Settings'!$U$26) * 'Income Settings'!$U$27))/(H7:H+1),2))))}
This solution assumes you have one job that is paid weekly, bi-weekly, or monthly, and that you fund your savings goals from that pay source. You don’t have to fund it from there, but the solution will still just break down the total monthly into chucks instead of the full month balance based on the input in income settings.
Full example:
So for a monthly goal of $600, instead of adding $600 to the available balance on the 1st, it adds nothing to start. You set up that you’re paid bi-weekly, and put in that your 1st check date was March 3, 2023, and that you are paid bi-weekly on Friday. My code would calculate that you get three checks in March (3rd, 17th, 31st), and two in April (14th, 28th).
Say we’re back in March. March 1st and 2nd, nothing is added for this month. The Savings & Debt sheet picks up that you are supposed to be paid three times this month, and you’ve received none. So you’re “On track” as far as funding goals go. Then on March 3rd, you get paid. You tag the transaction as ‘Income - Net Pay’ (or whatever you use for paycheck income). Savings & Debt sees you will receive 3 checks this month, and have now received one. So the ‘available’ balance will increase by $200 (1/3rd of the total monthly goal). It will update each time you get paid to reflect that you now need to put more money back for the goal (2nd time $400 is available, 3rd, all $600 available). Then on April 1st, it resets. It sees you are supposed to get paid twice this month, and have been paid zero times. The available balance will only consist of the balance from prior months, nothing from this month. It will show as on track until the 14th (your 1st check in April). It will then change to say you have all of the prior month’s budgets available, plus $300 of the current months budget ‘available’ (1/2 of the monthly goal amount).
I’ve just updated the title of your topic and added it to the Google Sheets Show & Tell Category.
