Why understanding Financial Modeling is so important when buying or selling a stocks?
1. What are the different types of financial models?
There are many types of financial modeling such as Discounted Cash Flow (DCF) Model, Merger Model (M&A), Leveraged Buyout (LBO) Model.
2. What is a DCF model?
A basic DCF model involves projecting future cash flows and discounting them back to the present using a discount rate (weighted average cost of capital) that reflects the riskiness of the capital you then add up all those discounted cash flows and the sum is really the intrinsic value of the company (equity Value). You can find Apple Inc. Discounted Cash Flow Statement in the financial summary page:
This is why so many people of the industry compare that value to market values to determine if something is overvalued, undervalued or valued correctly. As you can see here in our example Apple’s DCF is 191.92 which is higher than its actual price of 174.72 this is why the recommendation is a Buy (You could buy the stock hold it until it reach its intrinsic value of 191.92 and make 15 dollars of benefit)
So it really is an important form evaluation and almost all people in finance use this valuation to a certain degree it’s also worth noting that they are many different variations of the DCF but they are generally two different types unlevered and levered.
Unlevered DCF are before the payment of debts. So you will see that we are dealing with items like EBIT, EBIAT (which mean its before the payment of interests as well as debt pay down).
Levered is on the other hand after the interest expense and after debt pay down.
You can consult our ranking of stocks on DCF or choose a stock to get its DCF.
3. Start building our free cash flow statement
If you created a model before you know that you might have a financial statement model that precedes the DCF Analysis and in that model you have a forecast cash flow statement so you should wondering why do i even need to do a free cash flow build-up if I have that basic FSM ? The answer is that cash flow from operation does not include the capital expenditure and we know that free cash flow by definition is operating profit less reinvestment being capital expenditures so while this items (Account payable / inventories …) it is missing a critical element which is CAPEX (capital expenditure) so that is why we need a cash flow build-up as you see here.
The next thing that you’re probably wondering is why we forecasted for 5 years the real answer is well it depends on when your company has stable earnings so we’re assuming that in five years the company will have stable earnings so typically practitioners forecast cash flows anywhere between five and teen years and even if you’ve got a company that is mature like a coca-cola Procter & Gamble or GE it’s still recommended that you do five years so you can see the cash flow build-up now that we understand sort of what the section entails it let’s go ahead and start building it so we’re going to go ahead and reference revenues from our select operating data schedule
I’m going to do the same thing with EBITDA da as well as EBIT again this is an unlevered free cash flow analysis so it’s before the payments of debt now given that taxes are not an optional thing you have to pay taxes to the government we’re going to focus on EBIAT which is earnings before interest after taxes so we take a EBIT and we multiply by one minus the tax rate to find the after-tax value of EBIT another word for EBIAT that you might hear is NOPAT and that’s net operating profit after taxes next in order to go from EBIAT to to unlevered free cash flows we need to make several adjustments those adjustments include adding back non-cash expenses and subtracting out non-cash gains making our accrual adjustments and then subtracting out our reinvestment which is capital expenditures
So first thing we’re going to do is we’re going to add back our non-cash expenses which in our case is just D&A now as you recall when we when you build a cash flow statement an increase in an asset is viewed as a use of cash while an increase in liabilities or equity is viewed as a source of cash now I know that accounts receivable when we increase that it’s hard to really picture why that’s a use of cash so I’m going to explain it from a different angle here and that is that we know that from cash versus accrual accounting revenues includes both sales made on credit as well as sales made on with cash so in other words if our credit sales increase that’s going to indeed increase revenue but we know that we’re not actually receiving that cash yet we’re going to receive it at a later point in the future so if our accounts receivable goes up we need to make the necessary adjustments to EBIAT to reflect the actual cash that is coming in which is only cash sales so therefore an increase in accounts receivable will need to be deducted from EBIAT to correctly reflect cash coming in so one way to do this would be to do the first forecasted year minus the previous year but you need to make sure to add a negative sign to that value otherwise you won’t have the correct signage now what I’m going to do is I’m going to do previous year — the first forecasted here because that will automatically embed the sign now accounts payable being a liability an increase in that represent a source of cash so I’m going to do first forecasted period minus previous forecasted period
Now regarding capital expenditures they are an asset so an increase represents a use of cash okay now what we need to do is we’re going to add EBIAT D&A and our working capital adjustments as well as capex the present value of free cash flows which is unlevered free cash flow divided by 1 plus WACC raised to the period. so as you can see we’ve now built up our free cash flow for the explicit forecasted period ie stage one and it’s now time to focus on what we call stage two which is the terminal value. Remember that we still missing the WACC to go from Free Cash Flow to Present Value of Free Cash flow and we are going to calculate it after the terminal value.
4. Find our terminal Value
Terminal value represents all value beyond the explicit forecast period so from your 6 onward now it’s hard to use a basic discounted cash flow formula to to calculate terminal value given that a extends into the future long into the future so we there’s really two different methods to calculate terminal value the first one is what we call growth and perpetuity which is what we’re going to do in our model and the second is what we call exit EBIT on multiple method but we’re going to focus again on growth and perpetuity terminal value is a value that’s assumed to exist in year 5 and so we’re going to have to apply the discount factor from year 5 to this terminal value to arrive at the present value of stage 2 and our enterprise value will be that stage 1 plus stage 2 and so now that we know what terminal value is let’s go ahead and and calculate it so a WACC again will be calculated later on so our free cash flow and t plus 1 is going to be this unlevered free cash flow times 1 plus the growth rate we take that free cash flow and we divide by WACC minus the growth rate again growth and perpetuity and the value
So the present value of terminal value is going to be this terminal value divided by 1 plus the WACC from the fifth period raised to the 5th power
For now we forecast that free cash flows 5 years into the future and we will discount those cash flows back to the present to arrive at the present value of what we call stage 1 we then estimated what the value beyond the explicit period will be using growth and perpetuity growth and perpetuity method the value beyond the explicit period is what we call terminal value
Next we will Understand and calculate WACC, the only factor remaining that we need to have the present value of the free cash flow and fill all the missing info in our DCF
5. calculate the Weighted Average Cost Of Capital
Before we even begin what I want to do is just discuss what WACC is and why we use it so WACC is the weighted average cost of capital it is a blended rate of return for all the capital providers of a company technically they’re if a company had preferred stock you could include that in the WACC calculation now many practitioners don’t do this because preferred is not a very common part of capital structure and if it is it’s it’s usually pretty small but nonetheless from an academic standpoint es you would want to include it now.
You’re probably saying well why are we using WACC as the discount rate for our cash flows well just to recall this is an unlevered free cash flow analysis to recall what an unlevered free cash flow analysis is it is before the payments of interest EBI is before interest expense in other words these are cash flows that are available to all providers of capital not just the equity investors had we used a levered free cash flow approach the appropriate discount rate would have been cost of equity now WACC as you can see accounts for everyone that and so to make this an apples-to-apples analysis we should use WACC to discount these cash flows back to the present now that we understand why we use WACC let’s now start tackling this section so the first thing we’re going to have to do is reference share price so given that our valuation date is Mai 2018 let’s reference the share price on that date
Regarding the diluted share count we’re going to use the x number of shares you see now what do we mean by diluted shares outstanding well company has basic share account but you typically get from the front page of the filing but that’s not really all the claims of ownership on a company dilution accounts for all the different ownership claims so those claims are rising from options warrants convertible debt or convertible preferred that are in the money so we want to account for all the different ownership claims that’s why we’re using diluted share account
Next in our list you see cost of debt and we’ve got tax rate and then after-tax cost of debt well you’re probably wondering well why are we using after-tax cost of debt well to answer that question let’s take a look at our free cash flow build up as we mentioned before this is an unlevered free cash flow approach which is before the payments of interest but we know something very special about debt and that is interest expense provides a real tax shield to companies who pay taxes of course so that interest expense reduces taxable income in some cases a substantial amount so because we’re doing an unlevered free cash flow analysis some might think we’re ignoring the effect of interest tax shields the fact is we are accounting for it we’re accounting for it in our WACC calculation and that’s why we take the after-tax cost of debt
This after-tax cost of debt represents the interest tax shield that the company experiences by using debt in their capital structure so to calculate after-tax cost of debt would take cost of debt times 1 minus the tax rate and we get 3.7 percent so you can see that that is significantly though lower than the 5 percent and again it’s because of the interest tax shield now cause of debt is not a very highly debatable topic if you’re dealing with a company usually use yield to worst and if you’re using comparable company debt you usually use yield to maturity practitioners as well as academics don’t really butt heads on this cost of equity on the other hand is a highly debatable topic and you know Business School professors versus practitioners tend to disagree on what should what cost of equity should be and it’s highly debatable compared to cost of debt because with cost of debt you know what you’re getting principal plus interest expense with cost of equity you don’t really know what you’re getting because it’s a combination of potential dividend payments and price appreciation so some of the competing models that exist are farm of french — dividend discount model as well as capital asset pricing model now we’re going to focus on what practitioners use which is capital asset pricing model and that is equal to the risk-free rate plus beta times the market risk premium now this is essentially the formula for the security market line of a given market so assume that we did that analysis and we came up with 12.36%
By the Way the market risk premium is very subjective you can see the market risk premium table depending on what type of average you take (geometric , Arithmetic) the market risk premium varies a lot. To make is consistent we took the Risk Premium Arithmetic Average for the period 1928–2016 which is 7.96.
So debt holders request 12 that we know what our costs are for debt and equity let’s now figure out what our capital structure what our target capital structure will be now that’s sort of an important point you want to in your WACC calculation use what’s the target capital structure for most mature companies which we will assume for our model here the existing capital structure is the target capital structure is the target capital that you typically want to use market values for both debt and equity but many times you don’t have market values for debt so what you want to do is if you know your valuation date is let’s say in this case May 2018 the 2017 the latest numbers available before that balance sheet numbers will be valuation date so we could go ahead and use our debt as well debt figure from 2017 given that that book value is a good proxy for market value so to summarize if you don’t have market value figures for debt book values and acceptable proxy so let’s go ahead and reference the book value while our equity is going to be share price times the diluted share count
So my debt weighting is going to be the debt divided by total capital while the equity weighting is equity divided by the total capital you can see that this adds up to one hundred percent and it should and again if there were preferred stock you would do the same thing figure out the amount of preferred that’s in the capital structure and apply a weighting there as well so now that we have our debt weighting as well as our equity weighting we can calculate WACC so WACC again it’s going to be a blended rate of return so if we have our equity weighting we can multiply that by cost of equity plus our debt weighting times after-tax cost of debt and this should give us twelve percent so that’s going to be our cost of capital now if we go ahead and link that into our model you’ll see that our cash flows will start to update with the appropriate present values because we’ve now inserted the discount rate and now we’re going to assume that WACC doesn’t change in the terminal value period I mean for he terminal value so we’re going to use twelve percent again and again our stage two has now been updated with the correct present value so now that we’ve actually calculated WACC and brought that into the model we could go ahead and calculate enterprise value
6. Discover our Intrinsic Value and its meaning
we could go ahead and calculate enterprise value which is the sum of our stage one Plus stage 2 but again we’re concerned with equity value so we need to go from enterprise value to equity value how do we do that we need to subtract out net debt in other words equity value equals enterprise value less net debt okay so keeping this formula in mind we’ll go ahead and calculate net debt
I know most of you are thinking well we’ll just use plain vanilla debt the truth is you want to use all gross debt or all non equity claims so such items could include gross debt I mean debt as well as preferred stock minority interest really anything that’s considered an on equity claim we want to subtract out from enterprise value to arrive at equity value in our case we’re using a simple example all we have is debt and we’re going to subtract out cash because we’re dealing with net debt in other words net debt is equal to debt less cash and cash equivalents and the whole idea with net debt on Wall Street is that practitioners believe that if you have excess liquidity you can go ahead and pay off some of that debt so we’re going to assume that so enterprise value less net debt provides our equity value now if we bring in our diluted share count 500 we can figure out on a per share basis what the equity value per share is
We see that our equity value is 192 so if we take a look at this question if the stock is trading at 174.72 bucks a share and we believe that the DCF analysis is accurate would we buy or sell the stock?
I believe that if if our DCF analysis is indeed correct what we’re going to want to do is we’re going to want to buy this stock well the reason is because if we believe that the stock should be valued at 192 dollars and it’s valued at 174 a share then the stock is considered cheap so we would want to go ahead and buy it okay so now you can see how you can use this this really powerful analysis to make decisions for investments or trading or whatever it is you might be doing with the DCF so just to quickly recap what we did again we forecast it free cash flows into the future we discounted them back to the present using a discount rate that reflects the riskiness of the capital that gave a stage 1 we then went ahead and calculated stage 2 which is value beyond the explicit forecast period and that gave us a stage 2 after calculating stage one and stage two we calculated WACC we’re using WACC because we want to use a discount rate that’s available to all providers of capital so then we did worry about the discount stage one and stage two back to the present adding both together gives us enterprise value subtracting out all mod equity claims it gives us equity value and dividing by the looted share account gives us an equity value per share to which we can compare market value to make a decision. You can visit FinancialModelingPrep for more details.
