Reference no: EM131739969
Problem -
The marketing manager, Raj Mehta, picked up the phone to call Susan Ahmed, the company's cost accounting assistant and Raj's personal friend.
Mehta: Hi, Susan. Can you help me understand something that came up at a meeting earlier today? It was about break-even when we were discussing cost structure and operating leverage.
Ahmed: What's the problem?
Mehta: The president was talking about automating certain parts of the operations and was throwing out different alternatives.
Ahmed: What's new about that?
Mehta: Well, he was saying something about the changes to the cost structure as a result of that, and that our break-even points might change. As the marketing guy, I would like to better understand the implications of all these proposals for me. How will these potential changes affect my function in the company? Can we meet tomorrow afternoon about this? In the meantime, I will provide you with enough details to compute some numbers for me.
Ahmed: Sounds good, Raj. Now you owe me one!
Ahmed worked with the following documents to conduct her analysis:
- The contribution margin income statement for the most recent year
- Rough notes regarding three new proposals being considered
The following are notes regarding the new proposals:
- Invest in automation in three areas of sales and marketing: (1) order entry, (2) sales administration, and (3) sales distribution. As a result of this proposal, the contribution margin ratio is expected to increase by 8% and annual fixed costs are expected to increase by $9,570,500.
- Invest in automation in three areas of the organization: (1) sales administration, (2) human resources and corporate office, and (3) manufacturing operations. This move is expected to increase the contribution margin ratio by 11% and simultaneously increase annual fixed costs by $11,280,000. The total cost savings will be divided equally between the manufacturing and nonmanufacturing functions.
- Invest in an ABC system and a new performance measurement system, which will increase the annual fixed costs by $1,800,000. This move will eliminate the need for a total of 33 accounting and administrative clerical staff at an average cost of $32,000 per year per person, and streamline operations.
Required:
Complete the Problem using Excel. Prepare each of the required questions on a new worksheet on your Excel file.
Assume the role of Susan Ahmed, and do the following for Raj Mehta:
1. Recreate the following contribution margin income statement with additional columns for per-unit amounts and cost percentages (except for fixed costs). Also, compute the break-even point and operating leverage for the year.
2. Prepare three new contribution margin income statements to reflect the three proposals (assuming there is no effect on sales). Ensure that your statements contain columns for per-unit amounts and percentages.
3. Describe how the three proposals will affect the break-even sales for the company. Which proposal will result in the highest increase in income from increases in sales (within the relevant range of operations)? Support your answer with computations.
CONTRIBUTION MARGIN INCOME STATEMENT
Sales Revenues (300,000 units) $78,000,000
Less: Variable costs
Variable cost of goods sold 35,100,000
Variable selling and administrative expenses 23,400,000
Contribution Margin $19,500,000
Less: Fixed Costs
Manufacturing 9,560,000
Selling and administrative 2,783,000
Net Income $7,157,000
Textbook: Introduction to Managerial Accounting Brewer,Garrison,Noreen,Kalagnanam,Vaidyanathan McGraw Hill Ryerson, ISBN-13: 978-1-25-910570-8 ISBN-10: 1-25-910570-9
Chapter 8, page 492