Excel Analytics 13-1 (Static) Volume Trade-Off Decisions (L013-5) (The following information applies to the questions displayed below.) Conroy Company manufactures two products--B100 and A200. The company provided the following information wit respect to these products: Estimated customer demand (in units) Selling price per unit Variable expenses per unit B100 2,800 $1,200 $ 700 A200 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 (hours per unit) 1 2 2 A200 (hours per unit) 2 2 Fabrication Molding Machining Assemble & Pack Capacity (in hours) 4,000 6,000 5,000 4,500 3 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, Click here for a brief tutorial on SOLVER in Excel Click here for a a brief tutorial on Charts in Excel 32 . E A H G 1 Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis 6100 2,800 Departmental Data Hours Demanded per unit and Capacity Available 3100 A200 Capacity [Hours per Unit) [Hours per Unit) fin Hours) Fabrication 1 2 4.000 Molding 2 2 6.000 Machine 2 0 5.000 Assemble & Pick 1 4.500 5 $ $ 5 A200 2.000 2,100 1,200 900 100 1,200 $ 700 500 5 2505 8100 Max Units A200 Max Unit 4,000 2.000 3,000 3,000 2,500 1,500 Total Used 7 Units sold 8 Seling Varberpen 10 Contribution margin per un 11 Contribution muren per hour 12 13 16 sales 35 Vw pense 1 Contron man 17 18 19 30 21 22 100 A200 $ 3,360,000 $4,200,000 $1,960,000 $2.400,000 S1400,000 1.000.000 7.560,000 4, M60,000 1.200,000 Unused 12,8001 Fabrication Molding Machine Assamble & Pack Departmental Duta Uuedvs, Unused Capacity in Hours) 3100 A300 2,800 4,000 6.100 5,600 4,000 9.00 5.400 5,600 6,000 6.000 100 (1.500) 5 c 0 1 Conroy Company Volume Trade-Off Decisions with More Than One Constraint 2 Contribution Margin Analysis 5 3100 Departmental Data Hours Demanded per unit and Capacity Available 0100 A200 Capacity (Hour per Unit Hours per Unit Hours) Fabrication 1 2 4.000 Molding 2 2 Machining 2 5.000 Assemble & Pack 0 3 4.500 6.000 5 $ 5 5 A200 7.000 2.100 1,200 900 300 1.2005 7005 500S 2.50 $ 8100 Max Units A200 Max Units 4.000 2.000 3000 3.000 2,500 1.500 Units sold Selling prio Varuble expense per unit 10 Contribution martin per 11 Contribution martin per hour 12 13 14 Sales 15 Variable expenses 16 Comotion margin 1 18 19 20 21 22 0100 A200 $360,000 $4,200,000 $ 51.960,000 57.400,000 3 1.400,000 $ 1.800.000 Total 7.560,000 4 100.000 3.200.000 Fabio Moline Mining Astele Duptimental De Used VI. Unted Capacity in Hun 8100 A200 Used 2.100 4.000 000 5.600 2.600 5,600 5.800 6.000 6.000 Unused 12.2001 13.6001 16001 11.500 24 25 26 2 28 Excel Analytics 13-1 (Static) Part 3 3. Refer to the "Requirements 1-3" tab in your Excel spreadsheet. Based on the answers to requirements 1 and 2: a. Which product has the highest contribution margin per hour of its constraining resource? b. If the company decided to initiate production by maximizing the output of the product chosen in requirement 3a, then how many units of this product would it be able to make before encountering that product's constraint? c. If the company implemented the production plan in requirement 3b, then how many units of its remaining product could it make with the capacity that is still available? (Hints: Your answer from requirement 3b should be input into one of two choices, either cell B7 or C7. Then, you should direct your attention to the unused capacities in cells J15 through J18.) d. What total contribution margin would the company earn if it followed the production plan described in requirements 3b and 3c? (Hint: Your answer from requirement 3c should be input into one of two choices, either cell B7 or C7) 2. A200 has the highest contribution margin per hour b Excel Analytics 13-1 (Static) Part 4 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