Hello Company makes three different products. Due to the constraints of their manufacturing equipment and 4 warehouse facility, the company is only able to produce, store, and sell a total of 50,000 units each month. The production of Products A and B varies each month; however, Product Cis a special order for one customer who purchases the same number of units every month. Pete Davila, the CEO, has provided the following data from last 7 8 10 Income Statement Units Price per unit Variable expense per unit Total Fixed Costs Product A Product B Product C Max Capacity 43,000 5,000 2,000 50,000 $ 10.00 $ 8.00 $ 50.00 $ 3.00 $ 2.00 $ 15.00 $ 20,000 $ 40,000 $ 10,000 11 12 14 15 Product Sales Variable Costs Contribution Margin Fixed Costs Operating income (loss) $ 430,000 $ (129,000) 301,000 $ (20,000) 281,000 40,000 $ (10,000) 30,000 $ (40,000) (10,000 100,000 $570,000 (30,000) (169,000) 70,000 $401,000 (10,000) (70,000) 60,000 $331,000 17 13 19 20 21 Required 22 Using the Data Table What-lf Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based 4 on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 50,000 units.) 25 x Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis 7 using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. 8 Product Bcan vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in 4 1,000-unit increments. The production level of Product Cis the same each month at 2,000 units. 30 Units 31 32 SCENARIO 1 Product A Product B Product C ? ? ? Operating Income (Loss) ? ? ? $ 34 35 7 Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine the * number of units for each product that should be produced to the nearest thousand) to make each product line profitable. Units =10 21 E2 SCENARIO 2 Product A Product B Product C ? ? Operating Income (Loss) ? ? ? $ ? 25 16 27 Based on your calculations above, which scenario creates the highest overall Operating Income for the company? | SE =9 50 On the Data Tables - Student tab in your Excel spreadsheet, update the given information section of the Income Statement for Hello Company with the data below: Max Capacity 43, see Income Statement Units Price per unit Variable expense per unit Total Fixed Costs Product A 36,898 $ 11.08 $ 4.99 $20,899 Product B 4,800 $ 7.00 $ 1.ee $49, eee Product C 3,000 $ 60.00 $ 20.00 $10,000 Required: 2. Using the Data Table What-lf Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 43.000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis using a two variable data table. Product A can vary between 33,000 units and a maximum of 38.000 units. Product B can vary between 2.000 units and a maximum of 7.000 units. Both Products A and B are manufactured in 1.000-unit increments. The production level of Product Cis the same each month at 3.000 units. SCENARIO 1 Units Operating Income (Loss) Product A Product B Product C $ 0 Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table and then determine the number of units for each product that should be produced to the nearest thousand) to make each product line profitable. Units SCENARIO 2 Product A Operating Income (Loss) Product B Product C 0 $ 3. Based on your calculations above, which scenario creates the highest overall Operating Income for the company? O Scenario 1 O Scenario 2