Microsoft Office Tutorials and References
In Depth Information
USING SOLVER ON A NEW PROBLEM
Extremely small quantities of ambergris and other additives are required to make one bottle of each
product. Also, each product requires a different amount of ambergris.
Quantity Manufactured (Changing Cells) Section
This model contains a separate Changing Cells section called Quantity Manufactured, as shown in
Figure D-46. This section contains the cells that you want Solver to manipulate to achieve the highest net
income after taxes.
Quantity Manufactured (Changing Cells) section
Cells D22, E22, and F22 are yellow to indicate that Solver will change them to reach an optimal solution.
To begin, enter the minimum sales demand in these cells, which will remind you to specify the minimum
demand constraints from the Constants section in the Solver Parameters window.
Your model should contain the Calculations section shown in Figure D-47.
The section contains the following calculations:
Lbs. of Ambergris Used
This value is the pounds of ambergris per unit from the Bill of Materials
section, multiplied by Units Produced from the Quantity Manufactured section for each of the
three products. The Totals cell (G25) is the sum of cells D25, E25, and F25. Use the value in this
cell to specify the constraint that you only have 20 pounds of ambergris available to use for raw
materials (Constants section, cell C9).
Manufacturing Cost per Unit (Materials Costs plus Conversion Cost)
To get this value, write a
formula that multiplies the unit cost for each of the four product ingredients by the amount per
unit specified in the bill of materials, multiplied by Units Produced. The total materials costs for
the four ingredients are added together, and then the Conversion Cost per Unit is added from
the Constants section to obtain the Manufacturing Cost per Unit. Enter the following formula for
the Body Spray Manufacturing Cost per Unit in cell D26:
The Totals cell is not used in this row
you can fill the cell in gray to indicate that it is not used.
Total Manufacturing Costs per Product Line
This value is the Manufacturing Cost per Unit
multiplied by Units Produced from the Quantity Manufactured section. The Totals cell (G27) is the
sum of cells D27, E27, and F27. You will use the value in the Totals cell in the Income
Sales Revenues per Product Line
This value is the Sales Price per bottle from the Constants
section multiplied by Units Produced from the Quantity Manufactured section. The Totals cell
(G28) is the sum of cells D28, E28, and F28. You will use the value in this cell in the Income