Excel #5
Elimination of a Segment
Spring 2009
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 quarters results has indicated that something
needs to be done. He believes that PRODUCT B 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 Product B Product C Total
Sales $680,000 $890,000 $790,000 $2,360,000
- Variable Costs $390,000 $695,000 $585,000 $1,670,000
Contribution Margin $290,000 $195,000 $205,000 $690,000
Segment Fixed Costs $170,000 $130,000 $110,000 $410,000
Allocated Corporate Fixed Costs $90,000 $90,000 $90,000 $270,000
Pre-tax Profit $30,000 ($25,000) $5,000 $10,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 B and Use that capacity to increase Product A Sales by 40%
5. Eliminate Product B and Use that capacity to increase Product A Sales by 15%
and Product C Sales by 20%.
6. Given the options in parts 1-5, which provides the company with the highest total
profit?
Save File as:
LAST NAME FIRST NAME CLASS TIME EXCEL 5
Rules regarding the completion of the Excel Assignments:
1) You MUST use the Excel Templates provided on the ACCT 102 website for this
quarter.
2) You MUST sign the academic honesty pledge or you will automatically receive a
zero, NO EXCEPTIONS!
3) You MUST use formulas and links whenever possible. If you simply type in your
solutions rather than use formulas, you will receive a zero.
© Copyright of Michael J. Meyer 2009. Use by permission only.
