B D E F G H 1 K M N o P 1 10.0% 6.0% 4 10 15.2 1 4.2 17.0 -12.8 15.9 2 11.9 17.5 -5.6 15 15.2 Scenario: No real Option 6 7 8 9 8.0 9.8 12.8 1.3 0.3 6.7 12.5 15.9 3 12.9 5.3 7.6 9.4 4.1 5.3 5 8.1 3.5 4.6 11 14.9 8.1 6.8 12 14.6 -0.1 13 15.5 0.2 15.3 14 16.6 0.1 0.1 0.1 9.7 15.1 14.7 16.5 15.2 67.8 3 Discount Rate Risk free rate 5 6 7 Cash from Operations 8 [-] Capital expenditures 9 Net cash flow 10 Terminal Value 11 PV of NCF 12 13 14 Cash from Operations 15 - Capital expenditures 16 Net cash flow 17 Terminal Value 18 PV of NCF 19 PV of Cap Ex. (Years 1-2 58.8 3 1 4.2 2 11.9 13 15.5 3 12.9 5.3 7.6 15 15.2 4 9.4 4.1 5.3 Scenario: Real Option 8 9 9.8 12.8 15.9 0.1 0.3 9.7 12.5 15.9 5 8.1 3.5 4.6 6 8.0 1.3 6.7 10 15.2 0.1 15.1 11 14.9 8.1 6.8 12 14.6 -0.1 14.7 14 16.6 0.1 16.5 0.2 15.3 4.2 11.9 15.2 67.8 88.7 29.9 Snapshot in formula mode: D E F H 1 K 1 M N Discount Rate Risk free rate 0.06 Scenario: No real Option 1 De -F61 361 -6 ME -06 Cash from Operations 4.2 12.9 9.4 8 15.9 15.2 14. 155 16.6 15.2 8 H Capital expenditures 17 175 53 41 35 113 03 0 01 RI 01 02 0.1 0 9 Net cash flow -B-88 CH-CS 01-08 -E.ES 53-F8 048 MM -N-NS 07-08 -P-PB 10 Terminal 11 PV of NCF -NPVS853.89:10/(1+$B$3"6 12 Scenario Real Option 13 -D131 131 1341-61311 41341151151 14 Cash from Operation 12 1341 131 M1341-N13:1 -011 12. 8 15.5 16.6 15.2 15 H Capital expenditures 53 35 1.3 101 03 101 RI 0. 0 16 Met cash flow C1-C15 014-015 14-18 14-15 GIGIS -H14-15-14-15-14-15-14-15-14-L15 M14-MIS NI NIS014-035-P14-P15 17 Terminal 18 PV of NCS -NPVS$3,816P16P12/1$B$3913 19 PV of Cap Es. (Years 1-23-818-811 For the Black Scholes Model Airlines Airport Expansion ted Value Start Phase 1 Phase II Reven ues 300 Probabili ty Costs Phase III Success 55% 25 Net East Coast 20% 34 Failure 45% 20 180 Success 55%. 29 West Coast 33% 40 Failure 45% 22 Success 66% 30 410 Success 70%. 23 Caribbean 25%. 43 Start Failure 30% 32 7 Failure 22%. 33 Failure 34% 40 3. Decision Tree JTM really liked your work on the option pricing of the gates, so they ask you to look at their 3-phase expansion at their home airport. The three phases are: a. Upon purchase of the new gates, start a marketing program to promote JTM's routes to the East Coast, West Coast, and the Caribbean. If all goes well and the market is receptive, they will go on to phase 2. b. Phase 2 has JTM invest in new routes to the three sets of destinations listed. If at any time, JTM finds that this is not going to work, they will pull the plug on phase 2 and scratch the project. c. Phase 3 has JTM start the new routes to the destinations listed. If things don't go well on any of the three destinations, they will pull the plug on phase 3 and scratch that destination out. After much work with other departments, you generate enough data to calculate the NPV of the 3-phase expansion. Before you have a chance to save all your work, there is a power spike in the building, and you lose part of your work. You have to go back and complete it so you can present it to your manager. Please use the Excel template provided to complete problem 3. B D E F G H 1 K M N o P 1 10.0% 6.0% 4 10 15.2 1 4.2 17.0 -12.8 15.9 2 11.9 17.5 -5.6 15 15.2 Scenario: No real Option 6 7 8 9 8.0 9.8 12.8 1.3 0.3 6.7 12.5 15.9 3 12.9 5.3 7.6 9.4 4.1 5.3 5 8.1 3.5 4.6 11 14.9 8.1 6.8 12 14.6 -0.1 13 15.5 0.2 15.3 14 16.6 0.1 0.1 0.1 9.7 15.1 14.7 16.5 15.2 67.8 3 Discount Rate Risk free rate 5 6 7 Cash from Operations 8 [-] Capital expenditures 9 Net cash flow 10 Terminal Value 11 PV of NCF 12 13 14 Cash from Operations 15 - Capital expenditures 16 Net cash flow 17 Terminal Value 18 PV of NCF 19 PV of Cap Ex. (Years 1-2 58.8 3 1 4.2 2 11.9 13 15.5 3 12.9 5.3 7.6 15 15.2 4 9.4 4.1 5.3 Scenario: Real Option 8 9 9.8 12.8 15.9 0.1 0.3 9.7 12.5 15.9 5 8.1 3.5 4.6 6 8.0 1.3 6.7 10 15.2 0.1 15.1 11 14.9 8.1 6.8 12 14.6 -0.1 14.7 14 16.6 0.1 16.5 0.2 15.3 4.2 11.9 15.2 67.8 88.7 29.9 Snapshot in formula mode: D E F H 1 K 1 M N Discount Rate Risk free rate 0.06 Scenario: No real Option 1 De -F61 361 -6 ME -06 Cash from Operations 4.2 12.9 9.4 8 15.9 15.2 14. 155 16.6 15.2 8 H Capital expenditures 17 175 53 41 35 113 03 0 01 RI 01 02 0.1 0 9 Net cash flow -B-88 CH-CS 01-08 -E.ES 53-F8 048 MM -N-NS 07-08 -P-PB 10 Terminal 11 PV of NCF -NPVS853.89:10/(1+$B$3"6 12 Scenario Real Option 13 -D131 131 1341-61311 41341151151 14 Cash from Operation 12 1341 131 M1341-N13:1 -011 12. 8 15.5 16.6 15.2 15 H Capital expenditures 53 35 1.3 101 03 101 RI 0. 0 16 Met cash flow C1-C15 014-015 14-18 14-15 GIGIS -H14-15-14-15-14-15-14-15-14-L15 M14-MIS NI NIS014-035-P14-P15 17 Terminal 18 PV of NCS -NPVS$3,816P16P12/1$B$3913 19 PV of Cap Es. (Years 1-23-818-811 For the Black Scholes Model Airlines Airport Expansion ted Value Start Phase 1 Phase II Reven ues 300 Probabili ty Costs Phase III Success 55% 25 Net East Coast 20% 34 Failure 45% 20 180 Success 55%. 29 West Coast 33% 40 Failure 45% 22 Success 66% 30 410 Success 70%. 23 Caribbean 25%. 43 Start Failure 30% 32 7 Failure 22%. 33 Failure 34% 40 3. Decision Tree JTM really liked your work on the option pricing of the gates, so they ask you to look at their 3-phase expansion at their home airport. The three phases are: a. Upon purchase of the new gates, start a marketing program to promote JTM's routes to the East Coast, West Coast, and the Caribbean. If all goes well and the market is receptive, they will go on to phase 2. b. Phase 2 has JTM invest in new routes to the three sets of destinations listed. If at any time, JTM finds that this is not going to work, they will pull the plug on phase 2 and scratch the project. c. Phase 3 has JTM start the new routes to the destinations listed. If things don't go well on any of the three destinations, they will pull the plug on phase 3 and scratch that destination out. After much work with other departments, you generate enough data to calculate the NPV of the 3-phase expansion. Before you have a chance to save all your work, there is a power spike in the building, and you lose part of your work. You have to go back and complete it so you can present it to your manager. Please use the Excel template provided to complete problem 3