X V fix {=CB.Triangular(D2, C2,E2)} A B C D E F G Event Probability per year lost Likely Loss if occured ($1\\\\ Minimum Loss Maximum Loss Actual Impact if Occurs Expected Loss IT system, major failure 1.0% 5.0 3 7 4.173002261 Problem with manufacturing process 2.5% 1.0 0.5 1.5 0.756904906 Serious illness of a Board member 5.0% 0.1 0.05 0.15 0.113558938 Employee wins law suit 8.0% 2.5 2 3 2.214519601 Entry of new competitor 10.0% 10.0 5 15 13.84765294 Failure of new product launch 7.5% 6.0 4 8 5.919245878 Strengthening of $ Xrate 35.0% 1.0 0.5 1.5 0.827902642 Fire in head office 2.0% 2.5 2 3 2.779768207 Fraud 0.5% 5.0 6 4.979724881 Confidential data lost 1.0% 3.0 N 4 2.602419075 Large customer goes bankrupt owing 2.0% 5.0 money 5 5 5 Total Loss ($1,000,000)Problem 1 (25 points) A risk manager of a company has been trying to estimate the aggregated impact of many possible events to the company. The manager has listed potential events that could happen in the coming year, and has assigned a probability of occurrence to each event. The manager has also estimated the financial impact of each event. The data is summarized in Table 1. Table 1. List of Potential Events Event Probability per year Impact if Occurs ($1,000,000) Expected Loss ($1,000,000] IT system, major failure 10% 5.0 D.DS Problem with manufacturing process 2.5% 1.0 01025 Serious illness of a Board member 5.0% 0.1 0.DOS Employee wins law suit 8.0% 2.5 0.2 Entry of new competitor 10.0% 10.0 1 Failure of new product launch 6.0 0.45 Strengthening of $ Xrate 35 0% 1.0 0.35 Fire in head office 2.0% 2.6 D.DS Fraud 0.5% 5.0 0.025 Confidential data lost 3.0 0.03. Large customer goes bankrupt owing money 2.0% 5.0 10.1 Total 2.285 For example, with 1% probability, there will be a major IT system failure, and when it occurs, the company expects a loss of $5 million. Similarly, with 7.5% probability, the launch of a new product will fail, and that will bring a loss of $6 million to the company. Based on this information, the risk manager has calculated the expected loss of each event. For example, the expected loss of a major failure of the IT system is $0.05 million (1%*5 million). Based on this calculation, the expected total loss of these potential events is $2.285 million. However, the manager realizes that this simple analysis has (at least) two issues: 1. It does not provide useful risk measures such that the standard deviation and 95% Value-at-Risk of the total loss. 2. A fixed amount has been estimated for the financial loss of each event. In order to be more realistic, the loss of each event should be modeled as a probability distribution. As a first step to address the second issue, the manager has decided to use triangular distributions to model financial loss of the events. For example, the minimum loss of a major IT system failure is $3 million, the maximum loss is $7 million, while the most likely loss is $5 million. Then the loss of this event can be modeled as, in Crystal Ball, CB.Triangular(3,5,7) Please refer to ImpactMany Events.xlax for details. Ewint Probability per year Most Likely Loss if occured ($1M] Minimum Loss Maximum Loss IT system, major failure 1.0% 5.0 7 Problem with manufacturing process 2.5%% 1.0 0.5. Serious Illness of a Board member 5.0%% 0.1 0.0 Employee wins law sult 8.0% 2.5 2 3 Entry of new competitor 10.0% 10.0 15 Failure of new product launch 7.5% 6.0 4 Strengthening of $ Xrate 35.0% 1.0 OLS 15 Fire in hand office 2.0% 2.5 2 on w Fraud 0.5% 5.0 4 Confidential data lost 1.0% 3.0 Large customer goes bankrupt owing money 2.0% 5.0 Q1. Based the assumptions above, Simulate the total aggregated impact (loss) of the potential events listed Table 1 using Crystal Ball with 10,000 trials. What is the simulated expected total loss? Q2. What is the 95% VaR of the total loss