Compare the insurance offers that are on the table and summarize your recommendation. Base your conclusions on thorough quantitative analysis following the steps outlined below.
Step 1: Build (a static) model of all the insurance plans.
Build a spreadsheet model that calculates the costs of each insurance plan. The cost of an insurance plan is the insurance premium, plus any deductibles and uncovered losses, minus any rebates. In all insurance plan descriptions assume losses refer to both incidental as well as crashes (aircraft replacement costs).
Step 2: Add the simulation functionality
The plans' costs are a function of the number of crashes that happen. For simplicity you may assume that a plane that has an accident would get replaced immediately and that the probability of a plane having more than one crash in a year is negligible (and therefore assumed zero.)
This step therefore requires us to calculate the probability that a plane has a crash in a given year. For any given flight either a plane has a crash or not. Let q note the probability of a crash for a plane on a single flight. Therefore if a plane flies k flights a year, the probability of a crash in a given year can be calculated as:
The probability of a crash
= 1- probability of no crash
= 1 - P(no crash on first flight)P(no crash on second flight) ... P(no crash on last flight)
= 1- (1-P(crash on first flight)) (1-P(crash on first flight))... (1-P(crash on first flight))
= 1-(1-q)(1-q)...(1-q)
= 1-(1-q)k
Now the case requires us to model the number of crashes of each type of aircraft (because the replacement costs and the number of flights differ for each plane). Therefore using the simplifying assumptions above, we can model the number of crashes of each type of aircraft as a binomial random variable, with n equal to the number of aircrafts and p equal to the probability of a crash in a given year for a single airplane (given by the calculation above).
Further you may assume that the incidental aircraft damages have a uniform distribution. Ontario Gateway is focusing on the costs of the insurance plans. Make one of your outputs be the five year costs of the RCNC1 plan (you will need others).
Step 3: Run the simulation
Run the simulation and analyze the output. In particular, what are the important outcomes for Ontario Gateway? How do these compare across the contracts?
Step 4: Analyze two plans in more detail
Select the two most promising plans and compare their costs. Include in that comparison the likelihood that one plan turns out to be cheaper than the other.
Deliverable Instructions
Please hand in:
- A write up that includes:
- a) A concise summary of your recommendation to Mary Litton based on your simulation results, analysis and judgment. - 1 paragraph max
- b) A picture of the distribution of the 5-year insurance costs of the RCNC1 plan and an explanation of the distribution's shape.
- c) Summary of your analysis in step 3 (if it is not included as a part of your summary in a). Please limit your discussion and/or exhibits to a page. 1 paragraph max
- d) Your reasoning for the plans you selected to analyze in step 4 and a brief comparison (if not included as a part of your summary in a). Please limit your discussion and/or exhibits to a half a page.
- A well-formatted, easy-to-read, Excel spreadsheet containing your model. The Excel Model will not be directly graded, but will be used to assist in evaluating the answers as needed.