Avg. price increase

[goldcomac]

How do I figure out what price increases to take on my various
products when I want the average come out to 6%? One of the items is
going to take a 12% increase. Example:

Item A >> Units Sold YTD 1256 >> Current Price is $15.40 >> New
Selling Price will be $17.25
Item B >> Units Sold YTD 987 >> Current Price is $9.89 >> New
Selling Price will be ??
Item C >> Units Sold YTD 1500 >> Current Price is $12.76 >> New
Selling Price will be ??
etc.

My average price increase can not exceed 6%, but I need to take 12% on
Item A. How do I figure out what increase I can take on Items B, C,
etc.?

Thanks.

Chip

 

[Guest]

(12%+B%+C%+)/3<6% ==> B%+C% <6% now it's up to you to decide how to split the
price increase between two items lets say : B%=4% and C%=2% then
new B price = $10.29
new C price=$13.01

 

[David Biddulph]

.... or did the OP want a weighted average?

 

[Bernd]

Hi Chip,

But don't you need to know the number of sold items for each item,
too?

I would try something like this (cells A1:E6):
Item Old_Price New_Price %Increase Sales
A 100 =B2*(1+6%) =TEXT((C2-B2)/B2,"0.00%") & " Increase" 50
B 200 =B3*(1+6%) =TEXT((C3-B3)/B3,"0.00%") & " Increase" 40
C 1000 =B4*(1+6%) =TEXT((C4-B4)/B4,"0.00%") & " Increase" 30
D 10000 =B5*(1+6%) =TEXT((C5-B5)/B5,"0.00%") & " Increase" 10
Total =SUMPRODUCT(B2:B5,$E$2:$E$5) =SUMPRODUCT(C2:C5,$E$2:$E$5)
=TEXT((C6-B6)/B6,"0.00%") & " Increase in total"

You can overwrite C2:C5 and see the possible outcome instantly.

HTH,
Bernd