The question needed: Part B. Please explain a little how you got the answer, many thanks.

Part B is needed with a little bit of explanation, please.

First read the "Hawley Lighting Company" case study. Conduct necessary calculations and answer the questions listed below. Submit your Excel spreadsheet(s) with calculations. 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:18) 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 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. Hawley Lighting Company This problem was taken from Powell, S.G. and Baker, K.R. (2007). Management Science: The Art of Modeling with Spreadsheets, 2nd ed., Hoboken, NJ: John Wiley & Sons, Inc., p. 280-281 The Hawley Lighting Company manufactures four types of lamps at its factory including table lamps, floor lamps, ceiling lamps, and pendant lamps. Table 1 presents the average material costs for each of the products. Each product is made in one of two production processes by purchasing components, assembling and testing the product, and finally packaging it for shipping. Table lamps and floor lamps go through the assembly and finishing process in Department 1, while ceiling fixtures and pendant lamps go through the process in Department 2. Variable production costs and capacities are shown in Table 2. The capacities are measured in units of product. Note that there are regular and overtime possibilities for each department. Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices (see Table 3). Sales levels can also be affected by advertising expenditures. Starting with the demand levels in the table, in advertising raises the demand by the percentage shown in the last row times the amount spent on advertising. For example, an increase in advertising of $5,000 for table lamps would raise demand by 600 units. However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products. Table 1 Product Table Floor Ceiling Pendant Material cost $66 85 50 80 Table 2 Regular Time Overtime Process Unit Cost Capacity Unit Cost $16 18 Department 1 Department 2 100,000 90,000 Capacity 25,000 24,000 12 15 Table 3 Floor Table $120 60 Ceiling 100 Pendant 160 150 Selling price Potential sales (000) Advertising effect 20 100 35 12% 10% 8% 15% Costs and prices: r = regular time; o = overtime Table lamps (T) Floor lamps (F) Tr To Fr Fo 120 120 150 150 66 66 85 85 16 18 16 18 Celling price Material costs Production costs Unit profit Ceiling lamps (C) Cr 100 100 50 50 12 15 Pendant lamps (P) Pr 160 160 80 80 12 15 Department 1 Department 2 Decision Variables: Tr To Fr Fo Cr Co PY Units produced T F C P Advertising Objective function: max (Profit) = LHS RHS 0 Constraints: Capacity constraints: Department 1 regular time Department 1 overtime Department 2 regular time Department 2 overtime Demand constraints: Table lamps Floor lamps Ceiling lamps Pendant lamps 100000 25000 90000 24000 60000 20000 100000 35000 Advertising constraint 0 18000