How do I get Solver to work

[Guest]

I tried to use 'Guidelines for designing a model to find values with Solver',
to experiment with the model, but Solver returned the same values as original
$10,000 per quarter for Advertising. Target cell is $F$7, Constrained cell
is $F$5 = $40000, Adjustable cells are $B$5:$E$5. What am I doing wrong?

 

[Mike Middleton]

Graeme -

The first thing I would check to debug the model is to verify that the
target cell value (and the constraint formula cell value) changes when you
manually enter various values into the changing cells.

- Mike
www.mikemiddleton.com

 

[Guest]

Yes, the values change when entered manually

Graeme -

The first thing I would check to debug the model is to verify that the
target cell value (and the constraint formula cell value) changes when you
manually enter various values into the changing cells.

- Mike
www.mikemiddleton.com

 

[Mike Middleton]

Graeme -

If both F7 and F5 are changing in response to changes in each cell of B5:E5,
maybe try F5 <= 40000 instead of F5 = 40000 as the constraint.

- Mike

 

[Guest]

Hi Mike
No that resulted in a nonsense answer. Advertising for Q1 went to
-2,147,483,638,000, Q2, Q3 & Q4 advertsing remained 10,000, with Total
advertising -2,147,483,608,000, (constraint $F$5<=40000), and $F$7 result to
2,147,483,751,662

 

[Mike Middleton]

Graeme -

Add explicit non-negativity constraints for each of the decision variables
(changing cells), or check the "Assume Non-Negative" box of the Solver
Options dialog box.

Or, take a look at the advertising example of the "Quick Tour" worksheet in
the SolvSamp.xls workbook. This sample workbook is installed on your hard
drive when the Solver add-in is installed. On my system it's located at C:\
Program Files \ Microsoft Office Pro 2003 \ Office 11 \ Samples \
SolvSamp.xls.

- Mike
www.mikemiddleton.com

++++++++++++++++++++++++++++++++

 

[Guest]

Sensational - thanks very much

Graeme -

Add explicit non-negativity constraints for each of the decision variables
(changing cells), or check the "Assume Non-Negative" box of the Solver
Options dialog box.

Or, take a look at the advertising example of the "Quick Tour" worksheet in
the SolvSamp.xls workbook. This sample workbook is installed on your hard
drive when the Solver add-in is installed. On my system it's located at C:\
Program Files \ Microsoft Office Pro 2003 \ Office 11 \ Samples \
SolvSamp.xls.

- Mike
www.mikemiddleton.com

++++++++++++++++++++++++++++++++