QM323 Final 2

Maximum supply

Dependent on suppliers ability

Sensitivity Analysis

1. Allows us to have 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 conclusions affected when parameters of the model change? 4. For risk management -Helps identify variables that are most critical for outcomes -Helps identify which variables should be considered to include in simulation 5. Information from sensitivity analysis will help decide which variables to look at in more detail for simulation

Demand at regular price

Derived from demand distribution

Decision variables (changing cells)

Fractions invested in various stocks

=RANDBETWEEN(X,Y)

Generates 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, RISKSIMTABLE function enables use of same random numbers for all simulations

What is the objective?

Minimize portfolio variance

Running the simulation

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

Objective cell

Portfolio variance (minimize)

Dimensions of risk

To assess risk, three dimensions must be taken into account 1. expected magnitude of impact of event (risk) 2. probability of event (risk) 3. duration of impact of 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 demand for regular priced calendars (E13) and actual supply (C13). It will be LOWER of these two numbers and be multiplied by Regular Price (B6)

=RISKMEAN(J13,4)

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

Least risk

lowest variance and stdev

Uniform Distribution

1. "flat" distribution 2. 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

Replications/Iterations

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

Overconfidence

1. Assuming probability of success is higher than 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 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 "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 "To value" field, enter the number (X) -For breakeven, profit would be zero -So set this to zero 4. In "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 decision -Optimization models -Simulation models 3. Sensitivity analysis *Minimize variance (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

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

One way to analyze the risks facing a business

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

Correlated inputs

1. If positively correlated, then large numbers will tend to go with large numbers, and small with small 2. If 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 failed project on your hands 2. Write 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

Where does uncertainty come from?

1. Lack of knowledge 2. Difficulty of measuring important variables 3. Inability to control outcome of 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

Running multiple simulations

1. ultimate goal is to choose 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 same set of random demand numbers

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 -large expected return and -small variance (minimize risk) 2. *nonlinear* optimization problem 3. Most common approach: minimize variance subject to minimum expected return constraint 4. Financial analysts estimate required means, standard deviations, and correlations for stock returns from historical data -no guarantee that estimates are relevant for future returns

Walton Books

1. Objective: To illustrate difference between a deterministic model with 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 shapes of various distributions and calculate probabilities for them

Flaw of averages

1. Pitfall that should be avoided 2. If model contains uncertain inputs, it can be misleading to build deterministic model by using means of inputs to predict output 3. 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 number of simulations is limited to 1000 2. may be inadequate to capture all details of distributions 3. So, as discussed, each time simulations are run, results might be different 4. 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 *complementary ways to evaluate risk

Benefits of Scenario Analysis

1. Scenario analysis helps keep psychological biases from overly influencing our decisions 2. availability heuristic: causes decision-makers to rely too much on information most available in memories 3. Relying on vivid or available memories may lead decision-makers to make wrong choices because of misguided focus on most salient or available facts 4. Can 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 costs of mitigation are not greater than expected impact of scenario

Specify distribution

1. Select the distribution of the random input parameter -To generate 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 demand to 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. don't say anything about how LIKELY it would be for those variables to change by 1% 3. Simulation adds assumptions about probability distributions for key variables

Efficient frontier

1. Shows risk-return trade-off 2. Points below efficient frontier are feasible but inferior 3. Points above efficient frontier are unachievable, but preferred—company cannot achieve this high an expected return for 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. more flexible and intuitive than the normal distribution 3. excellent candidate for many continuous input variables 4. specified by easy-to-understand parameters: the minimum possible value, the most likely value, and the maximum possible value 5. shape of triangular density function is triangle 6. high point of triangle is above the most likely value

Variance of a portfolio

1. Standard deviation squared 2. weight of securities in the portfolio (w1,w2) 3. standard deviation of stock's returns 4. correlation between 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

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

Interpreting the tornado chart

1. The longer the bar, the stronger the relationship between that input and profit 2. Each bar shows how 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 amount of uncertainty

Modeling Issues

1. Typical real-world portfolio selection problems involve large number of potential investments. However, basic model does not change -Computing variance does become more cumbersome 2. If company is allowed to short a stock, fraction invested in that stock is allowed to be negative -To implement this, eliminate non-negativity constraints on the changing cells 3. 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 likely range of variation in objective function given assumptions about probability distributions of parameters 2. Allows us to consider thousands of cases (using distributions) 3. Can be used to model 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 uncertain quantity is not really continuous, or 3. When you want discrete approximation to a continuous variable 4. need to specify possible values and their probabilities, making sure that 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 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 supplier can fulfil demand or not *If statement

Probability distributions for input variables

1. building blocks of spreadsheet simulation models 2. primary difference between other spreadsheet models and simulation models is that at least one of 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 possible values of a variable and 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

Simulation model

1. 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

@RISK Models with a Single Random Input Variable

1. development of a simulation model is basically a two-step procedure -First, build model itself -This step requires you to enter all 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 -also reports any summary measures that you request in tabular or graphical form.

Scenario Analysis

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

Normal distribution

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

Advantages of @Risk

1. gives easy access to many probability distributions you might want to use in simulation models 2. allows you to perform simulations much more easily than is possible with Excel alone 3. advantage with @Risk is that 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 same random numbers used for all the simulations

Defining range names

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

Drawbacks of scenario analysis

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

Covariance

1. measure of how much two random variables change together 2. Cov(x,y) = corr(x,y) * stdev of x * stdev of y 3. variance of variable x would be: σX^2 = Cov (X,X) / ρXX 4. Since 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)

Information to draw from the simulations

1. minimum, maximum, mean, median, and standard deviation of profit for each simulation 2. value at risk (profit at the 5th percentile) for each simulation and the 95th percentile value 3. probability that profit is negative 4. A statement of which variables have largest influence on profit, according to results of 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?

Deterministic checks

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

Return of a portfolio

Return (Ep) = weight1 * E(R1) + weight2 * E(R2) *If only one asset, the return of theportfolio 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%