Risk Simulation Model/Probability
selen7
Copyofmegatron.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 | ERROR:#NAME? S. Raghavan: S. Raghavan: Assumption cell | ERROR:#NAME? | $/EU | |
| British pound | ERROR:#NAME? S. Raghavan: S. Raghavan: Assumption Cell | ERROR:#NAME? | $/BP | |
| EU Put option payoff | ERROR:#NAME? | (in US$) | ||
| BP put option payoff | ERROR:#NAME? | (in US$) | ||
| EU Revenue in one year (unhedged) | ERROR:#NAME? | (in million US$) | ||
| BP Revenue in one year (unhedged) | ERROR:#NAME? | (in million US$) | ||
| Total Revenue in one year (unhedged) | ERROR:#NAME? | (in million US$) | ||
| EU Revenue in one year (hedged) | ERROR:#NAME? | (in million US$) | ||
| BP Revenue in one year (hedged) | ERROR:#NAME? | (in million US$) | ||
| Revenue in one year (hedged) | ERROR:#NAME? | (in million US$) | ||
| Likelihood of revenue being >=US$ | ERROR:#NAME? | |||
| 706 | million | |||
| Mean Revenue Hedged | ERROR:#NAME? | |||
| Mean Revenue Unhedged | ERROR:#NAME? |
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 |
