Excel formulas for household budget...

[SenseAndIrritability]

My household budget has been wiped from my computer (grrr) and I now relise that I've completely forgotten how to use Excel.

Basically, we have some bills that are paid weekly, and some that are paid monthly, and I want to be able to use Excel to show me what each costs on a weekly, monthly and yearly basis - without having to individually calculate, so if a bill changes I can just add it in and Excel will do the calculations.

So, where a bill for, say, £10, goes out weekly, I need a formula for the monthly slot that will give me £10 times 52 divided by 12. And for the yearly slot, I just need £10 times 52.

And where the bill is monthly - say for £100 - I need a formula to go in the weekly slot that gives me £100 times 12 divided by 52. And, of course, £100 times 12...

Just trying to explain this has given me a headache. Anyone out there who will/can take pity on me?

[catinthehat2]

I would do something like this.
Line 1 headers
Col A Type
Col B £
Col C Frequency

then
Line 2
Column A "Food Shopping"
Column B 100
Column C Weekly

Line 3
Column A "Gas bill"
Column B 80
Column C Monthly

etc, (so you can easily change the amounts, add bills etc)

THen back to line 1
Column D I would call cost/week
Column E I would call cost/month
Column F I would call cost/year

Weekly bills
Col D type = B2
Col E type = B2/730 (or 31 or 28 or however many days you consider a month to be)
Col F type = B2/7365

Monthly bills
Col D type = B3/30 * 7 (or 31 or 28 or however many days you consider a month to be)
Col E type = B3
Col F type = B3*12

THis would give you a 2 line spreadsheet with 1 of each of a weekly or a monthly bill. Copy lines to insert more bills.

YOu can customise and elaborate this to make it far more efficient, but this will give you a basic table

[zebramama]

Cat - I would modify what you have done slightly, so that you don't need to tweak the formulae for each row dependent upon the frequency and you can use a wider variety of frequencies...

Assume:
Column A = Item Description
Column B = Cost per Frequency
Column C = Frequency

Frequency: Use numbers as follows
Weekly = 52
Fortnightly = 26
4 Weekly = 13
Monthly = 12
Bimonthly = 6
Quarterly = 4
Half-yearly = 2
Yearly = 1

Then:
Weekly Cost in Column D = B x C / 52
Monthly Cost in Column E = B x C / 12
Yearly Cost in Column F = B x C

Ping me if you want an example emailing to you... (DH of zebramama)

[Mousey84]

moneysavingexpert.com has a fab budget planner hereand you can download it and save a new one every month. Much simpler

[FAQtothefuture]

agree with mousey - MSE one is fab

[catinthehat2]

Er, ZebraDad, I think the little lady wanted to do her own fluffy little spreadsheet using the most basic version possible as a reminder accrding to her instructions. She can then colour it pink and add little kitten pictures round the edges as required.

But thanks for explaining , because - heck - it's unlikely she would have managed to work anything else out herself, being a girly and all that.

Because after all, Excel IS rocket science isn't it?

I'm sure you meant well, but honestly...

The MSE one looks quite a god start, but you lose a lot of detail. Simple example, one line for haircuts, rather than being able to keep tabs on the cost of everyone's haircut individually.

[zebramama]

Ouch! And I thought I was being helpful!

ZD

[quidnunc]

ZD - glad I didn't offer any suggestions. Just deleted my ideas as I saw the response you recieved.

Tin hats on.

[SenseAndIrritability]

Ouch! Didn't realise this had kicked off in my absence. Should have realised that if anything would cause a ruck it would be Excel technique ... and money...

Thanks ZD and Cat for your tips, I've got my budget back up and running now, so won't have to resort to the MSE one. If looks amazing, Mousey and FAQ, but I think it's almost too complicated for me. But I think I might graduate to it one day... for now I am going to merely retreat to the corner to weep over our advanced, irreversible state of broke-ness. How DID this creep up on us!?

(Now there's a sentiment we can all agree on. Or can we?)

[FAQtothefuture]

Sense - it's actually incredibly easy to use - if I can use it - anyone can

You just put the numbers in and it works it all out for you

[catinthehat2]

ZebraDad - I was unneccessarily harsh.

Sorry.

Catinthehat.