Calculating cost based on several factors

[FM1]

i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.

I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.

For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.

Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.

I dont know if any of that made any sense, but I have added an attachment so everyone can see what Im blabbering on about. Any help is very much appreciated.

Thanks in advance.

Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation. Thanks.

[DonkeyOte]

just to clarify and avoid confusion for others who may review... your original formula say in G3 should actually read as:

=$B3*$C3*G$1*MAX(0,(MIN($E3,EOMONTH(G$2,0))-MAX($D3,G$2)+1)/(EOMONTH(G$2,0)-G$2+1))

Correct ? I think you've referenced the originals to your override cells by accident.

EDIT: And more pertinent -- I think you will need to elaborate on how you're calculating DPM given this would need to be apportioned based on days worked in the month on each rate if a split mid-month occurs... ie if rate starts on the 16th June would you apportion DPM 50:50 to each rate or is there some more detailed basis on which the DPM are calculated ? Using Dec for ex ... are more days excluded at month end that at beginning of month in relation to public holidays etc...

[FM1]

just to clarify and avoid confusion for others who may review... your original formula say in G3 should actually read as:

=$B3*$C3*G$1*MAX(0,(MIN($E3,EOMONTH(G$2,0))-MAX($D3,G$2)+1)/(EOMONTH(G$2,0)-G$2+1))

Correct ? I think you've referenced the originals to your override cells by accident.

EDIT: And more pertinent -- I think you will need to elaborate on how you're calculating DPM given this would need to be apportioned based on days worked in the month on each rate if a split mid-month occurs... ie if rate starts on the 16th June would you apportion DPM 50:50 to each rate or is there some more detailed basis on which the DPM are calculated ? Using Dec for ex ... are more days excluded at month end that at beginning of month in relation to public holidays etc...

Hi Donkeyote

Yes, that is correct. Thanks for pointing that out.

Regarding the 18.83 DPM, it has been stipulated by senior management that each month is based on 18.83 work days once all holidays have been taken into account. I have challenged that and suggested that we use NETWORKDAYS but that was rejected as a lot of people here are resistant to change. So yes, if a rate starts on the 16th of June then we would split DPM 50/50 to each rate. As for Dec example - no, more days are not excluded at month end. Its simply the average over a month period.

Again, I do realise this is not the best way to work by any means and I have suggested different methods but they have been turned down. So this is pretty much what I have to work with.

[DonkeyOte]

[deleted]

error...

[DonkeyOte]

Amazing the trouble an erroneous absolute reference can cause...

OK I would do the following:

Based on your example... I would put the EOMONTH(date,0) result in each column given it's used repeatedly in your calculations... so:

Please Login or Register  to view this content.

Then I wonder if perhaps the following works for you:

Please Login or Register  to view this content.

Apply across matrix.

I'm convinced there's a better way -- and was looking into a stepped sumproduct of staff & rates but was having issues handling variety of dates... will post back if I think of a better alternative.

[DonkeyOte]

still wrong

multiplier at the end wasn't setup to account for both mid start & mid end position or even a start & end within same month (and would have been 1 day short) ... this should work though, honest :-)

Please Login or Register  to view this content.

[FM1]

still wrong

multiplier at the end wasn't setup to account for both mid start & mid end position or even a start & end within same month (and would have been 1 day short) ... this should work though, honest :-)

Please Login or Register  to view this content.

Firstly, thank you very much for your efforts in helping me out. It is greatly appreciated.

I have tried out the formula and unfortunately it doesnt work although it is extremely close. Just so you know, there are references in the formula to cell I9 which is a blank cell. I tried using the formula as is, but it was working. So I changed all I9 cell references to I10 (DPM) and it still isnt working.

From the looks of things, the formula is actually adding the normal rate and the over-ride rate when calculating the cost as opposed to just using the over-ride rate from the date stipulated. Example - XXX is on £300, 100% allocation from 01 Jan 09 to 31 Jun 09 so for each month its a calculated cost of £5649.00. Once I put in £100 as the over-ride rate, the calculated cost is £7532.00 (as opposed to £1883.00).

I tried going through the formula to see why that is happening buy my skills are no where near good enough to figure it out. Any chance you could have another look?

Thanks again.

[DonkeyOte]

Per my earlier post I9 should hold the EOMONTH value for I11 date, eg

I9: =EOMONTH(I$11,0)

copy across for other months.

Then retry the formula (using the I$9 references)

[FM1]

Per my earlier post I9 should hold the EOMONTH value for I11 date, eg

I9: =EOMONTH(I$11,0)

copy across for other months.

Then retry the formula (using the I$9 references)

Oh ok. Sorry about that. Completely missed that post.

Just tried it and it has worked! That is absolutely amazing. I cant even begin to try and even understand how that formula works. I will have a read through and try and understand the logic behind it.

Thank you again for your help. I seriously cannot convey how much this is appreciated as this has helped me out greatly. You are an absolute legend.

[DonkeyOte]

Although "solved" I *think* this is more accurate and a little less vague

Please Login or Register  to view this content.

The above simply calculates the total to be applied at rate 1 and then adds the total to be applied at rate 2... in both cases the amount is determined by:

(rate*utilisation)*((days rate applied in month / days in month)*(DPM))

Hope that helps...

The prior solution worked in similar vein but I wasn't comfortable with it... it was poorly put together IMHO.

[FM1]

Although "solved" I *think* this is more accurate and a little less vague

Please Login or Register  to view this content.

The above simply calculates the total to be applied at rate 1 and then adds the total to be applied at rate 2... in both cases the amount is determined by:

(rate*utilisation)*((days rate applied in month / days in month)*(DPM))

Hope that helps...

The prior solution worked in similar vein but I wasn't comfortable with it... it was poorly put together IMHO.

That definitely looks like a simplified version. I can even understand most of the logic behind it. Best thing is - it works perfectly too.

Thanks again mate.