Dividend Stock Fundamentals analysis Template

In this post, I will give a quick overview of how I can quickly analyze Dividend Stock Fundamentals. I built a simple dashboard, where I simply copy over the 3 financial statements and it ranks the company between 0 and 100.

For companies that are not in my portfolio, I find this a quick way to decide if I want to spend more time researching them. For companies in my portfolio, I find it useful to see when is a good time to buy the company.

Back in March 2020, I wrote 4 steps to screen dividend companies which is useful to create a watchlist. I would not suggest buying any company based on screeners. you have to get into the real meat of their financials to make a more informed decision.

Right off the Bat, I want to thank both European DGI and the Sunday Investor for the Inspiration. EDGI first introduced me to his spreadsheet in 2020 and the DCF model is a direct copy of what he used at the time. The Sunday Investor was also kind enough to help ( read design and implemented) the DDM model.

Dividend Stock Fundamentals Dashboard

The above image is an example of the final dashboard for Microsoft. Microsoft scored a total of 70 out of a 100, which just falls short being in the buy category.

The dividend is strong and growing, they have low payout ratios, little debt and revenue and earnings are growing. The only place it fell down was in my valuation which is an average between the 10 year average PE, Dividend Discount model and Discount cash flow.

I will take you through how I get the data and how the gsheet works in the below paragraghs.

Google sheet overview

The sheet has 7 tabs in total. Business Model, EMF Scoring (Dashboard) , Income Statement, Balance Sheet, FCF, Multi-stage DDM, and DCF.

Financial Statements

For the income statement, Balance Sheet, and Cash Flow Statement I use a website called TIKR. What I like about TIKR is that it has both US and European companies. This keeps my dividend stock fundamentals analysis cohesive for both regions. While I have found a couple of good sites like MACROTRENDS and STOCK-ANALYSIS-ON.NET from American companies. I have not found too many that offer them for European companies for over 10 years.

All I do is set the slider for the max number of years. Reverse the dates so the current year is first and select the button to copy data. I paste the data in the corresponding tab on the gsheet. I also copy the Ratios Tab and paste these to the bottom of the FCF tab.

That is pretty much the hard work done. Most of the rest of it should be automated but there are one or two elements to add manually.

Business Model

On this page, the only piece of information you need to enter is the Ticker, Company name, Description, and the dividend amount. This will then pull in the latest price, PE ratio, and EPS from google and will calculate the dividend yield. Both the Ticker and Dividend is mandatory for some of the calculations.

The rest of the data is pulled from the 3 financial statements and I split them up into 4 sections.

1. Company growth looks at Revenue, EPS, and Free Cash Flow. I calculate the 5 and 10-year compound annual growth rates for each of these.
2. Income overview looks at net income and profit margins
3. Balance sheet overview looks at the balance sheet
4. The cash flow overview looks at the cash flow statement.

I also included some graphs just to make it a little bit more pleasing on the eye!

Dividend Discount Model

The dividend discount model came about after I reached out for some help on the subject to the Sunday Investor. A very knowledgeable guy and is always willing to lend a hand if needed. Check out his post on the Dividend Discount Model. All I simply did was automate where the figures came from. There are only 2 cells to manually update on this page. The first is the Perpetual Growth Rate and the market rate of return. I am struggling to find out how to update these automatically so if anyone has any ideas drop them in the comments below.

Discounted Cash Flow

Again, this is something that I borrowed from European DGI. I have tried to automate as much as possible but you still have to enter your expected growth rate, Terminal growth rate and the Discount rate.

BTW for anyone who is interested in a full company analysis of Microsoft you should check out Europeans DGI’s review.

EMF SCORING

The final tab is where I score the company. I look at the Income growth, Profitability sustainability, and Value of the company. Everything is calculated from the 3 financial statements and the Financial Strenght and Dividend Safety Score is ranked out of 100.

The only pieces of information that need to be entered manually are the Sector, Dividend Growth Years, and moat. I always get the average 10 year PE ratio from guru focus.

If it is a new company that I have never looked at before, if a company scores in the high 60s I will generally look into the annual reports and try find out as much as I can on the management and future growth of the company. This allows me to get the full picture of a company and make a decision if I want to purchase the company.

If the company is already in my portfolio, I will pay more attention to the valuation and try to buy the company when they are fairly or undervalued.

If you think that you would find this useful and would like a copy of the sheet for yourself than you can grab a copy below.

---

---

Disclaimer - Engineer my Freedom is not a licensed or registered investment adviser or broker/dealer. We are not providing you with individual investment advice on this site. Please consult with a licensed investment professional before you invest your money. This site is for entertainment, informational, and educational use only. Any opinion expressed on the site here and elsewhere on the internet is not a form of investment advice provided to you. We use information, data, and sources in the articles we believe to be correct at the time of writing them, but there is no guarantee of their accuracy, completeness, timeliness, or correctness. We are not liable for any losses suffered by any party because of information published on this site or elsewhere on the internet. Past performance is not a guarantee of future performance. By reading this site or subscribing to it, you agree that you are solely responsible for making investment decisions in connection with your funds.