Question

Your company deals with Allyom and wants to hedge its risk using future contracts. However, Futures are not available in Allyom. 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 Allyom	65,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,240	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
Hedge Ratio	Answer
Total Gold Contracts Purchased	Answer

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.