CORE QM Final

Simulation model

1. A computer model that (more closely) imitates a real-life situation -Allows for uncertainty -Fundamental advantage is that it provides a distribution of results, not simply a single bottom-line result -Each different set of values for the uncertain quantities is a scenario 2. Probability distribution for uncertain inputs -> simulation model -> probability distribution for uncertain outputs 3. Assumes demand is a random draw

Covariance

1. A measure of how much two random variables change together 2. Cov(x,y) = corr(x,y) * stdev of x * stdev of y 3. The variance of variable x would be: σX^2 = Cov (X,X) / ρXX 4. Since the correlation of variable with itself, i.e., ρXX = 1, therefore σX^2 = cov (X,X) 5. Is the covariance of a variable xi with xj the same as the covariance of xj with xi? -Yes, covariance(xi, xj) = Corr(xi, xj)σiσj = covariance(xj, xi) -So, covariance(xi, xj) + covariance(xj, xi) = 2*covariance(xj, xi)

Sensitivity Analysis

1. Allows us to have a more informed discussion about the costs and benefits of investing in particular stocks 2. Quantifies how changes in model parameters or variables affect outcomes 3. How are our conclusions affected when the parameters of the model change? 4. For risk management -Helps identify the variables that are most critical for outcomes -Helps identify which variables should be considered to include in a simulation 5. Information from sensitivity analysis will help us decide which variables to look at in more detail for simulation

Replications/Iterations

1. As we have discussed earlier, each time a simulation is run, random numbers are generated based on the distribution 2. Since that is the case, no two simulations will yield the same result in terms of inputs 3. Consequently, the output results will differ (somewhat) each time depending on the actual random numbers generated for the input and the number of iterations used for the simulation

Overconfidence

1. Assuming the probability of success is higher than the data would suggest 2. Failing to recognize when past success was due to luck rather than skill 3. Play devil's advocate -"Asking people to explain why their answers might be wrong (or far off the mark) can decrease overconfidence by getting subjects to see contradictions in their judgment." (Bazerman)

Other calculated variables

1. Covariance between stock returns, total fraction of money invested, expected portfolio return 2. Constraints -total fraction invested = 1 -expected portfolio return >= minimum required expected portfolio return

Goal Seek for Breakeven

1. Data>Data Tools>What-If Analysis>Goal Seek 2. In the "Set cell" field, put a reference to the cell containing the formula -In this case, Gross Profit, Cell F12, from the Partial Income Statement sheet 3. In the "To value" field, enter the number (X) -For breakeven, the profit would be zero -So set this to zero 4. In the "By changing cell" field, enter a reference to the cell containing the variable In this case, the Direct Material Cost, Cell B5, from Costs sheet

Decisions with risk

1. Define and measure -Better measurement of risk →better decisions -Avoid decision-making biases 2. Model the decision -Optimization models -Simulation models 3. Sensitivity analysis *Minimize the variance (which is a measure of risk) subject to a minimum expected return constraint

Types of Probability Distributions

1. Discrete versus continuous 2. Symmetric versus skewed 3. Bounded versus unbounded 4. Nonnegative versus unrestricted

Value at Risk (VaR)

1. Financial analysts often call the 5th percentile the Value at Risk at the 5% probability level, or VaR 5%, because it indicates (nearly) the worst possible outcome 2. Usually VaR 5% is shortened to just VaR

What is the objective?

Minimize portfolio variance

Running the simulation

Once the model is ready 1. Specify simulation settings (Set number of iterations to 1000 and simulations to 1) 2. Run the simulation (click "Start Simulation")

Objective cell

Portfolio variance (minimize)

Return of a portfolio

Return (Ep) = weight1 * E(R1) + weight2 * E(R2) *If only one asset, the return of the portfolio is the return of that asset

Three measures of sensitivity analysis

Sensitivities tell us how big an impact do different variables have on our outcomes Three measures of sensitivity 1. Breakeven change -How much does this variable have to change before profits/NPV goes to 0? 2. Dollar impact of 1% change -How much are profits/NPV affected when this variable changes by 1% 3. Elasticity -How much are profits/NPV affected in percentage terms when this variable changes by 1%

=RISKMEAN(J13,4)

This command will return the average value of the output in cell J13 from the fourth simulation

Scenarios by priority (expected impact, including duration)

1. First address the high impact, high probability events -These are events that are likely to happen, and will seriously hurt the business if they do -Make provisions in your business plan to mitigate these events -Along with mitigation, plan on running before-and-after simulations for these type of events 2. Then address events that are low impact but high probability -Mitigation should focus on reducing the probability of occurrence of the event 3. High impact but low probability -Events such as fire or theft; usually purchase insurance to mitigate risk -Larger companies also insure against such risks by hedging 4. Investments in mitigation should be smallest for events that are both low probability and low impact

=RAND()

1. Generates a random number equally likely to be anywhere between 0 and 1 2. If there are n rows of cells containing =RAND() -approximately 50% of the cells will contain a number > 0.5 and -50% will contain a number < 0.5 -By extension, 20% of cells will contain a number < 0.2

Goal Seek

1. Goal Seek is used to find the value of a variable that makes a formula equal to a given number X 2. Data>Data Tools>What-If Analysis>Goal Seek 3. In the "Set cell" field, put a reference to the cell containing the formula 4. In the "To value" field, enter the number (X) 5. In the "By changing cell" field, enter a reference to the cell containing the variable

One way to analyze the risks facing a business

1. Identify scenarios that may affect the organization 2. Determine the impact of the scenario on the organization's objective 3. Make a qualitative estimate of the size of impact 4. Estimate qualitatively the probability that the scenario might occur 5. Identify mitigation strategies 6. Reminder: this is mostly a "qualitative" analysis

Correlated inputs

1. If they are positively correlated, then large numbers will tend to go with large numbers, and small with small 2. If they are negatively correlated, then large numbers will tend to go with small numbers, and small with large 3. Correlated inputs in @RISK are created with the RISKCORRMAT function

Pre-Mortem

1. Imagine yourself 5 years from now, with a failed project on your hands 2. Write the story of that failure

Demand at Regular Price and at Sale Price?

1. Is there a potential relationship between these two demands? 2. It's likely that if demand for calendars at the regular price is high, demand will also be high at the sale price -And vice versa, if demand is low

Advantages of @Risk

1. It gives easy access to many probability distributions you might want to use in your simulation models 2. It allows you to perform simulations much more easily than is possible with Excel alone 3. The advantage with @Risk is that the program can calculate these replications for many different types of distributions 4. Another advantage is that one can run simulations with much larger number of iterations, say 5,000 or even 10,000 quite readily 5. Allows running multiple simulations with the same random numbers used for all the simulations

Deterministic checks

1. It is sometimes useful to enter well-chosen fixed values for the random inputs, just to see whether the logic is correct 2. Fix any logical errors before reentering the random numbers and running the simulation

Where does uncertainty come from?

1. Lack of knowledge 2. Difficulty of measuring important variables 3. Inability to control the outcome of a process 4. Inherent randomness

Input variables

1. Means 2. Standard deviations 3. Correlations for stock returns 4. Minimum required expected portfolio return

Formulas using Range Name (Profit in this case)

1. Min -> =MIN(Profit) 2. Max -> =MAX(Profit) 3. Average -> =AVERAGE(Profit) 4. Standard deviation -> STDEV(Profit,0.25) 5. Median -> =MEDIAN(Profit) 6. 5th Percentile -> =PERCENTILE(Profit,0.05) 7. 95th Percentile -> =PERCENTILE(Profit,0.95) 8. % losses -> =COUNTIF(Profit,"<0")/100 9. % profitable -> =COUNTIF(Profit,">0")/100

Formulas for summary statistics (Cell F13 is Profit)

1. Min -> =RiskMin(F13) 2. Max -> =RiskMax(F13) 3. Average -> RiskMean(F13) 4. Standard deviation -> =RiskStdDev(F13) 5. Median -> =RiskPercentile(F13,0.5) 6. 5th Percentile -> =RiskPercentile(F13,0.05) 7. 95th Percentile -> =RiskPercentile(F13,0.95) 8. P(profit<0) -> =RiskTarget(F13,0) 9. P(profit>0) -> =1-RiskTarget(F13,0)

Excel reports in @Risk

1. Model Inputs 2. Model Outputs 3. Model Correlations

The Portfolio Selection Model

1. Most investors have two objectives -a large expected return and -a small variance (minimize risk) 2. A nonlinear optimization problem 3. Most common approach: minimize the variance subject to a minimum expected return constraint 4. Financial analysts estimate the required means, standard deviations, and correlations for stock returns from historical data -no guarantee that these estimates are relevant for future returns

Walton Books

1. Objective: To illustrate the difference between a deterministic model with a best guess for uncertain inputs and a simulation model that incorporates uncertainty more comprehensively 2. Objective: To learn about @RISK's basic functionality by revisiting the Walton Books problem

Using @RIsk to explore probability distributions

1. Palisades' @RISK add-in allows experimentation with probability distributions with its @RISK random functions 2. Can see the shapes of various distributions and calculate probabilities for them

Flaw of averages

1. Pitfall that should be avoided 2. If a model contains uncertain inputs, it can be very misleading to build a deterministic model by using the means of the inputs to predict an output 3. The resulting output value can be considerably different—lower or higher—than the mean of the output values obtained from running a simulation with uncertainty incorporated explicitly

Simulation results

1. Remember that the number of simulations is limited to 1000 2. This may be inadequate to capture all the details of the distributions 3. So, as discussed, each time the simulations are run, the results might be different 4. The Mean, Standard Deviation and Median will not vary much 5. The Min and Max can vary a lot

The three S's

1. Scenario Analysis 2. Sensitivity Analysis 3. Simulation *These are complementary ways to evaluate risk

Benefits of Scenario Analysis

1. Scenario analysis helps keep psychological biases from overly influencing our decisions 2. The availability heuristic causes decision-makers to rely too much on the information most available in their memories 3. Relying on more vivid or available memories may lead decision-makers to make the wrong choices because of a misguided focus on the most salient or available facts 4. Can also combat bias due to overconfidence

How do we identify?

1. Scenarios -history and forecasts 2. Estimated Impacts -sensitivity analysis, past data 3. Probabilities -Published sources -Estimates of experts 4. Mitigation -Reduce probabilities -Buy Insurance or Hedge -Make sure that the costs of mitigation are not greater than the expected impact of the scenario

Specify distribution

1. Select the distribution of the random input parameter -To generate a random demand, enter the formula =ROUND(RISKTRIANG(E4,E5,E6),0) in cell B13 2. RiskTriang(100, 150, and 250) specifies a triangular distribution with a minimum value of 100, a most likely value of 150 and a maximum value of 250 3. ROUND function rounds the demand to the nearest integer

Key differences between sensitivity and simulation analyses

1. Sensitivities tell us which variables have the biggest impact if they change by 1% 2. But they don't say anything about how LIKELY it would be for those variables to change by 1% 3. Simulation adds assumptions about the probability distributions for key variables

Efficient frontier

1. Shows risk-return trade-off 2. Points below the efficient frontier are feasible but inferior 3. Points above the efficient frontier are unachievable, but preferred—the company cannot achieve this high an expected return for the given level of risk

Triangular distribution

1. Similar to the normal distribution in that its density function rises to some point and then falls 2. It is more flexible and intuitive than the normal distribution 3. It is an excellent candidate for many continuous input variables 4. It is specified by easy-to-understand parameters: the minimum possible value, the most likely value, and the maximum possible value 5. The shape of a triangular density function is a triangle 6. The high point of the triangle is above the most likely value

Variance of a portfolio

1. Standard deviation squared 2. The weight of the securities in the portfolio (w1,w2) 3. The standard deviation of the stock's returns 4. The correlation between the returns on the securities 5. = w1^2*SD(R1)^2 + W2^2*SD(R2)^2 + 2w1w2*SD(R1)*SD(R2)*Corr(1,2) 6. Nonlinear function

Uniform Distribution

1. The "flat" distribution 2. It is bounded by a minimum and a maximum, and all values between these two extremes are equally likely 3. =RAND and =RANDBETWEEN are all examples of uniform distribution

Probability distributions for input variables

1. The building blocks of spreadsheet simulation models 2. The primary difference between other spreadsheet models and simulation models is that at least one of the input variable cells in a simulation contains random numbers -Technically speaking, input cells do not contain random numbers; they contain probability distributions -A probability distribution indicates the possible values of a variable and the probabilities of those values. There are many probability distributions to choose from, so it is important to choose an appropriate distribution for each specific problem

@RISK Models with a Single Random Input Variable

1. The development of a simulation model is basically a two-step procedure -First, build the model itself -This step requires you to enter all of the logic that transforms inputs into outputs (@RISK cannot do this for you) -Once this logic has been incorporated, @RISK replicates your model with different random numbers on each replication -It also reports any summary measures that you request in tabular or graphical form.

Normal distribution

1. The familiar bell-shaped curve 2. It is useful in simulation modeling as a continuous input distribution 3. It is not always the most appropriate distribution, because it is symmetric. Skewed distributions may be more realistic 4. It allows negative values, which may not be appropriate in several situations

Detailer Case

1. The focus is on the profit in Year 4 2. There are five variables that are defined as random inputs -Purchase intent -Awareness -Competition -Direct Materials -Direct Labor

Drawbacks of scenario analysis

1. The impact of each risk and its probability is an estimate 2. The probabilities are discrete and do not consider a range 3. Many of these estimates could be quite wrong

Interpreting the tornado chart

1. The longer the bar, the stronger the relationship between that input and profit 2. Each bar shows how the mean profit varies as each input varies over its range (other inputs held constant) 3. If a random input has a large effect on profit, it's worth the time and money to learn more about this input and possibly reduce the amount of its uncertainty

Information to draw from the simulations

1. The minimum, maximum, mean, median, and standard deviation of profit for each simulation 2. The value at risk (profit at the 5th percentile) for each simulation and the 95th percentile value 3. The probability that the profit is negative 4. A statement of which of the variables has the largest influence on profit, according to the results of the final simulation (in which all simulation variables are treated as random inputs) 5. Which variables are most likely to be correlated, and how might imposing a correlation between them change the results?

Scenario Analysis

1. The process of evaluating risk by making a list of possible business scenarios and specifying the probability of each one of the scenarios occurring 2. We also specify the impact of each scenario on the relevant outcome -For example, net present value or return on investment 3. Probability distribution of the impacts is the list of impacts and the probability of each impact 4. Scenario analysis then calculates the overall expected outcome and variance of that outcome 5. Considers and quantifies the effects of multiple hypothetical states of the world

Running multiple simulations

1. The ultimate goal is to choose an order quantity that provides a large average profit with low probability of being unprofitable 2. So, to have a fairer comparison, it is best to test each order quantity on the same set of random demand numbers

Defining range names

1. This is done by going to the "Formulas" tab and in the "Defined Names" group, click "Define Name" 2. For Excel in Mac OS 3. From the menu at the top, click "Insert" → "Name" → "Define"

Modeling Issues

1. Typical real-world portfolio selection problems involve a large number of potential investments. However, the basic model does not change -Computing variance does become more cumbersome 2. If a company is allowed to short a stock, the fraction invested in that stock is allowed to be negative -To implement this, eliminate the non-negativity constraints on the changing cells 3. There may also be constraints imposed by investors, such as: -Geographic restrictions -Diversification across industries -Green investing -Sharia-compliant investing

Building a spreadsheet

1. Use modules -Objective function -Choice variables -Parameters -Constraints 2. Enter parameters separately (not in formulas) 3. Label clearly 4. Validate formulas using a "reality check"

Simulation

1. Used to figure out the likely range of variation in the objective function given assumptions about the probability distributions of parameters 2. Allows us to consider thousands of cases (using distributions) 3. Can be used to model the uncertainty associated with future cash flows, including questions such as: -What are the mean and variance of a project's net present value (NPV)? -What is the probability that a project will have a negative NPV? -What are the mean and variance of a project's IRR?

Discrete distribution

1. Useful for many situations 2. For example, when the uncertain quantity is not really continuous, or 3. When you want a discrete approximation to a continuous variable 4.You need to specify the possible values and their probabilities, making sure that the probabilities sum to 1 -Because of this flexibility in specifying values and probabilities, discrete distributions can have practically any shape

Calculating the probability of negative profit

1. We can use the RiskTarget function in @Risk 2. Prob(Profit<=0) -> =RiskTarget('Partial Income Statement'!$F$12,0)

Formulate the problem

1. What is the objective? 2. What are fixed inputs? 3. What are the variables? 4. Let us build an influence chart

Risk Mitigation

1. What risk mitigation strategies are you going to adopt? Why? 2. What might be the costs and benefits of the proposed mitigation strategies? 3. How might your mitigation strategies change possible distributions of your input variables?

Questions and observations from sensitivity analysis

1. Which variable impacts profit the most? 2. Which variable impacts profit the least? 3. If you had to chose three variables to look at in more detail, which three variables might you focus on?

Actual Supply

1. Will depend on two things: how much the supplier can supply (B13) and how much Walton Books ordered (B10). It will be lower of these two figures 2. =MIN(B13,B10)

Cost paid by Walton

1. Will depend on whether the supplier can fulfil the demand or not *If statement

Maximum supply

Dependent on suppliers ability

Demand at regular price

Derived from the demand distribution

Decision variables (changing cells)

Fractions invested in the various stocks

=RANDBETWEEN(X,Y)

Generates a random number equally likely to be anywhere between X and Y

Scenario risk matrix

High Impact, low impact vs. low probability, high probability 1.

RISKSIMTABLE

In @RISK, the RISKSIMTABLE function enables the use of same random numbers for all the simulations

Least risk

lowest variance and stdev

Dimensions of risk

To assess risk, three dimensions must be taken into account 1. The expected magnitude of the impact of the event (risk) 2. The probability of the event (risk) 3. The duration of the impact of the event (risk) Examples: 1. Most natural disasters would have a very large impact but the probability of the event would be very small. The duration of this event until alternative arrangements could be made is likely to be mid-term 2. The probability of someone being absent from work on any given day might be fairly high but both magnitude of the impact of this event and the duration of this event are likely to be fairly small 3. Depending upon the industry the likelihood of a technological innovation may be high or low. The magnitude also could be from small to large. If significant the duration of the impact could be very long term, ala the impact of NetFlix on Blockbuster

Revenue at regular price

Will depend on the demand for regular priced calendars (E13) and the actual supply (C13). It will be lower of these two numbers and then it will be multiplied by the Regular Price (B6)