DS 235
Spread Sheet Modeling – The process of entering the inputs and decision variables into a spreadsheet and then relating them appropriately, by means of formulas, to obtain the outputs.
Phase 1 of Analysis – Analyst tries to envision the possible outcomes from an investment. - Analyst prepares estimates and forecast.
Phase 2 of Analysis – Analyst details underlying sources of risk (identify value drives and uncertainty. Forensic valuation analysis).
-Analyst seeks ways to mitigate risks and monitors throughout the life of the project (sensitivity of value to risks).
Flaw of Averages: The average model input does not always give the average model output.
May occur when a firm oversimplifies reality by fixating on a single number – the mean.
To avoid this the firm must start dealing in probability distributions.
Sensitivity Analysis Results Provide Us: The possible outcomes for a particular range of values of a variable. It assumes the other variables remain static and provides no information on the probability of each outcome occurring.
· Tool Needed: Monte Carlo Simulation.
· Sensitivity Analysis: Shows the relative importance of each of the variables and how the value of the project changes in response to changes in the value of each variable.
· Primary Purpose: Identify how sensitive a model’s output is to changes in its inputs.
· Evaluates how changes in input variables affect the outcome of a model or system.
Monte Carlo Simulation
· Simulations
o Incorporate probabilities or uncertainties to determine possible outputs.
o Provides a distribution of results rather than one finite answer.
o Useful in determining possible outputs as well as the risk or sensitivity involved.
o Require assumptions about the distributions of input variables.
o Particularly useful in estimating the impact of risk and uncertainty in quantitative analysis and decision making.
· Variables:
o Many variables can be random/have probability distributions.
· Simulation Settings
o The model used should have “uncertain variables.”
o Increasing the number of trials in a Monte Carlo simulation generally increases the accuracy of the results.
· Monte Carlo simulation is different from a deterministic model because Monte Carlo incorporates randomness and probabilities to model scenarios.
Probability Distributions
· Distributions
o Uniform_(min, max) --- EQUALLY LIKELY
o Triangular_(min, most likely, max)
o Normal_(mean, standard deviation)
o Histogram_(min, max, {p})
o Discrete_({x, x}, {p, p})
· Continuous vs Discrete
o Many of the distributions that are used are continuous while only one is discrete.
o Continuous means to have a range of possible values with any value within the range being used, while discrete have certain possibilities without variance. (Examples: uniform, triangular, histogram, and normal).
Basic Premise of Monte Carlo Simulation: a random sample has the same characteristics as the entire population. Based on statistical inference where a sample of the population is analyzed when it is impossible to analyze the entire population.
Goal of a Simulation: to determine the statistical distribution of a result.
Risk Analysis
· Simulation Outputs
§ Standard Deviation – measures how spread out the distributions is and is often a useful description of risk.
§ Percentiles
§ Mean – average value.
§ Probabilities
§ Correlation: measures the strength and direction of a relationship between two variables. 0 is weak, -1 to +1
· Corr_(input row, table range)
· High vs. Low Risk
o Simulations with lower standard deviations have lower risk.
o Percentiles will give us a good look at the range of possible outcomes.
§ 25th percentile tells us that 25% of the values are below it, and 75% are greater than it.
§ Higher percentiles value wise are better for the risk of the company.
o Probability section of results is manual input to tell us <,>, = a certain number
· Uncertainty
o Nothing is absolute, everything has risk.
Risk Analysis Methods
· Scenario Analysis: Broadens the horizons of DCF (discounted cash flow) forcing the analyst to think about the many different futures that are possible.
· Sensitivity Analysis: Shows the relative importance of each of the variables and how the value of the project changes in response to changes in the value of each variable.
· Simulation Models: Are mathematical representations of the project that incorporates the uncertainty, perform calculations, and record the results to determine statistical data.
· Decision Trees: Graphical representations of the relationships between various decision alternatives available and their possible outcomes over time.
Simulation:
· Simulation uses random numbers to drive the whole process.
· Probability distributions for input variables are the building blocks of spreadsheet simulation models.
o Spreadsheet vs Simulation Model difference = in a simulation model at least one of the input variable cells in a simulation contains random numbers.
NPV should be accepted if a project earns a positive NPV.
Business Investment Decisions Based on the Net Result of the forecasted investment costs and expected future revenues.
Multi-period models have uncertainties that unfold or evolve over time (stock prices, growth).
NET PRESENT VALUE (NPV)
Strategic Decisions = longer time horizons (quarters, years) mergers, long-term investments.
Operational Decisions= short time frames (days, weeks) production schedule, sales forecasting.
Deterministic – does not have uncertainty.
· Amanda’s portfolio of adding $5,000 each year to stocks and bonds.
Net Cash Flow = Total Costs – Total Revenue
NPV =NPV(discount rate cell, net cash flow years cells)
NPV(B10, B34:37)
B10 = 7.5% discount rate
With Correlation little 2 by 2 table prod cost x prod cost = 1
Prod cost x main cost = 0.8
