Tài chính doanh nghiệp - Topic 11: Monte carlo simulations using excel and @risk: hoffman mines

Topic #11Monte Carlo Simulations using Excel and @Risk: Hoffman MinesFinancial ModelingL. Gattis1Financial Simulation Process1. Create a model that estimates a future outcome (e.g., asset price, payoff, portfolio value) which has a stochastic variable such as asset returnStochastic variables are those in which the future value is uncertain (non-deterministic) – i.e., more than one possible outcome2. Specify the distribution of the stochastic variables (and their correlations)E.g., asset returns are normally If there are multiple stochastic variables -- specify the correlations3. Simulate many possible outcomes by randomly sampling from the specified distribution E.g., sample 5,000 possible values for asset return4. Evaluate the distribution of the outcomeE.g., mean, volatility, skew, confidence interval2Hoffman 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 water and gravity31. Hoffman Mine FY12 Static Financial ForecastOpen File: Topic#9_HoffmanMines_static4Simulation1. 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 outcomeWhat is the probability that FCF < 0?5Distributions and Correlations62. Define DistributionsSelect C4 first, Click on Define Distribution, Select Uniform, Type in Min, Max, and Static values or assign cell reference as argumentsRepeat for other variables73. Add OutputSelect C29 first, click on add outputThe title is obtained from the first text to the left of output cell84. Specifying Variable CorrelationsCorrelations1.  Go to Model Window (verify inputs and outputs)2.  Select Inputs Tab3.  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 copy5.  Right click on matrix --- select “copy coefficients from excel”, highlight correl matrix in excel6.  Select location for @Risk correlation matrix, and select ok, ok95. Run Simulation and View Results10Hoffman Simulation11Assignment12