Kemper Company's balance sheet and income statement are shown below (in millions of dollars). The company and its creditors have agreed upon a voluntary reorganization plan. In this plan, each share of the $5 preferred will be exchanged for one share of $1.00 preferred with a par value of $25 plus one 9% subordinated income debenture with a par value of $75. The $9 preferred issue will be retired with cash. The company's tax rate is 21 percent.

Balance Sheet prior to Reorganization (in millions
Current Assets	400		Current liabilities	350
Net fixed assets	450		Advance payments	20
			$5 preferred stock, $100 par value (1,000,000) shares	100
			$9 preferred stock, no par, callable at 100 (300,000 shares)	30
			Common stock, $1.00 par value (50,000,000) shares	50
			Retained earnings	300
Total assets	850		Total claims	850

a. Construct the pro forma balance sheet after reorganization takes place. Show the new preferred at its par value.

b. Construct the pro forma income statement after reorganization takes place. How does the recapitalization affect net income available to common stockholders?

Income Statement (in millions)
Prior to Reorganization			After Reorganization
Net sales	900.0
Operating expense	725.0
Net operating income	175.0
Other income	7.0
EBT	182.0
Taxes	38.2	21%
Net income	143.8
Dividends on $5 PS	5.0
Dividends on $9 PS	1.4
Income to Common SHs	137.3
Increased income available to common SHs with reorganization:

c. Calculate the required pre-tax earnings to cover debt and preferred stock obligations, before and after the recapitalization?

d. Calculate the debt ratio before and after the reorganization?

e. Would the common stockholders be in favor of the reorganization? Explain your answer, providing at least 2 reasons for it.

I need help with all parts. Please show how to do in excel with formulas so I can do new problem on my own. Thank you in advance!