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 B100 to $1,400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8a? 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. Req 8A Req 8B Req 8C What is the maximum contribution margin the company can earn with its available resources if it increases the price of B100 to $1,400? Maximum contribution margin Req 8A Req 8B > 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 B100 to $1,400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8a? 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. Req 8A Req 8B Req 8C How many units of each product is the company producing to earn the contribution margin from requirement 8a? Product A200 units Product B100 units 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 B100 to $1,400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8a? 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. Req 8A Req 8B Req 8C Is your answer to requirement 8a greater than, less than, or equal to your answer from requirement 7d? The answer from requirement sa is the answer from requirement 7d. 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 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: A200 Fabrication Molding Machining Assemble & Pack B100 (hours per unit) 1 2 2 0 (hours per unit) 2 2 0 3 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. Click here for a brief tutorial on SOLVER in Excel. Click here for a a brief tutorial on Charts in Excel. Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 3 4,500 $ $ 1,200 $ 700 $ 2,100 1,200 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ 500 $ 900 O B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ Unused 4,000 $ $ Fabrication Molding Machining Assemble & Pack 6,000 5,000 4,500 Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 0 3 4,500 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ $ $ 1,200 $ 700 $ 500 $ 2,100 1,200 900 B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ $ Unused 4,000 Fabrication $ Molding Machining Assemble & Pack 6,000 5,000 4,500 A B D E F G H 1 Fabrication 2 3 4 A200 Units Molding Machinery 0 Assemble & Pack 0 0 0 500 1000 5 6 1500 7 2000 B100 Units 8 0 2500 2500 9 0 10 3000 -3000 3500 11 12 13 4000 -2000 0 14 Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 0 3 4,500 $ $ 2,100 1,200 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ 700 $ (700) $ (350) $ $ 900 $ 300 B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ $ Unused 4,000 6,000 $ Fabrication Molding Machining Assemble & Pack 5,000 4,500 5. In the Excel template, navigate to the Requirement 5 tab. Using Charts, prepare a graph using the following three guidelines. First, label the X-axis B100: Units Produced and the Y-axis A200: Units Produced. Then establish a range on each axis of 0 4,000 units. Second, plot four lines that depict the production possibilities within each of the four departments. For example, the Fabrication Department could produce a maximum of 4,000 units of B100 (= 4,000 hours available = 1 hour per unit) or a maximum of 2,000 units of A200 (= 4,000 hours available = 2 hours per unit). Thus, this line should connect two data points, 4,000 units on the X-axis and 2,000 units on the Y-axis. Third, once you have plotted all four lines, pay particular attention to the portion of the graph that is enclosed by the four lines because it depicts the full range of Conroy's production possibilities. Based on the graph that you have created, which of the following statements are true? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) ? Producing 1,000 units of A200 and 1,500 units of B100 is outside the range of production possibilities. ? Producing 1,500 units of A200 and 1,500 units of B100 is outside the range of production possibilities. ? Producing 500 units of A200 and 2,500 units of B100 is within the range of production possibilities. ? Producing 2,000 units of A200 and 1,500 units of B100 is within the range of production possibilities. 7. In the Excel template, navigate to the Requirement 7 tab. Assume that Conroy is considering raising the price of B100 to $1,400. The company believes that the price increase would drop maximum customer demand from 2,800 units to 2,600 units. a. If Conroy implements the price increase, which product would have the highest contribution margin per unit of its constraining resource? b. If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? c. If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produc with the capacity that is still available? d. What total contribution margin would the company earn if it followed the production plan described in requirements 7b and 7c? Complete this question by entering your answers in the tabs below. Req 7A Req ZB Req 7c Req ZD If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? It would be able to produce units. 7. In the Excel template, navigate to the Requirement 7 tab. Assume that Conroy is considering raising the price of B100 to $1,400. The company believes that the price increase would drop maximum customer demand from 2,800 units to 2,600 units. a. If Conroy implements the price increase, which product would have the highest contribution margin per unit of its constraining resource? b. If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? c. If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produce with the capacity that is still available? d. What total contribution margin would the company earn if it followed the production plan described in requirements 7b and 7c? Complete this question by entering your answers in the tabs below. Req 7A Req 7B Req 7c Req 7D If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produce with the capacity that is still available? It would be able to produce units with the capacity that is still available. 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 B100 to $1,400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8a? 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. Req 8A Req 8B Req 8C How many units of each product is the company producing to earn the contribution margin from requirement 8a? Product A200 units Product B100 units 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 B100 to $1,400? b. How many units of each product is the company producing to earn the contribution margin from requirement 8a? 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. Req 8A Req 8B Req 8C Is your answer to requirement 8a greater than, less than, or equal to your answer from requirement 7d? The answer from requirement sa is the answer from requirement 7d. 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 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: A200 Fabrication Molding Machining Assemble & Pack B100 (hours per unit) 1 2 2 0 (hours per unit) 2 2 0 3 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. Click here for a brief tutorial on SOLVER in Excel. Click here for a a brief tutorial on Charts in Excel. Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 3 4,500 $ $ 1,200 $ 700 $ 2,100 1,200 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ 500 $ 900 O B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ Unused 4,000 $ $ Fabrication Molding Machining Assemble & Pack 6,000 5,000 4,500 Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 0 3 4,500 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ $ $ 1,200 $ 700 $ 500 $ 2,100 1,200 900 B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ $ Unused 4,000 Fabrication $ Molding Machining Assemble & Pack 6,000 5,000 4,500 A B D E F G H 1 Fabrication 2 3 4 A200 Units Molding Machinery 0 Assemble & Pack 0 0 0 500 1000 5 6 1500 7 2000 B100 Units 8 0 2500 2500 9 0 10 3000 -3000 3500 11 12 13 4000 -2000 0 14 Conroy Company Volume Trade-Off Decisions with More Than One Constraint Contribution Margin Analysis B100 A200 B100 Max Units A200 Max Units Units sold Departmental Data: Hours Demanded per Unit and Capacity Available B100 A200 Capacity (Hours per Unit) (Hours per Unit) (in Hours) Fabrication 1 2 4,000 Molding 2 2 6,000 Machining 2 0 5,000 Assemble & Pack 0 3 4,500 $ $ 2,100 1,200 Selling price Variable expense per unit Contribution margin per unit Contribution margin per hour $ 700 $ (700) $ (350) $ $ 900 $ 300 B100 A200 Total Departmental Data: Used vs. Unused Capacity (in Hours) B100 A200 Used Sales Variable expenses Contribution margin $ $ $ $ $ $ $ $ Unused 4,000 6,000 $ Fabrication Molding Machining Assemble & Pack 5,000 4,500 5. In the Excel template, navigate to the Requirement 5 tab. Using Charts, prepare a graph using the following three guidelines. First, label the X-axis B100: Units Produced and the Y-axis A200: Units Produced. Then establish a range on each axis of 0 4,000 units. Second, plot four lines that depict the production possibilities within each of the four departments. For example, the Fabrication Department could produce a maximum of 4,000 units of B100 (= 4,000 hours available = 1 hour per unit) or a maximum of 2,000 units of A200 (= 4,000 hours available = 2 hours per unit). Thus, this line should connect two data points, 4,000 units on the X-axis and 2,000 units on the Y-axis. Third, once you have plotted all four lines, pay particular attention to the portion of the graph that is enclosed by the four lines because it depicts the full range of Conroy's production possibilities. Based on the graph that you have created, which of the following statements are true? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) ? Producing 1,000 units of A200 and 1,500 units of B100 is outside the range of production possibilities. ? Producing 1,500 units of A200 and 1,500 units of B100 is outside the range of production possibilities. ? Producing 500 units of A200 and 2,500 units of B100 is within the range of production possibilities. ? Producing 2,000 units of A200 and 1,500 units of B100 is within the range of production possibilities. 7. In the Excel template, navigate to the Requirement 7 tab. Assume that Conroy is considering raising the price of B100 to $1,400. The company believes that the price increase would drop maximum customer demand from 2,800 units to 2,600 units. a. If Conroy implements the price increase, which product would have the highest contribution margin per unit of its constraining resource? b. If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? c. If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produc with the capacity that is still available? d. What total contribution margin would the company earn if it followed the production plan described in requirements 7b and 7c? Complete this question by entering your answers in the tabs below. Req 7A Req ZB Req 7c Req ZD If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? It would be able to produce units. 7. In the Excel template, navigate to the Requirement 7 tab. Assume that Conroy is considering raising the price of B100 to $1,400. The company believes that the price increase would drop maximum customer demand from 2,800 units to 2,600 units. a. If Conroy implements the price increase, which product would have the highest contribution margin per unit of its constraining resource? b. If the company decided to initiate production by maximizing the output of the product chosen in requirement 7a, then how many units of this product would it be able to produce before encountering that product's constraint? c. If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produce with the capacity that is still available? d. What total contribution margin would the company earn if it followed the production plan described in requirements 7b and 7c? Complete this question by entering your answers in the tabs below. Req 7A Req 7B Req 7c Req 7D If the company implemented the production plan in requirement 7b, then how many units of its remaining product could it produce with the capacity that is still available? It would be able to produce units with the capacity that is still available.