Learn Financial Ratio Analysis in 15 minutes
How to Do Common Ratio Analysis of the Financials
This article may benefit from a new introduction.
You can help wikiHow by the current introduction, or writing a new one to match the format described in the Writer's Guide. Please remove this notice once this page has been improved. Notice added on: 2019-06-01.
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".
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.
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 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.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
- 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.
Video: 3 Minutes! Financial Ratios and Financial Ratio Analysis Explained (Quick Overview)
How to Train a Blind Dog to Handle the Stairs
Martha Hunt on the sustainable new diamond jewellery brand she loves
How to Pay Bills from an Estate
21 Cute Thanksgiving Nail Ideas
9 Layering Pieces Every Woman Should Have for Winter
Kim Kardashian Breaks Down Talking About Paris Robbery for the First Time
How to Decorate for a Bridal Shower
How to Attend School if You Are Blind or Visually Impaired
New Look Menswear: SS13 Collection
How to Write a Student Council Speech
This Veggie Burger Recipe Puts All Others toShame
This is the stunning MS buy we’ll all be lusting after this autumn
5 One-Bowl Dinners That Are Almost Too Pretty To Eat
Khloé Kardashian Says This Photo of Her in a Bikini Is Not What Her Body Looks Like
How to Determine if Your Car Is Front or Rear Wheel Drive
Date: 05.12.2018, 01:59 / Views: 95471