Elimination of a Segment

Excel #5

Elimination of a Segment

Fall 2012

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 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 / Product B / Product C / Total
Sales / $900,000 / $875,000 / $680,000 / $2,455,000
- Variable Costs / $750,000 / $600,000 / $390,000 / $1,740,000
Contribution Margin / $150,000 / $275,000 / $290,000 / $715,000
Segment Fixed Costs / $140,000 / $130,000 / $120,000 / $390,000
Allocated Corporate Fixed Costs / $80,000 / $80,000 / $80,000 / $240,000
Pre-tax Profit / ($70,000) / $65,000 / $90,000 / $85,000

REQUIRED: Determine the Company Total under each of the below, mutually exclusive options:

Eliminate Product A
Eliminate Product B
Eliminate Product C
Eliminate Product A and Use that capacity to increase Product BSales by 60%
Eliminate Product A and Use that capacity to increase Product BSales by 25% and Product C Sales by 35%.
Given the options in parts 1-5, which provides the company with the highest total profit?

Rules regarding the completion of the Excel Assignments:

NOTE: You MUST use Microsoft EXCEL and not any other spreadsheet program.

1)You MUST use the Excel Templates provided on the Accounting 102 website for this quarter.

2)You MUST download the Excel Templates using YOUR account from the Accounting 102 website. Each template is individually coded to prevent academic dishonesty. Any student found using the template from another student’s account AND the student that allows another student to use his/her account will also receive a zero on the assignment and be subject to further penalties for academic dishonesty.

3)You MUST sign the academic honesty pledge or you will automatically receive a zero--NO EXCEPTIONS!

4)You MUST use formulas and links whenever possible. If you simply type in your solutions rather than use formulas, you will receive a zero.

5)You MUST name your Excel files per the instruction sheet (LastNameFirstNameExcel5). Failure to name your Excel file correctly will result in a zero--NO EXCEPTIONS. Please make sure that there are no blank spaces in the file name (e.g., SmithJohnExcel5).

6)Your Instructor will inform you how to send your assignments electronically. Failure to follow YOUR INSTRUCTOR’S INSTRUCTIONS will result in a grade of zero for the assignment (note that each instructor may have his/her own required method of turning in the assignment).

7)To receive credit, your instructor must RECEIVE your assignment by the due date no later than 11pm without exception.

8)If you decide to work in a group for Excel assignments #4-#5 (a group is mandatory for assignment #3), you MUST work with individuals IN YOUR CLASS. YOU MAY NOT WORK WITH SOMEONE IN ANOTHER SECTION (even if you both have the same instructor)!