Need help with a spreadsheet calculation

MrJester

Anyone good with spreadsheets? Specifically Google Sheets, but I assume most functions are in there.

So I have a sheet which details my direct debits like so:

Name - Amount - Date
Debit1 - £10 - 5th
Debit2 - £20 - 15th
Debit3 - £15 - 28th

I also then have a weekly budget, which is calculated by taking Income - (All debits) / 4

At the end of each week, I enter the total amount spent which is compared against the budget for the week (example):

Budget - Spent - Leftover - Date entered
£100 - £80 - £20 - 27thWhat I would like to be able to do is have another section, whereby I enter an updated Balance and a Date, which then compares against which Debits are yet to come out (e.g. if I entered an updated Balance on the 6th, then I would still have the 15th and 28th to be taken) as well as my weekly budgets for the rest of the month.

The aim: At any point of the month I can enter an updated balance, and see based on the debits and budget I have set whether I will have enough money left for the rest of the month.

Is this stupidly complicated

Stevie_Palimo

Don't think I could be bothered with all of this and I simply look at what I pay dd wise and account for it as a monthly total and make sure it is left in the dd account each month, If you use only one account the same principle can be applied ie :- monthly income £1000.00, monthly dd £30.00 = monthly left over £970.00.

MrJester

Stevie_Palimo wrote: »

Don't think I could be bothered with all of this and I simply look at what I pay dd wise and account for it as a monthly total and make sure it is left in the dd account each month, If you use only one account the same principle can be applied ie :- monthly income £1000.00, monthly dd £30.00 = monthly left over £970.00.

Yeah I get it, just my spreadsheet helps me see everything clearly with debts, debits, and now trying to stick to a weekly budget.

Because everything comes out of one account for me, so that's debits and spending, its harder to track at a glance what left is to spend and what isnt.

How do you do it? Do you have a seperate account just for your debit payments?

GunJack

Stevie_Palimo wrote: »

Don't think I could be bothered with all of this and I simply look at what I pay dd wise and account for it as a monthly total and make sure it is left in the dd account each month, If you use only one account the same principle can be applied ie :- monthly income £1000.00, monthly dd £30.00 = monthly left over £970.00.

then take the monthly left over and divide by 4 for approx. weekly

MrJester

GunJack wrote: »

then take the monthly left over and divide by 4 for approx. weekly

er yep i'm already doing that. that's not what im asking for.

Stevie_Palimo

MrJester wrote: »

Yeah I get it, just my spreadsheet helps me see everything clearly with debts, debits, and now trying to stick to a weekly budget.

Because everything comes out of one account for me, so that's debits and spending, its harder to track at a glance what left is to spend and what isnt.

How do you do it? Do you have a seperate account just for your debit payments?

I do use several accounts one for dd's, one for income, and three for savings, I have never used any kind of spread sheet and just look at what I have as income against outgoings and the difference is placed into savings and some spent on beer and other toot.

Le_Kirk

You need a formula that starts with your balance, subtracts payments (be they DD's, cheques or ATM withdrawals) from that balance and then IF the date is between set parameters, subtract any projected payments. You would need to set the parameters e.g. current month, next 30 days or from todays date to a date entered in another cell. Date formulae can be quite complicated to work with at first, so, rather than construct the formula in one go, start with a simple date calculation, e.g. subtract todays date from a date in the future stored in another cell and display the number, then build from there.

Sounds like you need a budget control programme like Microsoft Money (or their latest version thereof as I know MSMoney is no longer supported) which has "cash Flow Projection" which is what you are trying to do. Other budget control programmes are available!! I do not work for Microsoft but I do use MSMoney.

indesisiv

I would strongly suggest setting up a second account and moving all of your direct debits across to that account. Then set up a standing order to transfer the money to cover the DD's for that month.
Then it is out of sight in one payment and you don't need to worry about what money is used to cover them during the month.

If you go your way it looks like you will need to do a sumif based on the date.

bod1467

Is your income a monthly amount? (i.e. it gets paid in on the same date every month). There are no other income streams?

Unless your money IN comes in varying amounts at varying times, and you need to budget carefully to avoid being overdrawn, you shouldn't need to budget weekly ... as already said, Monthly Income - Monthly Liabilities = Balance. Divide by 4 to get weekly allowance.

MrJester

bod1467 wrote: »

as already said, Monthly Income - Monthly Liabilities = Balance. Divide by 4 to get weekly allowance.

Note to all - I am already doing this. The extra elements I wanted was, as one suggested, forecasting.

But yeah this is probably overcomplicating it with what I wanted to do. I'll be looking into having 2 accounts to make spends & debits split easier..

bod1467

None of us is saying it can't be done ... just questioning the benefit you'll get from the effort you'll need to put into setting it all up.