Expenses, formula for monthly / annual / etc

Hello,

I am creating a budget expenses sheet which different budget holders will fill in, showing each cost and its frequency. So an item may be:

Rent £10,000 May
Lighting £2,000 Monthly
Gas £500 Each month starting from July

With months along the top, I am looking for an all singing, all dancing formula which will look at the cost and allocate it according to whether it is for a single month, annual, bi-monthly, bi-annual, for a few months.

I am not expecting anyone to write a formula, but does anyone have any hints or tips, something they may have seen which could point me in the right direction?

It could be that VBA is the way to go, any thoughts welcome.

Thanks
Rich

This is one I already have ... yes it's in dollars and doesn't conform with your request re specific details, but at least it's a start. Have a look at it and note: Sheet 1 is the workings, Sheet 2 is the result using VLOOKUP.

Budget Planner.xlsx

Thanks guys, still working on this, I'll update with the result when I manage to finish.