The Evolution of My Equity Analysis Models

During my third year of university, I wanted to develop a more systematic approach to personal investing. I had spent a few years prior buying shares for fun with no real developed approach to it. I just stuck to what I knew was popular with and companies I was familiar with, with big names and larger brands. Given that the U.S. economy has been recovering from the 2007-2008 recession, it was hard to go wrong with this method. However, as I just started studying finance at school and learned more about investing, combined with reading and listening to podcasts on the topics, I gained a better understanding of what it meant to do analysis on a company. Furthermore, I gained a better understanding of what to look for in possible investment opportunities. I took what I was learning and started putting it to use. I began to slowly build an Excel model that I would use as a template to do my analysis on a company. A year later, I have made two completely new models as I learned more and accomplished my goal of having a more systematic way of making investment decisions. Below is an explanation of the development and evolution of each model, and what my analysis primarily entails. I won’t go into too much detail such as the formulas behind each cell, but rather discuss how each version changed and the basics of the template.

Model 1.0: The Beginning

The first version, retrospectively, was very limited and narrow in scope, but it was certainly a start. I am sure that in another year or two I will look back on my current version to say the same thing, given that I make changes to the model as I learn more. Regardless, the main sections of this template were sections for inputting basic financial statement (FS) information, which then fed into a discount dividends model (DDM) and a discounted cash flows model (DCF). These sections would give me an intrinsic share price based on the cash flows generated by the firm, which I could then compare to the market price, helping me to determine whether the stock is currently selling at a premium or discount.  I also calculated a few discount rates to use in the valuations, and there was a small section of ratio analysis which mainly looked at earnings multiples, profitability ratios, and other return-metrics. All the analysis done was looking at one company in isolation. Some of the main resources I used were from my online broker, MorningStar reports, and the company’s 10K (annual reports). I then made a final conclusion on the company at hand. A screenshot of the first version is below. Some of the companies I performed an analysis on with this version included Valeant Pharmaceuticals (VRX), Dollarama (DOL), Fortis (FTS), and Pfizer (PFE). As I learned more, I decided to adapt the template and make it more comprehensive, bringing about my second version, 2.0.

Screen Shot 2018-04-25 at 5.37.22 PM

Model 2.0: Thorough Isolation

After completing this template, I felt very proud as I compared it with my previous model. I definitely felt it was more comprehensive, as I expanded the previously existing sections such as the DDM and DCF models, calculations of discount rates, and ratios analysis. Furthermore, the section to input and analyze a company’s FS information was much more thorough. The model calculated year-over-year growth rates and average growth rates of certain FS elements. If the template showed that a company had an extremely high operating margin growth over the last five years, I could then find out more about why by looking through their 10Q’s or 10K’s. In addition, I added new features to the template such as a future free cash flow probability tree, an observations section for me to make more notes, a DuPont analysis, a breakdown of a company’s revenue streams and cost sources, and finally, a summary report. Later on, after using the template for a few months, I adapted it again to include a more comprehensive ratio analysis which looked at the five basic families (profitability, efficiency, leverage, liquidity, and market-value). The FCF probability tree gave me a likely future FCF growth rate that would feed into the valuation sections. The larger FS section helped me develop more notes to look into more. The more comprehensive ratio analysis section showed how a company’s operations or leverage had changed over time. The breakdown of revenues and costs helped me understand how the company derived its inflows and how it spent its outflows, expressed as a percentage of sales. The report was the most important part to me as I had a section where I could write more qualitative information and observations on the company. I tried to analyze the company’s basic operations, what their strengths, weaknesses, opportunities and threats were, the outlook for the company’s respective industry, and a conclusion on the stock. Some of the companies I analyzed with this version included Cheniere Energy (LNG), (OSTK), Intel (INTC), Royal Bank of Canada (RBC), and Amazon (AMZN). Again, this template looked at a company in isolation, with exception of a few ratios which I compared to market or industry averages. Recognizing this fault was the main reason why I decided to build a brand-new template starting from scratch again. The previous templates helped me understand the company’s business, operations, and financial performance, but without any context relative to its peers. For example, by analyzing Apple I could understand how they became more profitable or changed theircapital structure, but that didn’t matter unless I was seeing how their competitors’ profitability or leverage had changed over the same period of time. I had no context to put a company’s financial performance into perspective. This leads me to the third version of my personal investment model.

Screen Shot 2018-04-25 at 5.40.20 PM.png

Screen Shot 2018-04-25 at 5.40.36 PM.png

Screen Shot 2018-04-25 at 5.40.54 PM

Model 3.0: The Introduction of Peer Evaluation

Because I had to get away from analyzing a company in complete isolation, I had to start again from scratch. This was beneficial as I was able to get rid of certain elements that I found were not as beneficial as I thought they would originally be. While I wanted to begin analyzing companies in groups, I also wanted to become more efficient in the whole analysis process by cutting out the stuff I thought had less relevance. The model looks at four companies at the same time, belonging to the same sector, with possible differences in industry. For example, I could look at four companies belonging to the semiconductor sector, but within that sector there are a handful of separate industries or specialties of semiconductors. This would allow me to identify the best performing company within a peer group and understand possible downfalls of specific companies relative to its competitors. Along with this fundamental change, there were new additions and sections that I completely removed. As you can imagine, going from a template that analyzes one company to one that analyzes four, I needed to reduce in order to keep the template to a similar size. I didn’t want to bombard myself with information. I removed the FCF probability tree as I had no basis for estimating the possible paths for a company’s future FCF growth. I significantly reduced the FS information section to be more efficient, as a lot of it just required me transferring the information from an online source to the template but had little benefit to the model. I cut out the DuPont analysis. I also reduced some of the ratio analysis because I found little benefit to look at certain ratios, especially for certain industries. For example, for personal investment decisions, I found looking at a company’s efficiency in terms of inventory had little to no impact on my decision. Then I added new elements into the template. If you look at the screenshots below, you can see the general changes to the structure. However, the basic elements were now general trading information, costs of capital (discount rates), a slimmer FS section, FCF analysis, valuations analysis, revenue and costs breakdown, ratios analysis, and a market position map. In addition, I wanted to know the exposures the company faced based on revenue sources by country, the product and service groups, its competitors, and its customer markets (not geographically). The report had changed from the previous version. Finally, I changed the summary to be just basic highlights and a new feature called a scorecard, which is a relatively new concept to me. I will explain certain parts in greater detail below. Again, all these parts would be done for all four companies identically.

Screen Shot 2018-04-25 at 5.42.30 PM

Free Cash Flows and Valuations

With this model, I now had three valuation models to generate an intrinsic share price from a DDM, DCF to the firm, and DCF to the shareholder models. I also included a more in-depth analysis of the company’s future free cash flows (to the firm and to the shareholder), which then fed into the valuations. The three valuations would give me an idea of where I thought the share price belonged – a range for the intrinsic value of the share – which I could compare to the current market price. This would give me a general idea of whether the stock is trading at a premium or discount, with respect to the information inputted into the template. I would use the conclusions from these valuations in conjunction with the ratios and multiples for each firm. Below is a screenshot of the template for one company in the peer group.

Screen Shot 2018-04-25 at 5.42.40 PM

Highlights and Ratios

One of the most important things for me to identify is whether many of the multiples that are in the model show that a company is trading at a significant premium or discount to that of its peers, and why. For example, one of the most common indicators is the Price-to-Earnings (P/E) ratio. This simply shows that price the company is trading at in the market relative to the amount of profit the company generates. In other words, what are investors willing to pay for each dollar in profit generated by the company? By comparing this company’s multiple to that of its peers, you can see if investors are purchasing the company’s shares at a premium or discount. This multiple, along with other indicators, will give me a general idea of whether that company is trading at a premium (or discount), relative to the other companies in the study group. Once I identify the companies trading at significant premiums (or discounts), I then need to figure out WHY. For example, why are investors in the market willing to pay $32 per $1 generated in profit compared to $12 per $1 generated by one of its competitors? There can be many sources that can signal why the company may be more expensive than its peers. This is why learning about the company’s strategies, competitive advantages, opportunities, and more are really important in the analysis process. It also helps to look at other ratios and indicators to figure out why a company may be more expensive, such as profitability, efficiency, and leverage ratios.  This is why the ratio analysis is important and it is not as simple as seeing which companies have the ‘best’ ratios. I need to understand the “why” behind the important ratios and do my homework on the company and industry. Also note that there are many, many ratios and quantitative indicators one could look at. There are generic ones and ones that are more specific to industries. Doing the analysis work, I need to figure out which ratios I care about most.


This part of the model is a new addition and relatively new to me. I’m still trying to figure out the best way to use it, in terms of properly weighting each score metric. Once I was done all the inputting and the model would calculate everything else for me, I would use the information to generate rankings for each company based on a certain metric. On each metric, I would rank each company first to fourth, with the best score being a four and the worst score being a one. For example, the company with the greatest profit margins would receive a four, and the company with the lowest margin would receive a one. The main groups of metrics fall under profitability, efficiency, dividend policy, capital structure and leverage, and market multiples. If I add all the scores of each metric for each company, I’ll have a total score for that company. Ideally, I would deem the company with the highest score as the best investment option out of this group. However, it’s not as black and white as that. Currently one of the downfalls is that each metric is equally-weighted in the rankings list. That means that according to the scorecard, a company’s dividend history matters as much as their operating margins, as an example. Or that the company’s weighted-average cost of capital (WACC) mattered as much, quantitively, as their total asset turnover. I’m still figuring out this section and will adapt it as I learn more.


The final piece of the model is the report that ties all the analysis together. To be most efficient, I only looked at the sources of competitive advantages of a company, what the analysts are saying in terms of being bullish and being bearish, how I justify their current price-to-earnings ratio, and what my overall investment thesis is on the company, relative to its peers in the group. Below is a screenshot of the template of the report I would fill in.

Screen Shot 2018-04-25 at 5.42.58 PM


This was the general evolution of my equity analysis models and an overview of how I go about doing analysis on a company. The basis of each model was looking at the fundamentals of a company and determining the best investment options, relative to its current market price. There are many strategies to investing and in general, I tried to stick to looking for companies selling at fair valuations relative to its market price and its competitors. I stayed away from technical analysis but can understand its benefit when used in conjunction with fundamentals. For example, I may identify a company that I think is a bargain or ‘cheap’, but the stock may have even more downside to it. By using technical analysis, it can help me avoid ‘catching a falling knife’. To understand my view on investing strategies, see the article attached here. By performing this type of analysis using the template, I have a systematic approach to making investment decisions. Perhaps even more importantly, I am able to keep records of my thought process behind a decision so that I can go back to and reference after some time. For example, if a stock performs extremely poorly after a couple years, I can go back and look through the analysis to review WHY I made the investment, or where I went wrong during the analysis. If you have any questions, want to learn more about this, or have any ideas on how to improve my model or process, please comment! Thanks.



1 Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s