Skip to main content

My First DCF Model


If I'm going to start learning financial modeling from scratch, I think a Discounted Cash Flow model for stock valuation is one of the most important tools. I discovered the DCF naturally after learning about the 3 statement model- the backbone of a lot of financial modeling. I wanted to expand my skill set, and the DCF was one of the first new models I discovered.

I decided to try to evaluate Microsoft stock, since I think the company's future prospects are very exciting with Azure and the Xbox Series X.  

I decided to dive in head first by not using a template or making the model fancy. I am content with my initial efforts since learning by doing is the best approach and I didn't want to overwhelm myself. One of the most important resources for me was Yahoo! Finance, since all the relevant information is well structured and the general layout of the site isn't too confusing. Also, any public company will release their 10-K form annually. A 10-K is useful for finding things like the effective tax rate and the number of shares outstanding.

Before we start looking at predicted future cash flows, we have to make predictions of future total revenue and net income. Firstly, I want to clarify that my first year of predictions is 2021, as Microsoft uploaded their 10-K for 2020. Starting with Total Revenues, I went to MSFT's Yahoo! finance page and clicked on 'Financials'. The Total Revenue is available in the Income Statement, I took the values from 2017 to 2020, for the years of 2021 and 2022 I used the predicted values given by multiple analysts on the 'Analysis' page. Now we just have 2023 and 2024 values to predict. The way I formed a basis for my predictions of TR was the historical growth rates (%) for the revenues from the years 2017 to 2022. Afterwards, I used the AVERAGE function on Excel to get the average growth rate. 

Next, I pulled the historical Net Income data (2017-2020). To predict the future Net Income, I calculate the historic Net Income margins ( Net Income/ TR), and then average out the Net Income margins from 2021-2024. To calculate the predicted FCFs, we have to acquire the FCF/NI ratio, which allows us to acquire historical growth rates that serve as the key to unlock the predicted values. Once again, we average the historical values to get the predicted values for 2021-2024. 

Now we have to calculate one of the most important percentages for the DCF valuation, the required rate of return, or the Discount %. We can either chose a preferred rate or calculate the WACC (Weighted Average Cost of Capital). WACC is simply the weight of equity and debt that has gone into capital. WACC is calculated via this formula: Interest Expense/ Total Debt x Total Debt/ Market cap +TD x (1- Effective tax rate) + CAPM ( Capital Asset Pricing Model) x Market Cap/ MC+TD. Make sure to use the most up to date values. Even though you can get Market Cap on Yahoo, it's always a good idea idea to calculate it yourself. You can do this by multiplying Shares Outstanding (can be found in the first couple of pages of the 10-K) by share price. Make sure to divide shares outstanding by 1000, since all Yahoo Finance values are in the thousands. 

We now need to calculate the perpetual growth rate: this is usually determined as the historical inflation rate of between 2-3%. With the required rate of return, we can now calculate the Discount Factor. The DF is (1+RoR)^prediction year. The prediction year starts with 2021, so raised to the power of 1, and ends in 2024, which would be raised to the power of 4. With the RoR and the perpetual growth rate, we can now calculate the terminal value, which is the FCF where we assume the perpetual growth rate is added till forever. The formula is: FCFx(1+perpetual GR)/(RoR-GR). The FCF is the last FCF we calculated. 

We can now calculate the PV of all FCFs, this is done for the FCFs and the TV. This is done by dividing all the FCFs and the TV by the DF. After this is done, we can calculate Today's Value which is the sum of all PV of FCFs. To get the Fair Value of Equity (our predicted share price), we divide Today's Value by Shares Outstanding (in thousands). If this Fair Value of Equity is greater than the share price, the stock price is under-priced and vice versa. 

I will be posting my DCF for MSFT below. This valuation is rather messy and doesn't include a 3-statement. Thus, my new goal is to do a full DCF. For my next blog, I will be giving my step by step for a 3-S, for Netflix. Then, I will integrate that 3-S model into a DCF valuation. 

https://docs.google.com/spreadsheets/d/e/2PACX-1vRxZdV4P8MaoCy5GRq79Ci6w7R4g73BSvjFLKAIAfPm2pQF5ughMoJ5E1ezY0Eoxg/pubhtml
















Comments

Post a Comment

Popular posts from this blog

The Extra Strong Dollar: What that means for MNCs

 On July 12th, something unprecedented happened. The Euro reached parity with the US Dollar, meaning that 1 Euro is now worth 1 Dollar. For the 20 or so years the Euro has been around, it has never reached parity with the Dollar, but now that has changed. One reason why this has occurred is because the Federal Reserve has been steadily increasing interest rates in the US in order to combat inflation, while Europe has remained inactive in comparison. Another issue is that the ongoing Russian invasion of Ukraine has caused mass capital investment flight out of European markets & into American ones, increasing demand for Dollars and pushing up its value, thus making it stronger relative to the Euro. This stronger dollar has its advantages and disadvantages. A key disadvantage for multinational companies is that big currency fluctuations can have a significant impact on profitability. American companies with large international footholds will take a hit from converting their foreig...
1 comment
Read more

Going Private Again Is The New Trend On The Menu

With the start of the year, a growing number of newly public companies are changing course and going back to being private again. 10 companies that went public in the boom years of 2020 and 2021 have decided to sell themselves to private equity firms. As the US economy headed towards a downward trend in its economic cycle, a majority of the 2020-2021 class of initial public offerings have had a dismal performance, resulting in the decision to go private once again. Grill maker  Weber  agreed to be bought out by BDP Capital Partners in December of last year  for $8.05 a share, well below its $14 IPO price less than 18 months earlier.  Sumo Logic  Inc. agreed in February  to be bought by private-equity firm  Francisco Partners for $12.05 a share, down from the data-analytics software company’s $22 IPO price. However, not all companies are selling at a loss. KnowBe4 Inc. in October agreed to go private again in a deal with Vista Equity Partners, t...
Post a Comment
Read more

3-Statement Model - Netflix

To be honest, this was a lot more time consuming than I thought it would be. But, I'm glad I got it done. First thing to note with this 3S model is that it was designed specifically to integrate with a DCF Valuation, which will be shown in the next blog post. 
Post a Comment
Read more