Part 1. In your new role as a project financial analyst, you are tasked to evaluate project Tetra, a new and innovative software that allows doctors from around the world to communicate current best practices in real time. The project has two phases: you may invest in the first, both, or neither. Phase 1 (aka Tetra 1) requires an initial investment of $300. One year later, Tetra 1 will produce project CFs of either $560 or $60, each with equal probability of occurrence. The required return on the project is 9%.

I would like some help to get going.

How do you calculate cell b80 and 81?

At this point (year end 1) and after cash flows have been received, you have an option to simultaneously invest an additional $100 for Tetra 2. One year later, Tetra 2 pays out either 20% more in project CFs than Tetra 1 or (equally likely) 20% less. No taxes need to be 2 assumed. 3 Part 1 a: How much would the Tetra project be worth if it offered only the Tetra phase 1 opportunity? Part 1 b: How much would Tetra be worth if you had to make the entire decision today, once and for all, whether or not to invest in Tetra 4 (i.e. both phase 1 and 2)? Part 1 c: How much is Tetra project worth if you have access to both Tetra 1 and 2, but can wait to decide whether to invest in Tetra 2 5 after the first year (i.e. can see Tetra 1 through before deciding)? Utilize 6 Typing Numbers is okay The Scenario Labels Equations/functions 7 8 0.09 required return 9 $ 0 (300.00) ($300.00) $213.76 1 560 $513.76 $ 10 11 Part 1 a 12 Tetra 1 best 13 Project CF 14 PV Project CFS 15 NPV 16 17 Tetra 1 worst 18 Project CF 19 PV Project CFS 20 NPV 21 NPV of whole project 22 23 Part 1 b 24 Tetra 1 best, Tetra 2 best 25 CAPEX 26 OCF 27 project CF 28 NPV 29 Tetra 1 best, Tetra 2 worst 30 CAPEX (300.00) 60 ($300.00) $55.05 ($244.95) -$15.60 accept/reject? (choose 1) Reject 0 1 2 -$100 $560 $460 $672 $672 -$300 $0 -$300 $687.63 0 -$300 1 2. -$100 2 $672 $672 -$300 2 $448 $448 2 $72 $72 2 23 Part 1 b 24 Tetra 1 best, Tetra 2 best 0 1 25 CAPEX -$300 -$100 26 OCF $0 $560 27 project CF $460 28 NPV $687.63 29 Tetra 1 best, Tetra 2 worst 0 1 30 CAPEX -$300 -$100 31 OCF $0 $560 32 project CF -$300 $460 33 NPV $499.09 34 Tetra 1 worst, Tetra 2 best 0 1 35 CAPEX -$300 -$100 36 OCF $0 $60 37 project CF -$300 -$40 38 NPV -$276.10 39 Tetra 1 worst, Tetra 2 worst 0 1 40 CAPEX -$300 -$100 41 OCF $0 $60 42 project CF -$300 -$40 43 NPV -$296.30 44 NPV overall accept/reject? (choose 1) 45 46 Part 1 c, after year 1 47 Tetra 2 best 0 1 48 CAPEX 49 OCE 50 project CF 51 NPV tetra 2 best at year 1. 52 Tetra 2 worst 0 53 CAPEX 54 OCF 55 project CF 56 NPV tetra 2 worst year 1. 57 NPV total tetra 2 at year 0. Hints: value Tetra 2 on it's own (ignore relevant CFs from Tetra 1). First at time 1, 58 then discount back to time 0. $48 $48 2 2 59 NPV for whole project & option accept/reject? (choose 1) Hints: You've already valued Tetra 1 in "Part 1a" (NPV from cell B21), now cell B57 is Hints: You've already valued Tetra 1 in "Part 1a" (NPV from cell B21), now cell B57 is the NPV of Tetra 2, but remember the likelihood of taking on Tetra 2 is not 100%...calculate NPV of the whole project and the real option. 60 61 62 You're considering the timing option presented for Tetra. Currently, you believe there are 4,200 customers for tetra that will pay and generate a cash inflow of $900 per customer. This stream of CFs will last for 8 years from the time you introduce your software, whether this year or next. The required return is 12% and the intitial cost for this larger scale is $17.4 million. However, if you wait one year to start in order to better understand the healthcare market, there is a 60% chance that the price per customer will be $1,150 and a 40% chance it will be 63 $700. What is the value of the timing option of waiting? 2 3 4 5 6 7 8 I'll set up the template for the starting today scenario, you must set up the timing 64 options. Please highlight your NPVs and option value with a pink color. Hints: You will have 3 cash flow timelines/NPVs built in this section. 1 for starting today and 2 for starting in one year from today. The value of the timing option would be the difference between the projects value starting today versus the project value 65 starting in one year. 66 Tetra timing today 0 67 CAPEX 68 OCF $0 .. 69 project CF 70 NPV tetra timing today 71 Tetra timing year 1 vi 72 CAPEX 73 OCF 74 project CF 75 NPV tetra timing today v1 76 Tetra timing year 1 V2 77 CAPEX 78 OCE 79 project CF 80 NPV tetra timing today v2 Overall NPV of Tetra Timing 81 V1 and V2 82 Value of Tetra Timing Option 83 85 At this point (year end 1) and after cash flows have been received, you have an option to simultaneously invest an additional $100 for Tetra 2. One year later, Tetra 2 pays out either 20% more in project CFs than Tetra 1 or (equally likely) 20% less. No taxes need to be 2 assumed. 3 Part 1 a: How much would the Tetra project be worth if it offered only the Tetra phase 1 opportunity? Part 1 b: How much would Tetra be worth if you had to make the entire decision today, once and for all, whether or not to invest in Tetra 4 (i.e. both phase 1 and 2)? Part 1 c: How much is Tetra project worth if you have access to both Tetra 1 and 2, but can wait to decide whether to invest in Tetra 2 5 after the first year (i.e. can see Tetra 1 through before deciding)? Utilize 6 Typing Numbers is okay The Scenario Labels Equations/functions 7 8 0.09 required return 9 $ 0 (300.00) ($300.00) $213.76 1 560 $513.76 $ 10 11 Part 1 a 12 Tetra 1 best 13 Project CF 14 PV Project CFS 15 NPV 16 17 Tetra 1 worst 18 Project CF 19 PV Project CFS 20 NPV 21 NPV of whole project 22 23 Part 1 b 24 Tetra 1 best, Tetra 2 best 25 CAPEX 26 OCF 27 project CF 28 NPV 29 Tetra 1 best, Tetra 2 worst 30 CAPEX (300.00) 60 ($300.00) $55.05 ($244.95) -$15.60 accept/reject? (choose 1) Reject 0 1 2 -$100 $560 $460 $672 $672 -$300 $0 -$300 $687.63 0 -$300 1 2. -$100 2 $672 $672 -$300 2 $448 $448 2 $72 $72 2 23 Part 1 b 24 Tetra 1 best, Tetra 2 best 0 1 25 CAPEX -$300 -$100 26 OCF $0 $560 27 project CF $460 28 NPV $687.63 29 Tetra 1 best, Tetra 2 worst 0 1 30 CAPEX -$300 -$100 31 OCF $0 $560 32 project CF -$300 $460 33 NPV $499.09 34 Tetra 1 worst, Tetra 2 best 0 1 35 CAPEX -$300 -$100 36 OCF $0 $60 37 project CF -$300 -$40 38 NPV -$276.10 39 Tetra 1 worst, Tetra 2 worst 0 1 40 CAPEX -$300 -$100 41 OCF $0 $60 42 project CF -$300 -$40 43 NPV -$296.30 44 NPV overall accept/reject? (choose 1) 45 46 Part 1 c, after year 1 47 Tetra 2 best 0 1 48 CAPEX 49 OCE 50 project CF 51 NPV tetra 2 best at year 1. 52 Tetra 2 worst 0 53 CAPEX 54 OCF 55 project CF 56 NPV tetra 2 worst year 1. 57 NPV total tetra 2 at year 0. Hints: value Tetra 2 on it's own (ignore relevant CFs from Tetra 1). First at time 1, 58 then discount back to time 0. $48 $48 2 2 59 NPV for whole project & option accept/reject? (choose 1) Hints: You've already valued Tetra 1 in "Part 1a" (NPV from cell B21), now cell B57 is Hints: You've already valued Tetra 1 in "Part 1a" (NPV from cell B21), now cell B57 is the NPV of Tetra 2, but remember the likelihood of taking on Tetra 2 is not 100%...calculate NPV of the whole project and the real option. 60 61 62 You're considering the timing option presented for Tetra. Currently, you believe there are 4,200 customers for tetra that will pay and generate a cash inflow of $900 per customer. This stream of CFs will last for 8 years from the time you introduce your software, whether this year or next. The required return is 12% and the intitial cost for this larger scale is $17.4 million. However, if you wait one year to start in order to better understand the healthcare market, there is a 60% chance that the price per customer will be $1,150 and a 40% chance it will be 63 $700. What is the value of the timing option of waiting? 2 3 4 5 6 7 8 I'll set up the template for the starting today scenario, you must set up the timing 64 options. Please highlight your NPVs and option value with a pink color. Hints: You will have 3 cash flow timelines/NPVs built in this section. 1 for starting today and 2 for starting in one year from today. The value of the timing option would be the difference between the projects value starting today versus the project value 65 starting in one year. 66 Tetra timing today 0 67 CAPEX 68 OCF $0 .. 69 project CF 70 NPV tetra timing today 71 Tetra timing year 1 vi 72 CAPEX 73 OCF 74 project CF 75 NPV tetra timing today v1 76 Tetra timing year 1 V2 77 CAPEX 78 OCE 79 project CF 80 NPV tetra timing today v2 Overall NPV of Tetra Timing 81 V1 and V2 82 Value of Tetra Timing Option 83 85