115 fx A B C 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. 8 9 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 S 37 24 25 26 Calculations 27 28 Face Value of Bonds S 4,800,000 enter dota in bluve cet 7%6 29 Annual interest rate BA212 Ch 14 Problem Ready Calculations Face Value of Bonds $ 4,800,000 enter data in blue cells e Annual Interest rate 7% $ 336,000 enter formulas in yellow cells 0 Annual Interest Expense -1 32 Total Value of Preferred Stock 3,100,000 $ $ 33 par value 45 34 Number of Preferred shares 68,889 35 Annual total Preferred dividend 36 37 Total Value of Common Stock $ 4,500,000 38 par value $ 1 4,500,000 39 Number of Common shares 40 41 11.36 enter 42 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 50.5 Comite the dividende ner share of common stock BA212 Ch 14 Problem Ready e BIU... A ==Merge & Center Format Painter $ - % Clipboard Font Alignment Numbe 20 fx A B D E F 1. Compute the number of times bond interest charges are earned: 11.36 enter form 2. Compute the number of times preferred dividends are earned: . 3. Compute the earnings per share on common stock: 7 8 4. Compute the Price-Earnings ratio: 9 0 5. Compute the dividends per share of common stock: 51 52 6. Compute the dividend yield: 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 BA212 Ch 14 Problem Ready