Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine thenumber of units for each product that should be produced (to the nearest thousand) to make each product line profitable.OperatingIncome (Loss)SCENARIO 2Product AProduct BProduct CUnits???Based on your calculations above, which scenario creates the highest overall Operating Income for the company? Hello Company makes three different products. Due to the constraints of their manufacturing equipment and warehouse facility, thecompany 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 C is 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 month for each product.Income StatementProduct AProduct BProduct CМax Cарacity5,0008.00 $2.00 $Units43,00010.00 $3.00 $20,000 $2,00050,000Price per unitVariable expense per unit$$$50.0015.00 $20.00Total Fixed Costs40,000 $10,000Product Sales$430,000 $40,000 $100,000 $570,000(169,000)401,000(70,000)331,000Variable Costs(129,000)(10,000)30,000 $(30,000)70,000 $Contribution Margin$301,000 $Fixed Costs(20,000)281,000(40,000)(10,000)(10,000)60,000 $Operating income (loss)RequiredUsing the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product basedon the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 50,000 units.)Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysisusing a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units.Product B can vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in1,000-unit increments. The production level of Product C is the same each month at 2,000 units.SCENARIO 1Product AProduct BProduct COperatingIncome (Loss)331,00043,00043,000Units43,000 $5,000 $2,000 $50,000 $43,000%24

Particulars Product A Product B Product C Selling Price Per Unit 10 8 50 Less: Variable Cost -3 -2 -15 Contribution 7 6 35 In the above-mentioned question, it is stated that the production of Product A and Product B varies each month whereas, the production of Product C is fixed as it is a special order for a customer. The maximum number of units that can be produced, stored, and sold is 50,000 units.In order to find the highest profitable product mix, the product with the highest contribution per unit will be given priority. As the production of Product C is fixed due to special order, it will be given the first priority followed by Product A as its contribution is higher than Product B and at last, Product B will be produced. This analysis is to be done due to the major constraint of maximum production, storage, and selling capacity of the company. It is mentioned that a company can maximum produce, store, and sell 50,000 units. Tabular Presentation: Products Units Contribution per unit Contribution in $ Fixed Cost Operating profit Product C 2000 35 70000 -10,000 60,000 Product A 45000 7 315000 -20,000 2,95,000 Product B 3,000 6 18000 -40,000 -22,000 Total 50,000 4,03,000 -70,000 3,33,000 In this scenario, Pete wants each product line in the mix Profitable. As we have analyzed in scenario 1, Product line B is not profitable due to the highest fixed cost, We need to find the break-even unit in order to make each product line profitable. Break-even is the point where no profit or loss is there which means, the cost will be equal to the revenue. Break-even units=Total Fixed Cost of Each Product lineCotribution per unit of each product line Product A=20,0007=2857.14 Thus in multiple of 1000 units will be 3000 units of Product A Product B=40,0006=6666.66 Thus, in multiple of 1000 units will be 7000 units of Product B Calculation of each product-line in the mix profitable: Products Units Contribution per unit Contribution in $ Fixed Cost Operating profit Product C 2000 35 70000 -10,000 60,000 Product B 7000 6 42000 -40,000 2,000 Product A 41,000 7 287000 -20,000 2,67,000 Total 50,000 399000 -70000 329000