Create a problem statement for the case study. Roughly a paragraph or two.
Optimization Group Project Instructions Instructions 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. You may submit additional Excel spreadsheets if you feel they are necessary to support your answers. Prepare a management report, which includes your answers to the assignment questions. Your answers must be entered directly into this Word document below each question. Insert each answer below each question on this document and use as much space as needed. Include cover page and appropriate references. Grading A total of 15 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. Part A. (3 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 Please see picture attached: ???? · Calculate total units produced of each product family in cells B28:B31 ???? Estimate the optimal production plan and run the sensitivity analysis. Optimal production Plan as follows ?????? Part B. (2 points) A third-party company approached Hawley’s management offering to assemble and test 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. ???????? Part C. (8 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: ????? Executive Summary Hawley Lighting Company is a manufacturer of four types of lamps including table lamps, floor lamps, ceiling lamps, and pendant lamps. The Company’s different products each undergo the purchasing of components, assembling and testing the product, and packaging the product for shipping before the product is delivered to a customer. At Hawley these steps take place in two different departments; with table lamps and floor lamps being assembled and finished in Department 1 and ceiling fixtures and pendant lamps being assembled and finished in Department 2. The organization must optimize the production of these products in order to meet its capacity constraints, demand constraints, and advertising constraints to obtain a maximum profit. Through sensitivity analysis and an optimal production plan analysis, our group has determined the ideal production plan for Hawley Lighting Company’s four different products. An advertising budget has also been developed, to be spent proportionally on Hawley’s products in order to maximize the company’s profits based on the unit profit margin and the advertising effect percentages displayed in table 3. Problem Statement Hawley Lighting Company wants to maximize their profits Analysis a. Describe the method of analysis used to answer the case questions and explain why it was used to address the case problems (1.5 points). In this case we have Hawley Lighting, its objective function is trying to determine what is the best option to maximize their profits. Considering this, the method used in the analysis was Linear Programming. Linear programming is used to find the optimal solution while knowing that there are problems with constraints. There are several constraints (9) within this case study, one of the most important constraints is with advertising. Hawley needs to advertise between two departments. Department one has two different lamps: Table (T) and Floor (F) while Department 2 has another two: Ceiling (C) and Pendant (P). 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? (0.5 points): The optimal output plan for this company is to maximize profit from selling its four different lamps: (Table, Floor, Ceiling, Pendant). For Hawley Lighting to maximize profit, it needs to do various things in order to achieve their potential profit. For example, its ceiling for profit is at $215,000. It can produce ($60,000 Table Lamps, $20,000 Floor Lamps, 100,000 Ceiling Lamps and 35000 Pendant Lamps); nevertheless, the company is meeting only $195,800 of its potential, that is a difference of $19,200. · What factors could lead to even better level of performance (1.5 points): The factors which could lead to better performance is the allowable increase for the overtime in the departments where the shadow price is positive and marginal benefit will be positive if the capacity is extended. This will increase the overall profit for the organization with producing the products which have a positive shadow price. Another aspect is the impact of advertising on the overall sales and the shadow price for the advertising is positive. Hence, this could be leveraged more in order to increase the overall sales for the firm. · For each department, what is the marginal value of additional overtime capacity? The marginal value for Department 1 in additional Overtime capacity is: 0 The marginal value for Department 2 is additional Overtime capacity is: 35 · What is the marginal value of additional advertising dollars? The marginal value of additional advertising dollars is: 3.9 · What is the marginal value of additional sales for each product? The marginal value of additional sales for each product is: Table lamps – 38 Floor Lamps – 49 Ceiling Lamps – 0 Pendant Lamps - 30 · What is the trade-off between advertising expenditures and increased sales for Hawley Co. If the advertising budget is 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) The tradeoff between advertising expenditures and increased sales is that by increasing the advertising budget to $182,000, Department 1’s optimal plan of production will increase dramatically with an extra 16,400 units. It will change from 81,800 units to 98,200 units produced. At the same time, its production plan will not reach capacity. This shows that increasing advertising budget has a direct affect to sales increase which ultimately leads to maximized profits. 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) Hawley’s management should consider this offer since the overall profits of the organization would be better than without outsourcing. As we can see in the last tab, the outsourced pendant lights should be 23,700 and the in-house pendant lights should be 14,000. Advertising budget for the pendant lights should be 18000. 4. Conclusions and Recommendations (3 points). · 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? CONCLUSIONS: 1. Original Scenario · Company will not maximize profit by increasing Department 1 production plan with Original Scenario. · Department 1 is not using the production capacity in full. Department 2 is using the production capacity in full. Department 1 total capacity is 125,000 units produced in Regular and Overtime; however, the company is producing only 81,800 in regular time. Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $B$23 Department 1 regular time LHS 81800 0 100000 1E+30 18200 $B$24 Department 1 overtime LHS 0 0 25000 1E+30 25000 $B$25 Department 2 regular time LHS 90000 38 90000 21000 55000 $B$26 Department 2 overtime LHS 24000 35 24000 21000 24000 · To maximize profit and reach the optimal demand point, the company needs to meet demand of potential sales in the amount of $215,000 ($60,000 Table Lamps, $20,000 Floor Lamps, 100,000 Ceiling Lamps and 35000 Pendant Lamps); however, the company is meeting only $195,800. Demand constraints: Table lamps 60000 60000 Floor lamps 21800 21800 Ceiling lamps 79000 100000 Pendant lamps 35000 35000 · In the original scenario, the unmet constraint for this production plan is the demand for Ceiling Lamps. Current Demand for potential sales is 100,000, but the optimal plan resulted in 79,000. Ceiling lamps 79000 100000 2. Outsource Scenario · By outsourcing the Pendant Lamp production, the met demand increased by 8% (217,700) Demand constraints: Table lamps 60000 60000 Floor lamps 20000 20000 Ceiling lamps 100000 100000 Pendant lamps 37700 37700 · By outsourcing the Pendant Lamp production, the company will maximize its profits by 6% ($9,178,100). Prior profit $8,640,200 Original Scenario Objective function: max (Profit) = 8640200 Outsourced Scenario · Advertising will increase unit profit by $6.95 if using outsource production vs $3.90 producing in house. $B$33 Advertising constraint LHS 18000 6.95 · Unit profits for Ceiling and Lamps will increase up to 58% combined if production is outsourced. Outsource Scenario $B$30 Ceiling lamps LHS 100000 23 $B$31 Pendant lamps LHS 37700 53 Original Scenario $B$30 Ceiling lamps LHS 79000 0 $B$31 Pendant lamps LHS 35000 30 · Even though Marginal value for Department 2 production will decrease by outsourcing the pendant’s production, the overall profit increases. Outsource Scenario: Total $196.95 Final Shadow Cell Name Value Price $B$23 Department 1 regular time LHS 80000 0 $B$24 Department 1 overtime LHS 0 0 $B$25 Department 2 regular time LHS 90000 15 $B$26 Department 2 overtime LHS 24000 12 $B$28 Table lamps LHS 60000