TAILIEUCHUNG - Lecture Financial risks management - Topic 11: Monte carlo simulations using excel and @risk: Hoffman mines
Topic 11 - Monte carlo simulations using excel and @risk: Hoffman mines. The main contents of the chapter consist of the following: Financial simulation process, hoffman gold mine financial statement simulation, distributions and correlations,. | Topic #11 Monte Carlo Simulations using Excel and @Risk: Hoffman Mines Financial Modeling L. Gattis 1 Financial Simulation Process 1. Create a model that estimates a future outcome (., asset price, payoff, portfolio value) which has a stochastic variable such as asset return Stochastic variables are those in which the future value is uncertain (non-deterministic) – ., more than one possible outcome 2. Specify the distribution of the stochastic variables (and their correlations) ., asset returns are normally If there are multiple stochastic variables -- specify the correlations 3. Simulate many possible outcomes by randomly sampling from the specified distribution ., sample 5,000 possible values for asset return 4. Evaluate the distribution of the outcome ., mean, volatility, skew, confidence interval 2 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 and gravity 3 1. Hoffman Mine FY12 Static Financial Forecast Open File: Topic#9_HoffmanMines_static 4 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 What is the probability that FCF < 0? 5 Distributions and Correlations 6 2. Define Distributions Select C4 first, Click on Define Distribution, Select Uniform, Type in Min, Max, and Static values or assign cell reference as arguments Repeat for other variables 7 3. Add Output Select C29 first, click on add output The title is obtained from the first text to the left of output cell 8 4. Specifying Variable Correlations Correlations 1. Go to Model Window (verify inputs and outputs) 2. Select Inputs Tab 3. Highlight all variables in the left pane (Shift, click) and then right-click and select correlations 4. Re-arrange columns in the same order as your correlation matrix you are going to copy 5. Right click on matrix --- select “copy coefficients from excel”, highlight correl matrix in excel 6. Select location for @Risk correlation matrix, and select ok, ok 9 5. Run Simulation and View Results 10 Hoffman Simulation 11 Assignment 12
đang nạp các trang xem trước
