need help please with this project.

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: Estimated customer demand (in units) Selling price per unit Variable expenses per unit B100 2,800 1,200 700 1200 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) A200 (hours per unit) Fabrication Molding Machining Assemble Capacity (in hours) 4,000 6,000 5,000 4,500 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. Required: 1. Refer to the "Requirements 1-3 tab in your Excel spreadsheet. Assume the company focuses solely on producing B100: a. Which department would be the constraint? (Hint: Create formulas in cells J7 through J10 that calculate B100's maximum output in each department.). b. What is B100's contribution margin per hour of the constraining resource that you identified in requirement 1a? (Hint: Input a formula in cell B11 that translates B100's contribution margin per unit in cell B10 to its contribution margin per hour of the constraining resource.) Complete this question by entering your answers in the tabs below. Req1A Reg 1B Which department would be the constraint? (Hint: Create formulas in cells J7 through J10 that calculate B100's maximum output in each department.) The constraining department is Reg 18 > Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis 3100 A200 $ B100 Max Units Units sold Selling price Variable expense per unit 5 Contribution margin per units Contribution margin per hour Departmental Data: Hours Demanded per Unit and Capacity Available 8100 A200 Capacity (Hours per Unit) (Hours per Unit) in Hours) Fabrication 4,000 Molding 6,000 Machining 5.000 Assemblea Pack 4,500 A200 Max Units 1.2005 7 00 $ 500S 2.100 1.200 900 8100 A200 Total Variable expenses Contribution margin Departmental Data: Used vs. Unused Capacity (in Hours) B100 200 Fabrication Molding Machining Assemble Unused 4,000 6.000 5.000 4,500 Pack Required: 1. Refer to the "Requirements 1-3" tab in your Excel spreadsheet. Assume the company focuses solely on producing B100: a. Which department would be the constraint? (Hint: Create formulas in cells J7 through J10 that calculate B100's maximum output in each department.) b. What is B100's contribution margin per hour of the constraining resource that you identified in requirement ta? (Hint: Input a formula in cell B11 that translates B100's contribution margin per unit in cell B10 to its contribution margin per hour of the constraining resource.) Complete this question by entering your answers in the tabs below. Req1A Reg 1B What is B100's contribution margin per hour of the constraining resource that you identified in requirement la? (Hint: Input a formula in cell B11 that translates B100's contribution margin per unit in cell B10 to its contribution margin per hour of the constraining resource.) Product B100's contribution margin per hour is 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) Complete this question by entering your answers in the tabs below. Req3A Req 3B Req 3C Reg 3D Which product has the highest contribution margin per hour of its constraining resource? The product with the highest contribution margin per hour of its constraining resource is Reg 38 > 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 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 35 and 3c? (Hint: Your answer from requirement 3c should be input into one of two choices, either cell B7 or C7.) Complete this question by entering your answers in the tabs below. Reg 3A Reqt3B Reg 30 Reg 3D 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? The maximum output is units C Reg 3A Req30 > T YVUMUS LUCUS WYCISI MUCRUIS LOVINY 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 produc could it make with the capacity that is still available? (Hints: Your answer from requirement 3b should be input into one 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.) Complete this question by entering your answers in the tabs below. Req Req 3B Reg 30 Reg 3D 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 15 through J18.) The maximum output is units Reg 38 Reg 30 > Check my work L UIS MYLIOM SUIS LALELEZIOLE, LILILYVU WILUS VOUSYLLE MEDULIS LLIGLIVIIVY. 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 36 and 3c? (Hint: Your answer from requirement 3c should be input into one of two choices, either cell B7 or C7.) Complete this question by entering your answers in the tabs below. Req 3A Reg 3B Req 3C Reg 3D 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.) The total contribution margin is Reg 3C + Requirements 1-3 Collaborate Requirement Requirement Requirements Format Sort & Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis 3100 A200 8100 Max Units A200 Max Units 7 Units sold 8 Selling price Variable expense per unit 5 10 Contribution margin per units 11 Contribution margin per hour Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 4,000 Molding 6.000 Machining 5,000 Assemble & Pack 4,500 1 200 $ 700 S 500 S 2.100 1.200 920 8100 A200 14 Sales 15 Variable expenses Contribution margin B100 Departmental Data: Used vs. Unused Capacity (in Hours) A200 Used Fabrication Molding Machining Assemble at Pack Unused 4.000 6,000 5.000 4,500 Check my work 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? Complete this question by entering your answers in the tabs below. Reg 4A Reg 4B Req4C Req 4D Calculate the maximum contribution margin the company can earn given the capacities of its four manufacturing departments The maximum contribution margin is Req 48 > 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? bed Complete this question by entering your answers in the tabs below. Req 4A Req 48 Reg 4 Req 4D Is your answer to requirement 4a greater than, less than or equal to your answer from 3d? The answer in requirement 4a is the answer from requirement 3d. ( Req 48 Req 40 > 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? Complete this question by entering your answers in the tabs below. Req 4A Req 4B Reg 4C Red. 4D Which of the following best explains the reason for the answer to requirement 4c? Microsoft Solver simultaneously considers both products, the demands they make on each department, and the capacities available in each department, whereas the approach used in requirements 1-3 considers each product independently Microsoft Solver considers each product independently, whereas the approach in requirements 1-3 simultaneously considers both products, the demands they make on each department, and the capacities available in each department