This is a group assignment. In order to complete the assignment, first read the “Hawley Lighting Company” case study. Conduct necessary calculations and answer the questions listed below. Submit your Excel spreadsheet(s) with calculations to the assignment dropbox before the posted deadline.
Prepare a properly formatted Management Report , which includes your answers to the assignment questions. Include cover page and appropriate references.
Grading
A total of 20 points is possible for this assignment. This includes the point values which are assigned to each question (point values are noted next to each question below). This includes the point values which are assigned to each question (point values are noted next to each question below) plus 2 points which are earned based on following the prescribed assignment format, and the proper writing style including APA format.
For full credit for Parts A and B, you must turn in your spreadsheet with dynamically coded formulas (not hard-coded numbers).
1. Spreadsheet Model (in Excel)
Part A (3 points). Read the case study “Hawley Lighting Company”. Download Hawley Data Fall23.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.
Part B (2 points). A third-party company approached Hawley’s management offering to assemble and produce pendant lights for $27 per unit. Modify the production plan model in Part A to include the pendant light outsourcing. Assume the advertising budget with $18,000 limit. Estimate new optimal production plan and run the sensitivity analysis.
2. Management Report (in Word)
Part C (13 points). Prepare a management report, discussing the analysis results obtained in Part A and Part B. The report should include the following sections and must answer the following questions:
1. Executive Summary.
2. Problem Statement.
3. Analysis. This section must include the following information:
a. Describe the method of analysis used to answer the case questions and explain why it was used to address the case problems.
b. Analyze the tactical and strategic information provided by the optimal solution and sensitivity report in Part A:
· What is an optimal output plan for the company?
· What factors could lead to even better level of performance:
· For each department, what is the marginal value of additional overtime capacity?
· What is the marginal value of additional advertising dollars?
· What is the marginal value of additional sales for each product?
· What is the trade-off between advertising expenditures and increased sales for Hawley Co. If the advertising budget to be increased, how it would affect the production plan? Assume that you increased the advertising budget by the amount of the allowable increase, how this advertising budget change would affect the optimal production plan? (1 point)
c. Analyze the optimal solution obtained in Part B:
· 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. (0.5 points)
4. Conclusions and Recommendations.
· Summarize your conclusions and provide recommendations on optimal production strategy and on how, in your opinion, Hawley Co. should coordinate advertising and sales of different products?
5. Bibliography. Include appropriate references and follow APA style.