Executing an increase on a basket based on turnover

[SaajidHoosen]

Hi,

I need some help please, I work with a basket of products that fall under different groupings (a,b,c) I want to execute a price increase on my turnover and of x percent but I want to make group "A" increase by a certain percentage only and then be able to calculate the increase on the rest of the parts to equal the target revenue. I want to be able to do this based on the volume of sales on the part so that parts which don't sell don't increase.

Eg.

Total increase 5%
Current Turnover:500
Goal Turnover: 525
Group A 6% increase

Product	Grouping	Retail Price	Volume
XX	a	10	20
XX	b	9	11
XX	b	8	2
XX	a	7	3
XX	c	6	4
XX	c	5	10
XX	a	4	0

<tbody>
</tbody>

Please help me it's driving me crazy trying to solve this.

 

[oldbrewer]

make a spreadsheet so you can dial in price changes and observe the effect on overall turnover,,,, quick to do

 

[SaajidHoosen]

make a spreadsheet so you can dial in price changes and observe the effect on overall turnover,,,, quick to do

That doesn't solve the problem though, i need it formula driven to determine the rest of the increases without exceeding the target turnover.

 

[oldbrewer]

hen be able to calculate the increase on the rest of the parts to equal the target revenue

in which case please define if b and c are treated the same or differently according to some other rule(s)

 

[oldbrewer]

a	100
b	250
c	410
tot	760
5%	38
6% of a	6
needed	32
b	16
c	16
b	6.4	%
c	3.9	%

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

a	100
b	250
c	410
tot	760
5%	38
6% of a	6
needed	32
b	16
c	16								b	6.4
									c	3.9
b	6.4	%
c	3.9	%
										6.4 from
										=(($B$4*0.05-$B$1*0.06)/2*100)/$B$2

<colgroup><col span="3"><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>