Goal Seek

[mitchman7614]

IThe following is an example data set:

New Price ?? this is what I want to solve

Customer Units Price Gross Margin Ext Price Ext Margin
ABC 5 3500 2330 17500 11650
XYZ 10 2200 1100 22000 11000
123 20 2800 1250 56000 25000
456 5 2750 1400 13750 7000
789 20 2950 1500 59000 30000

Total 60 168250 84650

How would I go about setting one price (all customers receive the same price) while maintaining the margin of 84650? Can I use goal seek?

 

[Ralajer]

In an unused cell (say H2) set the start price at an arbitrary price. Reference the price for each individual customer to the above =$H$2. In the goal seek window:

Set Cell: [cell containing total margin]
To Value: 84650
By Changing Cell: [H2]​

 

[mitchman7614]

Thanks so much for your help!