1. Financial Data for four automotive parts manufacturers is given below:

	XYZ, Inc.	Delphi Automotive PLC.	Dragon Auto parts Inc.	Pegasus Industries Inc.
Selling Price	$70	$90	$120	$100
Unit Sales	1,200,000	900,000	700,000	1,000,000
Times Interest Earned	8	9	10	7
Variable Costs (% of Sales)	57%	55%	53%	60%
Fixed Costs	$9,000,000	$12,000,000	$10,000,000	$11,000,000
Return on Common Equity	11%	10%	9%	8%
Common Equity	$120,000,000	110,000,000	150,000,000	$140,000,000
Common Shares	13,000,000	12,000,000	15,000,000	14,000,000

1. Using the financial data given in the above table, create income statements for each firm. Assume a common tax rate of 40% for each company.
2. Determine the break-even points in both units and dollars, and the degrees of operating, financial, and combined leverage for each firm.
3. How many units would each company need to sell in order to achieve EBIT of $15M?
4. Create a Line chart that shows the various leverage measures for each company.

I have the answers but I want the Excel formulas of each step:

A B D E F G a: Using the financial data given in the above table, create income statements for each firm. Assume a common tax rate of 40% for each company. 1 2 XYZ, Inc. DelphAutomotive PLC DragonAuto parts Inc. Pegasusindustries Inc. 4 Sales 84000000 81000000 84000000 100000000 5 Less: Variable Costs 47880000 44550000 44520000 60000000 6 Contribution (e) 36120000 36450000 39480000 40000000 7 Less: Fixed Costs 9000000 12000000 10000000 11000000 8 EBIT 27120000 24450000 29480000 29000000 9 Less: Interest Expense 3390000 2716667 2948000 4142857 10 EBT 23730000 21733333 26532000 24857143 11 Taxes 9492000 8693333 10612800 9942857 12 Net Income (a) 14238000 13040000 15919200 14914286 13 Common Shares (b) 13000000 12000000 15000000 14000000 14 Contribution per unit (e)/ no. of units 30.1 56.4 40 40.5 0.45 15 Contribution % 0.43 0.47 0.4 16 17 Net Income available to Common 13200000 11000000 13500000 11200000 18 Preferred Dividends 1038000 2040000 2419200 3714286 19 EPS (a/b) 1.095 1.087 1.061 1.065 20 3