DCF Valuation Modeling

Ace your homework & exams now with Quizwiz!

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


Related study sets

Notes: Chapter 17 The Uterus and the vagina

View Set

Chapter 32: Skin integrity and wound care

View Set

Interpersonal Communication Midterm

View Set