Question

Your company deals with Alliance and wants to hedge its risk using future contracts. However, Futures Contracts are not available in Alliance. Closely correlated commodity is Gold in which future contracts are available. You are provided with the following data. Calculate the Optimal Hedge Ratio and Total Number of Gold Future Contracts that your company should buy to hedge their position.
Total Units of Alliance	75,000
Gold Future Contract Size	2,000
Day	Allyom Spot	Gold Future
0	1,200	1,220
1	1,207	1,230
2	1,225	1,245
3	1,235	1,254
4	1,254	1,260
5	1,250	1,254
6	1,265	1,270
7	1,220	1,245
8	1,225	1,250
9	1,251	1,263
10	1,235	1,254
11	1,215	1,210
12	1,220	1,240
13	1,210	1,230
14	1,185	1,195
15	1,194	1,209

Answer 1

Formulas USed:-

Correlation	=CORREL(B2:B17,C2:C17)
Std of Alloy	=STDEV(B2:B17)
Std of Gold	=STDEV(C2:C17)
Optimal Hedge Ratio	=F2*F3/F4
Contract Size	2000
Units Of Alliance	75000
NO of contracts required	=F7*F5/F6

I hope my efforts will be fruitful to you....

Thank YOU.