Textbook page 1137
Fill out:
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 S300,000 authorized, 3,000 issued, and outstanding 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 Discontinued operations, net of tax effect Net Income S487,000 (36,500) S450,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 carnings 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. Basic and diluted EPS data should be reported on the face of the income statement for all reporting periods presented in the comparative statements. Businesses without potential common shares present basic EPS only. Disclosure notes should provide additional Page 1137 disclosures including the following: 1. A reconciliation of the numerator and denominator used in the basic EPS computations to the numerator and the denominator used in the diluted EPS computations. An example of this is presented in Illustration 19-19 using the situation described previously in Mustration 19-12. 2. Any adjustments to the numerator for preferred dividends. 3. Any potential common shares that weren't included because they were antidilutive. 4. Any transactions that occurred after the end of the most recent period that would materially affect earnings per share. Illustration 1919 Reconciliation of Basic EPS Computations to Diluted EPS Computations Earnings per Share Reconciliation Share Income (Numerator) Per Share Amount (Denominator) ($ in millions) Net income Preferred dividends $ 154 Basic earnings per share 150 75 $2.00 None 3* Stock options Convertible debt Convertible preferred stock 18 12 4 3 Diluted earnings per share $ 172 93 $1.85 Note: Stock warrants to purchase an additional 4 million shares at $32.50 per share were outstanding throughout the year but were not included in diluted EPS because the warrants' exercise price is greater than the average market price of the common shares. *15 million -(15 million $20) = $25] = 3 million net additional shares. Additional Consideration It's possible that potential common shares would have a dilutive effect on one component of net income but an antidilutive effect on another. When the inclusion of the potential common shares has a dilutive effect on "income from continuing operations," the effect should be included in all calculations of diluted EPS. In other words, the same number of potential common shares used in computing the diluted per-share amount for income from continuing operations is used in computing all other diluted per-share amounts, even when amounts are antidilutive to the individual per-share amounts. A B D E F 1 2 3 A) EPS weighted average common shares outstanding 4 B) diluted earings per share for 2020 weighted-average common shares outstanding = 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 EPSCal FinSt + A O F G H B E F G 1 summarize the basic and diluted earnings per share for 2020 that Englewood must report on the income statement for 2020 2 3 4 5 6 7 8 9 10 prepare the required note disclosures including the earnings per share reconciliation 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 EPSCal FinSt +
