1 / 26

Introduction to Risk Analysis

Introduction to Risk Analysis. Using Excel. Learning Objective. Time management. Methods. (1) the analytical, mathematical approach and (2) the Monte Carlo simulation technique. Warm-up.

aggie
Download Presentation

Introduction to Risk Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Risk Analysis Using Excel

  2. Learning Objective

  3. Time management

  4. Methods • (1) the analytical, mathematical approach and • (2) the Monte Carlo simulation technique.

  5. Warm-up • The president of the small Pharmaceutical company must make the final decision about whether to market a new kind of cough drop. The yearly forecast for this venture is as follows:

  6. Warm-up • Considering a five year product live and 20% discount of the profit stream, analyze this venture using present value method and present some considerations about the decision based in your calcules.(10 minutes)

  7. Uncertainties

  8. Additional Information • Calculate the base-case, best-case and worst-case scenarios, through excel using these additional information (10 minutes): • Total industry sales of cough drops will be between $10 million and $20 million.• • The company's market share will be between 20% and 50%.• • The price will be between 10 and 20 cents per ounce. • The fixed cost of manufacturing will be between $100,000 and $300,000.• • The variable cost rate of production will be between 5 and 10 cents.

  9. Scenarios

  10. Uncertainties • There is a great deal of uncertainty in this venture. The board of directors is quite risk‑averse, so they want to know: • How good is the base‑case estimate? • What is the variability in the profit function? • What are the chances of making a profit? • What are the chances of making a profit of $500,000 or more? • What is the probability of a loss?

  11. What is Risk Analysis? • Consider now that: • Marketing believes, based on past records, competitive products, and intuition, that total industry sales will be around $15 million. • They believe it is very unlikely that sales will be less than $10 million or more than $20 million, but they are unable to decide the likelihood of any particular sales figure within that range. • Any value is equally likely. In other words, marketing feels that the probability distribution of total industry sales is a flat‑line segment between $10 million and $20 million, as shown in the next slide.

  12. 10 20 30 Total industry sales in millions of dollars Probability Distribution Probability Distribution of total industry sales

  13. 10 20 30 Total industry sales in millions of dollars The cumulative probability Distribution • The probability that random variable is “up to” a certain value is represented by the area under the probability distribution. Cumulative Probability Cumulative Probability Distribution 1.0 0.5

  14. Risk simulation process

  15. Risk analysis • Risk analysis calculates measures of uncertainty of the output variables, such as sales, profit, labor required, and so on. These measures include expected value, variance, standard deviation, median, mode, the complete output probability distribution, and the cumulative probability distribution.

  16. Risk analysis scenarios • The scenarios include not just single estimates of the variables but also the calculated probability values associated with critical factors, and answers to such questions as: • What is the probability that there will be no profit?• • What is the probability that the profit will be over $1,000,000?• • What is the probability that the project will be late by 20%? • What is the probability that the break‑even point will be under 1,000 units? • What is the probability that lost sales will be under 1,500 units?

  17. Using Excel: step 1 • The first thing you need to learn is how to generate random numbers using the Excel function RAND(). • When you enter this function, a random number between 0 and 1 appears. • This is an unusual function, for two reasons. First, it has no argument; that is, nothing goes inside the parentheses. However, the parentheses are required. Second, each time the worksheet is recalculated, a new random num­ber appears automatically. You should play with the RAND( ) function to under­stand how it works.

  18. Uniform random number • Uniform random distribution between the lower limit L and upper limit U: "=L+((U‑L) *RAND())". • For whole numbers: "=RANDBETWEEN(100,150)"

  19. Open the model The model

  20. The model

  21. Runs In order to repeat the simulation, a table was built in cells B25 to C124 based on the results in cells B11 and B12.

  22. Results

  23. Results • Cells B15 to B21 summarize the results of the table. These of runs. • The average will hover around $190,000. • The values of sigma and CF are warnings of the uncertainty involved. Indeed, the probability of loss hovers around 22%.

  24. Probability Distribution function

  25. Making Decision • • The expected yearly profit is $193,402.• • The standard deviation is $224,911.• • There is approximately a 20% chance of a loss.• • There is approximately an 18% chance that the yearly profit will be greater than $400,000.

  26. Reference • Operations Analysis Using Excel. Weida; Richardson and Vazsony, Duxbury, 2001, Chapter 12.

More Related