DCF Valuation Modeling
Equity Value formula First Method
(ULFCF/WACC) - Net Debt = Equity Value
What is the adjustment we do to make unlevered cashflows into leveraged cashflows?
+Interest Expense -Tax Shield from interest =Total Tax Shield
What is the order when calculating equity value per share from an unleveraged DCF
1. Calculate Enterprise Value 2.Subtract Debt 3. Add Cash 4. Divide by fully diluted shares outstanding
What are the two parts of a DCF?
1. Discrete Forecast 2. Terminal Value
What are the two methods to calculate Unlevered Free Cashflows?
1. EBITDA Method 2. Net Income Method
In what order do you solve for the following using data tables? Equity Value Premium/Discount to stock price Enterprize Value Equity Price Per share
1. Enterprize Value 2. Equity Value 3. Equity Price Per share 4.Premium/Discount to stock price
What are three important dates to consider for valuation and timing
1. Fiscal Year End 2. Cashflow Timing Date (Date we expect to have cashflows) 3. Valuation Date
What are the two roles that financial models fill?
1. Help with decision making 2. Helps communicate with stakeholders
What are two benefits of making a compacted DCF model
1. Helps us learn the main features of a DCF model 2. Helps in a situation where we need a quick analysis
Two reasons why model design is important?
1. It will result in a better financial model in the end 2. It will save large amounts of time on the model
What are the two types of tax schedules in a DCF?
1. Levered 2. Unlevered
When making a data table for enterprise value what function do you use?
1. Link up enterprise value using the perpetuity method schedule 2.Select the box 3. press "alt D T" to bring up data table 4. In the row input cell you put in the Terminal Growth Rate 5. In the column input cell you put in the WACC`
What three variables impact the time quantity of money?
1. Quantity 2.Inflation Rate 3.Inflation and Erosion
3 Key points on model drivers?
1. The two most important model drivers are sales volume and sales price. 2. You start by finding the minimum and maximum levels of the model drivers. 3. Only model drivers need to be tested
What are two important dates in a DCF
1. Timing of the cashflows 2. Date of Valuation
What are the two parts of a DCF?
1.Discrete Forecast 2. Terminal Value
What is the chronological order of making a DCF model? (Simplified)
1.Outputs 2.UFCF 3.Drivers 4.Inputs
What are two excel functions you can use to model best case, worst case and base case?
=Choose() =Index()
What is the formula for Unlevered Free Cashflow?
=EBIT- Taxes + Depreciation & amortization - Capex -+ non-cash working capital
What how do you make the discrete forecast excel formula?
=EDATE(today, # of months in future)
What excel formula do you use to calculate the WACC?
=SUMPRODUCT(weight of capital, Cost of Capital)
What excel formula should you use to discount using different times?
=XNVP(WACC,Total Unadjusted Cashflow, Dates) *Do not include the Terminal Year *Enter a 0 for Year 0s cashflow *Includes leap years
How to find the fraction of a year using an excel function?
=YEARFRAC(Start Date,End Date)
Advantages and Disadvantages of Precedent Transaction Analysis
Advantages: Readily available and observable data Shows market for sale or purchase of company Disadvantages: Hard to find perfectly comparable and recent transactions
Advantages and Disadvantages of Discounted Cashflow Analysis
Advantages: One of the soundest valuation techniques Provides an opportunity to learn about the business Disadvantages: Requires a-lot of inputs and the model is only as good as the inputs used
Advantages and Disadvantages of Comparable Trading Analysis
Advantages: Readily available and observable data Efficient Pricing Disadvantages: No peer companies are exactly the same as the target company.
Which of the following is a true statement with regards to WACC? A. We use the pre-tax cost of debt in the WACC calculation due to the interest expense tax shield B. We use the book value of equity for the equity weighting when calculating the WACC
After Tax cost of debt in the Wacc Calculation to capiture the interest expense tax shield A country risk premium should also be included in the cost of equity calculation The equity weighting in the WACC calculation is based on the market capitalization not the book value of equity
For a sensitivity analysis what is the excel shortcut
Alt A W T For row input you put terminal value growth rate For Column input cell you put WACC
What is the Levered Beta formula in the Model?
Averaged Unlevered Beta*(1+(1-Tax Rate)*(Debt/Equity ratio))
Which of the following is true? A. Current taxes are cash taxes due in the near future. B. Deferred taxes are cash taxes due in the near future. C. A common tax concession offered to companies across many different tax jurisdictions is the deductibility of goodwill. D. IFRS/GAAP and tax accounting rules always match.
B. Current taxes are cash taxes due in the near future.
Why is the Working capital schedule important in a DCF?
Because it affects cashflow
Discounted Cashflow Analysis
Builds a model of a company to get the present value of all future free cash flows (Absolute Valuation Technique) (Shows Your View on valuation)
A DCF is an example of a _______ valuation A. Relative B. Cost C. Absolute Valuation D. Intrinsic Valuation E. Market Valuation
C & D: Absolute and Intrinsic Valuation
Define Unlevered Free Cashflow
Cash flow available to all capital providers
What must you do before making a DCF model?
Create an Operational Model
What is the keyboard shortcut for Zooming in and out?
Ctrl Alt + or Ctrl Alt -
What is the excel shortcut to group tabs?
Ctrl Shift Pg up/dn
What is the tax shield formula?
Current Taxes (Unlevered) - Current Taxes (Levered) = Total Tax Shield
What is least expensive Debt or Equity?
Debt
Debt to capital formula
Debt/(Debt+Equity)
What is the debt equity ratio and what is an alternative for equity?
Debt/Equity Alternative: Debt/Market Capitalization
How to insert a combo Box
Developer Tab ->Insert -> Combo box -> Drag mouse to make box -> Right click combo box-> select format control-> Input range as best case, worst case and base case -> Link cell to the Driver switch
What are the three types of valuation techniques?
Discounted Cashflow Analysis Comparable Trading Analysis President Transaction analysis
What are the most important inputs in a financial model?
Drivers
EBITDA Method Formula
EBITDA -Current Taxes -Capital Expenditure -Change in NWC =Unleveraged Free Cashflow *More Common in Capital Market Groups Shorter to use Shows EBITDA (A profitability Measure)
What is the Enterprise Value in a DCF using Free Cashflows
Enterprise value = PV of UFCF/WACC
What is more expensive to finance Debt or Equity?
Equity
Equity Value formula
Equity Value = Enterprise Value - Net Debt
What if the formula for Equity Value per Share?
Equity Value/Shares Outstanding = Equity Value per Share
Before you make data tables you must check excel for what?
File->Options->Formulas Under workbook calculations make sure its set to "Automatic" not "Manuel" or "Except for data tables."
How long into the future should we forecast in a DCF?
In perpetuity (This is a common interview question)
What does Ctrl F1 do in excel?
It displays the ribbon
When is the start and end of the discrete forecast?
It starts when the company is growing faster than the overall economy and ends when the company grows at the same rate of the economy
Equity Value formula Second Method
LFCF/WACC =Equity Value Not as common method Levereged Free Cashflow Only from equity Providers
What is the levered Beta Formula?
Levered Beta*(1+(1-Tax Rate)*Debt Equity Ratio)
What are the differences between the two tax schedules in a DCF (Levered VS Unlevered)
Levered Tax Schedule Starts with EBT Shows taxes with debt in capital structure We need it for tax lines in the income statement Unlevered Tax Schedule Startes with EBIT Shows taxes without debt in capital structure We need it to calculate the tax shield
Define Precedent Transaction Analysis
Looks at the acquisition prices for similar peer companies in recent transactions. (Relative Valuation Technique) (Shows Acquirers "Buyers" view on valuation)
Define Comparable Trading Analysis
Looks at the valuation for similar peer companies that are publicly traded (Relative Valuation Technique) (Shows Market view on valuation)
What is an inportant thing to remember when making data tables
Make sure that the X and Y axis is stated as a value and not linked to any other cells Make sure the Terminal Growth rate is on the middle on the X axis Make sure that the WACC is in the middle on the y-axis
You don't really have a solid understanding of a business until you have ______
Modeled it
Net Income Method Formula
Net Income +Depreciation +Deferred Tax +Interest Expense -Tax Shield from interest -Capital Expenditure -Change in NWC =Unleveraged Free Cashflow
What does Enterprise Value represent?
Our view of the companies value
Whos view is the PP&E and Tax Basis of your assets?
PP&E = Accounting View of assets Tax Basis = Governments view of assets
Perpetuity Growth formula
PV1=CF2/(R-G) PV=Present Value of cashflow CF=Cashflow of the next year R= Required Return G=Growth Rate
What is the After Tax Cost of Debt Formula?
Pre Tax Cost of Debt*(1-Tax Rate)
What is the preferred model layout to communicate with stakeholders?
Presentation (Includes 1-3) 1. Cover Page 2. Outputs 3. Inputs (Includes Drivers, WACC, and Inputs) Appendix is kept separate from the presentation but is what drives all the material stakeholders look at. It includes Revenue costs, Unleveraged Free Cashflows and Discounted Cashflows
What is the formula for the cost of debt?
RD=RP(1-T) RD=Cost of debt after tax RP= Cost of debt pre tax T= Tax rate
What is the formula for the cost of equity
RE=RF+RC+(RM*BL) RE=Cost of Equity RF= Risk free rate RC= Country Risk Premium RM= Market Risk Premium BL= Levered Beta
What does the F4 key do?
Repeats the last command you did Ex. Change font color
When is the start and end of a Terminal Value forecast?
The Terminal value forecast starts when the company experiences the same amount of growth as the overall economy and then continues forever (Using a growing perpetuity formula)
Define going concern
The business is going to operate indefinitely into the future.
Define Weighted Average Cost of Capital
The cost of capital from all capital providers
The time value of money is also called the _______
The time quantity of money
What is Net Debt formula?
Total Debt - Cash = Net Debt
T/F Unleveraged FCF ->WACC Levereged FCF-> Cost of Equity Enterprise Value ->Unleveraged FCF and WACC Equity Value -> Levereged FCF and Cost of Equity
True
Enterprize Value formula
ULFCF/WACC=Enterprise Value *Both numerator and denominator represent all capital providers
What cashflows do you discount to get Enterprise Value?
Unleveraged Free Cash Flows
What is the Unlevered Beta formula?
Unlevered Beta/ (1+ (1-Tax Rate)*Debt Equity Ratio)
What is the simplified formula to discount the cashflows
Unlevered Free Cash Flow (UFCF)/ Weighted Average Cost of Capital (WACC)
WACC formula
WACC= (WD*RD)+(WE*RE) WD- Weight of debt RD- Cost of debt WE- Weight of equity RE- Cost of equity
How do we evaluate the importance of the model inputs?
We isolate the drivers, by their importance on the model. They should have the biggest impact on the volitility and carry a significant impact on the outputs.
What is the chronological order of making a DCF model? (Full)
We work backwards from outputs to our inputs 1. Outputs 2. Unleveraged Free Cashflows 3. Income Tax Calculations 4. Asset Schedule 5. Depreciation Schedule 6. Working Capital Schedule 7. Income Statement 8. Revenue Schedule 9. Cost Schedule 10. WACC 11. Model Drivers 12. Inputs
How do you forecast tax in a DCF for the terminal value Column (Also Called Term in model)
You set deferred tax to 0 since the government does not allow corporations to defer taxes into the future indefinitely. For Current tax you get EBT*Tax rate
If a company is a going concern how do you adjust depreciation?
You set depreciation equal to capex