Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Next, go to the Analysis group in the Data tab and click Solver to set up your problem (see Figure 8-9).
Use the Simplex LP solving method.
The Solver Parameters window
Run Solver and ask for the Answer Report when Solver has found a solution that satisfies the constraints.
When you finish, print the entire workbook, including the Solver Answer Report Sheet. Save the workbook by
clicking the File tab and then Save. For the rest of the case, you must decide whether to use the Save As
command for new Excel workbooks or to continue copying and renaming the worksheets. Both options offer
distinct advantages, but having all of your worksheets in one Excel workbook allows you to quickly and easily
compare different solutions as well as prepare summary reports.
Before continuing, look at the advertising mix that Solver chose for optimizing the net income. If you set
up Solver correctly, you should see a dramatic difference in the current net income and the net income from
the media selection that Solver made. Remember that these numbers are only estimates
increases will depend on other factors as well, including the state of the economy, the quality and timing of
the ads, and what the competition does. Even with these factors included, however, the media consultants
and your decision support model have provided Kuhlman
s with useful financial data.
Assignment 1B: Changing Parameters and Creating New Solutions
Using the model you created in Assignment 1A, Kuhlman
s can also review figures for different media
providers and see the effects of negotiating reduced ad prices on the decision model. Now that you know how to
copy spreadsheets, you can easily create additional spreadsheets and look at the mix that Solver
recommends, given changes in the media pricing. You will examine two reductions in ad prices: First, what happens
if the television station reduces its price per commercial from $3,000 to $2,500? Next, with the television
discount included, you will rerun Solver with magazine ads reduced from $500 to $300. Copy your original
Solver solution worksheet to a new worksheet named Kuhlmans—TV Discount, then change the value in cell
D13 (TV media cost) from $3,000 to $2,500. When Solver reaches its solution, create an Answer Report
will be named Answer Report 2 automatically. Examine the report to see how the TV discount changed your