TAILIEUCHUNG - Lecture Financial modeling - Topic 15: Building stochastic free cash flow and DCF models using excel and @Risk

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

• Tài chính doanh nghiệp
• Financial modeling
• Lecture Financial modeling
• Asset expected return
• Financial statement simulation
• Cash flow
• Cash flow models
• Indirect prices
• Direct prices
• Advanced financial modeling
• Financial applications
• VBA variables
• Option explicit
• Financial simulations
• Retirement ratios
• Changing asset allocation
• Portfolio optimization
• Portfolio risk return optimization
• Modeling support
• User defined function
• Calculating cost of capital
• Portfolio models
• Estimating asset risk
• Asset risk
• Portfolio risk
• Computing portfolio risk
• Portfolio value at risk
• Random walk simulations
• Monte Carlo simulation
• Advanced excel awesomeness
• Web queries
• Array functions
• Bond fundamental value
• Macaulay duration
• Modified duration function
• Income portfolio
• Portfolio duration
• Bond functions
• Default adjusted bond return
• Bond returns
• Adjusting returns
• Accrued interest
• Yield curve models
• Forward rates
• Forward contracts
• Random variables
• Forward contract
• Option contracts
• Monte carlo valuation
• Option contract
• Option pricing model
• Implied vols
• Volatility estimation
• BSM option greeks
• Dynamic delta hedging
• Short sales
• Distribution fitting
• Selecting distributions
• Simulation process