The Verbrugge Publishing Company's 2019 balance sheet and income statement are as follows (in Millions of dollars):

I need parts A-E answered. Please show how to do the problems in Excel with Formulas so I can do a new problem on my own. Thank you in advance!

Balance Sheet Current assets Net fixed assets $300 200 $ 40 80 110 Current liabilities Advance payments by customers Noncallable preferred stock, $6 coupon, $110 par value (1,000,000 shares) Callable preferred stock, $10 coupon, no par, $100 call price (200,000 shares) Common stock, $2 par value (5,000,000 shares) Retained earnings Total liabilities & equity 200 10 60 $500 Total assets $500 $540 Income Statement Net sales Operating expense Net operating income Other income EBT Taxes (25%) Net income Dividends on $6 preferred Dividends on $10 preferred Income available to common stockholders 516 $ 24 4 $ 28 7 $ 21 6 2 $ 13 Verbrugge and its creditors have agreed upon a voluntary reorganization plan. In this plan, each share of the noncallable preferred will be exchanged for 1 share of $2.40 preferred with a par value of $35 plus one 8% subordinated income debenture with a par value of $75. The callable preferred issue will be retired with cash generated by reducing current assets. a. Assume that the reorganization takes place and construct the projected balance. Show the new preferred stock at its par value. What is the value for total assets? For debt? For preferred stock? b. Construct the projected income statement. What is the income available to common shareholders in the proposed recapitalization? c. What were the total cash flows received by the noncallable preferred stockholders prior to the reorganization? What were the total cash flows to the original noncall- able preferred stockholders after the reorganization? What was the net income to common stockholders before the reorganization? After the reorganization. d. Required pre-tax earnings are defined as the amount that is just large enough to meet fixed charges (debenture interest and/or preferred dividends). What are the required pre-tax earnings before and after the recapitalization? e. How is the debt ratio (i.e., liabilities/total assets) affected by the reorganization? Suppose you treated preferred stock as debt and calculated the resulting debt ratios. How are these ratios affected? If you were a holder of Verbrugge's common stock, would you vote in favor of the reorganization? Why or why not? Balance Sheet Current assets Net fixed assets $300 200 $ 40 80 110 Current liabilities Advance payments by customers Noncallable preferred stock, $6 coupon, $110 par value (1,000,000 shares) Callable preferred stock, $10 coupon, no par, $100 call price (200,000 shares) Common stock, $2 par value (5,000,000 shares) Retained earnings Total liabilities & equity 200 10 60 $500 Total assets $500 $540 Income Statement Net sales Operating expense Net operating income Other income EBT Taxes (25%) Net income Dividends on $6 preferred Dividends on $10 preferred Income available to common stockholders 516 $ 24 4 $ 28 7 $ 21 6 2 $ 13 Verbrugge and its creditors have agreed upon a voluntary reorganization plan. In this plan, each share of the noncallable preferred will be exchanged for 1 share of $2.40 preferred with a par value of $35 plus one 8% subordinated income debenture with a par value of $75. The callable preferred issue will be retired with cash generated by reducing current assets. a. Assume that the reorganization takes place and construct the projected balance. Show the new preferred stock at its par value. What is the value for total assets? For debt? For preferred stock? b. Construct the projected income statement. What is the income available to common shareholders in the proposed recapitalization? c. What were the total cash flows received by the noncallable preferred stockholders prior to the reorganization? What were the total cash flows to the original noncall- able preferred stockholders after the reorganization? What was the net income to common stockholders before the reorganization? After the reorganization. d. Required pre-tax earnings are defined as the amount that is just large enough to meet fixed charges (debenture interest and/or preferred dividends). What are the required pre-tax earnings before and after the recapitalization? e. How is the debt ratio (i.e., liabilities/total assets) affected by the reorganization? Suppose you treated preferred stock as debt and calculated the resulting debt ratios. How are these ratios affected? If you were a holder of Verbrugge's common stock, would you vote in favor of the reorganization? Why or why not