Microsoft Office Tutorials and References
In Depth Information
USING SOLVER ON A NEW PROBLEM
Income Statement Section
The last section you need to construct is the Income Statement, as shown in Figure D-48. An explanation of
the needed formulas follows the figure.
Income Statement section with fill legend
Sales Revenues—This value is the total sales revenues from the Calculations section (cell G28).
Less: Manufacturing Cost—This value is the total manufacturing costs from the Calculations
section (cell G27).
Gross Profit—This value is the Sales Revenues minus the Manufacturing Cost.
Less: Sales, General, and Administrative Expenses—This value is the Sales Revenues multiplied
by the Sales, General, and Administrative Expenses per Dollar Revenue from the Constants
section (cell C8).
Net Income before taxes—This value is the Gross Profit minus the Sales, General, and
Less: Income Tax Expense—If the Net Income before taxes is greater than zero, this value is the
Net Income before taxes multiplied by the Income Tax Rate in the Constants section. If Net
Income before taxes is zero or less, the Income Tax Expense is zero.
Net Income after taxes—This value is the Net Income before taxes minus the Income Tax
Expense. You will use this value as your Optimization Cell because you want to maximize Net
Income after taxes.
Setting up Solver
You need to satisfy the following conditions when running Solver:
Your objective is to maximize Net Income after taxes (cell C37).
Your Changing Cells are the Units Produced (cells D22, E22, and F22).
Observe the following constraints:
You must produce at least the Minimum Sales Demand for each product (cells D6, E6,
Your total Lbs. of Ambergris Used (cell G25) cannot exceed the Available Ambergris
You cannot produce negative units of any product (enter constraints for the Changing Cells
to be greater than or equal to zero).
You can only produce whole units of any product (enter constraints for the Changing Cells
to be integers).
Run Solver and create an Answer Report when Solver finds the solution. When you complete the
program, print your spreadsheet with the Solver solution, and print the Answer Report. Save your work and