Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Income Statement section
Sales Revenues—This value is the total Sales revenues from the Calculations section (cell I19).
Less: Cost of Goods Sold
This value is the total Cost of Goods from the Calculations section (cell I20).
Gross Profit—This value is the Sales Revenues minus the Cost of Goods Sold.
Less: Advertising plus other Operating Expenses—This value is the sum of the total Advertising
Expense from the Calculations section (cell I17) and the Other Operating Expenses from the
Constants section (cell C7).
Net Income before taxes—This value is the Gross Profit minus the Advertising plus other
Less: Income tax—If the Net Income before taxes is greater than zero, then the store made a
profit, and this value is the Net Income before taxes multiplied by the Income Tax Rate from the
Constants section. If the store makes nothing or has a net loss, then the income tax is zero.
However, assume that the store made a profit. Using an IF statement in this line of calculations
will create a nonlinear model, which means you will not be able to use the Simplex LP solving
method. Later in the case, however, you can modify this calculation with the IF statement and
use a different solving method.
Net Income after taxes—This value is the Net Income before taxes minus the income tax. You
will optimize this cell because you want to maximize Net Income after taxes in this case, so you
should fill the cell with an appropriate background color to easily identify it as the Optimization
If your formulas are correct, the “historical” Income Statement for Kuhlman’s will appear as shown in
Completed Income Statement for historical advertising
As you can see, Kuhlman
s is barely making money, which is why it is considering other advertising media.
Setting up and Running Solver
Before using the Solver Parameters window, you should consider jotting down the parameters you must define
and their cell addresses. Here is a suggested list:
The cell you want to optimize (Net Income after taxes) and whether you want to minimize or
The cells you want Solver to manipulate to obtain the optimal solution (Changing Cells)
The constraints you will define:
Each Changing Cell has a maximum amount of advertising you can buy (in the row below
the Changing Cells).