simple yet complex scenario - goal seek problem

[stef]

Excel 2002 SP3
Win XP Pro SP2

*Follow-up to: microsoft.public.excel*

Hi,

I have the following:

Column C is "Sales Growth Rate"
Cells in C3 to C23 each contain a sales growth rate for a period (a
month or quarter, etc.), increasing randomly (I should say "estimated"
but not following a set pattern, i.e not 2% per period, etc., but just a
best arbitrary estimate that is not linear).

Column F is "Market Share"
Cells in F3 to F23 each contain the market share of each period as a %,
meaning market value captured by the company divided by total market value.

F23 is my total market share at the end of the series; e.g. 10%

I need to change sales growth rates in C3 to C23 so that I arrive a
desired values of market shares in F23 meaning:

I need to run the scenario assuming a total ending market share (in F23)
of 10% (that's done); 2.5% and 4.25% (rates here are just for
illustration) which correspond to: worse, middle and best case scenarios.

What is the best way to adjust my cells in C3 to C23 so that I end up
with the desired market share in F23 (i.e. 2.5% and 4.25%)? It would be
best to reflect the same growth ratios, for the grow rates, etc.

I can't do Goal Seek (I believe) because that needs to only change 1
single cell where I need to change the range C3:C23 so that F3:F23
changes proportionately to end up with the desired 2.5% and 4.25% market
shares....

Makes any sense?

 

[stef]

I used the Solver to change F23 to the desired value by changing C3-C23.
Works.

I saved each "solver" solution as a scenario.

e.g. is it possible to make a "spinbox" or combo box, etc.; so that one
can see the 3 scenarios and run them with the click of a button?

How...?

Any comments as to better approaches, saving scenarios and running them
in turn, etc., are always welcome....

 

[stef]

Actually, the solver used without constraints does solve but seems to
find a random solution and use that as long as it solves for the
defined value in F23; regardless of other parameters.

So the rates of change in the sales growth rates in C3:C23 do not stay
close the original but vary widely--huge standard deviation--which is
incorrect for what I need.....

I had to use several constraints to solve with a similar growth
progression than original....

 

[stef]

I posted a separate thread re. the spinbox, etc.; linked to scenarios.

 

[ryguy7272]

See these sites:
http://www.vertex42.com/ExcelArticles/excel-solver-examples.html
http://office.microsoft.com/en-us/excel/HA011118641033.aspx


Regards,
Ryan---