PLS SHOW EXCEL FORMULA

Part 2 (30 points) (Make or Buy) A sudden increase in the demand for hair clippers due to barber closures has left Wahl Clipper Corporation (WCC) with insufficient capacity to meet demand. The company has seen monthly demand from its retailers for its electronic and battery operated detectors rise to 20,000 and 10,000, respectively, and WCC wishes to continue meeting demand. WCC's production process involves three departments: Fabrication Assembly, and Shipping. The relevant quantitative data on production and prices are summarized below. Department Monthly hours available Hours/unit (electronic) Hours/unit (battery) Fabrication 2.000 0.15 0.10 Assembly 4,200 0.20 0.20 Shipping 2,500 0.10 0.15 Variable cos/unit $18.80 $16.00 Retail Price $29.50 $28.00 The company also has the option to obtain additional units from a subcontractor, who has offered to supply up to 20,000 units per month in any combination of electronic and battery operated models, at a charge of $21.50 per unit. For this price, the subcontractor will test and ship its models directly to the retailers without using WCC's production process. a) What is the maximum profit and the corresponding make/buy levels? Note that this is a planning model, and fractional values for the decisions are acceptable. b) If the fabrication capacity were to increase by 10%, how will the optimal buy/make decisions change? How will the optimal profit change