Analysis of difference of products: If I budget 5 resources, w/ rate of $100/unit...

[Gotha2981]

Hi all,
I have a question on a subject which I thought was easy to solve, but I am scratching my head as I do not seem to find a solution.

I am making a simple analysis of the variance of the actual vs budget production cost/unit.
The formula is: A x B / C , where
A = number of resources
B = rate of the resources
C = production volume

If I budget 5 resources, with a rate of 100 dollar/unit, and produce 200 units, the budgeted cost/unit is 2.5 dollar/unit
If in actual I use 6 resources, with a rate of 90 dollar/unit, and produce 230 units, the actual cost/unit is 2.347 dollar/unit
So in actual my cost/unit is -0.152$/unit better than budget, this is easy.

My question is: what is the formula(s) to breakdown these -0.152 by each of the three contributing factors: resource increase, rate decrease, production increase. That is, can I get three numbers, each linked to the changed resources, rate and production, which would then sum up to -0.152?

Thank you in advance for everyone's help.

 

[Dr.Peterson]

Hi all,
I have a question on a subject which I thought was easy to solve, but I am scratching my head as I do not seem to find a solution.

I am making a simple analysis of the variance of the actual vs budget production cost/unit.
The formula is: A x B / C , where
A = number of resources
B = rate of the resources
C = production volume

If I budget 5 resources, with a rate of 100 dollar/unit, and produce 200 units, the budgeted cost/unit is 2.5 dollar/unit
If in actual I use 6 resources, with a rate of 90 dollar/unit, and produce 230 units, the actual cost/unit is 2.347 dollar/unit
So in actual my cost/unit is -0.152$/unit better than budget, this is easy.

My question is: what is the formula(s) to breakdown these -0.152 by each of the three contributing factors: resource increase, rate decrease, production increase. That is, can I get three numbers, each linked to the changed resources, rate and production, which would then sum up to -0.152?

Thank you in advance for everyone's help.

I don't believe this is possible. The effect of a change in one variable depends on all the other variables, so they can't be separated. There are some things you could do to break down the effect of a "marginal" change in each variable, but this would not extend to a situation where they are all changing by significant amounts.

Did you have some reason to think this could be done?

 

[Gotha2981]

I don't believe this is possible. The effect of a change in one variable depends on all the other variables, so they can't be separated. There are some things you could do to break down the effect of a "marginal" change in each variable, but this would not extend to a situation where they are all changing by significant amounts.

Did you have some reason to think this could be done?

Hi,
thanks for your reply.
Actually, I also think it is not really possible. I tried hard to find a solution but could not really manage.
However, on the other side, my boss wants to understand, out of the -0.152 $/unit reduction, how much is due to the number of resource change, on the rate reduction, and on the production increase. So somehow I need to come with a breakdown. I hoped that I could end up with three addends which would sum up to -0.152 $/unit, but I guess I will have indeed to approximate.

 

[Denis]

i am making a simple analysis of the variance of the actual vs budget production cost/unit.
The formula is: a * b / c , where
a = number of resources
b = rate of the resources
c = production volume

if i budget 5 resources, with a rate of 100 dollar/unit, and produce 200 units,
the budgeted cost/unit is 2.5 dollar/unit.
if in actual i use 6 resources, with a rate of 90 dollar/unit, and produce 230 units,
the actual cost/unit is 2.347 dollar/unit.
so in actual my cost/unit is -0.152 dollar/unit better than budget, this is easy.

My question is: What is the formula(s) to breakdown these -0.152 by each of the
three contributing factors: Resource increase, rate decrease, production increase.
That is, can i get three numbers, each linked to the changed resources,
rate and production, which would then sum up to -0.152?

OK: using "easier to work with amounts", I believe you mean this:

         a      b      c     a*b/c
actual: 150   1100   5000  : 33 (150*1100/5000 = 33)
budget: 120   1500   4000  : 45 (120*1500/4000 = 45)
        ---   ----   ----  : --
         30   -400   1000  :-12 (30*(-400)/1000 = -12)

And you're trying for something like this:
30*u + -400*v + 1000*w = -12
Calculate u, v and w.

Like DrP says, don't think this is possible: 3 variables, 1 equation.

 

[Deleted member 4993]

Hi,
thanks for your reply.
Actually, I also think it is not really possible. I tried hard to find a solution but could not really manage.
However, on the other side, my boss wants to understand, out of the -0.152 $/unit reduction, how much is due to the number of resource change, on the rate reduction, and on the production increase. So somehow I need to come with a breakdown. I hoped that I could end up with three addends which would sum up to -0.152 $/unit, but I guess I will have indeed to approximate.

That will be dictated by your production system - every system will have different combination of multipliers (weighting factors). Your technical/marketing department has to assign those weighting factors.

 

[Dr.Peterson]

Hi,
thanks for your reply.
Actually, I also think it is not really possible. I tried hard to find a solution but could not really manage.
However, on the other side, my boss wants to understand, out of the -0.152 $/unit reduction, how much is due to the number of resource change, on the rate reduction, and on the production increase. So somehow I need to come with a breakdown. I hoped that I could end up with three addends which would sum up to -0.152 $/unit, but I guess I will have indeed to approximate.

If we just have to satisfy a boss, not us perfectionists, then we can try for a reasonable approximation. I mentioned marginal changes; I was thinking there of a total differential, which approximates a change due to small changes in independent variables, as a linear combination of the individual changes (differentials). This would let us proportionally assign weights.

You put the question under arithmetic, but this solution will depend on calculus. But if I do the calculus, you can do the arithmetic. (Actually, I'll do the arithmetic too, since I need to see what the results look like in order to be sure it's sensible.)

Here's what we'll do. First, I want to change the variable names so they feel right:

w = f(x,y,z) = xy/z

x = 5, y = 100, x = 200
dx = 6 - 5 = 1, dy = 90 - 100 = -10, dz = 230 - 200 = 30

\(\displaystyle dw=\frac{\partial w}{\partial x}dx+\frac{\partial w}{\partial y}dy+\frac{\partial w}{\partial z}dz = \frac{y}{z}dx+\frac{x}{z}dy-\frac{xy}{z^2}dz \) \(\displaystyle = \frac{100}{200}(1)+\frac{5}{200}(-10)-\frac{500}{200^2}(30) = -.125\)

That is not equal to your actual change, -0.152, but is in the ballpark. The important thing is that we got it by multiplying each change by a derivative that can be taken as a relative weight. To put it another way, we got our approximation as a sum of three terms, each associated with one variable; just multiply each term by 0.152/0.125 to scale it, and you have a somewhat reasonable answer to give.

But note that our three parts of -0.125 are +0.5, -0.25, and -0.375, which scale to 0.608, -0.304, and -0.456. It make sense to me that one change is increasing the result, while the others are decreasing it. Is your boss okay with that? And what will he do with these numbers?

 

[Denis]

BEWARE...you may need to explain to the Board Of Directors

 

[Gotha2981]

If we just have to satisfy a boss, not us perfectionists, then we can try for a reasonable approximation. I mentioned marginal changes; I was thinking there of a total differential, which approximates a change due to small changes in independent variables, as a linear combination of the individual changes (differentials). This would let us proportionally assign weights.

You put the question under arithmetic, but this solution will depend on calculus. But if I do the calculus, you can do the arithmetic. (Actually, I'll do the arithmetic too, since I need to see what the results look like in order to be sure it's sensible.)

Here's what we'll do. First, I want to change the variable names so they feel right:

w = f(x,y,z) = xy/z

x = 5, y = 100, x = 200
dx = 6 - 5 = 1, dy = 90 - 100 = -10, dz = 230 - 200 = 30

\(\displaystyle dw=\frac{\partial w}{\partial x}dx+\frac{\partial w}{\partial y}dy+\frac{\partial w}{\partial z}dz = \frac{y}{z}dx+\frac{x}{z}dy-\frac{xy}{z^2}dz \) \(\displaystyle = \frac{100}{200}(1)+\frac{5}{200}(-10)-\frac{500}{200^2}(30) = -.125\)

That is not equal to your actual change, -0.152, but is in the ballpark. The important thing is that we got it by multiplying each change by a derivative that can be taken as a relative weight. To put it another way, we got our approximation as a sum of three terms, each associated with one variable; just multiply each term by 0.152/0.125 to scale it, and you have a somewhat reasonable answer to give.

But note that our three parts of -0.125 are +0.5, -0.25, and -0.375, which scale to 0.608, -0.304, and -0.456. It make sense to me that one change is increasing the result, while the others are decreasing it. Is your boss okay with that? And what will he do with these numbers?

Hello Dr. Peterson,
this is great - it is definitely what I was looking for!
I have to say that I had arrived (in a more brutal way, not as elegant as yours) to identify the three factors 0.5, -0.25, -0.375
However, I had got stuck to the fact that their sum was not matching the cost difference, and as I had no proper theory behind I could not progress anymore.

The advantage of being engineers and not mathematicians is that we can justify the approximation in a business environment. For my work area, it is important to understand how much each factor contributes to the final cost, considering that not all the factors are in our control. So, their weight is indicating how much we are really effective on the ones we can control.

Thanks again for your and other peoples' help!

 

[Dr.Peterson]

Hello Dr. Peterson,
this is great - it is definitely what I was looking for!
I have to say that I had arrived (in a more brutal way, not as elegant as yours) to identify the three factors 0.5, -0.25, -0.375
However, I had got stuck to the fact that their sum was not matching the cost difference, and as I had no proper theory behind I could not progress anymore.

The advantage of being engineers and not mathematicians is that we can justify the approximation in a business environment. For my work area, it is important to understand how much each factor contributes to the final cost, considering that not all the factors are in our control. So, their weight is indicating how much we are really effective on the ones we can control.

Thanks again for your and other peoples' help!

Great - sounds like you are up to the challenge; I wasn't sure what level you would be at mathematically.

It occurred to me that you could try using other points (the actual values rather than the budget, or the average of the two) in the formula to see how stable the results are. If you get weights similar to those I calculated, you can more confidently use them for whatever analysis comes next. The fact that the differential worked out reasonably close to the actual change was also encouraging. If the changes had been much greater, this probably would not be true, and we would be left with no trustworthy answer, as I originally expected.

 

[Gotha2981]

Great - sounds like you are up to the challenge; I wasn't sure what level you would be at mathematically.

It occurred to me that you could try using other points (the actual values rather than the budget, or the average of the two) in the formula to see how stable the results are. If you get weights similar to those I calculated, you can more confidently use them for whatever analysis comes next. The fact that the differential worked out reasonably close to the actual change was also encouraging. If the changes had been much greater, this probably would not be true, and we would be left with no trustworthy answer, as I originally expected.

Actually, your observation is valid.
If I try to make the differential based on the "actual", and not on the budget, and therefore try to achieve +0.152, the absolute value of the three contributors are very different (obviously, they are in opposite direction as they increment instead of decrementing, and viceversa.

I get, for number of resources, -0.3387 vs 0.6086
for rate change 0.2258 vs -0.3045
for production change 0.2650 vs -0.4565

So, it is true that there is not one solution.
However, business wise, it makes sense to always consider the budget as a starting point, and then study the variation based on that. So I would stick to your first suggestion, and I am sure I could get results that are reasonable to use in the context.

 

[Dr.Peterson]

Actually, your observation is valid.
If I try to make the differential based on the "actual", and not on the budget, and therefore try to achieve +0.152, the absolute value of the three contributors are very different (obviously, they are in opposite direction as they increment instead of decrementing, and viceversa.

I get, for number of resources, -0.3387 vs 0.6086
for rate change 0.2258 vs -0.3045
for production change 0.2650 vs -0.4565

So, it is true that there is not one solution.
However, business wise, it makes sense to always consider the budget as a starting point, and then study the variation based on that. So I would stick to your first suggestion, and I am sure I could get results that are reasonable to use in the context.

Let's check that you did what I had in mind. My idea was to use a different x,y,z, but the same dx,dy,dz, and compare the terms. Using "actual", I get this:

w = f(x,y,z) = xy/z

x = 6, y = 90, x = 230
dx = 6 - 5 = 1, dy = 90 - 100 = -10, dz = 230 - 200 = 30

\(\displaystyle dw=\frac{\partial w}{\partial x}dx+\frac{\partial w}{\partial y}dy+\frac{\partial w}{\partial z}dz = \frac{y}{z}dx+\frac{x}{z}dy-\frac{xy}{z^2}dz \) \(\displaystyle = \frac{90}{230}(1)+\frac{6}{230}(-10)-\frac{6\cdot90}{230^2}(30) = 0.3913 - 0.2609 - 0.3062 = -.1758\).

So the three components are +0.3913, -0.2609, -0.3062, compared to +0.5, -0.25, and -0.375; their sum is not too far from what it was. After scaling by 0.152/0.1758 = 0.8646, the components are 0.3383, -0.2256, -0.2647.

So, yes, you did what I meant (apart from the sign), and the numbers are significantly different. In that case, my plan was to either use x,y,z from the midpoint of the change, or use the averages of the components, to get numbers that reflect both endpoints of the change. But ultimately, it is an arbitrary choice what to use, since it isn't quite real in the first place.