Case Studio – Excel Simulation Modeling

Introduction
Read the case Ontario Gateway from the course pack,

answer the questions that follow below.
http://www.meiss.com/columbia/en/teaching/2000/summer/B6015/ontario.pdf
(Please ignore enclosure 4)
________________________________________The Assignment
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).

Case Studio – Excel Simulation Modeling

Introduction
Read the case Ontario Gateway from the course pack,

answer the questions that follow below.
http://www.meiss.com/columbia/en/teaching/2000/summer/B6015/ontario.pdf
(Please ignore enclosure 4)
________________________________________The Assignment
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).

Case Studio – Excel Simulation Modeling

Introduction
Read the case Ontario Gateway from the course pack,

answer the questions that follow below.
http://www.meiss.com/columbia/en/teaching/2000/summer/B6015/ontario.pdf
(Please ignore enclosure 4)
________________________________________The Assignment
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).