Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you will create spreadsheets that model the business decision Kuhlman
s is seeking. In
Assignment 1A, you will create a Solver spreadsheet to model the media selection decision. In Assignment
1B, you will copy and rerun the Solver spreadsheets, given changes in media costs for television and
magazine ads. In Assignments 2 and 3, you will use the spreadsheet models to develop recommendations for
the best media mix, and you will prepare an oral presentation of your analysis and recommendations.
This section helps you set up each of the following spreadsheet components before entering the cell
formulas:
￿
Constants
￿
Calculations
￿
Income Statement
The Calculations section is the heart of the decision model. You will set up each of the media in its own
column; one of the rows will be the Changing Cells range for Solver to manipulate. The final Net Income after
taxes in the Income Statement will serve as your optimization cell.
NOTE
You cannot use an IF statement for the income tax calculation if you want to use the Simplex LP method to solve the decision
model. When used to calculate the income tax, the IF statement will render the decision model nonlinear and require a different
optimization method in Solver.
Assignment 1A: Creating the Spreadsheet
Base Case
A discussion of each spreadsheet section follows. This information will help you set up each section of the
model and learn the logic of the formulas in the Calculations and Income Statement sections. If you choose to
enter the data directly, follow the cell structure shown in the figures. You can also download the spreadsheet
skeleton if you prefer. To access the base spreadsheet skeleton, go to your data files, select Case 8, and then
select Kuhlmans.xlsx.
Constants Section
First, build the skeleton of your spreadsheet. Set up the spreadsheet title and Constants section as shown in
Figure 8-1. An explanation of the line items follows the figure.
FIGURE 8-1
Spreadsheet title and Constants section
￿
Spreadsheet title
Enter the spreadsheet title in cell B1, and then merge and center the title
across cells B1 through I1. In Figure 8-1, the title font is 14-point Arial bold. The rest of the
spreadsheet uses Arial bold as well; section titles are 12 points, and the rest of the spreadsheet is
11-point text.
￿
Average Sales per new customer per year
This value, $100.00, is the average sales per new
customer per year that Kuhlman
s has determined from its historical records.
￿
Maximum Budget for Advertising
s
has budgeted this year for advertising expenses, exclusive of the fee that was paid to the media
consultants. Usually this expense is part of the Other Operating Expenses, but it has been
broken out separately to use in this model as a constraint.
This value, $80,000, is the amount of money that Kuhlman
 
Search JabSto ::




Custom Search