Statistics/Simulations- Please see attached
selen7
megatronSpreadsheet.xlsxCB_DATA_
hedging
| Megatron Hedging Spreadsheet | |||||||||||
| Euro receivable in one year | 322.5 | (in million EU) | |||||||||
| British Pound receivable in one year | 234.1 | (in million pounds) | |||||||||
| Current Euro rate | 1.3026 | (in US$/EU) | |||||||||
| Current British pound rate | 1.434 | (in US$/British pound) | |||||||||
| Strike price of 1-year Euro put options | 1.2000 | ||||||||||
| Cost per Euro put option | 0.016776 | (in US$) | |||||||||
| Number of Euro put options | 250 | (in million) | |||||||||
| Strike price of 1-year British pound put options | 1.2000 | ||||||||||
| Cost per British pound put option | 0.003808 | (in US$) | |||||||||
| Number of British pound put options | 175 | (in million) | |||||||||
| Euro/British pound correlation | 67.5% | @RISK Correlations raghavan: @RISK Correlation NewMatrix1 Updated: 9/23/2010 11:07:34 PM | Euro / Return in $C$16 | British pound / Return in $C$17 | |||||||
| Euro volatility | 9% | Euro / Return in $C$16 | 100.00% | ||||||||
| British pound volatility | 11% | British pound / Return in $C$17 | 67.50% | 1 | |||||||
| Return | Value | ||||||||||
| Euro | 15.85% S. Raghavan: S. Raghavan: Assumption cell | 1.5091 | $/EU | ||||||||
| British pound | 6.85% S. Raghavan: S. Raghavan: Assumption Cell |
raghavan: @RISK Correlation NewMatrix1 Updated: 9/23/2010 11:07:34 PM | 1.5323 | $/BP | |||||||
| EU Put option payoff | 0.0000 | (in US$) | |||||||||
| BP put option payoff | 0.0000 | (in US$) | |||||||||
| EU Revenue in one year (unhedged) | 486.6903 | (in million US$) | |||||||||
| BP Revenue in one year (unhedged) | 358.7107 | (in million US$) | |||||||||
| Total Revenue in one year (unhedged) | 845.4010 | (in million US$) | |||||||||
| EU Revenue in one year (hedged) | 482.4963 | (in million US$) | |||||||||
| BP Revenue in one year (hedged) | 358.0444 | (in million US$) | |||||||||
| Revenue in one year (hedged) | 840.5406 | (in million US$) | |||||||||
| Likelihood of revenue being >=US$ | 0.7581256183 | ||||||||||
| 706 | million | ||||||||||
| Mean Revenue Hedged | 754.845990073 | ||||||||||
| Mean Revenue Unhedged | 755.7866892734 |
FX_Data
| Strike Price for EU | Cost | Strike Price for BP | Cost | ||
| (in $) | (in $) | (in $) | (in $) | ||
| 1.1 | 0.002802 | 1.10 | 0.0002846647 | ||
| 1.18 | 0.012582 | 1.15 | 0.0013175908 | ||
| 1.2 | 0.016776 | 1.20 | 0.0038075352 | ||
| 1.22 | 0.021702 | 1.25 | 0.0091325075 | ||
| 1.24 | 0.027422 | 1.30 | 0.0187599829 | ||
| 1.26 | 0.034002 | 1.35 | 0.0329374455 | ||
| 1.28 | 0.04159 | 1.40 | 0.0523550619 | ||
| 1.3 | 0.064382 | 1.45 | 0.0960249466 | ||
| 1.32 | 0.17171 | 1.50 | 0.1594273217 |
