DCF Modeling
Copyright 2008 © by Wall Street Prep, Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
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
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
…show more content…
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 ***************************** expenditures - Capital +/- Net SAMPLE PAGES FROM TUTORIAL GUIDEborrowing Levered FCF 6 For illustrative Purposes Only
Historical
•Income Statement (10-K / 10-Q / PR / Company) •Use normalized EBIT Use effective tax rate CFS / IS / Footnotes
Projections
Analyst research Company Internal projections Use marginal tax rate Analyst research Company Internal projections
*****************************
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. ***************************** We will calculate wacc shortly. SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
Calculation = days post-deal date / 365
7
Discounting to reflect
The appropriate discount rate was calculated using WACC formula as shown in the ‘WACC’ exhibit using the following assumptions:
* She is considering the cash flow paid to all the equity or debt holders. So she cannot use the equity cost of capital.
Using the data in Figure 1, we can calculate the WACC for the Telecom Services division and the Products and Systems division of Teletech. Telecom Services WACC is 10.404% while Products and Services WACC is 10.417%.
1. The first step to evaluating the cash flows is to conduct the depreciation tax flow analysis. Depreciation is not a cash flow, but the depreciation expense lows the taxes payable for the company. As a result, the tax effect of deprecation needs to be calculated as a cash flow. There are two depreciable items on the company's balance sheet the building and the equipment. The equipment is known to have a seven year depreciable life, which will be assumed to be straight line. The building is also assumed to be subject to straight line depreciation, this time of forty years. The tax saving reflects the depreciation expense multiplied by the tax rate, which in this case is assumed to be 28%. The following table illustrates the tax effect in future dollars of the depreciation expense:
10. What is the correct capital structure and weighted average cost of capital for discounting the investment’s free cash flow. Assume a 35% tax rate. A correct response requires that you define capital structure and Weighted Average Cost of Capital (WACC) with a formula. When defining a term with a formula be sure that all the variables are also defined.
Complete an income statement, balance sheet and statement of cash flows for 2011. Please provide information on any assumptions you make not already stated in the case.
Based on information given, we established the free cash flows from operations for Torrington, for the period 1999 to 2007. We made the assumption that net working capital was 7% of sales for Torrington, based on historic patterns. From this assumption, we found “Change in Net Working Capital” for the selected years. Next, we chose a value for “Capital Expenditures”, again based on historic patterns. From this we computed the “Free Cash Flows to the Firm”.
Lastly, the interest rate was calculated by dividing interest expense by long-term debt for the company. These numbers, along with equity and debt data given to us in the case, resulted in a WACC of 13.89%.
WACC (Weighted Average Cost of Capital) - When we are supposed to value AirThread Connections with the WACC valuation method we will have to use the following steps:
* We assume the cost of capital to be a stated annual rate to facilitate calculations;
All of these calculated figures can then be used to calculate the WACC which is (17% x 3.47%) + (83% x 11.2%) = 9.87% WACC. This WACC percentage can then be used to value the investment and as a comparative in valuation methods. The full calculation and numerical values are shown in Appendix 1.
For our pro forma, we first began with the income statement. To determine Sales, we assumed an increase at a consistent rate each year. COGS and operating expenses were estimated as a percentage of Sales. Exceptional Costs and Restructuring Costs were not considered since pro forma statements exclude unusual and nonrecurring transactions. With these figures, we were able to determine our Profit Before Tax (PBT). For our tax expense, we assumed a constant tax rate. By subtracting the Tax Expense from our PBT we determined the Profit/(loss) After Tax. Lastly, we subtracted dividends, which remained unchanged each year, from the Profit/(loss) After Tax to find the company’s Retained Earnings. Below is a diagram illustrating these steps:
For tax purposes, it is important to forecast cash flow and income. Sally, in Y1 you posted taxable income of $250k, in Y2 $10k, and in Y3 $150K. Were these numbers based on actual gross sales, or did your deductions and depreciation change drastically between Y1 and Y2?
6. Does your estimate of WACC differ from Cohen’s estimate? Why? What are the mistakes that Ms. Cohen make
YEAR 0 2009 1 2010 2 2011 3 2012 4 2013 5 2014 6 2015 7 2016 8 2017 9 2018 10 Initial Investment Gross Revenue 2 COGS 3 Add'l revenue Less: COGS Loan down payment 4 Loan repayment Depreciation Additional workers Land square required Moving cost 5 Operating Expenses Total Expenses Net Income Before Tax Income Tax Net Income After Tax After Tax Cash Flow ATCF Cummulative ATCF NPV through Year N