Hello Company makes three different products. Due to the constraints of their manufacturing equipment and 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 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 Statement Product A Product B Product C Max Capacity Units 43,000 10.00 $ 3.00 $ 20,000 $ 5,000 8.00 $ 2.00 $ 2,000 50,000 Price per unit Variable expense per unit Total Fixed Costs 50.00 $ 15.00 $ 10,000 20.00 $ 40,000 $ Product Sales $ 40,000 $ 100,000 $ (30,000) 70,000 $ (10,000) 60,000 $ 430,000 $ 570,000 Variable Costs (129,000) $ (10,000) 30,000 $ (40,000) (10,000) (169,000) 401,000 301,000 $ (20,000) 281,000 Contribution Margin Fixed Costs (70,000) 331,000 Operating income (loss) Required Using the Data Table What-If 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 50,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 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 in 1,000-unit increments. The production level of Product C is the same each month at 2,000 units. SCENARIO 1 Product A Product B Product C Operating Income (Loss) 331,000 43,000 43,000 43,000 Units 43,000 $ 5,000 $ 2,000 $ 50,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

Related questions