Finding Optimum Price

[nmnm99]

Hi! I need to find the optimum price for a specific amount of sales in the time of 20 years.
These are the amount of sales throughout the 20 years. I have this formula in order to find the optimum price and market share...MS = 44.971* 2.50 / P_NB – 24.867. What excel function can I use to find the optimum price..thanks!
[TABLE="width: 188"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10900000[/TD]
[/TR]
[TR]
[TD]11990000[/TD]
[/TR]
[TR]
[TD]13189000[/TD]
[/TR]
[TR]
[TD]14507900[/TD]
[/TR]
[TR]
[TD]15958690[/TD]
[/TR]
[TR]
[TD]17554559[/TD]
[/TR]
[TR]
[TD]19310014.9[/TD]
[/TR]
[TR]
[TD]21241016.39[/TD]
[/TR]
[TR]
[TD]23365118.03[/TD]
[/TR]
[TR]
[TD]25701629.83[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
[TR]
[TD]28271792.82[/TD]
[/TR]
</tbody>[/TABLE]

 

[PaddyD]

Hi - welcome to the board.

I think you're going to need to specify your problem a little more. The formula you've provided is simply linear so, assuming that P_NB is the price, maximising share is a matter of minimising the price. Optimising is another thing altogether - you need to identify the constraints that you're working with (i.e. what are you optimising with respect to?)

Post back with more details.

 

[nmnm99]

Hi thanks for the reply!

What other information would be required? (P_WB / P_NB) should be no less than 0.5 and no more than 3.0 and market share is between 0-100. Also my profits should be greater than my costs yearly in order for my model to be successful therefore I'd assume those are my constraints?

 

[nmnm99]

Optimizing in respect of maximizing profit to an extent where market share is not too low allowing the company that produces balls to have some sort of control in the market I would suppose.

 

[PaddyD]

"What other information would be required?"

The kind of things you've just supplied - what is P_WB? (For that matter, what is P_NB?). Where are your costs and profits for the historical data series? You've given us the sales numbers, but what price was in place for these sales and what market share did they achieve?