I would like you to take the @RISK spreadsheet model megatron.xlsx provided to you and use it to perform analysis to provide a recommendation to Ramesh Srinivasan (CEO of Megatron) on the hedging strategy for Megatrons next years 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 fixed 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)? (Advanced/Optional: (i) Choose the best combination so that you have more than 95% confidence 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 years revenue from Europe and Great Britain would be at least $706 million. Re-run the simulation model using the best combination of put options from part (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? (Advanced/Optional: 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 part (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 years revenue is greater than $706 million) and run a simulation to verify.

Your submission should be a memo addressed to Ramesh Srinivasan (CEO of Megatron, who is not very savvy with analytics) from Bill Sullivan (Vice President for Finance, whose role you take in this assignment), describing your findings, detailed analysis (along the lines suggested above so Mr. Srinivasan can better understand the analysis), and recommendations. This memo should be no more than 3 pages long (4 with figures/tables at the most).

Note: To compare across simulations one should select the seed for the random number generator instead of @RISK selecting it randomly.