K14 Jx A B D E 1 BA212 Chapter 14 Excel Problem 2 3 Instructions: Read the Problem Data and complete the calculations section. Enter 4 formulas in the yellow cells. When you are finished, "Save As" your workbook an 5 filename. Upload your file to the dropbox located in the Chapter module on Blackb 6 7 For Excel help, click on the ? symbol in the upper right corner of your screen. 6 00 10 Problem Data 11 12 The balance sheet for XYZ Co. at the end of the current fiscal year indicated the following: 13 14 Bonds Payable, 7% $ 4,800,000 15 Preferred 3% stock, $45 par 3,100,000 16 Common Stock, $1 par 4,500,000 17 18 Other information includes: 19 20 Net Income before tax 3,480,000 21 Income tax expense 1,050,000 22 Cash dividends paid on common Stock 1,050,000 23 Market price for common stock, per share 37 24 25 26 Calculations 27 28 Face Value of Bonds enter data in blue cells 29 Annual Interest rate 30 Annual Interest Expense enter formulas in yellow cells 31 BA212 Ch 14 Problem Ready K15 x & fe B C F A 25 26 Calculations 27 enter data in blue cells 28 Face Value of Bonds 29 Annual Interest rate 30 Annual Interest Expense 31 enter formulas in yellow cells 32 Total Value of Preferred Stock 33 par value 34 Number of Preferred shares 35 Annual total Preferred dividend 36 37 Total Value of Common Stock 38 par value 39 Number of Common shares 40 41 42 1. Compute the number of times bond interest charges are earned: enter formulas in yellow 43 44 2. Compute the number of times preferred dividends are earned: 45 46 3. Compute the earnings per share on common stock: 47 48 4. Compute the Price-Earnings ratio: 49 50 5. Compute the dividends per share of common stock: 51 BA212 Ch 14 Problem Ready nual Interest Expense enter formulas in yellow cells tal Value of Preferred Stock ar value Sumber of Preferred shares Annual total Preferred dividend Total Value of Common Stock par value Number of Common shares 1 enter formulas in yellow cells 2 1. Compute the number of times bond interest charges are earned: 43 44 2. Compute the number of times preferred dividends are earned: 45 46 3. Compute the earnings per share on common stock: 47 48 4. Compute the Price-Earnings ratio: 49 SO 5. Compute the dividends per share of common stock: 57 52 6. Compute the dividend yield: BA212 Ch 14 Problem seady