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.

Part 1

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. Once you verify this, proceed to Part 2.

Part 2

Use the Data Analysis > Solver tool to build an optimization model based of the what-if model you created in Part 1. 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. For an example of how this might look, consider the screenshot from a different problem on the next page.

M M R Model | Parameters 2 B Battery Pack PT-100 Quant. Required 200,000 100,000 150,000 Production Plan Product Philippines Mexico PT-100 PT-200 1 Production Capacity Plant Philippines Mexico PT-200 PT-300 5 PT-100/PT-200 175,000 160,000 PT-300 Battery Pack - Plant PT-300 75,000 100,000 Total Total Quant. Quant. Product Produced Relation Relation Required B Product Mexico Philippines $0.95 9 PT-100 $0.98 Plant PT-100 > 0 Production Costs PT-200 $0.98 $1.06 Philippines Mexico PT-200 >= 1 PT-300 $1.34 $1.15 Cost of Shipping/unit $0.18 $0.10 PT-300 >= 2 3 Total Quant. Plant Product Philippines Mexico PT-100+PT-200 #REF! Plant Relation Philippines Mexico