DCF Valuation Modeling

Equity Value formula Second Method - answer-LFCF/WACC =Equity Value Not as common method Levereged Free Cashflow Only from equity Providers For a sensitivity analysis what is the excel shortcut - answer-Alt A W T For row input you put terminal value growth rate For Column input cell you put WACC How do we evaluate the importance of the model inputs? - answer-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. How do you forecast tax in a DCF for the terminal value Column (Also Called Term in model) - answer-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 How long into the future should we forecast in a DCF? - answer-In perpetuity (This is a common interview question) How to find the fraction of a year using an excel function? - answer-=YEARFRAC(Start Date,End Date) How to insert a combo Box - answer-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 If a company is a going concern how do you adjust depreciation? - answer-You set depreciation equal to capex 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 - answer-1. Enterprize Value 2. Equity Value 3. Equity Price Per share 4.Premium/Discount to stock price Net Income Method Formula - answer-Net Income +Depreciation +Deferred Tax +Interest Expense -Tax Shield from interest -Capital Expenditure -Change in NWC =Unleveraged Free Cashflow Perpetuity Growth formula - answer-PV1=CF2/(R-G) PV=Present Value of cashflow CF=Cashflow of the next year R= Required Return G=Growth Rate T/F Unleveraged FCF ->WACC Levereged FCF-> Cost of Equity Enterprise Value ->Unleveraged FCF and WACC Equity Value -> Levereged FCF and Cost of Equity - answer-True The time value of money is also called the _______ - answer-The time quantity of money Two reasons why model design is important? - answer-1. It will result in a better financial model in the end 2. It will save large amounts of time on the model WACC formula - answer-WACC= (WD*RD)+(WE*RE) WD- Weight of debt RD- Cost of debt WE- Weight of equity RE- Cost of equity What are the differences between the two tax schedules in a DCF (Levered VS Unlevered) - answer-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 What are the most important inputs in a financial model? - answer-Drivers What are the three types of valuation techniques? - answer-Discounted Cashflow Analysis Comparable Trading Analysis President Transaction analysis What are the two methods to calculate Unlevered Free Cashflows? - answer-1. EBITDA Method 2. Net Income Method What are the two parts of a DCF? - answer-1. Discrete Forecast 2. Terminal Value What are the two parts of a DCF? - answer-1.Discrete Forecast 2. Terminal Value What are the two roles that financial models fill? - answer-1. Help with decision making 2. Helps communicate with stakeholders What are the two types of tax schedules in a DCF? - answer-1. Levered 2. Unlevered What are three important dates to consider for valuation and timing - answer-1. Fiscal Year End 2. Cashflow Timing Date (Date we expect to have cashflows) 3. Valuation Date What are two benefits of making a compacted DCF model - answer-1. Helps us learn the main features of a DCF model 2. Helps in a situation where we need a quick analysis What are two excel functions you can use to model best case, worst case and base case? - answer-=Choose() =Index() What are two important dates in a DCF - answer-1. Timing of the cashflows 2. Date of Valuation What cashflows do you discount to get Enterprise Value? - answer-Unleveraged Free Cash Flows What do you need for the WACC equation? A. Cost of debt after tax B. Cost of debt pre-tax - answer- What does Ctrl F1 do in excel? - answer-It displays the ribbon What does Enterprise Value represent? - answer-Our view of the companies value What does the F4 key do? - answer-Repeats the last command you did Ex. Change font color What excel formula do you use to calculate the WACC? - answer-=SUMPRODUCT(weight of capital, Cost of Capital) What excel formula should you use to discount using different times? - answer-=XNVP(WACC,Total Unadjusted Cashflow, Dates) *Do not include the Terminal Year *Enter a 0 for Year 0s cashflow *Includes leap years Wh