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.

FIGURE D-46

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.

Calculations Section

Your model should contain the Calculations section shown in Figure D-47.

FIGURE D-47

Calculations section

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:

—

C10*D16

C11*D17

C12*D18

C13*D19

D5

¼

þ

þ

þ

þ

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

Statement section.

—

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

Statement section.

—