Topic 15 - Building stochastic free cash flow and DCF models using excel and @Risk. After completing this unit, you should be able to: Forecast and simulate free cash flows, value common stock using discounted cash flow, use other distributional assumptions in @Risk to create stochastic DCF models. | Topic #15 Building Stochastic Free Cash Flow and DCF Models using Excel and @Risk Financial Modeling L. Gattis 1 Two Excel Files for this topic: Topic_15_Hoffman_ Mines Topic_15_Equity_DCF Objective Forecast and simulate free cash flows Value common stock using discounted cash flow Use other distributional assumptions in @Risk to create stochastic DCF models 2 Simulation 1. Create a model that estimates a future outcome which has a stochastic variable Estimate free cash flows Select stochastic variables 2. Specify the distribution of the stochastic variables (and their correlations) Distributions Correlations 3. Simulate many possible outcomes by randomly sampling from the specified distribution Sample 10,000 correlated values for stochastic variables 4. Evaluate the distribution of the outcome Mean, Percentiles 3 Hoffman Gold Mine Financial Statement Simulation The Hoffman Gold Mine (HGM) operates in the Klondike region of the Yukon Territory in Canada HGM’s sole revenue consists of gold HGM’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 bedrock Transport the “pay dirt” to the wash plant where rocks are removed and gold is separated from other material using water 4 5 Hoffman Mine FY2012 (Season 3) Forecast Open File: HoffmanMines_static / Launch @Risk 6 In this example: FCF Excludes Depreciation, changes in Working Capital and this Firm has no Debt Hoffman Mines Add four distributions and parameters Add input correlations Add one output (FCF) Run 10,000 iterations Obtain distribution statistics Riskmean, risktarget(0), riskpercentile(.05),(.95) 7 8 Discounted Cash Flow Models The intrinsic value of a financial asset is the discounted present value of expected cash flows Several DCF Models 9 Cashflow Discount Rate Constant Growth Equity Value Dividend Discount Model (DDM) Cost | Topic #15 Building Stochastic Free Cash Flow and DCF Models using Excel and @Risk Financial Modeling L. Gattis 1 Two Excel Files for this topic: Topic_15_Hoffman_ Mines Topic_15_Equity_DCF Objective Forecast and simulate free cash flows Value common stock using discounted cash flow Use other distributional assumptions in @Risk to create stochastic DCF models 2 Simulation 1. Create a model that estimates a future outcome which has a stochastic variable Estimate free cash flows Select stochastic variables 2. Specify the distribution of the stochastic variables (and their correlations) Distributions Correlations 3. Simulate many possible outcomes by randomly sampling from the specified distribution Sample 10,000 correlated values for stochastic variables 4. Evaluate the distribution of the outcome Mean, Percentiles 3 Hoffman Gold Mine Financial Statement Simulation The Hoffman Gold Mine (HGM) operates in the Klondike region of the Yukon Territory in Canada HGM’s sole revenue consists of
