Help With An Oddball Math Problem

[Brainster]

This is based on a problem I tutored a college student on today, which baffled us both.

We have four variables, just call them A,B,C and D that when multiplied together give us our total costs.

Our original budget called for A=42,000, B=.07, C=.90 and D=$4,750, meaning a total cost (A*B*C*D) of $12,568,500. However, our actual results were A=42,000, B=.095, C=.85 and D=$4,900, for a total cost of $16,618,350.

Management has asked us to come up with the variance in the total dollar amount of cost (or cost savings) attributable to each change in the variables. That is, how much of the $4,049,850 cost overrun was attributable to the change in B, how much to the change in C, and how much to the change in D? (Since A did not change, obviously it did not cause any of the cost overrun).

My original thought was to figure out what the total costs would have been if B had stayed at its original level. Any difference between that total and the actual expenditures is attributable to the change in B. I came up with $4,373,250 attributable to B. Doing the same for the others, I found that the change attributable to C was -977,550 (that is, it saved us money) and the change attributable to D was 508,725. But when I added up all those changes it came out to $3,904,425, or $145,425 less than the total budget overrun.

So then I tried putting B only at the new level while leaving C and D at their budget estimates. Any difference between those total costs and the original budgeted cost must be due to the change in B. Following this through for C and D, I came up with the following attributions: B $4,488,750, C -698,250, and D 396,900. But when I added them up, they came out to total $4,187,400, or $137,550 more than the actual budget overrun.

Anybody know what I'm doing wrong here, and what the correct method is?

 

[Aepervius]

It is a four dimensional equation... I am guessing you will not get a result easily.

To give you an idea, assume for first that two are constant. You are left with a Z=x*y plot.

This is how it looks like.

Now imagine h=z*x*y....

Since the relationship described is *linear* that is if 3 variables are maintained constant you got an equation, if A vary and C,B,D constant then you got y=P*A where P=B*C*D.

But then you get a meaningless answer. I am not even sure what your hypothetical chief asked make sense.

Let me think about it a bit more.

 

[madurobob]

You don't have enough data for a true statistical regression. So, best you can do is say "all other things being equal, a x% change in (variable) drives a y% change in the total"

In your results, you have three of your four variables changing, and they are counter-veiling. So you could calc the impact of each variable's actual change from budget and sum them to get the total delta. But, remember one of the numbers will be negative.

 

[Aepervius]

What about simply using variance as the math definition, e.g. you calculate the mean of both number in $, then calculate the variance for each variable and multiply both ? Seem non sensical but that is the nearest I have got.

 

[Aepervius]

You should probably take advice from madurobob and not from me

 

[madurobob]

You should probably take advice from madurobob and not from me

I kind of doubt that. As you point out its gonna be tough to be exact because of cross terms. My suggestion (and what Brainster did to get 4,187,400) is inexact for that reason. But, for we folks in corporate finance, it explains 80% of the issue so we stop digging any deeper.

 

[dakotajudo]

Here's my thought.

Let A1,...,D1 be the estimates from your original budget. Let A2,...,D2 be the final estimates.

Then A1*B1*C1*D1 = 12,568,500 and A2*B2*C2*D2 = 16,618,350.

Note that we can write each final estimate in terms of the original estimate, that is, B2 = B1*coef.b, so coef.b = B2/B1 = 1.3571429. Similarly, coef.a = 1.0, coef.c = 0.94444444 and coef.d = 1.0315789, and (A1*coef.a)*(B1*coef.b)*(C1*coef.c)*(D1*coef.d) = 16,618,350.

Note that coef.a*coef.b*coef.c*coef.d = 16,618,350/12,568,500 = 1.3222222.

Personally, I'd stop there - coef.b dominates the ratio between initial and final budgets. But if you need differences (and I'm too lazy to work this out) then:

Note that each coefficient can be written as, say, coef.a = 1 + dif.a = 1 + 0, etc. so

(1 + dif.a)*(1 + dif.b)*(1 + dif.c)*(1 + dif.d) = 16,618,350/12,568,500. Expand the left hand side to give something like

(1 + ... ...) = 16,618,350/12,568,500 and rearrange to

12,568,500 * (1 + ... ...) = 16,618,350
12,568,500 *1 +12,568,500 * (... ...) = 16,618,350
12,568,500 * (... ...) = 16,618,350 - 12,568,500

where ... ... will be something like dif.a + dif.b + dif.c + ... + dif.a*dif.b*dif.c*dif.d

 

[GodMark2]

It's only a three variable equation (A is constant) with two known states... not enough information to fully solve, as others have posted. But, if the boss wants numbers, the boss gets numbers.

I'd calculate the 'variation' from expectation ((final/expected) -1) for each of {B,C,D}. Then sum those variances, giving a 'total' if variations. This is then used to generate a 'overrun per variation' (overrun / sum(variations)), which can then be multiplied by each individual variation to give an 'attributable overrun'.

var|initial|final|ratio|variation|attributable overrun
A|42000|42000|1.0000.000|0
B|0.700|0.950|1.357|+0.357|+43413011
C|0.900|0.850|0.944|-0.056|-6753135
D|4750|4900|1.032|+0.032|+3838624
-|product|product|-|sum
-|125685000|166183500|-|0.333
-|-|total overrun|-|overrun per variation|sum
-|-|40498500|-|121556431|40498500

The same effect can be achieved by using logarithms, if you really want to keep the boss in the dark.

 

[jt512]

It's only a three variable equation (A is constant) with two known states... not enough information to fully solve, as others have posted. But, if the boss wants numbers, the boss gets numbers.

I'd calculate the 'variation' from expectation ((final/expected) -1) for each of {B,C,D}. Then sum those variances, giving a 'total' if variations. This is then used to generate a 'overrun per variation' (overrun / sum(variations)), which can then be multiplied by each individual variation to give an 'attributable overrun'.

var|initial|final|ratio|variation|attributable overrun
A|42000|42000|1.0000.000|0
B|0.700|0.950|1.357|+0.357|+43413011
C|0.900|0.850|0.944|-0.056|-6753135
D|4750|4900|1.032|+0.032|+3838624
-|product|product|-|sum
-|125685000|166183500|-|0.333
-|-|total overrun|-|overrun per variation|sum
-|-|40498500|-|121556431|40498500

The problem is that the model for the cost is multiplicative. So, assuming that this multiplicative model is correct, the true answer is that there was a cost overrun of 32.2%, the net result of cost overruns of 0.0%, 30.5%, –5.7% and 3.2% for each factor, respectively. If the boss insists on relating this multiplicative model to the additive difference in total cost, then you have to fudge. A reasonable approach is to normalize the multiplicative factors by dividing them by their sum so that the add to 1, as GodMark2 did to compute his "attributable overrun" figures.

 

[Brainster]

Thanks for all the suggestions. I was noodling over the problem yesterday and saw that there is a way to prove that at least part of the cost overrun must be based on the interactions of the individual mistaken estimates.

I used a simpler problem to make the calculations easier. Suppose we had planned a party at a local watering hole. We had estimated that there would be A) 8 guests, that they'd have B) 7 beers each and that on average, the beers would run us C $6 apiece for a total party budget of $336. Instead the lushes showed up with a friend, drank like fish and ordered Heineken, so that in the end we paid for 9 people at 8 beers each, for $7 apiece and we were near fainting when we saw the check was $504.

You will note that each item had a cost overrun of 1 unit--a person, a beer and a dollar. This is to simplify the calculations. So thinking about it in terms of the budgeted variables A (guests), B (beers) and C(cost apiece), we can say that the final cost was: (A+1)*(B+1)*(C+1).

Starting to see the problem? If we multiply that out, we get:

ABC+AB+BC+CA+A+B+C+1.

We can see the 1 at the end is a problem because it is not defined in either terms of the original budget or the actual costs, but a combination of the two. But things get even worse, because as we remember, the 1 was an arbitrary number that we chose for all three cost overruns. Suppose that A had been over by 3, B by 2 and C by 1? Then our formula looks like this:

ABC+AB+3BC+2CA+2A+3B+6C+6

So no matter what we do, we know that there will always be a leftover amount that has to be allocated by some method to one of the three variables. Which is what I was left with by the two prior methods I tried.

 

[Modified]

We have four variables, just call them A,B,C and D that when multiplied together give us our total costs.
...
Management has asked us to come up with the variance in the total dollar amount of cost (or cost savings) attributable to each change in the variables.

I think I see the problem.

 

[BOATGOD]

42