Formula for automatically adjusting running total where change can be plus or minus values

[halesowenmum]

Hi

I have a spreadsheet with each column representing a day of the month starting with the 28th which is payday.

Down the left are all the income and outgoing that are predictable and repeating.

In row 46 I have my Predicted balance ie what it would be if the income and expenditure occurred exactly as it had been planned.

However we know life's not like that so I go in and check my bank balance every few days so I can enter the actual balance - in row 47.

I also total up the pending transactions and add them in to row 48.

What I need in row 49 is something which says right looking at the predicted, you've entered an actual balance which is + or - what you'd planned plus (if there are any) there are pending transactions which aren't included in the actual balance as they've not been applied to the account yet, so go ahead and if the actual is more than the planned balance adjust on that basis and if there are any pending transactions, reduce the amount down to take account of the pending transactions which will be going out of the account in the next 24 hour (I'd rather show myself with less balance left than more and apply it a day or two in advance as that's protective of any possible overspending).

However, I'm not sure that adding/subtracting/MINUS actually do it because it's not the case that every time I adjust the actual balance that it's necessarily going down. It could be I got a refund for something and my actual balance has in fact increased so I'm not sure how I deal with that. I need a formula I can fill across row 49 across all the columns so that by the last column which i the day before I get paid, I have an accurate view of how well I'm doing on my budgeting.

Does that make sense??

 

[iliace]

If I am understanding, in any given column, Row 46 is "net cash flow" for the day, Row 47 is "current balance per bank", and Row 48 is "total unposted transactions". In that case, 47+48 is your current balance.

To reconcile, you need to take your previous balance (from left column), add row 46 (CF) from current column, and reconcile the difference of 47+48 and that new number.

On the following day (next column), your balance-per-bank (47) should reflect any previous day transactions that now posted, as well as any remaining and new pending transactions that will now go in Row 48.

In practice, this is generally done on a per-item basis, so depending on your transaction volume may be easier said than done. But, the mechanics of it should not require more than + or - calculations.

Does that help?

 

[halesowenmum]

Hi - thanks for replying, specially on a Sunday evening from across the pond!

It works so long as it allow for, automatically, if the actual balance is higher than the planned, or if the actual balance is lower than the planned. I need a formula I can replicate by filling right which will be able to determine the difference (whether it's plus or minus, but without me having to manually say 'subtract this' or 'add that') and also deduct the pending set out in the row below. Can you give an example of what the formula would look like that can automatically handle increases and decreases in amount???

 

[iliace]

so let's say column C is the 29th.

In column B, you should have had some kind of starting balance in your bank account. Then you have your paycheck as income, and any expenses you were planning on the 28th.

In B46, you have predicted cash balance: =Starting + Income - Expenses
In B47, input your actual balance per bank
In B48, input your net amount of pending transactions.

Now, in B49 is your variance. =B47-B48-B46

If the number is negative, that means your planned expenses were higher than actual. If it is positive, means that actual expenses are higher.

In either case, for C46, your starting balance should be coming from column B, most likely B47. That will be reflected in C46 (starting).

Now, when you enter C47 your ending balance per bank as of the 29th, that number will include any pending transactions from B48 that got posted on the 29th. In C48, the new total for pending transactions will include any amounts from B48 that are still pending, plus any new pending transactions.

And so on. Does that make sense?

 

[halesowenmum]

Hi

I'm maybe not explaining this correctly (my fault) so let me try again.
In each case the predicted balance

[TABLE="class: grid, width: 110"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]a
28th[/TD]
[TD]b
29th[/TD]
[TD]c
30th[/TD]
[TD]d
31st[/TD]
[TD]Explanation[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Predicted balance[/TD]
[TD]£2,500[/TD]
[TD]£2,400[/TD]
[TD]£3,000[/TD]
[TD]£2,900[/TD]
[TD]I find out on the 29th that I'm getting given £600 on the 30th so I put it into my unexpected income cells in the columns above - woohoo! So it's still a predicted balance, but the prediction is higher due to this unexpected windfall[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Actual bank balance[/TD]
[TD]£2,500[/TD]
[TD][/TD]
[TD][/TD]
[TD]£2,500[/TD]
[TD]The bank balance when checked matches the predicted on the 28th. On the 31st due to an insane spending spree I have less in my bank account than I thought I'd have when I check the actual total on my bank account[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pending outgoings not yet reflected in the Actual bank balance[/TD]
[TD][/TD]
[TD][/TD]
[TD]£50[/TD]
[TD][/TD]
[TD]To make sure that pending transactions influence the Accurate Balance especially what the balance will be on the last day before the next payday, I put in pending payments if there are any[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]This is how the calculation would behave (no adjust to the formula would be needed regardless of the various combinations of balances, negatives, positive and pendings):[/TD]
[TD]Predicted balance and pending balance match so the calculation runs but in the Accurate Balance the result would be £2,500[/TD]
[TD]I don't check my bank balance this day so the calculation returns £2,500 as per the predicted balance[/TD]
[TD]So the predicted is still £3000 and the result should display that however in this example there's £50 of pending debits to come out which aren't yet showing in my bank balance so I want to put them in so I don't overspend. So the formula would need to be exactly the same as in column A, but be able to accommodate this scenario as well[/TD]
[TD]And in this example there's been higher than expected costs and therefore my balance is lower than I predicted - it's not a negative value, it's just lower and again the same formula needs to be able to handle that[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I don't know if that is any of a better explanation. The only other variable is that I guess due to severe unforseen cirumstances I could end up in a negative bank balance when I check it and the formula would need to take account of this. I guess I can't see how just simple - this or + that would actually work (without me having to decide which items were having a reducing, increasing or no effect on £££s.

I do hope I can find something that will do all of the above or my budgeting is scuppered!!!