Tip for cash flow forecast

Google Sheets Show & Tell
1

:exclamation: This topic has been superseded by this discussion from @bbruck.

To control our family finances, I really need two things: A budget vs actual (Yearly Budget sheet) and a look at what our projected cash flow will be. The projected cash flow is the actual spending to date plus the budgeted spending for remaining months against the budget for the remaining months. I struggled with this for a long time until I found the following quick work around:

One Time

  1. Copy the Yearly Budget tab to a new Forecast tab
  2. In the Forecast tab, add a new Column B to the left of the current Column B (Budget)
  3. Change the label in C6 to Projection.
  4. Enter this formula for B6: =‘Yearly Budget’!B7
  5. Copy this formula to the entire column. This ensures that the budget figures taken from the Categories tab will always display in the Forecast tab.
  6. At the beginning of February, replicate the January Actuals column to the Budget column using the formula =G6 in cell F6, then copying F6 to the bottom row of the column.

Monthly
Copy F6:F to the Budget column of the previous month.

This gives a snapshot of where you will be over and under budget at the end of the year, without disturbing the original budget figures in the Categories tab.

Make sense?

2

Hi @bbruck ,

I’m not quite sure that I follow. Are your references to B6, G6, and F6 intended to be B7, G7, and F7?

It might help to maybe show the columns that you end up with. And for steps 5 and 6 might be helpful to reference the column titles of the intended swaps.

For illustration purposes, below is what I end up with after inserting the column to the left of the original column B. I renamed C6 to Projection (step 3) and made B6 Budget (Step 4) as I’m not sure that the intent really is to make B6 =‘Yearly Budget’!B7.

image
image3386×222 17.7 KB

I think the concept might be useful, but I might not be doing it right. Also, since it’s September now, what should happen with February through August? Should the actuals from January be copied to February Budget, February actuals to March Budget, etc.?

Lastly, for the Monthly step, copy F6:F to the Budget of previous month does not make sense, since F6:F is the first month of the year.

Cheers,

AHB

3

I’m not quite sure that I follow. Are your references to B6, G6, and F6 intended to be B7, G7, and F7?
In step 4, I made a mistake. Rather than:
Enter this formula for B6: =‘Yearly Budget’!B7 it should be
Enter this formula for B7: =‘Yearly Budget’!B7
The references to G6 and F6 in step 6 are correct.

4

It might help to maybe show the columns that you end up with. And for steps 5 and 6 might be helpful to reference the column titles of the intended swaps.

Column B will be named Budget, and shows the budget from the Yearly Budget tab, which is derived from the Categories tab.

At the end of the process, all previous months of the year will show the following columns: Actual, Actual, Available. The current and upcoming months will show the following columns, Budget, Actual, Available. You can see this in the picture below. Note that the top left blue cell in the picture says “Budgeted Cashflow”; I guess in theory I should change it to “Actual Cashflow” since that’s what it now is, but it would be an extra step and I don’t do it.
Screenshot 2024-09-08 at 6.29.44 AM

5

Since it’s September now, what should happen with February through August? Should the actuals from January be copied to February Budget, February actuals to March Budget, etc.?

I wasn’t as clear as I should have been. The answer is No. You should copy each past month’s actuals to that month’s budget column:

So, to start using this tab on September 8, you would copy J6:J (February > Actual) to I6 (February > Budget) to replace the February budget amounts with the February Actual amounts; M6:M to L6 for March, etc., though August (in this case).

6

Again I wasn’t terribly clear, for which I apologize. Basically at the end of each month, you are replacing the budget column of the previous month with that month’s actual column.

So now on October 1, I notice that the Budget column for September is AD. I can take ANY of the “new” actual columns from January through August and copy them into September’s Budget column AD, since they all simply reference the column to the right of them which is the Actual figure. In the instructions I used the new January Actual column F, but you can use any of the new Actual columns.

7

Hi @bbruck ,

Thank you for taking the time to provide such detailed explanations. I made the updates per your instructions and it’s all working correctly now. I appreciate you sharing this on the forum and like the concept a lot.

Below are some additional changes I made to the sheet which automate the process of replacing Budget with Actual for completed months. Significantly, these changes also allow you to go back in time (or forward if already defined) and have the columns automatically updated.

In cell F4: Use the formula below to automatically change the cell value to Actual Cashflow or Budgeted Cashflow based on whether the month has already passed or not.

=IF(EOMONTH(F$3, 0) < TODAY(), "Actual Cashflow", "Budgeted Cashflow")

Then copy cell F4 to I4, L4, etc. (for each month)

In cell F6: Use the formula below to automatically change the column header from Budget to Actual once the month has passed.

=IF(EOMONTH(F$3, 0) < TODAY(), "Actual", "Budget")

Then copy cell F6 to I6, L6, O6, R6, U6, etc. (these are the Budget column headers for each month)

In cell F7: Use the formula below to automatically take the Actual value for the month once the month has already passed.

=IF(isblank($A$7:$A),iferror(1/0),if(EOMONTH(F$3,0) < TODAY(), G7,if(isna(match(row(F7:F)-row(F$7),'Monthly Budget'!$N$16:$N,0)),if(isna(match(row(F7:F)-row(F$7),'Monthly Budget'!$O$16:$O,0)),iferror(VLOOKUP($A$7:$A,{INDIRECT("Categories!$"&$AT$3&"$2:$"&$AT$3),offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1)},2,FALSE)),iferror(sumifs(offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1),INDIRECT("Categories!$"&$AV$3&"$2:$"&$AV$3),$A$7:$A,INDIRECT("Categories!$"&$AW$3&"$2:$"&$AW$3),"<>Hide",INDIRECT("Categories!$"&$AU$3&"$2:$"&$AU$3),if(counta('Monthly Budget'!$N$16:$N)=2,if(row(G7:G)-row(G$7)>=max('Monthly Budget'!$N$16:$N),"Expense","Income"),'Monthly Budget'!$Q$16)),0)),iferror(SUMIFS(offset(Categories!$A$1:$A,1,match(F$3,Categories!$1:$1,0)-1),INDIRECT("Categories!$"&$AU$3&"$2:$"&$AU$3),$A$7:$A,INDIRECT("Categories!$"&$AW$3&"$2:$"&$AW$3),"<>Hide"),0))))

Then copy Cell F7, highlight cells F8 to the last row of column F and select Edit > Paste Special > Formula Only.

Once you have done that, highlight cells F7:F, select copy, then select cell I7 and select Edit > Paste Special > Formula Only.

Repeat for each of the months.

Thanks again for sharing, I think this will be my default instead of using the Yearly Budget worksheet.

Cheers,

AHB

8

AHB that’s brilliant!!! Thank you so so much.
Now that it’s really automatic, I wonder if the tab can simply be shared in some way so that other members of the community could use it?

9

Hi Guys! Thank you @bbruck for this great idea! and @AHB for the diligent followup and additional share. I am a bit lost in it all, @bbruck is it possible to update your original post with the corrections so that it’s clear from there instead of needing to scroll through the subsequent replies to get to the correct information?

And please advise if I have missed something too!

Thank you.

10

Out of town. Will do this early next week.

11

Much appreciated ! Safe travels.

12

I don’t know how to edit my post. When I click the pencil icon I see a two-pane window but can’t edit the text in either pane. If someone would be good enough to tell me how to edit my original post I’ll be happy to do so.

13

The pane on the left is the text you edit. The pane on the right what it will look like when posted.

14

Hi @bbruck ,

You could always just do a new post if editing isn’t working out for you.

AHB

15

When I click the pencil icon to edit the post, Twalane’s name appears rather than mine. I’m not allowed to delete the post either.

I’ve created a new post named New Cash Flow Forecast. Could a moderator either delete or freeze this post with an appropriate notation in the main body of the post, since I cannot?

thanks!

16

Which specific post are you trying to edit?

17

The post at the top of the thread attributed to me that starts:

18

Ok, it has the number 2 next to the pencil icon. I assume that means two people have edited it. Or it means you have edited it twice before. Maybe @twalane edited it? @twalane Twalane, did you possibly edit the original post directly or indirectly? If not, why does your user id show up when @bbruck Bill goes to edit it? What is preventing him from editing his own original post? Asking for a friend :grinning:

19

@bbruck Bill,

As a test, try editing your post (hopefully right above this reply of mine) and provide a link to your new post. Let’s see if you can edit it.

I’m referring to your post where you mention you created a new post named New Cash Flow Forecast.

Editing my own post: will that didn’t work

Well, that didn’t work

This post :point_up_2:

20

Hey Clint - I can edit my replies; I can’t edit the original post. Assume it’s because Twalane edited it. When/if I can, I’ll point people to the new replacement post.

1 Like