Photon Technologies, Inc., a manufacturer of batteries for mobile phones, signed a contract with a large electronics manufacturer to produce three models of lithium-ion battery packs for a new line of phones. The contract calls for the following:

Battery Pack	Production Quantity
PT-100	200,000
PT-200	100,000
PT-300	150,000

Photon Technologies can manufacture the battery packs at manufacturing plants located in the Philippines and Mexico. The unit cost of the battery packs differs at the two plants because of differences in production equipment and wage rates. The unit costs for each battery pack at each manufacturing plant are as follows:

	Plant
Product	Philippines	Mexico
PT-100	$0.95	$0.98
PT-200	$0.98	$1.06
PT-300	$1.34	$1.15

The PT-100 and PT-200 battery packs are produced using similar production equipment available at both plants. However, each plant has a limited capacity for the total number of PT-100 and PT-200 battery packs produced. The combined PT-100 and PT-200 production capacities are 175,000 units at the Philippines plant and 160,000 units at the Mexico plant (so for example, Mexico could not produce 100,000 PT-100s and 80,000 PT-200s because this adds up to 180,000 total units of PT-100 and PT-200, which is more than their 160,000 maximum). The PT-300 production capacities are 75,000 units at the Philippines plant and 100,000 units at the Mexico plant. To be clear, the limits on PT-300 are separate from the limits on the other two. The cost of shipping from the Philippines plant is $0.18 per unit, and the cost of shipping from the Mexico plant is $0.10 per unit.

Suppose that both facilities will, for now, be producing 100,000 units of PT-100, 80,000 units of PT-200, and 75,000 units of PT-300; enter these numbers as the starting values for your decision variables. Build the what-if model according to the instructions above, and enter relation symbols (e.g.

What total cost can management expect using this plan? To make sure your model is working properly, make sure that your total cost computed at this point is $614,350.

Use the Data Analysis > Solver tool to build an optimization model based on the what-if model you created. Use the optimization model to calculate the optimal Production Plan that satisfies the production requirements for each product at the lowest possible cost. Validate your optimization model by making sure it finds the optimal total cost of $535,000. Once you have verified that your optimization model is correct, then take a screenshot of the Solver dialog box and paste it onto the spreadsheet next to the what-if model.

Parameters Battery Pack PT-100 PT-200 PT-300 Quant. Required 200,000 100,000 150,000 Production Capacity Plant Philippines Mexico 175,000 160,000 75,000 100,000 PT-100/PT-200 Battery Pack PT-300 Product Mexico Plant Philippines $0.95 $0.98 Production Costs PT-100 PT-200 $0.98 $1.06 Plant Philippines Mexico PT-300 $1.34 $1.15 Cost of Shipping/unit $0.18 $0.10 Model Production Plan Philippines Mexico Product PT-100 PT-200 PT-300 Total Quant. Produced Total Quant. Required Relation Product PT-100 PT-200 PT-300 Production Capacity Total Quant. Produced Plant Philippines Mexico Plant Relation Philippines Mexico Product PT-100+PT-200 PT-300 Plant Philippines Mexico Production Cost Shipping Cost Total Cost