Adding Prior Month Column to the Savings Budget

Is there any possibility of getting a Prior column on the Savings Budget sheet (similar to the column on the Envelope Budget worksheet) so that we can see at a glance what we spent in a category the prior month? Eventually it would also be really useful to have an “Average” column that would show the last 12 months average actuals for each category.

Maybe these things are difficult or impossible, but just thought I’d ask!

Good question, @kevinwholland. I worked up a simple solution you can add to your copy…

First, insert a new column to the right of column E.

Second, insert this formula into cell F6:
=E6&upper(text(EOMONTH(BD2,-1)," MMMM"))

Third, insert this formula into cell F8:
=if(M8="Category",if(N8="Expense",-1,1)*sumifs(INDIRECT("Transactions!"&$BD$30&"2:"&$BD$30),INDIRECT("Transactions!"&$BD$29&"2:"&$BD$29),$B8,INDIRECT("Transactions!"&$BD$28&"2:"&$BD$28),">="&(EOMONTH($BD$2,-2)+1),INDIRECT("Transactions!"&$BD$28&"2:"&$BD$28),"<"&(EOMONTH($BD$2,-1)+1)) ,iferror(1/0))

Fourth, expand the formula in cell F8 to the bottom of column F.

Keep in mind that if/when you update or restore your Savings Budget, you will need to reimplement this change— it will be overwritten.

Let me know how this works for you.


As for your question about “an average”, consider leveraging the Yearly Budget which works in the same spreadsheet and off the same budget as the Savings Budget. That sheet will pull monthly actuals which can be averaged.

This was on my mental todo list, thanks!

This worked great, thanks very much!

1 Like