Excel 2 Problem and Data CedarWorks manufactures playground equipment from Northern White Cedar wood which is free of all chemical additives and never splinters The current manufacturing process is heavily labor intensive, so the company is studying ways to improve profits given that it currently has a significant amount of unused capacity CedarWorks contribution margin income statement for the month of December 31, 2017 is given below Total Per Unit Sales $4,500,000 $3,000 Variable expenses 3,150,000 Contribution margin 1,350,000 Fixed expenses 900,000 Net operating income $ 450,000 What If 1 1 The company is studying the effect on its financial statements of purchasing some new equipment which would allow it to automate a large portion of its operations Since direct labor costs will decline, variable costs would decrease by $900 00 per unit However, total fixed costs would increase by $2,250,000 The volume of sales is expected to increase by 600 units if the new equipment is purchased If the company operates in an industry that is sensitive to changes in the economy, do you think CedarWorks should purchase the new equipment Explain What If 2 2 As an alternative, rather than purchasing the new equipment, the president is thinking about changing the companys marketing method Under the new method, the president is proposing that CedarWorks pay its sales people a 5 commission on sales and decrease the monthly fixed salary by $420,000 Paying the sales force commissions is also expected to increase sales volume by 20 (or 300 units) each month Do you agree with the presidents proposal Explain What If 3 3 Management is currently in contract negotiations with the labor union If the negotiations fail and the company does not buy the equipment (part 1) or change the companys marking method (part 2), direct labor costs will increase by 10 (or $90 per unit) and fixed costs will increase by $25,000 per month If these costs increase, how many units will the company have to sell to earn a profit of $1,000,000 Original Data What If 1 What If 2 What If 3 Units Units Units Units Last Year 1,500 Proposed Proposed Proposed Total Per Unit Total Per Unit Total Per Unit Total Per Unit Sales $4,500,000 3,000 00 $ $ $ $ $ $ Less variable expenses 3,150,000 2,100 00 Contribution margin 1,350,000 900 00 $ $ $ Less fixed expenses 900,000 Net income $450,000 $ $ $ Contribution Margin Ratio 30 0 0 0 Breakeven point in Dollars $3,000,000 $ $ $ Breakeven Point in Units 1,000 $1,500,000 $ $ $ Margin of Safety Operating Leverage $3 0 0 0 1 0 1 Increase (Decrease) in NOI after proposed changes VALUE $ $

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 26, 2024

Excel #2 Problem and Data CedarWorks manufactures playground equipment from Northern White Cedar wood which is free of all chemical additives and never splinters. The

Excel #2 Problem and Data CedarWorks manufactures playground equipment from Northern White Cedar wood which is free of all chemical additives and never splinters. The current manufacturing process is heavily labor intensive, so the company is studying ways to improve profits given that it currently has a significant amount of unused capacity. CedarWorks contribution margin income statement for the month of December 31, 2017 is given below: Total Per Unit Sales $4,500,000 $3,000 Variable expenses 3,150,000 Contribution margin 1,350,000 Fixed expenses 900,000 Net operating income $ 450,000 What If #1: 1. The company is studying the effect on its financial statements of purchasing some new equipment which would allow it to automate a large portion of its operations. Since direct labor costs will decline, variable costs would decrease by $900.00 per unit. However, total fixed costs would increase by $2,250,000. The volume of sales is expected to increase by 600 units if the new equipment is purchased. If the company operates in an industry that is sensitive to changes in the economy, do you think CedarWorks should purchase the new equipment. Explain. What If #2: 2. As an alternative, rather than purchasing the new equipment, the president is thinking about changing the companys marketing method. Under the new method, the president is proposing that CedarWorks pay its sales people a 5% commission on sales and decrease the monthly fixed salary by $420,000. Paying the sales force commissions is also expected to increase sales volume by 20% (or 300 units) each month. Do you agree with the presidents proposal? Explain. What If #3: 3. Management is currently in contract negotiations with the labor union. If the negotiations fail and the company does not buy the equipment (part 1) or change the companys marking method (part 2), direct labor costs will increase by 10% (or $90 per unit) and fixed costs will increase by $25,000 per month. If these costs increase, how many units will the company have to sell to earn a profit of $1,000,000.	Original Data		What If #1		What If #2		What If #3
		Units		Units		Units		Units
	Last Year	1,500	Proposed:	?	Proposed:	?	Proposed:	?
	Total	Per Unit	Total	Per Unit	Total	Per Unit	Total	Per Unit
Sales	$4,500,000	3,000.00	$ ?	$ ?	$ ?	$ ?	$ ?	$ ?
Less variable expenses	3,150,000	2,100.00	?	?	?	?	?	?
Contribution margin	1,350,000	900.00	?	$ ?	?	$ ?	?	$ ?
Less fixed expenses	900,000		?		?		?
Net income	$450,000		$ ?		$ ?		$ ?

Contribution Margin Ratio	30%		? 0%		? 0%		? 0%

Breakeven point in Dollars	$3,000,000		$ ?		$ ?		$ ?

Breakeven Point in Units	1,000		?		?		?
	$1,500,000		$ ?		$ ?		$ ?
Margin of Safety	$1,500,000		$ ?		$ ?		$ ?

Operating Leverage	$3		? 0.0		? 0.1		? 0.1

Increase (Decrease) in NOI after proposed changes:			#VALUE!		$ ?		$ ?