AC 313 Intermediate Accounting III - Spring 2022 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 company reported the following results from operations for the year ended December 31, 2021. Englewood is subject to a 40% tax rate. Summary Income Statement Income from Continuing Operations, after tax Discontinued operations, net of tax effect Net Income $1,000,000 (430,000) $570,000 At the beginning of the year, January 1, 2021, the following shareholders' equity portion of the balance sheet related to Englewood Enterprises was reported. Shareholders' Equity Section Convertible 4% preferred stock - $35 par value; 4,000 shares authorized, issued, and outstanding $140,000 Common Stock - $1par value; 500,000 shares authorized, 150,000 shares issued and outstanding 150,000 Additional paid-in capital - Preferred Stock 160,000 Additional paid-in capital - Common Stock 280,000 Retained Earning 245,000 Total Shareholders' Equity Section $975,000 The following common stock transaction occurred during 2021: Date and Event 6/1 New common shares issued 8/1 Two-for-one common stock split Number of Shares 24,000 174,000 AC 313 Intermediate Accounting III - Spring 2022 10/1 New common shares issued 12/1 Treasury stock 50,000 (45,000) Further, the company had the following stock options and convertible securities that represent potentially dilutive adjustment: The company granted 20,000 stock options on January 1 that allowed employees to acquire 20,000 common shares at $18 per share. The average market price of the company's common shares is $24 per share. There is a $500,000 of 6% convertible bonds outstanding as of the beginning of the year. Each $1,000 bond is currently convertible into 8 shares of common stock. To date, no bonds have been converted. 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. Requirements: 1. Create an Excel worksheet to display the computation for (a) basic earnings per share for 2021 including weighted-average common shares outstanding and (b) diluted earnings per share for 2021 including weighted-average common shares outstanding. Show all calculations and the earnings per incremental share effect for each potentially dilutive security. 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 2021 that Englewood must report on the income statement for 2021. 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. AC 313 Intermediate Accounting III - Spring 2022 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 company reported the following results from operations for the year ended December 31, 2021. Englewood is subject to a 40% tax rate. Summary Income Statement Income from Continuing Operations, after tax Discontinued operations, net of tax effect Net Income $1,000,000 (430,000) $570,000 At the beginning of the year, January 1, 2021, the following shareholders' equity portion of the balance sheet related to Englewood Enterprises was reported. Shareholders' Equity Section Convertible 4% preferred stock - $35 par value; 4,000 shares authorized, issued, and outstanding $140,000 Common Stock - $1par value; 500,000 shares authorized, 150,000 shares issued and outstanding 150,000 Additional paid-in capital - Preferred Stock 160,000 Additional paid-in capital - Common Stock 280,000 Retained Earning 245,000 Total Shareholders' Equity Section $975,000 The following common stock transaction occurred during 2021: Date and Event 6/1 New common shares issued 8/1 Two-for-one common stock split Number of Shares 24,000 174,000 AC 313 Intermediate Accounting III - Spring 2022 10/1 New common shares issued 12/1 Treasury stock 50,000 (45,000) Further, the company had the following stock options and convertible securities that represent potentially dilutive adjustment: The company granted 20,000 stock options on January 1 that allowed employees to acquire 20,000 common shares at $18 per share. The average market price of the company's common shares is $24 per share. There is a $500,000 of 6% convertible bonds outstanding as of the beginning of the year. Each $1,000 bond is currently convertible into 8 shares of common stock. To date, no bonds have been converted. 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. Requirements: 1. Create an Excel worksheet to display the computation for (a) basic earnings per share for 2021 including weighted-average common shares outstanding and (b) diluted earnings per share for 2021 including weighted-average common shares outstanding. Show all calculations and the earnings per incremental share effect for each potentially dilutive security. 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 2021 that Englewood must report on the income statement for 2021. 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