Microsoft Office Tutorials and References

In Depth Information

**ASSIGNMENT 2: USING THE WORKBOOK FOR DECISION SUPPORT**

media mix and net income. Next, copy the TV Discount worksheet and rename it Kuhlmans—Mag Discount.

Change the value in cell G13 from $500 to $300, and rerun Solver. When Solver reaches its solution, create

another Answer Report, which will be named Answer Report 3 in a new tab. Again, examine the results to see

how the discounted magazine ad changed your media mix and net income.

The last change you will examine is the effect of changing the model

s solving method from Simplex LP

to Evolutionary. Copy the worksheet that contains the original Solver solution data, and rename the new

worksheet Kuhlmans—Evolutionary.

The Evolutionary solving method can work with nonlinear,

’

problems, such as those that

contain IF statements in their formulas. If you recall, you could not use an IF statement to calculate the

income tax because the Simplex LP method will not work with it. In cell C29 of the Kuhlmans

“

non-smooth

”

Evolutionary

worksheet, enter the IF statement needed to calculate the income tax. Remember that if the Net Income

before taxes is less than zero, the income tax is zero. Otherwise, the income tax is the Net Income before

taxes multiplied by the Income Tax Rate from the Constants section. To demonstrate that the Simplex LP

method will not work, select the method in the Solver Parameters window, and then attempt to run Solver.

You will see an error message like the one in Figure 8-10.

—

FIGURE 8-10

Error message when Simplex LP method is used on nonlinear models

In Excel 2010, Solver can use three solving methods: Simplex LP, GRG Nonlinear, and Evolutionary. You

can consult Microsoft Online Help for an explanation of each method. For most Solver decision models,

Simplex LP works well, but as you have seen, it probably will not work for nonlinear models. You could try

the GRG Nonlinear method, but in this case it would not work either. Therefore, open the Solver Parameters

window and change the solving method to Evolutionary. The method will take longer to solve the problem

than the Simplex LP method, but you should get a solution. Create another Answer Report, then compare the

Income Statement from the Evolutionary solution with that of your original Solver solution using Simplex LP.

The net income in the Evolutionary solution will probably be $10,000 to $20,000 less, depending on the

amount of time you gave the Evolutionary model to run the problem. To summarize, if you can keep your

formulas linear (no IF formulas or second-order equations), Simplex LP is the best solving method.

Modify the titles at the top of each worksheet to append an appropriate description. For instance, the

original historical worksheet could be titled Kuhlman’s Department Store—Media Problem—Historical. When

you finish modifying the titles, save your workbook and print all of your new worksheets and Answer Reports.

ASSIGNMENT 2: USING THE WORKBOOK FOR DECISION SUPPORT

You have built a series of worksheets to determine the advertising media mix that optimizes Kuhlman

s net

income while staying within the prescribed advertising budget. You will now complete the case by using your

solutions and Answer Reports to gather the data needed to make the advertising decisions and by

documenting your recommendations in a memorandum.

’