Excel Project II Calculation and Presentation of EPS Learning Objectives 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. AC 313 Intermediate Accounting III Fall 2021 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 Englewoods common stock was $35 per share during 2020. Englewoods 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.