C16 X V fx =@RiskNormal(0%,9%, RiskCorrmat(NewMatrix1,1)) A B C D E F G H K L M N O P Q R S T U 1 Megatron Hedging Spreadsheet 2 Euro receivable in one year 322.5 (in million EU) 3 British Pound receivable in one year 234.1 (in million pounds) 4 Current Euro rate 1.3026 (in US$/EU) 5 Current British pound rate 1.434 (in US$/British pound) Strike price of 1-year Euro put options 1.2000 7 Cost per Euro put option 0.016776 (in US$) 8 Number of Euro put options 250 (in million) Strike price of 1-year British pound put options 1.2000 10 Cost per British pound put option 0.003808 (in US$) 11 Number of British pound put options 175 (in million) 12 Euro/British pound correlation 67.5% @RISK Corre Euro / Return British pound / Return in $C$17 13 Euro volatility 9% Euro / Return 100.00% 14 British pound volatility 11% British pound 67.50% 1 15 Return Value 16 Euro 0.00% 1.3026 $/EU 17 British pound 0.00% 1.4340 $/BP 18 19 EU Put option payoff 0.0000 (in US$) 20 BP put option payoff 0.0000 (in US$) 21 22 EU Revenue in one year (unhedged) 420.0885 (in million US$) 23 BP Revenue in one year (unhedged) 335.6994 (in million US$) 24 Total Revenue in one year (unhedged) 755.7879 (in million US$) 25 26 EU Revenue in one year (hedged) 415.8945 (in million US$) 27 BP Revenue in one year (hedged) 335.0331 (in million US$) 28 Revenue in one year (hedged) 750.9276 (in million US$) 29 30 Likelihood of hedged revenue being >=US$ 31 706 million 32 Mean Revenue Hedged 750.927581 33 34 Mean Revenue Unhedged 755.7879 35 36 hedging FX_Data +W N - Strike Price for EU Cost Strike Price for BP Cost (in $) (in $) (in $) (in $) 1.1 0.002802 1.10 0.000285 1.18 0.012582 1.15 0.001318 8 1.2 0.016776 1.20 0.003808 9 1.22 0.021702 1.25 0.009133 10 1.24 0.027422 1.30 0.01876 11 1.26 0.034002 1.35 0.032937 12 1.28 0.04159 1.40 0.052355 13 1.3 0.064382 1.45 0.096025 14 1.32 0. 17171 1.50 0. 159427 15 16 17 18 19 20 21 22 23 24 25 26 27 hedging FX_Data +In this assigmnent I would like your team to take the @RISK spreadsheet model megatronxlsx provided to you by your instructor and use it to perform analysis to provide a recommendation to Ramesh Srinivasan (CEO of Megatron) on the hedging strategy for Megatron's next year's earnings. Ramesh Srinivasan would like to determine if hedging is a good strategy for Megatron, and if so what the best combination of number of put options and strike price are. For pedagogical reasons it is suggested that the analysis should be done in two parts as follows: (a) Keep the number of put options Megatron buys xed at 250 million put options on EU and 175 million put options on BP. Megatron is considering the following 4 potential strike prices for EU: $1.20, $1.24, $1.28, and $1.32; and the following 4 potential strike prices for BP: $1.20, $1.30, $1.40 and $1.45. Evaluate all 16 options (on the criteria maximize the probability that the earnings are above $706 million) and select the best combination. Is the best combination better than not hedging (i.e. is Megatron better off not buying any put options)? (AdvancedfOptional: (i) Choose the best combination so that you have more than 95% condence it is best choice, (ii) Try all 81 combinations!) (b) By changing the number of put options that Megatron could buy on the EU exchange rate and the number of put options Megatron could buy on the BP exchange rate, it might be possible to further increase the likelihood that next year's revenue from Europe and Great Britain would be at least $706 million. Re-lun the simulation model using the best combination of put options from pait (a) but with the number of EU options purchased (in millions) taking on the values 100, 175, 250, and the number of BP options purchased (in millions) taking on the value 125, 175, and 225. Of the 9 possible combinations which combination maximizes the likelihood that next years\" revenue from Europe and Great Britain would be at least $706 million. What is your estimate for the mean and standard deviation of revenues next year using this combination? (AdvancedfOptional: Choose the best combination so that you have more than 95% confidence it is best choice.) (c) Do you notice anything about the likelihood of the revenue being greater than 706 million in pait (b) as you increase the number of put options bought? If so, can you suggest the best number of EU and BP put options to buy (in order to maximize the likelihood next year's revenue is greater than $706 million) and run a simulation to verify