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

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

Apple Multiples Valuation

Unlike a traditional DCF model, doing a multiples analysis with comparable companies is a lot easier and quicker to do. The four companies I used to compare with Apple are: Microsoft, Amazon, Netflix, and Google. I used Microsoft because Apple and Microsoft are competitors in the field of hardware, mainly computers. Amazon and Apple are comparable because they're both tech giants in their respective fields and even have services in common such as Amazon Prime/Apple TV and Siri/Echo. Similarly, Netflix and Apple compete with each other in regards to their streaming services. Google and Apple both produce hardware like  home-pods, phones and computers, despite Apple being considerably larger.  The multiples that I used for the valuation are: EV/Sales, EV/EBITDA, EV/EBIT, and P/E. EV is the Enterprise Value of a company, which can be calculated by subtracting cash and cash equivalents and adding total debt to the Market cap. EV is the true value of a company while taking into acc...
Post a Comment
Read more