Conroy Company manufactures two products-B100 and A200. The company provided the following information with respect to these products: B100 A200 Estimated customer demand (in units) Selling price per unit Variable expenses per unit 2,800 $1,200 $ 700 2,000 $2,100 $1,200 The company has four manufacturing departments-Fabrication, Molding, Machining, and Assemble & Pack. The capacity available in each department (in hours) and the demands that one unit of each of the company's products makes on those departments is as follows: B100 A200 city (in hours) (hours per unit) (hours per unit) Fabrication Molding Machining Assemble & Pack 2 4,000 6,000 5,000 4,500 2 2 The company is trying to decide what product mix will maximize profits. Given that its fixed costs will not change regardless of the chosen mix, the company plans to identify the product mix that maximizes its total contribution margin. Click here to download the Excel template, which you will use to answer the questions that follow. 4. In the Excel template, navigate to the "Requirement 4" tab. Using Solver: a. Calculate the maximum contribution margin the company can earn given the capacities of its four manufacturing departments. b. How many units of each product would the company produce to earn the contribution margin from requirement 4a? c. Is your answer to requirement 4a greater than, less than, or equal to your answer from 3d? d. Which of the following best explains the reason for the answer to requirement 4c? 203 Home Page Layout Formulas Data Review View + Share Insert X Cut > AutoSum Calibri (Body) 11 - A- A- E Wrap Text General 9 Copy E Fill B I U A 24 +.0 .00 % 00 . Conditional Format Formatting as Table Paste H Merge & Center ) Cell Insert Delete Format Sort & Filter .0 Format Styles Clear : x v fx H20 A B C D E F G H K 1. Conroy Company 2 Volume Trade-Off Decisions with More Than One Constraint 3 4 Contribution Margin Analysis Departmental Data: Hours Demanded per Unit and Capacity Available A200 acity (in Hours) 5 B100 6. B100 A200 (Hours per Unit) (Hours per Unit) B100 Max Units A200 Max Units 7 Units sold Fabrication 2 4,000 8 Selling price 9 Variable expense per unit 1,200 $ 2,100 Molding 2 6,000 $ 700 $ 1,200 Machining 2 5,000 10 Contribution margin per unit 500 $ 900 Assemble & Pack 3 4,500 11 Contribution margin per hour 12 13 B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) 14 Sales $ $ B100 A200 Used Unused 15 Variable expenses $ Fabrication 4,000 16 Contribution margin $ Molding 6,000 17 Machining 5,000 18 Assemble & Pack 4,500 19 20 21 22 23 24 25 Requirements 1-3 Requirement 4 Requirement 5 Requirement 7 Requirement 8 +