work needs to be done in Excel. thank you
Excel#5
Elimination of a Segment Summer 2023
Mike Company has three products,cach 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 quarters results has indicated that something needs to be done. He bclieves 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 $1,640,000
Product B $1,600,000
Product C $960,000
Total $4,200,000
Sales
-Variable Costs
$1,300,000
$1,000,000
$500,000
$2,800,000
Contribution Margin
$340,000
$600,000
$460.000
$1,400,000
Segment Fixed Costs Allocated Corporate Fixed Costs
$280.000 $160,000
$260.000 $160,000
$240,000 $160.000
$780.000 $480,000
Pre-tax Profit
$100,000
$180,000
$60.000
$140,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 70% 5.Eliminate Product A and Use that capacity to increase Product B Sales by 40% and Product C Sales by 35% 6. Given the options in parts 1-5,which provides the company with the highest total profit?
