Adding two differently weighted penetration levels

[barkir]

I have a situation where I am trying to add different weighted averages of sales penetration. If they are weighted equally it is pretty easy. In my example below Bob has a goal of selling in 12% Apples and 60% of the Oranges. What I am trying to determine is what his goal penetration would be.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Units on Hand[/TD]
[TD]Units Sold[/TD]
[TD]Penetetration[/TD]
[TD]Goal Penetration[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]1,000,000[/TD]
[TD]100,000[/TD]
[TD]10%[/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]50,000[/TD]
[TD]25,000[/TD]
[TD]50%[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bob's totals[/TD]
[TD]1,050,000[/TD]
[TD]125,000[/TD]
[TD]11.9%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Assuming Apples and oranges carry the same weight the formula is easy
=(e2*(b2/b5))+(e3*(b3/b5)
=(.12*1,000,000/1,050,000))+(.60*(50,000/1,050,000))
=.1143+.0286= 14.29%

What I am having issues with is understanding how to do this when Apples say account for 80% of his goal and Oranges account for 20% of his goals.
=(e2*(b2/b5))*.8)+(e3*(b3/b5)*.2) doesn't seem to be right but I can't seem to wrap my head around how to do it.

Thanks!!

 

[oldbrewer]

Bob has 2 separate goals and I do not think they can be combined. You can say things like he met one target and failed to meet the other. What if he had a goal to achieve 45 miles per gallon in his company car ? That clearly cannot be combined even if it is dependent on sales ie he drives the car flat out to meet more customers to sell more apples...

 

[barkir]

Thanks for the response. I think this is not an apples and oranges comparison (pun intended) example like your 45 miles per gallon and sales. It is a sales goal for two products with one weighted more. There has to be a way to determine if he met his goals even if you look at it separately. If he blows out his orange goal at 75% sell in but misses his apple goal and only hits 11% has he met his overall sales goals? That is what I am trying to figure out. It's not an all or nothing goal. Thanks

 

[oldbrewer]

if he sold ten times his target of oranges but only one quarter of apples target I do not believe saying he achieved 975% of his target would be meaningful.

 

[barkir]

Exactly my dilemma You are right. That's why I can't wrap my head around how to come up with the correct combined percentage. I know it can be done because this is how my company does it. And as I showed in the first example if they are weighted equally it is easy to come up with it using the equation I provided. Howecpver when weighted differently it becomes confusing. I am just trying to figure out how they do their math or if there is an excel formula/function that does it

 

[oldbrewer]

if you give us an example of one persons figures we should be able to figure it out

(and maybe your company is doing it wrong)

(( targets are a minefield - they ALWAYS drive up costs))

((( your company will not believe that )))

 

[barkir]

SOLVED IT!!!!

1. First determine the number of units if they hit their goal. So 12% X 1,000,000 and 60% x 50,000 = 120,000 Apples and 30,000 Oranges is the goal.
2. ((120,000 apples X 80%)+(30,000 oranges X 20%)) / (1,000,000 Apples on Hand X 80%) + (50,000 Oranges X 20%) =
102,000/810,000= 12.59% as the goal

I kept missing multiplying the denominator by the weights which was throwing me off.

Thanks for the extra pair of eyes oldbrewer!!!!

 

[oldbrewer]

the goal can still be achieved with far exceeding the target for one and missing the target for the other....