Spreadsheet -- Standing orders & direct debits tracker

PowerSavingMode

Olinda99 said:

DJDools said:

frugalmacdugal said:

Hi,

have a look HERE at MSE Budget Planner.

Thank you, but I did have a look at this last week and found it far too confusing. I wanted a simple spreadsheet for tracking direct debits.

what exactly do you want to track ? presumably the date they come out each month is the same. The SO amount won't vary, neither will most of the DDs. A pen and paper would suffice ?

I have a lot of different direct debits coming out of a number of different bank accounts, so it's just a way of keeping on top of everything. I was stung for a 'return unpaid fee' last week for not being on top of one of them (rare, but it happens, I guess). And, on a cheery note, it would be good for my SO if the inevitable ever happens.

Heedtheadvice

victor2 said:

x_raphael_xx said:

DJDools said:

x_raphael_xx said:

I just typed mine out then added formula to copy each month. ie G7 = $C$7

Thank you so much. I must be having a totally brain-fog year as even that 'G7 = $C$7' makes absolutely no sense to me. 

Lol, so I have the list with the date, DD name and amount. Then each month in it's own columns. In square G7 I write ' =$C$7 '. Then in square G8 I write ' =$C$8 ' and so on.

This will copy the amount that I put in the DD list. The $ means that when you copy that row to the next month, it will always read from C7 and won't auto adjust the formula to another column.
Here it is with formulas showing

Put your helmet on to protect you from the incoming posts telling you why that is not the way to do it!

Quite right but I will refrain from adding to it....at least till heads out of the sand and helmets on!!

x_raphael_xx

Heedtheadvice said:

victor2 said:

x_raphael_xx said:

DJDools said:

x_raphael_xx said:

I just typed mine out then added formula to copy each month. ie G7 = $C$7

Thank you so much. I must be having a totally brain-fog year as even that 'G7 = $C$7' makes absolutely no sense to me. 

Lol, so I have the list with the date, DD name and amount. Then each month in it's own columns. In square G7 I write ' =$C$7 '. Then in square G8 I write ' =$C$8 ' and so on.

This will copy the amount that I put in the DD list. The $ means that when you copy that row to the next month, it will always read from C7 and won't auto adjust the formula to another column.
Here it is with formulas showing

Put your helmet on to protect you from the incoming posts telling you why that is not the way to do it!

Quite right but I will refrain from adding to it....at least till heads out of the sand and helmets on!!

OP specifically asked for a "simple template". But I'm curious to know why mine is wrong.

victor2

x_raphael_xx said:

Heedtheadvice said:

victor2 said:

x_raphael_xx said:

DJDools said:

x_raphael_xx said:

I just typed mine out then added formula to copy each month. ie G7 = $C$7

Thank you so much. I must be having a totally brain-fog year as even that 'G7 = $C$7' makes absolutely no sense to me. 

Lol, so I have the list with the date, DD name and amount. Then each month in it's own columns. In square G7 I write ' =$C$7 '. Then in square G8 I write ' =$C$8 ' and so on.

This will copy the amount that I put in the DD list. The $ means that when you copy that row to the next month, it will always read from C7 and won't auto adjust the formula to another column.
Here it is with formulas showing

Put your helmet on to protect you from the incoming posts telling you why that is not the way to do it!

Quite right but I will refrain from adding to it....at least till heads out of the sand and helmets on!!

OP specifically asked for a "simple template". But I'm curious to know why mine is wrong.

For a start you entered all the formulae manually. You could have just entered the top one as absolute on the column only (eg =$B7 in cell F7) and copied it down.

But say you used that model for subsequent months and then when items increased in cost, if you just updated the cost in your green highlighted cells, all the history would change and ongoing balances would therefore be wrong.

Far simpler just to use the "green" cells for the first month, then copy them over to each following month (or enter a formula linking back to them) then adjust the copied cells or formula as necessary.

Don't get me wrong, it is a good start, but if you want to explore the spreadsheet capabilities more, it could be improved, as would your understanding of Excel.

x_raphael_xx

victor2 said:

For a start you entered all the formulae manually. You could have just entered the top one as absolute on the column only (eg =$B7 in cell F7) and copied it down.

I usually use the dragdown/autofill method, however when I add another month, I copy the whole previous month and paste. Without the 2nd $ the cell number would change.

But say you used that model for subsequent months and then when items increased in cost, if you just updated the cost in your green highlighted cells, all the history would change and ongoing balances would therefore be wrong.

Far simpler just to use the "green" cells for the first month, then copy them over to each following month (or enter a formula linking back to them) then adjust the copied cells or formula as necessary.

I delete previous months once passed, and update the beginning month totals. I'm not interested in the history, just how any increase in a DD (ie my electricity recently went up) would effect the upcoming months, so I can budget.

Don't get me wrong, it is a good start, but if you want to explore the spreadsheet capabilities more, it could be improved, as would your understanding of Excel.

Not at all! I love spreadsheets and excel formulas and always happy to learn new tips and tricks.
When I discovered the formula for combining two data entries into one cell I was ecstatic 
I asked because I wondered if I could improve mine in anyway.

victor2

x_raphael_xx said:

victor2 said:

For a start you entered all the formulae manually. You could have just entered the top one as absolute on the column only (eg =$B7 in cell F7) and copied it down.

I usually use the dragdown/autofill method, however when I add another month, I copy the whole previous month and paste. Without the 2nd $ the cell number would change.

But say you used that model for subsequent months and then when items increased in cost, if you just updated the cost in your green highlighted cells, all the history would change and ongoing balances would therefore be wrong.

Far simpler just to use the "green" cells for the first month, then copy them over to each following month (or enter a formula linking back to them) then adjust the copied cells or formula as necessary.

I delete previous months once passed, and update the beginning month totals. I'm not interested in the history, just how any increase in a DD (ie my electricity recently went up) would effect the upcoming months, so I can budget.

Don't get me wrong, it is a good start, but if you want to explore the spreadsheet capabilities more, it could be improved, as would your understanding of Excel.

Not at all! I love spreadsheets and excel formulas and always happy to learn new tips and tricks.
When I discovered the formula for combining two data entries into one cell I was ecstatic 
I asked because I wondered if I could improve mine in anyway.

If you are not interested in any history, then surely you could just have one month in the spreadsheet - the current month, and just manually change the starting balance as you change months. No need to copy the previous month at all then. Personally, I would want to see the history, but if you only want to look forward one month, then that would do.If you wanted to predict the forthcoming year, you could very easily create a column for each month of the year, although they'd be "current month", "current month+1" etc., and put in a formula linking to the master table of costs, absolute on the column only, so that it will copy down and across if necessary (like you add a new item). When an item increases, you'd have no history to worry about, but your 12 month ahead view would update. Although if you're not accommodating changes during the forecast, just add an "annual cost" column, which is 12x the current month costs.

I have a spreadsheet which has monitored my phone and broadband monthly costs since 2008. Must admit I don't look back much, but it is interesting if you're that way inclined.

I just copy the elements of the bill each month to the following month and adjust them if/when necessary. I calculate the total and cross check it against my actual bill. I also calculate the percentage change to the previous month and 12 months ago. My last bill was 19.95% less than it was a year ago, which is nice to see.

Heedtheadvice

There above are some good pointers to the 'low level' I.e. down to cell level good practice.

To give more ideas

The OP asked for a 'good', 'template' to 'track' and then later 'simple'.

Probably pedantic but your solution has shortcomings ( including as above comments ) is not a template as such and we do not know what is meant by track in the OPs query. Pedant over...

At a high level: Always a good idea to start with the requirement!

Obviously yours will meet your need but is does not really track the SOs/DDs as it does not record the history (you do not want to track that at present) is partial data recording -does not record which bank and does not record actual planned or transacted dates etc.

Thus it limits what analysis can be done Such as, over time, the ability to chart planned and actual transactions and running daily balance or (for several banks) individual balances too. Similarly ( given you delete monthly data on updates ) it is near impossible to look at changes over time for individual transations value or other chsracteristics. 

As we do not know the OPs percieved requirement or what future potential requirement to 'track' could be needed, the basic limited monthly discard solution is not a flexible solution. Flexible (amongst other things) equals good

Suggestions

It is always good to separate data entry on to one sheet and do the tracking analysis on another sheet. Record lots of data entry info in columns such as full planned transaction and actual transaction date, destination ( such as recipient), purpose of transaction, type ( SO, DD or maybe other routine transaction), transaction amount.

All these individual entries could be done on a row for each column with descriptive column header titles.

This is then a database of transactions that just require simple entries every month and can be edited if errors found. Copy and paste or copy down can be used for data that changes infrequently. Useful to do entries in chronogical order but can be sorted or filtered without affecting analysis. Colour coding of entries can be used to trap entry errors that do not meet one's own defined rules.

That database can then be the source of the analysis often best via a pivot table or tables on a separate sheet (or sheets) to the entry data.

Thus still simple but also flexible ( data entered need not be important now so remains unused but can easily be used later purely by pivot table selections and options. Still simple but very powerful!

That type of implementation can also be used to find errors either of data entry or cell computations rather than just what is needed as a result.

OK, all a bit high level but that, plus built in Excel functions helps to produce a good working workbook.

Hope that helps? Excel is very powerful and there are several ways to achieve desired results. Something simple that can be easily understood at a later date is good practice so things like named cell or ranges and headers is simple but calculations lculations. All with learning.

Le_Kirk

I used to use Excel for banking (I use it for work so well versed in it and its complexities) but then discovered Microsoft Money in Windows XP and it is still working in Windows 11.  That is no longer available to buy but I discovered KMyMoney, which is free.  I would have used it if a) M$Money had failed in W11 and b) it had been easier to transfer existing Money accounts to it.  I thought it would be great for someone starting from scratch and it can be as simple as adding your accounts, along with all your DDRs and STOs and updating weekly.  You then don't need to worry about formulae in Excel, particularly $ (absolute)!

DigSunPap

It really doesn't have to be a complicated spreadsheet - just a list of all your outgoings along with your incomings. Then using simple =SUM(....) equations in Excel can help you to work out how much you have left over. I do this every month. Can even factor your savings into it also.