Changing settings in spending money

Google Sheets
,
1

I really like the concept behind the spending money sheet. However, because I have an irregular income, I even it out by allocating myself a set amount per month, and I’d like to reflect that amount in the “estimated cash flow.”

I tried manually changing my estimated cash flow to my set allocation, but it didn’t have any impact on the “estimated available spending money” box, which seems to have picked an arbitrary number (I doubt it’s actually arbitrary, but I can’t figure out what it’s based on).

So my first question is whether it’s possible to manually override the estimated cash flow amount in a way that will be reflected in the estimated available spending money box.

Second, I noticed that the “discretionary spending so far” box was very high because it included categories that I had chosen to hide. The only way I could eliminate those categories from discretionary spending was to label them as “transfers.” Is that the best solution? I’d prefer to keep them as “expenses,” since that’s what they are.

Thanks in advance.

2

Welcome @Parisian, :wave:

Sorry for the delay here.

I’m not totally sure how to do what you’re asking, but I can see the value in it.

@jono do you have ideas about how to change the “estimated cash flow” for an irregular income scenario and can you please check whether the Spending Money sheet is ignoring categories marked as “hide” on the Categories sheet?

3

Hi @Parisian,
I’m glad to hear you like the concept behind the spending money sheet.
We developed it 4 years ago, so I don’t remember all the details off the top of my head.

But looking into it, I have some answers for you.

Your idea to even out your irregular income by allocating a set amount each month makes sense. That amount should flow through to the estimated cash flow.

The sheet won’t work if you manually override the Estimated Cash Flow value. That cell is in Grey. Only the Green cells should be changed. You might get odd results if you change the Estimated cash flow cell in B3. That cell should contain the formula: =sumif(D10:D,"Income",B10:B)-sumif(D10:D,"Expense",B10:B)

You should only change the Estimate for an individual Category in B10 and down.

Regarding the “estimated available spending box” calculation, if you click the little triangle at the top right in the C column, you can open the hidden part of the sheet to see how it works.

That amount is based on the time period you select in A7 and the value in I8. I8 is the result of the Actual total of your Unselected Categories plus the estimated value of your Selected Categories. Those numbers might be negative if you are spending more than your income.

The sheet does include categories that are hidden. Transfers are no included.

If you want the sheet to not include hidden income and expense categories, you would need to make the following changes to the sheet.

  1. In H24, enter “Hide From Reports”

  2. In I24 to K24, fill down the formulas from I23 to K23. This will tell the sheet where the Hide From Reports column is in your Categories sheet.

  3. Add a new column to the right of the last column P.

  4. In Q1, enter this formula to show which transactions are from hidden categories.
    =ARRAYFORMULA(IF(M:M="","",IFERROR(VLOOKUP(N:N,{INDIRECT($K$22),INDIRECT($K$24)},2,FALSE),"")))

  5. Change the formula in E4 from:
    =sumif(P2:P,“Income”,O2:O)+sumif(P2:P,“Expense”,O2:O)

to
=sumifs(O2:O,P2:P,"Income",Q2:Q,"")+sumifs(O2:O,P2:P,"Expense",Q2:Q,"")

Make sure when you copy those formulas, the double quote marks show straight lines in Google Sheets. Sometimes they can turn into open and closed quote marks and then the formulas won’t work.

I tested this and it worked for me. But it’s possible I’m missing something. Let me know if it works for you.

Jon
cc @heather

2 Likes
4

Thanks for these detailed instructions! One question: In step 2, wouldn’t I fill down the columns from I23 to K23 (as opposed to from I23 to I24)? That’s what I did, and the entries appear as 5 (column I), E (column J), and Categories!E2:E201 (column K).

What I still don’t understand is how to allocate a set amount of income per month, given that I can’t override the estimated cash flow in B3.

5

@Parisian ,
You are correct. Good catch. You should fill down from I23 to K23. (I’ve updated the instructions above to reflect that.)

Any transactions from a “hide from report” category should say ‘Hide’ in the new column Q.

What I still don’t understand is how to allocate a set amount of income per month, given that I can’t override the estimated cash flow in B3.

Can you include the Income category that you use to record your Income as about of the options in Column A below row 9? And put the estimate in Column B of that income row?

6

I did a variation of that, which seems to work: I added in an “Other Income” category with an estimate equivalent to (1) the amount that currently appears in box 3B , plus (2) the amount I want to allocate to discretionary spending (i.e., the amount I want 3B to end up as). After doing this, the amount in 3B changed to what I wanted it to be.

Thanks for your help!

7

That sounds like a good solution. Glad you got it working the way you want.

1 Like