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.

FIGURE D-48

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

Administrative Expenses.

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,

and F6).

Your total Lbs. of Ambergris Used (cell G25) cannot exceed the Available Ambergris

(cell C9).

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

close Excel.