Percent Contribution

[JR573PUTT]

I have the following issue:

Average store
cat 1 is 47.1% or 21 pair
cat 2 is 24.2% or 11 pair
cat 3 is 17.2% or 8 pair
cat 4 is 11.5% or 5 pair

Total pair is 45 or 100% of the display.


AA Store is 10% less in cat 1 and 2, & 10% better in cat 3 and 4.
A Store is 20% less in cat 1 and 2, & 20% better in cat 3 and 4.
B Store is 30% less in cat 1 and 2, & 30% better in cat 3 and 4.


What is the formulas to redistribute the pairs amongs the AA, A, and B
stores for each category. The new redistrution must obviously equal
45.........

Thank in advance for the assistance.

 

[JR573PUTT]

Any brainiacts in the house

 

[joeu2004]

Average store
cat 1 is 47.1% or 21 pair
cat 2 is 24.2% or 11 pair
cat 3 is 17.2% or 8 pair
cat 4 is 11.5% or 5 pair
Total pair is 45 or 100% of the display.
AA Store is 10% less in cat 1 and 2, & 10% better in cat 3 and 4.
A Store is 20% less in cat 1 and 2, & 20% better in cat 3 and 4.
B Store is 30% less in cat 1 and 2, & 30% better in cat 3 and 4.
What is the formulas to redistribute the pairs amongs the AA, A, and B
stores for each category. The new redistrution must obviously equal 45.

I presume that "10% less" means 10 pct pts less; for example, 37.1%
instead of 47.1%.

If B1:E1 contains {47.1%,24.2%,17.2%,11.5%} and F1 contains 45, and if
A2:A4 contains the percentage offsets {10%,20%,30%}, then the
distribution for store AA could be written as follows:

B2: =round($F$1*max(0,B$1-$A2),0)
C2: =round($F$1*max(0,C$1-$A2),0)
D2: =round($F$1*min(1,D$1+$A2),0)
E2: =$F$1-sum(B22)
F2: =sum(B2:E2)

Copy B2:F2 through B3:F3 and B4:F4 for stores A and B.

Of course, if this were a real-world problem, you would probably
dispense with A2:A4 and fill in the appropriate percentage offsets in
each formula.

F2 is just a double-check. The formula in E2 ensures that the total is
indeed 45. It is necessary to compensate for round-off error. The use
of MIN() and MAX() is necessary to account for the case where the
percentage offset would cause the category percentage to go below zero
or to exceed 100% (e.g. cat2 for store B).

As a double-check, you might set up H2 as follows and copy through
I2:K2, then copy H2:K2 through H3:K3 and H4:K4:

H2: =B2/$F2

You might notice that each store's category percentages do not equal
the average category percentage plus or minus the offset. This is due
to round-off error (integer quantization), just as 21 is 46.7%, not
47.1%, for cat1 in the average distribution above.