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