AutoSave O Search Thatche SUB - Excelknalytics_VolumeTradeOffDecisions_Template.xlsx - Excel Page Layout Help File Home Insert Draw Formulas Data Review View 8 J7 f ='Requirements 1-3'07 A H K Part 8 of 9 1 Required Information The following information applies to the questions displayed below.) Conroy Company manufactures two products-B100 and A200. The company provided the following information with respect to these products: D G Conroy Company Volume Trade-Off Decisions with More Than One Constraint 2 3 3 Contribution Margin Analysis 222 points 6100 Max Units A200 Max Units BIDO A 200 8 Blee Azee 2,8ee 2, eee $1,200 $2,100 $ 780 $1,200 Estimated customer demand (in units) Selling price per unit Variable expenses per unit 02:55:13 Departmental Data: Hours Demanded per Unit and Capacity Available 8100 A200 Capacity (Hours per Unit) (Hours per Unit) [in Hours) Fabrication 1 2 Molding 2 2 6,000 Machining 0 5.000 Assemble & Pack 0 3 4,500 $ S S $ $ 700S (700) (350) 2,100 1,200 900 300 7 Units sold a Selling price 9 Variable expense per unit 10 Contribution margin per unit 11 Contribution margin per hour 12 13 14 Sales 15 Variable expenses 16 Contribution margin 17 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: Book B100 A200 Total A280 (hours per unit) $ $ $ $ $ $ $ $ $ References B180 (hours per unit) 1 2 2 Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Fabrication Molding Machining Assen Pack Capacity (in hours) 4,000 6,899 5.800 4, see Fabrication Molding Machining Assemble & Pack Unused 400D 6.000 5.000 4500 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 brief tutorialon Charts in Excel 8. In the Excel template, navigate to the "Requirement 8" tab. Using Solver, a. What is the maximum contribution margin the company can earn with its available resources if it increases the price of 100 to $1.400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8e? c. Is your answer to requirement 8a greater than less than or equal to your answer from requirement 7d? Complete this question by entering your answers in the tabs below. . Red BA Red BB Req BC What is the maximum contribution margin the company can earn with its available resources if it increases the price of B100 to $1,4007 Maximum contribution margin Rege Req3B >
