Tài chính doanh nghiệp - Topic 15: Building stochastic free cash flow and dcf models using excel and @risk

Topic #15Building Stochastic Free Cash Flow and DCF Models using Excel and @RiskFinancial ModelingL. Gattis1Two Excel Files for this topic:Topic_15_Hoffman_ MinesTopic_15_Equity_DCFObjectiveForecast and simulate free cash flowsValue common stock using discounted cash flowUse other distributional assumptions in @Risk to create stochastic DCF models2Simulation1. Create a model that estimates a future outcome which has a stochastic variableEstimate free cash flowsSelect stochastic variables2. Specify the distribution of the stochastic variables (and their correlations)DistributionsCorrelations3. Simulate many possible outcomes by randomly sampling from the specified distribution Sample 10,000 correlated values for stochastic variables4. Evaluate the distribution of the outcomeMean, Percentiles3Hoffman Gold Mine Financial Statement SimulationThe Hoffman Gold Mine (HGM) operates in the Klondike region of the Yukon Territory in CanadaHGM’s sole revenue consists of goldHGM’s primary expense is diesel fuel used to operate its mining equipment (Bulldozer, Excavator, and Loader)The mining process:Use the mining equipment to remove top soil to get access to the “pay dirt” above bedrockTransport the “pay dirt” to the wash plant where rocks are removed and gold is separated from other material using water45https://youtu.be/5MlTRMktl-AHoffman Mine FY2012 (Season 3) ForecastOpen File: HoffmanMines_static / Launch @Risk6In this example: FCF Excludes Depreciation, changes in Working Capital and this Firm has no DebtHoffman MinesAdd four distributions and parametersAdd input correlationsAdd one output (FCF)Run 10,000 iterationsObtain distribution statisticsRiskmean, risktarget(0), riskpercentile(.05),(.95)78Discounted Cash Flow ModelsThe intrinsic value of a financial asset is the discounted present value of expected cash flowsSeveral DCF Models9CashflowDiscount RateConstant Growth Equity ValueDividend Discount Model (DDM)Cost of Equity (Ke)CAPM: Ke=Rf+βe(MRP)Ve = Div / (Ke-g)Free Cash Flows to Equity (FCFE)Cost of Equity (Ke)Ve = FCFE / (Ke-g)Free Cash Flow to Firm (FCFF@WACC)Weighted Average Cost of Capital (WACC)WACC=Ke(E%)+Kd(1-t)(D%)Ve= FCFF/(WACC–g)–Debt +CashFree Cash Flows10FCFF (Free Cash Flows to the Firm)Cash flows available to distribute to both debt and equity holdersbefore interest and dividend payments, but after taxesNon-cash “accounting costs” are added back (Depr + Amort)capital expenditures (which are depreciated) are subtractedAppropriate to value total firm (LBO/PE/IB)-- must subtract the value of debt to value the equity stakeFCFF = NOPAT + Deprec&Amort – CAPEX - ∆NWC NOPAT = Net Operating Profit after Tax (before interest)CAPEX: Capital Expenditures on Capital GoodsΔNWC = change in noncash net working capital (C.A.-C.L.)FCFF1 = Sales0*(1+g1)*EBIT Profit Margin*(1-T)*(1-Net Reinv Rate)EBIT Margin = EBIT/SalesEBIT is earnings before interest and tax, aka, operating profitNet Reinvestment Rate = (CAPEX + ∆NWC -D&A)/NOPATFCFF Equity Valuation ModelsDCF with Growth PerpetuityValuation very sensitive to perpetual growthPerpetual growth cannot exceed growth of economyDCF with Exit FCF MultipleMultiples often range 5-10 and vary by industryOther common multiples are EBITDA, NET EARNINGS11WACCWeighted Average Cost of Capital (WACC): a firm’s weighted average cost of debt and equity. Where E and D are the market values of the firm’s equity and debt instruments; t is the firm’s tax rate (in practice, many use market cap of equity and book value of debt)Cost of Equity (Ke): required rate of return for equity holders CAPM is commonly used to compute: K=Rf+Beta(MRP)Beta: Common approach is to use an unlevered industry beta and re-lever using target firms D/E (may provide a more stable and long term beta estimate)Cost of Debt (Kd): required rate of return for debt holdersSources: (1) Yield on firm’s debt or interest rate on incremental bank financing, (2) Yield Spread Based on Company Credit Rating or Coverage RatiosConstant Growth Perpetuity DCF FormulasConstant Growth Perpetuity DCF ModelDeterministic FCFF@WACC Valuation15Open File: Equity DCF ModelDeterministic FCFF@WACC Valuation16ObjectiveForecast and simulate free cash flowsValue common stock using discounted cash flowUse other distributional assumptions in @Risk to create stochastic DCF models17