*QNT 5160 Optimization Case Study Assignment*
*Part A. (7 points)* Read the case study “Hawley Lighting Company”. Download HawleyData.xlsx file and complete the model:
– Calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19). – Calculate total capacity required in Department 1 (overtime) in cell B24 and Department 2 (regular and overtime) in cells B25:B26. – Calculate total units produced of each product family in cells B28:B31.
Estimate the optimal production plan and run the sensitivity analysis. Analyze the tactical and strategic information provided by the optimal solution:
1. What is an optimal output plan for the company? *(2 points*) 2. What factors could lead to even better level of performance: *(3 points)* 1. For each department, what is the marginal value of additional overtime capacity? 2. What is the marginal value of additional advertising dollars? 3. What is the marginal value of additional sales for each product? 3. What is the trade-off between advertising expenditures and increased sales for Hawley Co. if the advertising budget is to be increased? How would the increase affect the production plan? Assume an increase in the advertising budget by the amount of the allowable increase – how does this advertising budget changeaffect the optimal production plan? *(2 points)*
*Part B. (3 points)* A third-party company approached Hawley’s management offering to assemble and test pendant lights for $27 per unit. Should the Hawley’s management consider the offer? If yes, how much of pendant light units should be outsourced and how many units should be produced in-house. Assume the advertising budget with a $18,000 limit.
*Part C. (5 points)* Prepare a management report, no longer than 2 pages (12 pt. Arial font), discussing the questions in Part A, in Part B and referring to your Excel spread sheets. Include at least one graphic or table to support your approach. Provide your recommendations on how, in your opinion, Hawley Co. should coordinate advertising and sales of different products.
*Note:* each of the 3 scenarios (Initial, Advertising Trade-off, Outsource) should have Excel Tabs for each: Model Calc, Answer Report and Sensitivity Report.
