Monthly Cashflow - Spending Plan Alternative

Google Sheets Show & Tell
1

What is the goal of your workflow? What problem does it solve, or how does it help you?

The Monthly Cashflow sheet allows me to keep up with how much money that I have to spend on things that aren’t bills. Bills being a lose term to any kind of recurring expense.

How did you come up with the idea for your workflow?

I used Simplifi by Quicken for a little while and they had this feature and I more or less copied the way it works. It’s really simple and so far my favorite way to make sure I’m not overspending on any given month.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

Pretty much everything happens on the Monthly Cashflow sheet. It does pull in the Spending Actual from F7 from the Monthly Budget Sheet but that the only time it interacts with another sheet. There aren’t any scripts and all of the formulas are basically addition/subtraction.

The sheet is pretty manual. First you need to input all of your expected incomes and how much you expect to earn that month. If your paycheck is higher than budgeted, your left to spend will increase when you put in the actual amount. If it is less than expected then it will go down. You do the same thing for your expenses.

The B and C cells are just simple addition/subtraction formulas. It gets your spendable income from the income that you input on the income section and will move up or down if the actuals are different. The Bills & Subscriptions works the same way.

Spending Plan is just the difference between Spendable Income and Bills & Subscriptions.

You can manually enter a Savings Goal total in if you to allocate some money to savings and keep it out of your Left to Spend amount.

Spent so Far takes the Spending Actual from the Monthly Budget sheet and subtracts the sum of your actuals from the M column.

Left to Spend takes the difference between Spending Plan and Spent so Far. This is the amount of money you have left to spend on things that aren’t accounted for in the Monthly Cashflow sheet.

Anything else you’d like people to know?

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

Yes. I’m not very adept at sheets so if you can make the sheet more aesthetically pleasing or improve its functionality please do. I wouldn’t mind seeing any improvements to it as I’m sure it could be improved upon.

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

2

Awesome! Thanks for sharing, @Damon!

3

Hi @Damon,
Thanks for sharing your sheet. It provides a simple way to figure out how much you have spent so far and how much you have left to spend.

The sheet offers a different way of monitoring cash flow. It differs from existing Tiller sheets because you are not making a budget for each category for a month. You are making budgets for specific transactions on specific dates. Budgets are entered here and not on the Categories sheet.

For example, you list Salary and Interest twice, on different dates.

This system has some advantages but also some disadvantages.

At first, I was hoping to automate your sheet to calculate the Actuals column, but the normal way of doing that matches for category totals for the month. If I tried to match for the Description or Category on a specific date, it might not match if the actual transaction was reported a few days different for the budgeted date. So, it would be hard to automate it accurately.

A word of warning. The sheet refers to F7 in the Monthly Budget sheet to get the Actual Spending amount, as Damon noted above. But, the Monthly Budget sheet can be set to any month and year. You need to make sure the month and year of the Monthly Budget matches Monthly Cashflow sheet.

I see you made a copy of the manual Income and Expense charts in the hidden columns. I’m not sure why this is needed? You could use formulas on the charts in columns E thru M to get the results for columns B & C.

For example, instead of getting Spendable Income in C4 using the formula =S2 and then in S2 = sum(S4:S), you could just put this is C4:

=sum(H4:H)

Anyone using this sheet should be warned, as Damon does, it’s a very manual process. And it could be easy to make an error if you are not careful when manually updating it.

An alternate approach that might achieve similar results would be to Group all the Bill / Recurring expense categories under 1 group. Then by using the existing Tiller Monthly Budget you would automatically generate Actuals and Budgeted amounts (if Budgets were added to the Categories sheet). You could compare the Bills group to the other expense group(s).

Jon

4

The reason that I made the array formula in O3 and U3 is mainly for the expenses total column in M. If the “actual” is blank then the total column uses “budgeted”. If “actual” is filled out it ignores the “budgeted” so that if you send more or less than budgeted it will reflect in the left to spend. There is probably a cleaner way to do this but I didn’t think the total columns in E:M made for a clean UI.

I’ll edit my post to reflect that you need to make sure the right month/year is input in the Monthly Budget Sheet. That was on oversight on my part, thanks for pointing it out.

1 Like
5

Thanks for clarifying that @Damon.

Now I see why you made those hidden columns. We use a lot of hidden columns, when needed, in many Tiller sheets. So your hidden columns concept to use them makes sense.

You don’t need to make any changes to the sheet, but to be more efficient, you could just calculated Income Totals and Expense Totals in the hidden part. Then, have the formulas refer to the viewable part.

For example, in S4, instead of:
=if(ISBLANK(R4),Q4,R4)

Use
=if(ISBLANK(H4),G4,H4)
Then you aren’t duplicating the Budget and Actual columns.

While your sheet works fine, if you want to take the sheet to the next level, you could use an ARRAYFORMULA for the calculated Totals columns. Instead of having to fill down the formula for each row, an ARRAYFORMULA achieves all the calculations in the entire column with one formula.

=ARRAYFORMULA(IF(ISNUMBER(R4:R),IF(ISBLANK(R4:R),Q4:Q,R4:R),IFERROR(1/0)))

or using the viewable columns:
=ARRAYFORMULA(IF(ISNUMBER(H4:H),IF(ISBLANK(H4:H),G4:G,H4:H),IFERROR(1/0)))

The first IF checks for whether there is a number in the Actual column. If no number, then the IFERROR(1/0) returns a blank cell. Zero is counted as a number. This is for any row with no Actual entry.

The second IF checks if H is blank or zero just like your original formula. But since it uses ARRAYFORMULA and the range H4:H, it does the formula on all cells from H4 to the last row in the sheet.

Jon