#### Question Details

##### [solution] » The following table gives data on monthly changes in the spot

Brief item decscription

Step-by-step solution file

Item details:

The following table gives data on monthly changes in the spot
More:

The following table gives data on monthly changes in the spot price and the futures price for a certain commodity.  You can answer this problem using Excel.  Please provide a printout of your Excel table of results.

 Month Change in spot price \$ Change in the futures price \$ 1 0.2235 2.4475 2 -2.1667 -3.2794 3 8.0260 8.3493 4 4.8476 5.2488 5 -0.0651 -4.9403 6 11.7370 13.1473 7 12.2756 11.1016 8 -1.9149 -0.4247 9 5.3617 7.3709 10 1.6130 2.2933 11 3.6174 1.3498 12 0.3334 0.6617 13 -1.4396 -6.2554 14 7.9459 4.8917 15 -0.0492 -1.5287 16 2.9435 1.5628 17 3.5694 -1.0717 18 1.5904 3.4981 19 3.8853 2.7490 20 8.1038 10.9887

• Calculate the standard deviation of the change in the spot price and the change in the futures price, as well as the correlation between the change in the spot and futures price.
• Calculate the optimal hedge ratio and the hedging effectiveness using the minimum variance hedge ratio model.
• Calculate the slope of the regression of the change in the spot price on the change in the futures price, as well as the associated R Square of the regression.
• Estimate the optimal hedge ratio and the hedging effectiveness using the results of part C.

STATUS
QUALITY
Approved

This question was answered on: Feb 21, 2020

Solution~00065908179.zip (18.37 KB)