Philco Lighting makes crystal lamps in the shape of continents for sale in its many gift shops situated at international airports. These lamps are very popular and Philco believes it can sell all the units they produce. Management would like to know the optimal production levels for each of their products and the estimated Total Contribution Margin that can be expected. Data for the final three steps before shipment to customers is as follows:

Prepare a Solver solution (including the Answer Report and the Sensitivity Report) showing the optimal solution. Then answer the following questions by typing your answers in bold RED directly into this WORD document. Drop both your completed Excel file AND Word document into the dropbox provided (there should be 2 files in your dropbox when you are done).

	Assembly	Polishing	Packing	CM/Unit
North America	2	3	2	15
Europe	4	2	3	25
Asia	3	3	2	30
South America	7	4	5	45
LHs Available	10,000	5,000	6,000

Questions:

1. Write down the objective function of this problem in the correct format.

2. Write down the constraints in the correct format:

3. How many of each product should be produced?

4. What is the highest CM Philco can earn given its current expectations?

5. Which constraints are binding?

6. How many Assembly hours are unused?

7. How much would you be willing to pay for additional Assembly hours

8. How many polishing hours are unused?

9. If you could purchase 1,000 extra hours for $4.00hour, which hours would you purchase?

10. What is the most you would you be willing to pay for additional Polishing hours?

11. How many Polishing hours would you be willing to buy at the above price?

12. If we were forced to produce 1 South American unit, how would this affect our CM?

13. By how much would the North American CM have to increase before it would be

profitable to produce?

14. If management is forced to reduce the CM on Asia units to $26 to remain competitive how

will this affect the objective function (CM)?

15. If management had to drop the CM on Asia units again to $24 per unit, what would be the

effect on the objective function (CM)?

The Answer Report and Sensitivity Report are available on the following page.

Use the Reports below to help you answer the questions in this assignment:

Adjustable Cells Constraints \begin{tabular}{lccccr} \hline Cell & Name & Cell Value & Formula & Status & Slack \\ \hline \$C\$18 LH's Used Assembly & 8,200 & \$C\$18FinalValue & ShadowPrice & ConstraintR.H.Side & AllowableIncrease & AllowableDecrease \\ \hline \$C\$18 LH's Used Assembly & 8,200 & 0 & 10000 & 1E+30 & 1800 \\ \hline SD\$18 LH's Used Polishing & 5,000 & 8 & 5000 & 4000 & 1000 \\ \hline SE\$18 LH's Used Packing & 6,000 & 3 & 6000 & 1500 & 2666.666667 \\ \hline \end{tabular}