INTERMEDIATE PROBLEM 2 - Show all in excel with formulas

The Verbrugge Publishing Companys 2016 balance sheet and income statement are as follows (in millions of dollars):

Balance Sheet

Balance Sheet Current assets $168

Net fixed assets 153

Goodwill 15

Total assets $336

Current liabilities $ 42

Advance payments 78

Reserves 6

$6 preferred stock, $112.50 par value 135 (1,200,000 shares)

$10.50 preferred stock, no par, callable 9 at $150 (60,000 shares)

Common stock, $1.50 par value 9 (6,000,000 shares)

Retained earnings 57

Total claims $336

Income Statement

Net sales $540

Operating expense $516

Net operating income $24

Other income 3.0

EBT $27

Taxes (50%) 13.5

Net income $13.5

Dividends on $6 preferred 7.2

Dividends on $10.50 preferred 0.6

Income available to common stockholders $5.7

Verbrugge and its creditors have agreed upon a voluntary reorganization plan. In this plan, each share of the $6 preferred will be exchanged for one share of $2.40 preferred with a par value of $37.50 plus one 8% subordinated income debenture with a par value of $75. The $10.50 preferred issue will be retired with cash.

a. Construct the projected balance sheet while assuming that reorganization takes place. Show the new preferred stock at its par value.

b. Construct the projected income statement. What is the income available to common shareholders in the proposed recapitalization?

c. Required earnings is defined as the amount that is just enough to meet fixed charges (debenture interest and/or preferred dividends). What are the required pre-tax earnings before and after the recapitalization?

d. How is the debt ratio affected by the reorganization? If you were a holder of Verbrugges common stock, would you vote in favor of the reorganization? Why or why not?