Microsoft Office Tutorials and References
In Depth Information
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
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.
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.
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.
Search JabSto ::

Custom Search