This is an individual assignment; while you are permitted to ask the instructor or a classmate specific questions if you get stuck, you are to complete your own work. This assignment comes with a template; no template is perfect, but this one follows my convention of input parameters in blue, decision variables in red, constraints are in simple black borders with relation symbols in between, and the objective function is double-bordered in black. The information below will provide the context you need to set up the what-if model required for this assignment. The overall template has been provided for you, and all the input parameters have been entered into the template, so now you must complete the spreadsheet model by entering the formulas necessary to calculate what is missing and entering relation symbols (e.g.

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. 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.

can you please show me how should i do the second part solver. PLEASE SHOW YOUR ANSWER IN EXCEL.

Solver Parameters Set Objective: To: Max lin Value Of: By Changing Variable Cells: Subject to the Constraints: Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth