DCFModelingCourseManual_541c69f0a5dd8

DCF Modeling Copyright © by Wall Street Prep, Inc.

Check out other great products at www. www.wallstreetprep.com wallstreetprep.com Self Study Programs

Live Boot Camps & Webcasts

• • • • • • • • • •

Comparable Company Modeling Comparable Transaction Modeling Discounted Cash Flow (DCF) Modeling Leveraged Buyout (LBO) Modeling Mergers & Acquisitions (M&A) Modeling Advanced Financial Modeling Modeling Crash Course in Accounting Reading Financial Reports Crash Course in Excel How to Ace the Investment Banking Interview

Contact us

617-314-7685 800-646-3575 (toll-free) www.wallstreetprep.com

Licensed to Sheikh Sadik. Email address: [email protected]

Check out other great products at www. www.wallstreetprep.com wallstreetprep.com Self Study Programs


• • • • • • • • • •

Comparable Company Modeling Comparable Transaction Modeling Discounted Cash Flow (DCF) Modeling Leveraged Buyout (LBO) Modeling Mergers & Acquisitions (M&A) Modeling Advanced Financial Modeling Modeling Crash Course in Accounting Reading Financial Reports Crash Course in Excel How to Ace the Investment Banking Interview

Contact us



Terms of Use The tutorial and enclosed models are proprietary to Wall Street Prep and are designed for illustrative and training purposes only. only. Distributing, sharing, copying, copying, duplicating or altering these models in any way is prohibited without the expressed, written permission of Wall Street Prep, Inc. The Self Study Program is designed for illustrative purposes only and does not, in any way, way, constitute any investment thesis or recommendation.

Copyright Wall Street Prep, Inc. All rights reserved. "Wall Street Prep", "Wall Street Prep", and various marks are trademarks of Wall Street Prep, Inc.


Table of contents SECTION 1: OVERVIEW DCF in theory and in practice Unlevered vs. levered DCF SECTION 2: MODELING THE DCF Modeling unlevered free cash flows Discounting to reflect stub year and mid-year adjustment Terminal value using growth in perpetuity approach Terminal value using exit multiple approach Calculating net debt Shares outstanding using the treasury stock method Modeling the weighted average cost of capital (WACC) Sensitivity analysis using data tables Modeling synergies


Introduction to valuation Intrinsic value • The value of a business equals the sum of all the cash flows it will generate, discounted to the present value using a discount rate that reflects the riskiness of the business.

t=n

Valuet = ∑ t=1

Cash flowt (1+discount rate)t

Hot dog stand 

It is January 1, 2007. Your hot dog stand is expected to generate $10,500 in 2007, and cash flows are expected to grow 5% each subsequent year.



Assuming a discount rate (r) of 10%, what is the present value of cash flows generated in the first 5 years?


Introduction to valuation Hot dog stand 

Assuming a discount rate of 10%, what is the present value of cash flows generated in the first 5 years? Discount each year’s cash flows by the discount rate:

Year

Cash flow

Cash flow / (1+r)t

2007

10,500.0

9,545.5

2008

11,025.0

9,111.6

2009

11,576.3

8,697.4

2010

12,155.1

8,302.1

2011

12,762.8

7,924.7

Sum:

43,581.21


Introduction to valuation Growth in perpetuity • The value of a business with a perpetual growth rate g is equal to next year’s cash flows divided by the discount rate minus the growth rate. • This is a well-established perpetuity formula in mathematics.

Cash flowst+1 Valuet = discount rate growth rate –

Hot dog stand 

Continuing with our example, assuming the business grows at 5% in perpetuity, what is the value of the hot dog stand today?


Introduction to valuation Hot dog stand 

Continuing with our example, assuming the business grows at 5% in perpetuity, what is the value of the hot dog stand today? Using the growth in perpetuity formula, we can arrive at a value for the hot dog stand as illustrated:

Valuet

=

10,500.0

=

210,000.0

(10% - 5%)


DCF in theory and in practice DCF in theory

• The DCF valuation approach is based upon the theory that the value of a business is the sum of its expected future free cash flows, discounted at an appropriate rate. • Discounted cash flow (DCF) analysis is one of the most fundamental, commonly-used valuation methodologies. It is a valuation method developed and supported in academia and also widely used in applied business practices. DCF in practice

• There is no consensus on implementation – controversies predominantly over the estimation of the cost of equity. • Extremely sensitive to changes in operating, exit and discount rate assumptions. • That said, there are general rules of thumb that guide implementation. Two-stage DCF model is prevalent form

• • • •

The prevalent form of the DCF model in practice is the two-stage DCF model. Stage 1 is an explicit projection of free cash flows generally for 5 -10 years. Stage 2 is a lump-sum estimate of the cash flows beyond the explicit forecast period. In addition to the two-stage DCF, there are multi-stage manifestations of the DCF model (3-stage, high-low models, etc.) designed to more clearly identify cash flows generated at different phases in a firm’s life cycle. • We will focus on the two-stage model in this course, given its prevalence in practice. Licensed to Sheikh Sadik. Email address: [email protected]

DCF in theory and in practice Two-stage DCF model Stage 1: Free cash flow projections Stage 2: Terminal value • What is the projected operating and • We cannot reasonably project cash flows financial performance of the business? beyond a certain point. • Typical projection period is 5-10 years • As such, we make simplifying assumptions about cash flows after the explicit projection • How do we calculate free cash flows period to estimate a terminal value that represents the present value of all the free t=n FCFt cash flows generated by the company after Valuet = ∑ the explicit forecast period. (1 + r)t t=1 • Analysts use both the perpetual growth and exit multiple methods to estimate terminal value Discount rate Both stages should be discounted to the present using a rate that appropriately reflects the cost of capital (much more on this later)

Valuet =

FCFt+1 r – g

Valuet = Exit EBITDA x multiple


DCF in theory and in practice DCF Advantages  Theoretically, the most sound method of valuation  Less influenced by temperamental market conditions or non-economic factors  Can value components of business or synergies separately from the business

Two-stage DCF model visualized Discounted Ca sh Flows

450.0

400.0

400.0 350.0 s 300.0 n o i 250.0 l l i m200.0 n i $ 150.0 100.0

45.0

49.5

54.5

59.9

65.9

72.5

79.7

87.7

50.0 DCF Disadvantages 0.0  Present values obtained are sensitive 2005 2006 2007 2008 2009 2010 2011 2012 to assumptions and methodology Years  Terminal value represents a significant portion of value and is highly sensitive to valuation assumptions  Need realistic projected financial statements over at least one business cycle (7 to 10 years) or until cash flows are “normalized”  Sales growth rate, margin, investment in working capital, capital expenditures, and terminal value assumptions along with discount rate assumptions are key to the valuation




Unlevered vs. levered DCF Unlevered free cash flows (FCF)

• An investment property should theoretically equal the present value of its rental income (minus required maintenance). In the same way, the enterprise value of a firm equals the present value of its FCF, where the appropriate FCF are before the effect of leverage (meaning FCF has not been adjusted down for interest or principal payments). Such cash flows are called unlevered FCF.

“rental income is

unlevered, rental income minus mortgage is levered”

• Isn’t rental income the same regardless of how the rental property was financed? Isn’t it fair to say that the leverage does not affect the expectation of future rental income? Similarly, debt-related payments such as interest expense and principal are ignored (i.e. we pretend there is no debt, or resulting interest expense and tax shield) when calculating FCF used to determine the enterprise value. • Of course, when discounting the unlevered FCF back to the present, they must be discounted at a rate commensurate with the riskiness of the cash flows – which is absolutely a function of leverage. In other words, even though the expectation of rental income is unaffected by leverage, the discount rate applied to determine the present value of expected rental income is indeed a function of the capital structure and expected returns of the providers of capital. • Indeed, unlevered FCF should be discounted using the weighted average cost of capital (WACC), reflecting the costs of debt and equity weighted by their respective proportions of the total capital invested in the enterprise. Licensed to Sheikh Sadik. Email address: [email protected]

Unlevered vs. levered DCF Levered free cash flows (FCF)

• If I project future rental income of an investment property and discount it to the present using the WACC, the value obtained will be the total value of the property, or enterprise value. I can subtract all non-equity claims from this value to arrive at my equity value. This approach at arriving at equity value is called the unlevered DCF approach because unlevered FCF are projected and discounted. • Alternatively, If I project future rental income minus interest and principal (i.e. mortgage) and discount it to the present at the cost of equity, I will directly arrive at my equity value. • This approach is called the levered DCF approach because the projected cash flows are levered. In other words, interest expense, the interest tax shield, and principal payments are explicitly projected in the calculation of cash flows, which represent cash flows only to equity holders, and the discount rate reflects the cost of capital only to equity holders. • Both approaches should theoretically yield the same equity value. • The unlevered DCF approach is the predominant approach for valuing most businesses because it is easier to implement1 (with the exception of the valuation of financial institutions, for which levered DCF is preferred).

In line with standard practice, we will focus on unlevered DCF in this module

Footnote 1 For the DCF analysis to be usable, we must assume a relatively stable capital structure for the life of the business. When using the levered DCF approach and explicitly projecting interest expense and principal payments in a way that maintains a stable debt/equity ratio, we would have to explicitly recalibrate debt/equity weights each year, possibly along with the costs of debt and equity themselves. This is far more Licensed to Sheikh Sadik. Email address: [email protected] difficult to implement than the unlevered DCF approach.

Unlevered vs. levered DCF The process of DCF analysis Step #1: Projecting free cash flows (FCF)

Step #2: Calculating the terminal value

Project unlevered free cash flows over forecast period (typically 5-10 years)

Estimate the value of the enterprise at the end of the forecast period

Discount at the cost of capital

Enterprise value (value of operations) Less: Equals:

Net debt Equity value Divided by diluted shares outstanding

Equals:

Equity value per share


Unlevered vs. levered DCF DCF Exercise

• Shares of company XYZ currently trade at $20 per share. • The company has net debt (debt outstanding less cash & equivalents) of $400 million and 400 million diluted shares outstanding. • You have projected free cash flow for XYZ for each year through 2015, estimated the terminal value in 2015 , and calculated WACC as illustrated.

Valuation date:

January 1, 2008

Free cash flows for year ending December 31: 2008

415.0

2009

546.0

2010

594.0

2011

652.0

2012

723.0

2013

781.0

2014

812.0

2015

843.0

Terminal value in 2015 Weighted average cost of capital

1,200.0 10%

Assuming all cash flows are generated at year-end:

1. Estimate the present value of the projected FCF discounted at the stated WACC Discount the terminal value back to the present 2. Calculate XYZ’s implied enterprise value 3. Calculate XYZ’s equity value 4. Derive fair value per share 5. According to the DCF valuation method, are XYZ’s shares overvalued or Licensed to Sheikh Sadik. Email address: [email protected] undervalued?

Unlevered vs. levered DCF Solutions 1. Estimate the PV of the projected FCF discounted at the stated WACC unlevered free cash flowst = $3,419.8 million Note: Use of financial ∑ (1+wacc)t calculators or the NPV t=n t=1 function in Excel is helpful here 2. Discount the terminal value back to the present

terminal value (1+wacc)final projection year

=

$559.8 million

3. Calculate XYZ’s implied enterprise value

PV of unlevered free cash flows + PV of terminal value = $3,979.7 million 4. Calculate XYZ’s equity value

Enterprise value – Net debt = Equity value = $3,979.7 - $400 = $3,579.7 million 5. Derive fair value per share Equity value / Diluted shares outstanding = $3,579.7 / 400 = $8.95 per share 6. According to the DCF valuation method, are XYZ shares overvalued or undervalued? Answer: overvalued by: $20.00 - $8.95 = $11.05 Licensed to Sheikh Sadik. Email address: [email protected]

Unlevered vs. levered DCF Integrity of projections

• • • •

DCF is quite sensitive to assumptions for FCF projections, terminal value assumptions, and changes in the discount rate Care should be taken to make reliable assumptions. If assumptions are not reasonable and thoughtful, the results of DCF will reflect this “Garbage in, garbage out” Do not explicitly change discount rate assumptions to adjust value of model – only in sensitivity analysis

Sensitivity analysis

• • •

DCF valuation is based on assumptions – assumptions for future free cash flows, for the terminal value, and for the discount rate Therefore, DCF values should be represented in ranges Key assumptions to sensitize: – Discount rate – Sales growth – EBITDA margin – WACC – Exit multiple – Perpetuity growth rate Licensed to Sheikh Sadik. Email address: [email protected]



Modeling the DCF Our case study is Colgate (ticker: CL)

• In our case study, we are performing a DCF valuation on Colgate on April 13, 2007. • As of April 13, 2007, CL shares were trading at $67.42 and the latest CL financial report available is CL’s fiscal year 2006 10-K. Before proceeding, make sure you have downloaded the following files: 1. Colgate’s 10-K for fiscal year ending December 31, 2006. 2. The DCF model • DCF model includes a tab with a completed financial statement model from the financial statement modeling module. • The DCF model also includes a completed DCF analysis, as well as a blank DCF template. We will be working together through the empty template, building the model step-by-step. • If at any point you run into questions, review the completed DCF tab for guidance.




Modeling unlevered free cash flows Unlevered free cash flows

Unlevered FCF represent the cash flows solely from the operating performance of the business – independent of leverage or non-operating investments. Think of unlevered cash flows as cash flows for an all-equity financed company with no non-operating assets. But Colgate does have debt, shouldn’t that be factored in somehow?

It should and it will – just not in the calculation of cash flows. Recall that to value a house, you would project rental income and then discount those cash flows, as opposed to projecting rental income less mortgage (i.e. interest) payments. It is the same idea here – we want to project cash flows for Colgate irrespective of its capital structure. The capital structure will be reflected later – in the estimation of the discount rate, as well as how many non-equity claims must be subtracted from the derived enterprise value to arrive at the equity value. About the tax deductibility of interest expense –doesn’t that provide real tax benefits?

Very true. We ignore this for now, but will factor it into the discount rate, by adjusting the company’s cost of debt by the tax shield. More on this later. Where do we factor in non-operating (investment) assets?

As an addition to the company’s enterprise value at the end of the analysis. Barring any explicit guidance otherwise, record the present value of these investments (excess cash, short-term investments, etc.) at their balance sheet (book) values. Licensed to Sheikh Sadik. Email address: [email protected]

Modeling unlevered free cash flows 

Unleveled free cash flows must be projected and then appropriately discounted to determine a present value of the company under analysis.



Since firms do not report this figure of free cash flows, analysts must make adjustments to information provided in the reported financial statements.

Start with EBIT

The typical starting point for calculating unlevered free cash flows is operating income (operating profit before interest and taxes, or EBIT) reported on the income statement.


Modeling unlevered free cash flows Arriving at unlevered free cash flows from EBIT: Free cash flow calculation

Historical

Projections

EBIT (Operating income)

•Income Statement (10-K Analyst research / 10-Q / PR / Company) Company •Use normalized EBIT Internal projections

EBIT (1 – tax rate) (Tax-effected EBIT, EBIAT or NOPAT)

Use effective tax rate

Use marginal tax rate

Plus: Depreciation and amortization Less: Increases in working capital assets 2 Plus: Increases in working capital liabilities Less: Increases in deferred tax assets Plus: Increases in deferred tax liabilities Less: Capital expenditures Less: Other required investments

CFS / IS / Footnotes

Analyst research Company Internal projections

Equals: Unlevered free cash flows Footnote – calculating levered free cash flows When valuing financial institutions, levered FCFs are projected to arrive at equity value directly. Projected income and cash flow streams are after interest expense and net of any interest income:

Net income - Increases in working capital +/- Deferred taxes + D&A - Capital expenditures +/- Net borrowing Licensed to Sheikh Sadik. Email address: [email protected] Levered FCF

Modeling unlevered free cash flows Projecting unlevered free cash flows With the exception of the discount rate and shares outstanding, we have already made most of our projections in the financial statement model. In the DCF the most important assumptions are:  EBIT and tax rate: From financial model  Depreciation & amortization: From financial model  Capital expenditures: From financial model  Changes in net working capital: From financial model  Synergies: Important in strategic acquisitions; estimate in dollar terms in year one and evaluate margin impact over time – usually projected separately from the core FCF build-up. To be estimated in the model worksheet.  WACC: To be estimated in the model worksheet.


Modeling unlevered free cash flows Always remember to:  Footnote assumptions in detail  Test your assumptions  Use consistent cash flows and costs of capital

Reference from core model

Input WACC of 10% for now

Calculation = days post-deal date / 365


Modeling unlevered free cash flows

Year formula =YEAR(D15) Insert dynamic date formula as illustrated



Reference revenues through tax rate from core model In Excel: 1. type ‘=‘ 2. Ctrl Page Up 3. Use arrow keys to find 2007 revenues 4. Hit Enter



Calculate EBIAT Also called NOPAT (Net operating profit after taxes)



Reference D&A and working capital inflows/outflows from core model as illustrated

Calculate total cash impact of working capital changes

Reference capital expenditures from the core model’s cash flow statement. Include any other required investments when applicable (acquisitions of intangible assets, etc.)



• Since the deal date is 4/13/0207 and the first projected year’s cash flows are for the full period 1/1/2007 through 12/31/2007, we need to subtract a portion of the projected free cash flows since they have presumably already been generated prior to the deal date. • Adjustment in the model: stub year fraction x year 1 projected free cash flow

Valuation year (year 1) EBIAT + D&A +/- Changes in working capital - Capital expenditures Unlevered free cash flows X stub year fraction Stub-adjusted free cash flows Post-valuation year EBIAT + D&A +/- Changes in working capital - Capital expenditures Unlevered free cash flows




Discounting to reflect stub year and mid-year adjustment Calculating present value of FCFs in the model Now we are ready to calculate the present value of the projected free cash flows. • We will assume a weighted average cost of capital of 10% for now (we will delve into the components of WACC later).

• Present value of cash flow stream: The present value calculation takes into account the cost of capital by placing greater value on those cash flows generated earlier in the projection period versus later cash flows1. PV of FCF

=

FCF1 + FCF2 + (1+wacc)1 (1+wacc)2

FCF3 + ……. FCFN (1+wacc)3 (1+wacc)N Where N = L ast projection year


Discounting to reflect stub year and mid-year adjustment

Discount free cash flows back to the present



Discount free cash flows back to the present


Discounting to reflect stub year and mid-year adjustment Mid-Year Adjustment

Since most businesses do not generate all of their FCF on the last day of the year, but rather in a generally continuous basis throughout the year, present value calculations of FCF are sometimes made using a “mid-year convention”, which takes into account the fact that FCF occur throughout the year. PV of FCFMY = FCF1 + (1+wacc)0.5

FCF2 +

FCF3

(1+wacc)1.5

(1+wacc)2.5

+

…FCFN (1+wacc)N-0.5

Where N = Las t projection year

Note that in the first year, discounting is actually 0.5 of the stub year fraction

1 See

appendix for detailed discussion of present value calculations







Calculate PV of FCFs




Terminal value using growth in perpetuity approach Terminal value

• • •

•

• •

We have calculated the present value of unlevered FCF for CL through 2015 Clearly the firm is expected to generate cash flows beyond this period, but we cannot project free cash flows on an annual basis indefinitely with any degree of accuracy As a result, we make simplifying assumptions at the end of the explicit projection period in order to calculate terminal value using a perpetuity formula – namely, we assume that after the explicit forecast period, the firm slows down into mature, normalized, and sustainable EBIAT, growth rates, and WACC The terminal value represents value of a business at the end of the projection period – Accounts for all the cash flows to be generated beyond the projection period – Assumes that the business is held in perpetuity and that the free cash flows continue to grow at an assumed perpetual rate The terminal value often represents a significant portion of the total enterprise value derived in a DCF model As a result, the assumptions used to arrive at the terminal value are extremely important

2 popular methods in practice

•

There are two common approaches to estimating the terminal value in practice: • Growth in perpetuity method • Exit multiple method Licensed to Sheikh Sadik. Email address: [email protected]

Terminal value using growth in perpetuity approach Terminal value – growth in perpetuity method

• Assumes that the business will grow in perpetuity after the explicit forecast period at a constant, long-term nominal industry-demand growth rate 1 with normalized FCF and sustainable WACC. • Implementation: The perpetuity growth method takes the free cash flow in the last year of the projection period and grows it one more year 2. This free cash flow is then divided by the discount rate (WACC) minus the perpetual growth rate: Terminal Value t =

FCF t+1 (WACC – g)

FCF t+1 = FCF in the last projected year * (1+g) WACC = weighted average cost of capital g = sustainable perpetual growth rate 1

2

No business can be expected to have cash flows that grow forever above the economy. Be conservative when estimating growth rates in perpetuity. In practice long-term nominal GDP growth is an acceptable proxy. Recall that the perpetuity growth formula is based on the principle that the terminal value of a business is the value of its next cash flow, divided by the difference between the discount rate and a perpetual growth rate. Licensed to Sheikh Sadik. Email address: [email protected]

Terminal value using growth in perpetuity approach Terminal value – growth in perpetuity method Additional considerations • If the estimated terminal year + 1 cash flows do not reflect “steady-state” relationships between revenues and EBIAT, working capital, capital expenditures and depreciation, they may need to be revised. • Commonly, higher-growth companies whose capital expenditures during the explicit forecast period were very high relative to depreciation and revenue growth will need to be adjusted when calculating the terminal year + 1 free cash flows to reflect lower required capital investments to sustain lower growth rates. Remember – since we are projecting macroeconomic terminal growth rates, companies should essentially be spending just enough to replace older equipment. • Working capital may also need to be adjusted to reflect realistic perpetuity working capital inflows/outflows. Discounting the terminal value to the present • Once calculated, the terminal value reflects the value at the end of the explicit projection period. • In order to calculate its present value, it must be discounted back to the valuation date using the relevant WACC.


Terminal value using growth in perpetuity approach

3-5% growth rate range Unless specifically instructed otherwise (with a corresponding justification), a 3-5% range is acceptable as the common norm in non-inflationary environments. A growth rate greater than 5% is difficult to justify because it implies company will grow faster than the economy – this is obviously unsustainable.





Mid-year adjustment The mid-year adjustment also applies to the growth in perpetuity formula, which otherwise assumes all future cash flows are generated at year-end.



Discounting Remember that the value obtained using the perpetuity formula is for the present value at the terminal year. This value must be discounted back to the valuation date.



As you can see, TV typically represents a significant portion of overall value • Care should be taken to reduce dependence on terminal value because it is highly sensitive to assumptions – typically by expanding the explicit projections when possible • What percentage of total value comes from the terminal value? Licensed to Sheikh Sadik. Email address: [email protected]



Terminal value using exit multiple approach Terminal value – exit multiple method

• In addition to (or instead of) the growth in perpetuity method, bankers often calculate terminal value by simply forecasting the purchase price of the business at the end of the explicit forecast period by applying a current “steady-state” multiple to some financial operating metric projected in the final year (usually EBITDA). • This method is used because it is simple to apply and requires fewer explicit assumptions about future cash flows, growth, etc. • Of course, the steady-state multiple selected incorporates implicitly all the assumptions about growth and cash flows embodied in the value driver formula. • Nonetheless, this method’s application is somewhat flawed because the multiple selected is typically derived from market-based valuation, which in turn, is used to determine intrinsic value. • Both methods are widely used in business valuation, but perpetuity growth method avoids the fundamental problem of applying a relative, market-based valuation to arrive at an intrinsic valuation.


Terminal value using exit multiple approach Terminal value – exit multiple method

This method assumes that the business will be valued/sold at the end of the last year of the projected period: 

Generally use an EV / EBITDA multiple: The terminal value is generally determined as a multiple of EBITDA (or any relevant statistic). This value will then be discounted to its present value using the calculated discount rate. If you select a different statistic, make sure you use an enterprise value multiple (vs. an equity value multiple) for unlevered DCF and vice versa.



Multiple should reflect steady-state industry multiple: A steady-state long-term industry multiple should be used rather than a current industry multiple, which can be distorted by contemporaneous industry or economic factors.



Multiple derived from comps: Steady-state industry multiples are generally derived from comparable trading and transaction analyses.



Do not apply mid-year adjustment: Since the derived terminal value represents the actual purchase price expected on the last day of the explicit projection period, we do not need to adjust the value for a mid-year adjustment.


Terminal value using exit multiple approach








Terminal value using exit multiple approach Exit multiple method – implied perpetual growth rate

• While the exit multiple method is used more commonly in practice for its simplicity, the growth in perpetuity method is more academically sound for the reasons discussed earlier.

Implied g = WACC –

FCF t+1 EBITDA t x multiple

• When using an exit multiple, we are making implicit assumptions about the cash flows, growth and riskiness of the business. • As a result, it is helpful to calculate the implied growth in perpetuity rate arising from the exit multiple method as a sanity check. If you are using an exit multiple whose implied perpetual growth rate is substantially above the macroeconomic growth rate, clearly you are being too aggressive in your assumptions (and vice versa).








Calculating net debt From enterprise value to equity value

• Now that we calculated enterprise value (read the DCF value of operations), our focus shifts to calculating equity value. Add non-operating assets • First, all non-operating assets (typically excess cash and other investments) must be added to the enterprise value. • Why? Understand that we just calculated expected cash flows generated from the operating assets of the business – the cash flows related to non-operating assets (i.e. interest income) were not reflected in the FCF calculation. • Instead the book value of these assets (as identified on the most recent 10-K or 10-Q) is typically used as a proxy for the intrinsic value of these assets (the book value of cash is, after all, typically the market value of cash, right?) Subtract non-equity claims • Next, all non-equity claims (debt and equivalents) must be subtracted to identify what the equity in the business is. • Include all non-equity claims on the business that have

Net Debt is defined as:

Short-Term Debt + Current Portion of LT Debt + Long-Term Debt + Minority Interest + Preferred Stock + Leases – (Cash + Investments) Net Debt

Net Debt

not been accounted for in the calculation of FCF.

• Common items are debt, preferred stock, minority Equity interests, leases. Value • Use the book values of these items as proxies for the Licensed to Sheikh Sadik. Email address: [email protected] market value unless instructed otherwise. For illustrative Purposes Only

Calculating net debt








Shares outstanding using the treasury stock method In order to calculate the equity value per share, we will need to calculate CL’s diluted shares outstanding, where latest diluted shares = latest basic shares + dilutive securities Dilutive securities Defined as securities that are not common stock in form, but that enable their holders to obtain common stock upon exercise or conversion. The most notable examples include stock options, warrants, convertible bonds, and convertible preferred stock. For general valuation analysis, dilution is only assumed from exercisable (vested) options. For M&A analysis, all outstanding options are assumed to be exercisable.

• Stock options are issued in stock compensation plans and used to pay and motivate employees. This type of security gives selected employees the option to purchase common stock at a given price over an extended period of time. • Warrants are similar to options. They are certificates entitling the holder to acquire shares of stock at a certain price within a stated period. period. When warrants warrants are exercised, the holder must pay a certain amount of money to obtain the shares. Also, when stock warrants warrants are attached to debt, debt, the debt remains after the warrants are exercised. • Convertible bonds – company issues bonds which can be converted into common shares. The conversion feature allows the corporation an opportunity to obtain equity capital without giving up more ownership control than necessary and/or entice investors to accept lower interest rates than they would normally accept on a straight debt issue. • Convertible preferred stock is similar to convertible debt, except that preferred stock, instead of debt, is originally origi nally issued.


Shares outstanding using the treasury stock method

Basic shares outstanding Latest count of basic shares outstanding (found on the front page of a company’s latest 10-Q or 10-K filed with the SEC)


Shares outstanding using the treasury stock method Options Information about options can usually be found in the footnotes of a company’s latest 10-K. 10-K. 10-Qs do not typically include options data.

This represents a fairly standard layout of the option data disclosed in a company’s option footnote (10-K only). Use options exercisable tranches for stand-alone analysis and options outstanding for M&A valuation.


Shares outstanding using the treasury stock method Treasury stock method

Treasury stock method assumes that all proceeds from exercised options and warrants are used to repurchase outstanding shares. Treasury stock method example:

Tranche 1: Tranche 2:

Options exercisable 1.5 million 2.0 million

Exercise price $10.00 $20.00

Status “in-the-money” “out-of-the-money”

Current stock price: $15.00 Dilutive effect from options: = 1.5 million – ($10.00 * 1.5 million) = 0.5 million shares $15.00


Shares outstanding using the treasury stock method Convertible preferred stock

• If the company has convertible preferred stock on its balance sheet, and the conversion price is below the offer value, the preferred stock may be converted to common shares as follows: Liquidation value of preferred shares1 Conversion Price2 • Companies typically issue preferred dividends, so remember that if we are assuming preferred stock is being converted to common shares, we must make an adjustment going forward to exclude this preferred stock from the balance sheet and any preferred dividends from the income statement. Shares issued =

Convertible debt

• Same mechanics as preferred stock • Convertible debt is converted to common shares as follows: Shares issued =

Book value of convertible debt Conversion Price2 • Companies typically pay interest on convertible debt, so if assuming conversion, exclude this debt from the balance sheet and any associated interest expense from the income statement. 1 Use 2 See

preferred stock value on balance sheet footnotes of the most recent 10-K


Shares outstanding using the treasury stock method Convertible securities Information about convertible securities can usually be found on the balance sheet and in the footnotes of a company’s 10-K and 10-Q’s.



Diluted shares outstanding Set up the shares outstanding schedule as illustrated



Diluted shares outstanding Set up the shares outstanding schedule as illustrated



Total $ proceeds • In-the-$ shares x avg. strike price • Calculate option proceeds using the SUMPRODUCT function Total shares repurchased • Proceeds / current share price



Convertible securities Information about convertible securities can usually be found on the balance sheet and in the footnotes of a company’s Licensed to Sheikh Sadik. Email address: [email protected] 10-K and 10-Q’s







Modeling the weighted average cost of capital (WACC) Weighted average cost of capital • In our model, we inputted a discount rate assumption of 10%. In practice, you may need to arrive at WACC through a more rigorous analysis. • In an unlevered DCF valuation, the weighted average cost of capital (WACC) is the appropriate discount rate because we are discounting free cash flows to all providers of capital. • The WACC, an important assumption in DCF analysis, represents the required rate of return of an investment given the risks associated with the business. An investor contributes capital with the expectation that the riskiness of cash flows will be offset by an appropriate return. • A more intuitive way to think about the discount rate is to think of it as the forecasted opportunity cost of investing in a particular business vs. in an alternative business with similar risk. • As such, the cost of capital is typically estimated by studying capital costs for existing investment opportunities which are similar in nature and risk to the one being analyzed.


Modeling the weighted average cost of capital (WACC) Capital structure • Since unlevered DCF assumes a constant WACC throughout the forecast period, a consistent unlevered DCF valuation requires that companies manage to a target capital structure – that is to say that management will maintain a stable ratio of the market value of its debt to the market value of its equity. • Substantial deviations from this assumption will require that users adjust their WACC assumptions for each projection period (both weights and cost of debt and equity). • In this case, other valuation models such as the adjusted present value model may be easier to work with. WACC in valuation for M&A • When performing a DCF for M&A, the cost of capital should be calculated based on the risk profile of the target, not the risk of the acquirer.


Modeling the weighted average cost of capital (WACC) WACC formula

Since most firms’ capital structure includes a combination of debt and equity to fund their operation, the overall cost of capital is the market-based weighted average of the cost of debt and the cost of equity. The formula for WACC is: WACC = [r debt ] x [1-tr] x [D/(D+E)] + [r

equity] x [E/(D+E)]

tr = marg inal tax rate D = market value of debt E = market value of equity

Market value of equity

Use the market value of the equity (dil. shares x market share price) to calculate the WACC (even though you are using a market-based valuation of equity to determine the weight, this calculation is designed only to provide context for a target capital structure). Estimating private company capital structure 1) Use an equity value derived from a comps analysis for the purposes of calculating capital structure, or: 2) Use the equity value derived in the DCF valuation to determine the weight. This is also acceptable for public companies if you expect that the derived fair value of equity is a better indicator of the future capital structure. Recognize that this creates a circular calculation because the WACC in turn is used to derive the value of equity, so be sure that iterations are checked to 100 in Excel under Tools > Options and a circuit breaker is in place. Licensed to Sheikh Sadik. Email address: [email protected]

Modeling the weighted average cost of capital (WACC) Market value of debt

In practice, we use the book value of debt as an approximation for market value of debt unless interest rates have changed substantially since debt issuance. In this case, use the market price of the company’s debt if it is actively traded; otherwise value each bond by discounting its cash flows (face value x coupon rate) by the yield to maturity of comparably-rated debt. Tax shield

Because interest is tax deductible, the true cost of debt is the after-tax rate due to the ability of interest expense to shield taxes. The tax rate used should be the marginal tax rate for each specific company.

Tax shield

Tax shield Think of it this way: $1 of interest expense reduces earnings not by $1, but by $1 x (1 - marginal tax rate) because interest expense is tax deductible (assuming, of course, that the company is profitable and has sufficient pretax profit to use the shield provided by interest expense).


Modeling the weighted average cost of capital (WACC) Exercise: Calculate XYZ’s WACC

• Company XYZ has 50 million common shares outstanding trading at $20 per share • It has $200 million in debt outstanding at a 10% interest rate, and this is the current rate in the market • The marginal tax rate is 40% • The expected equity return in this company is 15% Calculate XYZ’s WACC


Modeling the weighted average cost of capital (WACC) Exercise: Calculate XYZ’s WACC

WACC = [r debt ] x [1-tr] x [D/(D+E)] + [r

equity]

x [E/(D+E)]

WACC = [10% (1-40%) x 200/1,200] + [15% x 1,000/1,200] WACC = 13.5%


Modeling the weighted average cost of capital (WACC) Cost of debt

• The required return on debt “r d” is directly observable in the market and is best approximated by the current yield-to-maturity on the company’s long-term debt or – if the company has no long-term debt or yields are not observable – debt of equivalent risk (for investment-grade debt). • Bloomberg is a good source for yields and prices. • When determining current yields using debt of equivalent risk, use credit agencies such as Moody’s and S&P which provide yield spreads over US treasuries by credit rating. • Remember to always use the yield to maturity – not the coupon rate – since the coupon rate does not reflect the current market cost of borrowing equivalent debt. Impact of capital structure on cost of debt • The required return on debt will obviously increase with the level of debt as a percentage of the capital structure because a more highly levered business has a higher default risk.


Modeling the weighted average cost of capital (WACC) Cost of equity

• The equity cost of capital is equal to the expected rate of return for a firm’s equity. • It represents the opportunity cost of investing in a particular business versus an alternative investment with similar risk. • Difficult to estimate: Since the cost of equity is not readily observable in the market like cost of debt, it is much more difficult to estimate. – Expected return correlated with risk: Fundamentally, the cost of equity represents the required rate of return an equity investor applies to expected equity cash flows to determine how much should be paid for those cash flows. A higher perceived risk by the investor will require a greater return. – Risk premium: Excluding investors in risk-free investments, all providers of capital assume risk in some form. Debt holders generally assume a lower risk than equity holders. Their returns are defined within a narrow range, their investments are usually secured by the assets of the company and, especially in the circumstances of bankruptcy or liquidation, they are paid before the equity holders. Conversely, equity holders share in all the benefits of the upside whereas debt holders only receive their contractual payments. The range of possible returns to the equity holders is much greater and, as a result, equity investment is considered riskier and, hence, more costly.


Modeling the weighted average cost of capital (WACC) Estimating the equity cost of capital There are several competing asset-pricing models. The most popular and commonly used in practice is the capital asset pricing model (CAPM). Other models include the Fama-French three factor model and the arbitrage pricing model (APT). The Capital Asset Pricing Model (CAPM) classifies risk into two parts: 1. Unsystematic risk: Company-specific risk that can be eliminated by investing in a diversified portfolio. Since diversified investors will not consider this risk when making investment decisions, equity prices will not reflect this risk. 2. Systematic risk: Risk related to investing in the stock market and to sensitivity of the specific security to the overall market. Since systematic risk is unavoidable, investors should be rewarded with an equivalent return.


Modeling the weighted average cost of capital (WACC) Here is a scenario to help illustrate this distinction of risk:  John is not diversified and considers investing in Google. The risks inherent in investing in the stock market, and particularly in a company that is so sensitive to market fluctuations, means that John will expect to be compensated with at least an annual return of 10%. This reflects Google’s systematic risk.  In addition to these inherent risks, there is also a 10% risk that company-specific issues will hurt share prices (while the rest of the industry and stock market do fine). To be compensated for both risks, John will require a 20% annual return. 

Meanwhile, Susan is diversified and is considering Google as an investment as well. Her other investments essentially eliminate her company-specific risk, so she only requires a 10% annual return to compensate her for risks.



As a result, assuming both have equal expectations about Google’s projected cash flows, Susan will be willing to pay more for Google shares than John. In fact, diversified investors will bid up the price of Google shares until the implicit required rate of return equals only the un-diversifiable, systematic risk of the investment. Licensed to Sheikh Sadik. Email address: [email protected]

Modeling the weighted average cost of capital (WACC) Capital Asset Pricing Model • The CAPM concludes that the cost of equity equals the risk free rate, plus an incremental return above the risk-free rate to compensate investors for the additional risk. • This incremental return equals the market risk premium, common to all companies and defined as the risk of investing in the market portfolio, times the company specific beta, which measures the company’s sensitivity to stock market changes. The CAPM formula: Cost of equity (r e) = Risk free rate (r f ) + β x Market risk premium (r m-r f ) Note: There is also an error term in the CAPM formula, but this is generally omitted


Modeling the weighted average cost of capital (WACC) Capital Asset Pricing Model • The risk free rate (r f ) – Should theoretically reflect yield to maturity of a default-free government bonds of equivalent maturity to the duration of each cash flows being discounted. – In practice, lack of liquidity in long term bonds, have made the current yield on 10-year U.S. Treasury bonds as the preferred proxy for the risk-free rate for US companies and the 10-year German Eurobond for European companies. – Bloomberg is a good source for current yields

• The market risk premium (r m-r f ) – Represents the excess returns of investing in stocks over the risk free rate – There are many competing models used to estimate this premium – Practitioners often use the historical excess returns method, and compare historical spreads between S&P 500 returns and the yield on 10 year treasury bonds. A good source for long term historical spreads is the Ibbotson & Sinquefield yearbook.


Modeling the weighted average cost of capital (WACC) Beta (β) – Beta provides a method to estimate the degree of an asset’s systematic (nondiversifiable) risk. Beta equals the covariance between expected returns on the asset and on the stock market, divided by the variance of expected returns on the stock market. – Beta is typically calculated by regressing the individual share returns versus the returns of the market index. The formula for Beta is as follows:

β = cov(Ri,Rm)/σ2(Rm) = ρ(Ri,Rm)σ(Ri)/ σ(Rm) where:

• • • • •

cov(Ri,Rm) = Covariance between security i and the market index σ2(Rm) = Variance of the market index ρ(Ri,Rm) = Correlation coefficient between security i and the market index σ(Ri) = Standard deviation of returns of security i σ(Rm) = Standard deviation of market returns


Modeling the weighted average cost of capital (WACC) Interpreting beta • A company whose equity has a beta of 1.0 is “as risky” as the overall stock market and should therefore be expected to provide returns to investors that rise and fall as fast as the stock market. A company with an equity beta of 2.0 should see returns on its equity rise twice as fast or drop twice as fast as the overall market. Company xyz returns vs. S&P 500 monthly returns (last 60 months) 20.0%

s n r u t e r 0 0 5 P & S -15.0%

Beta = 0.37

15.0%

2

R = 0.4884

10.0% 5.0% 0.0% -10.0%

-5.0%

0.0% -5.0%

5.0%

10.0%

15.0%

20.0%

25.0%

-10.0%

Company XYZ returns

• Returning to our CAPM formula, the beta value determines how much of the market risk premium will be added to the risk-free rate. Since the cost of capital is an expected value, the beta value should reflect an expected value as well. Licensed to Sheikh Sadik. Email address: [email protected]

Modeling the weighted average cost of capital (WACC) Application • When daily stock prices for the business are available, some bankers use projected betas provided by services such as Barra. Other services such as Bloomberg and S&P also provide Beta calculations, but both are based on historical prices and are therefore not forward looking. As such, the preferred source of forward looking betas is Barra. • However, calculating raw betas from historical returns and even projected betas is an imprecise measurement of future beta because of estimation errors (i.e. standard errors create a large potential range for beta). • As a result, it is recommended that we use an industry beta. In other words, we should calculate the unlevered beta (more on this shortly) of companies in the same industries facing similar operational risks. Assuming the errors are uncorrelated, they will cancel each other out the more companies are added. Delevering and relevering betas of comparable companies

• Comparable companies with similar operating characteristics may very well have substantially different financial (capital structure) characteristics. • We need to undo the distorting impact of different capital structures on beta because more highly leveraged companies will have higher observed betas. Why? Because cash flows to equity holders are more volatile due to the higher fixed interest payments • As a result, we need to delever betas of comparable companies so that we can relever Licensed to Sheikh Sadik. Email address: [email protected] them at the target company’s target capital structure.

Modeling the weighted average cost of capital (WACC) Delevering beta

β(Unlevered) =

β(Levered) 1+ (Debt/Equity) (1-tr)

β(Unlevered) = Unlevered beta β(Levered) = Levered beta tr = Marginal tax rate

Relevering beta:

Once you have derived the unlevered beta, you need to relever it at the target capital structure using the reverse of the formula: β(Levered) = β(Unlevered) x [1+(Debt/Equity) (1-tr)]


Modeling the weighted average cost of capital (WACC) Exercise: beta

• XYZ’s observed beta is 1.2 – Based on a leveraged balance sheet (it included debt) • XYZ’s net debt is $15,000 million • Market equity is $140,000 million • Tax rate is 35% Calculate the company’s unlevered beta


Modeling the weighted average cost of capital (WACC) Exercise: beta •

•

•

βu = βlev / 1+(1-T)*(D/E) βu = 1.2 / 1+(1-35%)*(15,000/140,000) βu = 1.2 / 1.07 βu = 1.12

Notice that when the effect of leverage is excluded from beta, beta decreases, implying less volatile expected returns on equity (lower risk).


Modeling the weighted average cost of capital (WACC)




Modeling the weighted average cost of capital (WACC) Calculate the industry beta as illustrated If your target company is public, WSP recommends including the company in the group.







Now we can relever the weighted average industry beta…



…and calculate the cost of equity…


Modeling the weighted average cost of capital (WACC) …and the weighted average cost of capital






Sensitivity analysis using data tables Sensitivity (what-if) analysis • Based on the assumptions in our analysis, and given a current share price of $67.42, are CL shares overvalued, undervalued, or correctly valued? 1. Change the discount rate: What would fair value per share be if the discount rate assumption was 7%? 8%? 9%? 10%? 2. Change the perpetual growth rate: What would fair value per share be if we changed the perpetual growth rate assumption to 3%? 5.0%? 3. Change the EBITDA multiple: What would fair value per share be if we changed the exit multiple assumption to 16.0x instead of 12.0x? • Since the DCF analysis is particularly sensitive to assumptions affecting the discount rate and terminal value calculation, it is important to perform a “sensitivity analysis” • This analysis employs data tables to calculate fair value per share based on a range of assumptions for discount rates and the perpetual growth rates. Commonly sensitized assumptions include: – The discount rate (WACC) – Terminal growth rate – Sales growth, operating projections – Exit multiples – By observing how the intrinsic value changes if certain assumptions were to be changed, important insights can be gained Licensed to Sheikh Sadik. Email address: [email protected]

Sensitivity analysis using data tables Data tables are great tools for sensitivity analysis • Data tables are used to build sensitivity matrices through built-in tool in Excel • Review Wall Street Prep’s Fundamentals of Financial Modeling Manual for a quick refresher on constructing Data Tables 



Examines a piece of output data – such as CL’s intrinsic enterprise value or fair value per share Analyzes the impact on the output by changing input variables – such as the discount rate, terminal growth rate, sales growth operating projections, or exit multiples.



Gives a range of possible values based on different input variable assumptions.



Provides an elegant way to summarize and present the results of various scenarios.

Data table dialog box in Excel


Sensitivity analysis using data tables


Sensitivity analysis using data tables




Modeling synergies Synergies When valuing a business using the DCF to determine its value for an acquirer, free cash flow projections may need to include incremental impact of cost savings due to the acquisition (synergies): – Cost savings (more common): merging companies can often make a significant cut in expenses by eliminating overlapping workforce, overlapping infrastructure, etc.

– Revenues: merging companies, as a result of combined technology/intellectual property, may be able to bolster their revenue stream with new products and cross-selling opportunities.

Modeling synergies

Synergies can be discounted independently or by adjusting the explicitly projected free cash flows.

C ommon error: When modeling revenue synergies, don’t forget that these incremental revenues have costs associated with them that need to be modeled in as well (typically at the company’s tax-effected EBIT margin).


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies


Modeling synergies

0


Modeling synergies

0


Modeling synergies

0


Modeling synergies

0


Check out other great products at www.wallstreetprep.com Self Study Programs


• • • • • • • • • •

Financial Statement Modeling Comparable Company Modeling Comparable Transaction Modeling Leveraged Buyout (LBO) Modeling Mergers & Acquisitions (M&A) Modeling Advanced Financial Modeling Crash Course in Accounting Reading Financial Reports Crash Course in Excel How to Ace the Investment Banking Interview

Contact us



DCFModelingCourseManual_541c69f0a5dd8

Recommend Documents