16 Mar I need a ratio worksheet of 3 yeras for a company

,

Ratio Worksheets (Due March 16):

-Calculate the ratios in Excel for the last three years for your company.

-Must three ratios from each category. I have created a template but you can change out some of the ratios if you like

-Your worksheet should include calculations in Excel with clear understanding of inputs (show calculation for most recent year) and a trend analysis column for comment on whether your ratio is improving or deteriorating over the three year time period.

– Also include benchmark (industry or company) ratio for as many of the ratios as you can find.

-Attach/link financial statements (balance sheet, income statement and statement of cash flows) and any other sheets containing data that were used for the ratios.

– Include a short executive summary (narrative not to exceed 1 page) summarizing the ratio analysis and how your company is performing in each category.

FAQ on ratio worksheets & other tips

1. How do I find industry information?

A: I like the website http://reuters.com

1. Type on the magnifying glass in the upper right corner and type in your company name.

2. The first thing that should pop is their ticker symbol. Click on it to pull up stock data.

3. Click on the FINANCIALS tab (3rd from right)

4. Scroll down and you will find industry ratios for most of your ratios – it is okay if you are missing a few

2. What are we supposed to put in the calculation column?

A: I would like to see the numbers that went into your ratio calculation for the most recent year. You don’t have to show me your calculations for the other two years, just the ratio.

3. What are we supposed to put in the trend column?

A: One or two word description of how the ratios are trending over the 3-year period. Like better or worse, improving or deteriorating. Do not use higher or lower.

4. I am having trouble with the market value ratios.

1) What is price per share/market price per share?

A: This is your stock price. It is the top of every ratio in market value category. Do a historical look up of stock price. I like yahoo finance.

http://finance.yahoo.com

1. Type in your company name in yahoo search bar

2. Click on Historical Data tab (3rd from right)

3. Change time period to 5 years and frequency to monthly

4. Pick stock price near time your financial reports were reported (eg. Dec 2017, Dec 2016, Dec 2015) It doesn’t really matter if you pick high/close/etc.

5. Market price per share in market book ratio is stock price – same as other two ratios.

2) How do I find EPS (Earnings Per Share)?

A: Make sure you use a 10k statement or Morningstar. Http://morningstar.com

1. Type your company into the search bar to pull up ticker symbol/stock info.

2. Click on Financials Tab (5th from left)

3. Click on “All Financials Data” at the bottom of the table

4. Scroll down on the income statement EPS is listed. I used the Basic number.

3) How do I find Sales per share and book value per share)?

A: Similar to EPS above, make sure you use a 10k statement or Morningstar. Http://morningstar.com

1. Follow steps 1-4 above under finding EPS but below the EPS is weighted average shares outstanding. I use the Basic number

2. Sales per share = Sales/SO

3. Book value per share = Equity (from balance sheet)/SO