Needs to show how it is calculated for excel for example tot

Needs to show how it is calculated for excel for example total sale = b4/b2 or else will not be correct

BREAKEVEN POINT AND SELLING PRICE Parks Castings Inc. will manufacture and sell 200,000 units next year, Fixed costs will total S300,000, and variable costs will be 60 percent of a·The firm wants to achieve a level of earnings before interest and taxes of S250,000, what selling sales. is necessary to achieve this resul? b. Set up an analytical income statement to verify your solution to part (a). DATA Total units Fixed costs Variable costs 1. Start Excel. Download and open the workbook named: Keown Martin 2In cell B14, calculate the level of total sales. (1 point) 3. In cell B15, calculate the average selling price.(point) 4. In cell B19, calculate the sales amount. (1 point) 5. In cell B20, calculate the total variable costs. ( point) Note: The outpu 6. In cell B2.ealculate the revenue before fixed costs. (I point) 7. In cell B22, ealculate the sotal fixed costs amount. (1 poimt) Note: The 200,000 S300,000 60% a. The firm wants to achieve a level of carmings before interest and taxes of S250,000. What selling price per unit is necessary to achieve this resul? 8. In cell B23, calculate the EBIT.( poin 9, Save the workbook. Close the woekbook and then exit Excel Submit th $250,000 EBIT Total sales Average selling price b. Set up an analytical income statement to verify your solution to part (a). Sales Total variable costs Revenue before fixed costs Total fixed costs EBIT

Solution

a. Contribution ratio = 100 - 60 = 40%
Required contribution to earn 250000 before interest and taxes = 250000 + 300000 = $550,000
Sales required to achieve this contribution = 550000 / 40% = $1,375,000
Selling Price = 1375000 / 200000 = $6.875 per unit

Total Sales = $1,375,000
Average Selling Price = $6.875

b.

Income Statement
	$
Sales (200000*6.875)	1,375,000
Total Variable Cost	825,000
Revenue before fixed cost	550,000
Total fixed costs	300,000
EBIT	250,000