Question

Use the information from the following Income Statement to create and Projected Income Statement and solve the questions at bottom of the page. Complete the Income State to reflect the expected sales increase, then also complete the grey cells at the bottom of the page. Complete the other two spreadsheets in this workbook and after doing all the calculations discuss the impact of variable versus fixed expenses. Include in your conclusion which company you think is in the better financial situation and why. Use 3-4 paragraphs for your total discussion. Income Statement Projected Income Statement Sales Revenue $1,550,000 Variable Costs Purchases $250,000 0.161290323 Direct labor $200,000 0.129032258 $450,000 $1,100,000 Fixed Costs Selling $250,000 Administrative $300,000 Manufacturing Overhead $350,000 $900,000 Profit Dollars Percentage Calculate the Contribution Margin Calculate the Gross Margin Ratio Calculate Breakeven Sales Calculate Margin of Safety based on the 30% expected sales increase. have to be in excel

Answer 1

		Dollars	Percentage
	Sales Revenue	$      1,550,000	100.00%
	Less: Variable Costs
	Purchases (0.161290323)	$         250,000	16.13%
	Direct labor (0.129032258)	$         200,000	12.90%
	Total Variable Costs	$         450,000	29.03%
a.	Contribution Margin	$      1,100,000	70.97%
	Less: Fixed Manufacturing Overhead	$         350,000	22.58%
b.	Gross Margin	$         750,000	48.39%
c.	Breakeven Sales = Total Fixed Cost / Contribution Margin Percentage
		900000/70.97%
		$                                 1,268,141
d.	Revised Sales (1550000*130%)	$                            2,015,000.00
	Margin of Safety = Actual Sales - Breakeven Sales
		2015000-1268141.47
		$                                    746,859
	Margin of Safety = (Revised Sales - Breakeven Sales)/(Revised Sales)
		(2015000-1268141.47)/2015000
		37.06%

Excel Workings

	Dollars	Percentage
Sales Revenue	1550000	=B2/$B$2
Less: Variable Costs
Purchases (0.161290323)	250000	=B4/$B$2
Direct labor (0.129032258)	200000	=B5/$B$2
Total Variable Costs	=SUM(B4:B5)	=B6/$B$2
Contribution Margin	=B2-B6	=B7/$B$2
Less: Fixed Manufacturing Overhead	350000	=B8/$B$2
Gross Margin	=B7-B8	=B9/$B$2
Breakeven Sales = Total Fixed Cost / Contribution Margin Percentage
	900000/70.97%
	=900000/70.97%
Revised Sales (1550000*130%)	=1550000*130%
Margin of Safety = Actual Sales - Breakeven Sales
	2015000-1268141.47
	=2015000-1268141.47
Margin of Safety = (Revised Sales - Breakeven Sales)/(Revised Sales)
	(2015000-1268141.47)/2015000
	=(2015000-1268141.47)/2015000