How to Do Common Ratio Analysis of the Financials

Step-by-Step Guide

1. 1

   Step 1: Open a new workbook in Excel from the desktop

   Double click on Excel (either the green X on the dock or the app title in the folder) and select File New Workbook. ,, Doing so will select the entire worksheet.
   
   Format the number of cells to decimal places 2, show comma.
   
   Format the cells alignment left.
   
   Format Cells Font size to 9,10 or 12, bold.
   
   Color the cells the lightest sky blue.
   
   Title the worksheet, "Acctg Ratios" and save the workbook as "Financial Ratios" into an appropriate folder such as 'LifeGuide Hub Articles'.,,,,,, For more art charts and graphs, you might also want to click on Category:
   Microsoft Excel Imagery, Category:
   Mathematics, Category:
   Spreadsheets or Category:
   Graphics to view many Excel worksheets and charts where trigonometry, geometry and calculus have been turned into art, or simply click on the category appearing in the upper-right, white portion of this page or at the bottom-left of the page.
2. 2

   Step 2: from the dock

3. 3

   Step 3: or from within your Applications folder inside the Microsoft folder.

4. 4

   Step 4: In Preferences

5. 5

   Step 5: in General

6. 6

   Step 6: set R1C1 to unchecked or Off; in Ribbon

7. 7

   Step 7: set Ribbon to checked or On; and in View

8. 8

   Step 8: set Show Formula Bar by default to checked or On.

9. 9

   Step 9: Click in the far upper-left corner above the 1 of row 1 and to the left of column A.

10. 10

    Step 10: Make this selection: select columns A:C and Format Column Width 7.35".

11. 11

    Step 11: Make these entries: enter the Column Headers in columns A:C and Format Cells Font underline them: Enter to cell A1 the label

12. 12

    Step 12: Ratio or Other Measurement Enter to cell B1 the label

13. 13

    Step 13: Method of Computation Enter to cell C1 the label

14. 14

    Step 14: What It Shows

15. 15

    Step 15: Enter the Ratios: Enter to cell A2

16. 16

    Step 16: 01) Return on Total Assets Enter to cell B2

17. 17

    Step 17: (Net Income + Interest Expense) / Average Investment in Assets Enter to cell C2

18. 18

    Step 18: Productivity of assets Enter to cell A3

19. 19

    Step 19: 02) Return on Common Stockholder's Equity Enter to cell B3

20. 20

    Step 20: (Net Income - Preferred Stock Dividends) / Average Common Stockholder's Equity Enter to cell C3

21. 21

    Step 21: Earning power on residual Owner's Equity Enter to cell A4

22. 22

    Step 22: 03) Earnings per Share of Common Stock Enter to cell B4

23. 23

    Step 23: (Net Income - Preferred Stock Dividends) / Average Number of Shares of Common Stock OUTSTANDING Enter to cell C4

24. 24

    Step 24: Amount earned on each share of Common Stock Enter to cell A5

25. 25

    Step 25: 04) Price-Earnings Ratio for Common Stock Enter to cell B5

26. 26

    Step 26: Market Price per Share / Earnings per Share Enter to cell C5

27. 27

    Step 27: Whether market price of common stock is in line with earnings Enter to cell A6

28. 28

    Step 28: 05) Dividend Yield on Common Stock Enter to cell B6

29. 29

    Step 29: Dividends per Share / Market Price per Share Enter to cell C6

30. 30

    Step 30: Return to Common Stockholders based on Current Market Price of Common Stock Enter to cell A7

31. 31

    Step 31: 06) Dividend Payout Ratio for Common Stock Enter to cell B7

32. 32

    Step 32: Dividends per Share / Earnings per Share Enter to cell C7

33. 33

    Step 33: Percentage of Earnings distributed as Dividends Enter to cell A8

34. 34

    Step 34: 07) Number of Times Interest Earned (before Income Taxes Enter to cell B8

35. 35

    Step 35: Operating Income / Annual Interest Expense Enter to cell C8

36. 36

    Step 36: Coverage of Interest Expense (particularly on Long-term Debt) Enter to cell A9

37. 37

    Step 37: 08) Times Preferred Stock Dividends Earned Enter to cell B9

38. 38

    Step 38: Net Income / Annual Preferred Stock Dividends Enter to cell C9

39. 39

    Step 39: Adequacy of Earnings to pay Preferred Stock Dividends Enter to cell A10

40. 40

    Step 40: 09) Equity (Book Value) per Share of Common Stock Enter to cell B10

41. 41

    Step 41: Common Stockholder's Equity / Number of Shares of Common Stock OUTSTANDING Enter to cell C10

42. 42

    Step 42: Amount of Net Assets allocable to each share of Common Stock Enter to cell A11

43. 43

    Step 43: 10) Current Ratio Enter to cell B11

44. 44

    Step 44: Current Assets / Current Liabilities Enter to cell C11

45. 45

    Step 45: Short-term debt-paying ability Enter to cell A12

46. 46

    Step 46: 11) Quick Ratio (acid test) Enter to cell B12

47. 47

    Step 47: Quick Assets / Current Liabilities (see Tips below) Enter to cell C12

48. 48

    Step 48: Short-term liquidity Enter to cell A13

49. 49

    Step 49: 12) Inventories Turnover Enter to cell B13

50. 50

    Step 50: Cost of Goods Sold / Average Inventories Enter to cell C13

51. 51

    Step 51: Ability to control investment in Inventories Enter to cell A14

52. 52

    Step 52: 13) Accounts Receivable Turnover Enter to cell B14

53. 53

    Step 53: Net Sales on credit / Average Accounts Receivable Enter to cell C14

54. 54

    Step 54: Possible excessive Accounts Receivable; effectiveness of collection policy Enter to cell A15

55. 55

    Step 55: 14) Debt Ratio Enter to cell B15

56. 56

    Step 56: Total Liabilities / Total Assets Enter to cell C15

57. 57

    Step 57: Extent of borrowing and trading on the equity (financial leverage) Enter to cell A16

58. 58

    Step 58: 15) Equity Ratio Enter to cell B16

59. 59

    Step 59: Total Stockholder's Equity / Total Assets Enter to cell C16; Protection to creditors and extent of trading on the equity (financial leverage) Enter to cell A17

60. 60

    Step 60: 16) Debt to Equity Ratio Enter to cell B17

61. 61

    Step 61: Total Liabilities / Total Stockholder's Equity Enter to cell C17

62. 62

    Step 62: Relationship between borrowed capital and equity capital

63. 63

    Step 63: Make these selections: select columns A:C and Format Column Autofit Selection

64. 64

    Step 64: Your worksheet should resemble the above image.

65. 65

    Step 65: Make use of helper articles when proceeding through this tutorial: See the article How to Do Cost Volume Profit Analysis for a list of articles related to Excel

66. 66

    Step 66: Geometry and/or Trigonometry

67. 67

    Step 67: Charting/Diagramming and Algebraic Formulation.

Detailed Guide

About the Author