2. 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 destinations listed. If at any time, JTM finds that this is not going to work, they will pull the plug on everything.

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 everything.

d. 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 and you lose part of your work. You have to complete it for a presentation. Please use the Excel template provided to complete this

Complete All Highlighted sections.

File Horne Inser! Draw Page Layout Formulas Dala Review View Help Acrobat Comments Share 1 v Ruhr Formula Bar Q mo A Koop Exit Now Options Normal Page Break Page Custom Previn layout Views Gridlines Headings Zoom 100% Toom to Selection New Window Split View Side by Side Hine IDI Syncro Scaling Panes Unhide Det Windows Window Switch Windows Macros AI She Vic Workbook Views Shaw Zoom Meer JZ L T G II I J K L M N 3 3.8 3.7 4 4 4.6 4.5 5 5.3 5.2 6 6.1 6.0 = 0.1 0.1 0.1 0.1 5.0 A B C D 1 JTM 2 Rates: 3 Discount rate 7.0% 4 Risk-free rate 2.5% 5 Scenario: No Real Options 6 Start 1 2 7 Cash from Operations 2.3 3.1 8 minus: Capital Expenditures 1.0 2.2 3.0 9 = Net Cash Flow = (1.0) 0.1 0.1 10 Terminal Value 11 PV of NCT 2.84 12 Scenario: Real Options 13 Start 1 2 14 Cash from Operations 2.3 3.1 15 minus: Capital Expenditures 1.0 16 = Net Cash Flow (1.0) 2.3 3.1 17 Terminal Value 18 PV of NCF 7.17 19 PV of Cap. Ex. (Yrs. 1-2) 20 21 22 Option Pricing: PV of Cap. Ex. (Yrs. 1-2) Maturity PV of NCF Risk free rate Volatility BS calculations: d1 #DIV/0! N(01) #DIV/0! d2 #DIV/0! Nd2) #DIV/0! Price of call #DIV/0! Difference: - Value of Call over No Real Option -% of PV of No Real Option 6 6 6.1 3 3.8 3.7 0.1 4 4.6 4.5 0.1 5 5.3 5.2 0.1 6.0 0.1 5.0 4.33 Answer part B, 4 in the box below: 23 24 Prob. 1 Prats 2 JAN 22 . . 2 W. Hewly Count:21 ampley Settings 7417A x T 10:17 AM 1/30/2022 File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Camments Share Times New Roman - 12 AA - 19 Wrap Text AutoSum YO 27 Pasto X Cut [Copy s Format Panto Clipboard 0 BTU 90 B 93 Merge & Center $ $ % 98 99 sort De Fom Conditional Format Cell Formatting Styles Syles Sort Find Sir Solat Analys Data Clear Links Font Alignment Number Gails Isling Analysis K18 H 1 J K L L 1 6 6 7 6.05 8 5.95 9 =117-118 10 3 am+nONE 99999 Option Pricing: PV of Cap. Ex. (Yrs. 1-2) Maturity PV of NCF Risk free rate Volatility BS calculations: dl N(dl) d2 N(22) Price of call Difference: - Value of Call over No Real Option - % of PV of No Real Option 12 13 G 14 6.05 15 5.95 +(LN(K6/K4)+(K7+K82/2)*K5)((K8*K5^0.5)) NORMSDIST(K10) =+K10-(K8)*K540.5 VORMSDIST(K12) =+K6 K11-(K4"K13"(2.71828^((-K7)"K5))) 16 =H14-H15 17 5 18 19 20 Answer part B, 4 in the box below: 22 24 25 26 Prob. 1 Prabs. 2 JAN 22 V. Inter Empley Smilings N K D 1024 AM 1/30/2022 File Home Insert Draw Page Layout Formular Data Review View Help Acrobat Comments Share = >AutoSum Times New Roman 12 AA = 29 Wrap Text General AY Ich) Pasto B 1 U A + Merge & Center Insert Die Form La Copy fomat Painter Cipboard Conditional Formats Coll Formatting Table Styles Sykes Sort & Fida Filter Select Edling Analyze Data Lintlo Font Alignment Number E Cela Analysis H25 1 B C D E F G H 1 JTM 1 2 Rates: 3 Discount rate 4. Risk-frcc ratc 5 0.07 0.025 Jario: No Real Opt Start 1 1 2.3 2.2 =B7-B8 =C7-C8 Option P PV of Cap Maturity PV of NC Risk free free Volatility 3 3.8 3.7 5 5.3 2 3.05 2.95 =D7-D8 4 4.55 4.43 =17-18 5.2 6 6.05 5.95 =117-118 =7-18 =G7-G8 BS calcul 5 =NPV(B3,B9:H9 nario: Real Optic Start 1 0 23 1 =B14-B15 C14-15 2 305 3 38 3.7 =E14-15 4 4.53 4.45 5 53 52 6 6.05 5.95 =H14-H15 di N(dl) d2 12 N(22) Price of c Differenc - Value o - % of PV D14-015 =F14-F15 7 Cash from Operations 8 minus: Capital Expenditures 9 = Net Cash Flow 10 Terminal Value 11 PV of NCF 12 12 13 14 Cash from Operations 15 minus: Capital Expenditures 16 = Net Cash Flow 17 Terminal Value 18 PV of NCF 19 PV of Cap. Ex. (Yrs. 1-2) 20 21 22 23 - 24 25 25 26 27 28 =G14-G15 =NPV(B3,B16: =B18-B11 10 Prob. 1 Prabo. 2 JAN 22 | V. pley Smitings M ! FA W K 1025 AM 1/30/2022