Do Common Ratio Analysis of the Financials
Do Common Ratio Analysis of the Financials You will learn 16 common ratios which are used to analyze financial statements.
Steps
Preparation
- Open a new workbook in Excel from the desktop, from the dock, or from within your Applications folder inside the Microsoft folder. Double click on Excel (either the green X on the dock or the app title in the folder) and select File New Workbook.
- In Preferences, in General, set R1C1 to unchecked or Off; in Ribbon, set Ribbon to checked or On; and in View, set Show Formula Bar by default to checked or On.
- Click in the far upper-left corner above the 1 of row 1 and to the left of column A. 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 'wikiHow Articles'.
- Make this selection: select columns A:C and Format Column Width 7.35".
The Tutorial
- Make these entries: enter the Column Headers in columns A:C and Format Cells Font underline them:
- Enter to cell A1 the label, Ratio or Other Measurement
- Enter to cell B1 the label, Method of Computation
- Enter to cell C1 the label, What It Shows
- Enter the Ratios:
- Enter to cell A2, 01) Return on Total Assets
- Enter to cell B2, (Net Income + Interest Expense) / Average Investment in Assets
- Enter to cell C2, Productivity of assets
- Enter to cell A3, 02) Return on Common Stockholder's Equity
- Enter to cell B3, (Net Income - Preferred Stock Dividends) / Average Common Stockholder's Equity
- Enter to cell C3, Earning power on residual Owner's Equity
- Enter to cell A4, 03) Earnings per Share of Common Stock
- Enter to cell B4, (Net Income - Preferred Stock Dividends) / Average Number of Shares of Common Stock OUTSTANDING
- Enter to cell C4, Amount earned on each share of Common Stock
- Enter to cell A5, 04) Price-Earnings Ratio for Common Stock
- Enter to cell B5, Market Price per Share / Earnings per Share
- Enter to cell C5, Whether market price of common stock is in line with earnings
- Enter to cell A6, 05) Dividend Yield on Common Stock
- Enter to cell B6, Dividends per Share / Market Price per Share
- Enter to cell C6, Return to Common Stockholders based on Current Market Price of Common Stock
- Enter to cell A7, 06) Dividend Payout Ratio for Common Stock
- Enter to cell B7, Dividends per Share / Earnings per Share
- Enter to cell C7, Percentage of Earnings distributed as Dividends
- Enter to cell A8, 07) Number of Times Interest Earned (before Income Taxes
- Enter to cell B8, Operating Income / Annual Interest Expense
- Enter to cell C8, Coverage of Interest Expense (particularly on Long-term Debt)
- Enter to cell A9, 08) Times Preferred Stock Dividends Earned
- Enter to cell B9, Net Income / Annual Preferred Stock Dividends
- Enter to cell C9, Adequacy of Earnings to pay Preferred Stock Dividends
- Enter to cell A10, 09) Equity (Book Value) per Share of Common Stock
- Enter to cell B10, Common Stockholder's Equity / Number of Shares of Common Stock OUTSTANDING
- Enter to cell C10, Amount of Net Assets allocable to each share of Common Stock
- Enter to cell A11, 10) Current Ratio
- Enter to cell B11, Current Assets / Current Liabilities
- Enter to cell C11, Short-term debt-paying ability
- Enter to cell A12, 11) Quick Ratio (acid test)
- Enter to cell B12, Quick Assets / Current Liabilities (see Tips below)
- Enter to cell C12, Short-term liquidity
- Enter to cell A13, 12) Inventories Turnover
- Enter to cell B13, Cost of Goods Sold / Average Inventories
- Enter to cell C13, Ability to control investment in Inventories
- Enter to cell A14, 13) Accounts Receivable Turnover
- Enter to cell B14, Net Sales on credit / Average Accounts Receivable
- Enter to cell C14, Possible excessive Accounts Receivable; effectiveness of collection policy
- Enter to cell A15, 14) Debt Ratio
- Enter to cell B15, Total Liabilities / Total Assets
- Enter to cell C15, Extent of borrowing and trading on the equity (financial leverage)
- Enter to cell A16, 15) Equity Ratio
- Enter to cell B16, 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, 16) Debt to Equity Ratio
- Enter to cell B17, Total Liabilities / Total Stockholder's Equity
- Enter to cell C17, Relationship between borrowed capital and equity capital
- Make these selections: select columns A:C and Format Column Autofit Selection
Your worksheet should resemble the above image.
Helpful Guidance
- 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, Geometry and/or Trigonometry, Charting/Diagramming and Algebraic Formulation.
- For more art charts and graphs, you might also want to click on Microsoft Excel Imagery, Mathematics, Spreadsheets or 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.
Tips
- Quick Assets include: Cash, Short-term Investments / Marketable Securities and Short-term Receivables. It should be mentioned that Current Liabilities should include the Current Portion of Long-term Debt Due.
- Many, probably most, of these ratios are concerned with comparing the firm's financial position with that of other firms, which is of importance to creditors, investors, government agencies and well-informed members of the general public.
- Other values are important, such as the amount of Current Assets in excess of Current Liabilities, i.e. Working Capital.
- Generally, Extraordinary Gains/Losses are excluded from the Net Income figures utilized above in actual corporate practice.
Related Articles
- How to Calculate Working Capital
- How to Do Trend Analysis in Excel
- How to Calculate Return on Capital
- How to Calculate Asset to Debt Ratio
- How to Calculate Debt to Equity Ratio
- How to Calculate Ratios
- How to Prepare a Broad Working Capital or Cash Flows Budget
- How to Prepare the Statement of Cash Flows
