HELP with budget planner for house build

**helloworld101** · 09-26-2011, 04:55 PM

I really need some advice here... i am trying to sort out a budget calculation sheet and I am STUCK!!

Here is what I am trying to do...

i have a Fixed budget or "target value" for my Project say $100,000

I have a number of "budget" varible values that add up to this figure but become fixed when the price of the item is agreed or purchased..

What I need is a way to show the impact of the fixed item purchase on the remaining varibles as the project progresses along.

i.e. each time I purchase something the price becomes "fixed" if I go over my budget then I need to identify this and re-adjust all the varibles by a fixed percentage amount to still reach the given over all target of $100,000

So for example I budget for windows - say $5000 but then they really cost me $6000which then becomes the fixed value.

After this my budget is over and I need to save some money on the remaining varible.. But the question is by what percentage on each item ???

**Andrew-R** · 09-26-2011, 05:25 PM

How about something like the attached sheet?

The total budget in B1 is just a sum of the budgets for each individual item, while the formula in column D takes the actual costs and sets a target price for each item based on the proportion of the original budget it represented.

Is that the kind of thing you were looking for?

**ConneXionLost** · 09-26-2011, 06:04 PM

A slight correction to Andrew-R's worksheet. In cell D4, use:

=IF(A4="","",IF(C4<>"",C4,B4*(($B$1-SUM($C$4:$C$500))/($B$1-SUMIF($C$4:$C$500,"<>"&"",$B$4:$B$500)))))

autofill down.

**helloworld101** · 09-26-2011, 06:33 PM

Originally Posted by Andrew-R

How about something like the attached sheet?

The total budget in B1 is just a sum of the budgets for each individual item, while the formula in column D takes the actual costs and sets a target price for each item based on the proportion of the original budget it represented.

Is that the kind of thing you were looking for?

Hi Andrew, thanks for the speedy reply...

This is along the line's but ...

Once the actual cost is entered then I need all of the remaining "non" Actual (or still varible) costs to adjust accordingly to still reach the original budget target... £100,000

So simply put ... I have 5 items each are budgeted at £20,000 each from the start so the total budget is obviously £100,000

Then lets say that the first item is actually bought for 25,000 then the other 4 remaining varibles would need to automatically adjust down to 18750 each or by 6.25% for the £100,000 target to remain possible.

This shows me what I need to acheive to stay on budget as I go along... and I can then see where the pricing issues are before they trip me up..

Obviously the more Fixed or Actual pricing I can get, the more possible the budget is of being correct.

**ConneXionLost** · 09-26-2011, 06:54 PM

So, putting the pieces together...

Cheers,

**Andrew-R** · 09-27-2011, 02:08 AM

Thanks, Connex, I realised after I went to bed last night that I'd messed up the maths. Not an unusual event for me

**helloworld101** · 09-27-2011, 04:24 PM

Andrew and Connexionlost...

Just thought I would pop back and say a massive thankyou to both of you for your speedy help and the excellent formula you gave me,.. I have adapted the sheet with a few more simple bits in other cells... but now I have exactly what I was after which is often difficult to describe for a novice...

Again a big Thanks...

regards

Adrian