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

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?

***Please show how you achieved these answers using excel, thank you****