Question

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 company’s 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 president’s 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 company’s 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!			$    ?			$    ?

Answer 1

• All working forms part of the answer
• Solution is provided as asked.
• Amounts are in $

		Original Data		What if #1		What if #2		What if #3
			Units		Units		Units		Units
		Last Year	1,500	Proposed:	2100	Proposed:	1800	Proposed:	2377
Working Column		Total $	Per Unit $	Total $	Per Unit $	Total $	Per Unit $	Total $	Per Unit $
A	Sales	4500000	3000	6300000	3000	5400000	3000	7131000	3000
B	Less variable expenses	3150000	2100	2520000	1200	4050000	2250	5205630	2190
C=A-B	Contribution margin	1350000	900	3780000	1800	1350000	750	1925370	810
D	Less fixed expenses	900000		3150000		480000		925000
E=C-D	Net income	450000		630000		870000		1000370
F=C/A	Contribution Margin Ratio	30%		60%		25%		27%
G=D/F	Breakeven point in Dollars	3000000		5250000		1920000		3425926
H=G/sale price per unit	Breakeven Point in Units	1000		1750		640		1141.975
I=A-G	Margin of Safety	1500000		1050000		3480000		3705074
J=C/E	Operating Leverage	3		6		1.55172414		1.924658
	Increase (Decrease) in NOI after proposed changes:			$180000		$420000		$550370