One thing that I found is your investing research process tends to get better and better over time.
You tend to find little tricks and hacks to do things faster and analyze things better.
When I got my first job as a equity research analyst out of college, the firm I worked at had some financial model templates. You would basically plug in some numbers and the software would spit out a bunch of ratios.
I left that job recently, but I learned that having excel templates is a great way to stay organized, pull important information quickly, and to help your research process.
That’s why I created the Ultimate Financial Ratios Spreadsheet for dividend investors.
Ultimate Financial Ratios Spreadsheet
The Ultimate Financial Ratios Spreadsheet For Dividend Investors contains over 25 of the most common financial ratios you’ll need to analyze dividend stocks.
Here are the financial ratios included in this spreadsheet:
- Gross margin
- Operating margin
- Net margin
- Return on assets (ROA)
- Return on equity (ROE)
These profitability ratios are used to determine how much profit the business is bringing in and how well it is using assets/capital to generate returns.
As dividend investors, it’s important to know how the margin profile of the business is trending!
- Current ratio
The current ratio measures the ability of the business to meet short-term obligations. Obviously, a current ratio of at least one is desired.
- Accounts receivable turnover
- Inventory turnover
- Asset turnover
- Accounts payable turnover
The turnover (or asset efficiency) ratios measures how effective the business is using its assets to generate cash/sales.
I always utilize these metrics to see how fast a company is turnover its assets. Accounts receivable and inventory turnover are my favorite metrics.
Cash conversion cycle:
- DSO (days sales outstanding)
- DSI (Days sales of inventory)
- DPO (days payable outstanding)
- Cash conversion cycle
The cash conversion cycle measures how fast the business is generating cash.
- Interest coverage ratio
These debt leverage ratios measure how much debt the business holds relative to assets and profits.
Investing ratios for dividend investors:
- Dividend payout ratio (based on net income and cash flow)
- Dividend yield
- Price-to-earnings (P/E) ratio
- Price-to-book (P/B) ratio
- Price-to-cash flow (P/CF) ratio
- price-to-free cash flow (P/FCF) ratio
The spreadsheet reports all of the financial ratios for the past 4 – 5 years. The valuation information is only shown based on the current market data (for obvious reasons).
Using the spreadsheet
Using The Ultimate Financial Ratios Spreadsheet For Dividend Investors is simple. It contains 4 different excel tabs that you will need to fill information for.
All of the information you need to fill in are highlighted in yellow.
Step #1: Fill in the income statement
The first tab you need to fill in is the income statement tab. Basically, you need to input all of the data on the income statement for the past six years.
I kept the income statement very generic so it should be able to accommodate companies in a wide variety of industries.
What’s really cool about the income statement tab, is I have built it so the model will spit out the year-over-year change and common size income statements below.
The year-over-year change income statement will calculate the growth rate of all the items on the income statement. This is really useful if you want to see how fast revenue or expenses have increased/decreased over the past five years.
The common size income statement expresses all expenses as a percentage of revenue. For example, it’ll calculate what taxes are as a percentage of revenue or what operating expense is as a percentage of revenue.
This is very helpful if you want to see how the margins of the business have been performing.
Step #2: Fill in the balance sheet
The second step is to fill in the balance sheet tab, which is much easier than the income statement tab.
This tab is much easier to fill out as it is only a condensed balance sheet.
Step #3: Fill out cash flows
The third step is to fill out the cash flow information. This is easy as the information is taken directly from the statement of cash flows.
Note: most companies have a “purchases of property, plant, and equipment” line item on the statement of cash flows. This is the same thing as capex.
Step #4: Fill out the market data
The fourth and final step is to fill out three important pieces of market data:
- The current stock price
- The annual dividend payment
- And the market capitalization
All of this information can be found very fast on Google Finance.
The market data will be used to calculate important valuation ratios.
Who is this spreadsheet for?
I made this spreadsheet primarily for dividend investors. However, it is still useful for any investor.
If you want to speed up your research process or organize your financial information, I would highly recommend getting this free spreadsheet!
Just sign up below: