1. Create Excel worksheet

2. Use appropriate formulas to calculate required values

3. Apply cell referencing in worksheet

4. Apply appropriate numeric format in worksheet

5. Present results in financial statement format including required disclosure

Background

Englewood Enterprises is a publicly traded company with a complex capital structure. The following shareholders’ equity portion of the balance and summary income statements relate to Englewood Enterprises on December 31, 2020.

Shareholders’ Equity Section

Convertible 8% preferred stock - $100 par value; 5,000 shares authorized, 3,000 issued, and outstanding	$300,000
Common Stock - $10 par value; 75,000 shares authorized, 20,000 shares issued, 17,500 shares outstanding	200,000
Additional paid-in capital – Preferred Stock	120,000
Additional paid-in capital – Common Stock	280,000
Retained Earning	245,000
Treasury stock (2,500 common shares at $31)	(77,500)

Total Shareholders’ Equity Section

$1,067,500

Summary Income Statement

Income from Continuing Operations, after tax	$487,000
Discontinued operations, net of tax effect	(36,500)
Net Income	$450,500

The following information is available:

• January 1, 2019, the company issued 10%, $200,000 bonds at face value. Each $1,000 bond is currently convertible into 40 shares of common stock. To date, no bonds have been converted.

• Balance, January 1, 2020 – Common stock outstanding 13,500 shares. On September 1, 2020, Englewood sold 4,000 additional shares of common stock.

• Each share of preferred stock is convertible into 2.5 shares of common stock. To date, no preferred stock has been converted and no additional shares of preferred stock have been issued.

• During 2020, Englewood paid required dividends on its preferred stock.

• Unexercised compensatory share options to purchase 10,000 shares of common stock at $22 per share were outstanding at the beginning and ending of 2020. The average market price of Englewood’s common stock was $35 per share during 2020.

• Englewood’s effective tax rate is 25%.

Requirements:

1. Create an Excel worksheet to display the computation for (a) basic earnings per share for 2020 including weighted-average common shares outstanding and (b) diluted earnings per share for 2020 including weighted-average common shares outstanding. Show all calculations. Format earnings per share using accounting format with 2 decimal places. Label the worksheet ‘EPSCal’.

2. Add a new worksheet and based on the results of requirement 1, summarize the basic and diluted earnings per share for 2020 that Englewood must report on the income statement for 2020. Label the worksheet ‘FinSt’.

3. On the worksheet ‘FinSt’ prepare the required note disclosures including the earnings per share reconciliation (see page 1137 in textbook).

4. The worksheets should be formatted with appropriate numeric values using accounting format with zero decimals unless indicated otherwise, proper titles, and in a professional style.

5. Use appropriate Excel formulas to perform calculations and apply cell referencing where appropriate.

All the information needed is provided.