Excel #5 Elimination of a Segment Summer 2020 Mike Company has three products, each are operated as separate segments. Mike Company allocates all of its corporate fixed costs to each of its operating segments. Mike Company's CEO, after looking at this past quarter's results has indicated that something needs to be done. He believes that PRODUCT A is losing money and has solicited your help in evaluating several options. Below are the results of the past quarter for each of the product segments and the Company Total. Product A $820,000 Product B $800,000 Product C $480,000 Total $2,100,000 Sales - Variable Costs $650,000 $500,000 $250,000 $1,400,000 Contribution Margin $170,000 $300,000 $230,000 $700,000 $140,000 $130,000 $390,000 Segment Fixed Costs Allocated Corporate Fixed Costs $120,000 $80,000 $80,000 $80,000 $240,000 Pre-tax Profit ($50,000) $90,000 $30,000 $70,000 REQUIRED: Determine the Company Total under each of the below, mutually exclusive options: 1. Eliminate Product A 2. Eliminate Product B 3. Eliminate Product C 4. Eliminate Product A and Use that capacity to increase Product B Sales by 60% 5. Eliminate Product A and Use that capacity to increase Product B Sales by 25% and Product C Sales by 35%. 6. Given the options in parts 1-5, which provides the company with the highest total profit? A B D E F 1 Product A $820,000 Product B $800,000 Product C $480,000 Total $2,100,000 2 Sales $650,000 $500,000 $250,000 $1,400,000 $170,000 $300,000 $230,000 $700,000 3 4 - Variable Costs 5 6 Contribution Margin 7 8 Segment Fixed Costs 9 Allocated Corporate Fixed Costs 10 11 Pre-tax Profit 12 13 14 15 16 17 $140,000 $80,000 $130,000 $80,000 $120,000 $80,000 $390,000 $240,000 ($50,000) $90,000 $30,000 $70,000 B C E G H - J K Eliminate A, Increase B Sales 60% Eliminate A, Increase B Sales 25% and C Sales 35% 1 Eliminate A Eliminate B Eliminate C MOODE 2 Sales 3 4 - Variable Costs 5 6 Contribution Margin 7 8 Segment Fixed Costs 9 Allocated Corporate Fixed Costs 10 11 Pre-tax Profit 12 13 14 15 Which provides the greatest profit? 16 17 18 19 20 21 22 23 24 25 10000 To answer this question, Link to one of the following phrases: Eliminate A Eliminate B Eliminate C Eliminate A and Increase B Sales 60% Eliminate A, Increase B Sales 25% and C Sales 35% Do Nothing and keep all three segments