Microsoft Office Tutorials and References

In Depth Information

**ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT**

Normally, you would choose Simplex LP for the solving method, but in this case the model is not linear,

so Solver would not provide a solution. If you try using Simplex LP, you should receive the error message

shown in Figure 9-7.

FIGURE 9-7

Error message when trying to use the Simplex LP method

The Simplex LP method will not work for a couple of reasons. First, you did not define an upper bound

for your monthly production; you defined the constraint as greater than or equal to the sales forecast. To see

what happens when you define an upper bound, add a

s

production in the Solver Parameters window. To open this window, click the Data tab and click Solver in the

Analysis group. For example, add $F$21

“

less than or equal to

”

constraint for each month

’

s

production. If you run Solver again using the Simplex LP method, you will receive the error message shown in

Figure 9-8.

$F$17

10 for January. Repeat the constraint for each month

’

<¼

þ

FIGURE 9-8

Solver error message indicating that the model is nonlinear

Solver discovered that some of the equations in your model are nonlinear. Remember the IF statements

in the formulas for Hires and Layoffs? Luckily, you can use other solving methods. Follow the advice shown

in the Solver Results window, and change your solving method to GRG Nonlinear in the Solver Parameters