Please try and solve using Excel*****

1 Week 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 D Heating Oil F. Kerosene Spot Price Change 1 $ 0.051 $ 0.129 2 $ (0.098) $ (0.112) 3 $ 0.088 $ 0.141 4 $ 0.041 $ 0.201 5 $ (0.001) $ 0.014 6 $ 0.182 $ 0.365 7 $ 0.024 $ (0.162) 8 $ 0.074 $ 0.066 9 $ (0.191) $ (0.270) 10 $ 0.175 $ 0.236 11 $ 0.113 $ 0.142 12 $ (0.101) $ (0.181) 13 $ (0.030) $ (0.019) 14 $ 0.136 $ 0.247 15 $ 0.004 $ 0.002 16 $ (0.050) $ (0.171) 17 $ 0.015 $ (0.097) 18 $ 0.080 $ 0.099 19 $ (0.087) $ (0.161) 20 $ 0.112 $ 0.041 21 $ 0.064 $ 0.185 22 $ 0.201 $ 0.367 23 $ 0.033 $ 0.050 24 $ 0.014 S 0.149 25 $ 0.157 $ 0.164 26 $ (0.060) $ (0.114) 27 $ (0.124) $ (0.221) 28 $ (0.114) $ (0.013) 29 $ (0.026) $ (0.051) 30 $ (0.168) $ (0.239) 31 $ (0.103) $ (0.114) 32 $ (0.056) $ 0.095 33 $ 0.084 $ 0.208 34 $ 0.062 $ 0.043 35 $ (0.032) $ (0.155) 36 $ (0.003) $ 0.051 37 $ (0.040) $ (0.021) 38 $ 0.137 $ 0.232 39 $ 0.026 $ 0.058 40 $ 0.026 $ (0.002) 41 $ (0.044) $ (0.093) 42 $ 0.043 $ 0.005 43 $ (0.108) $ (0.141) 44 $ 0.036 $ 0.172 45 $ 0.018 $ (0.127) 46 $ 0.259 $ 0.321 47 $ 0.074 $ 0.133 48 $ 0.010 $ (0.235) 49 $ 0.003 $ 0.095 50 $ 0.180 $ 0.334 51 $ (0.179) $ (0.295) 52 $ (0.107) $ (0.216) 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 3) You are a refiner who has a commitment to sell kerosene (one of your products) later in 2019, but you think that the kerosene futures market is too illiquid now. Heating oil futures however, are quite liquid, and you believe they are reasonably well correlated with kerosene spot prices. Given the provided time series of weekly changes in heating oil futures and kerosene spot prices, what is appropriate hedge ratio? Use the formula: S h" :p F (both kerosene and heating oil are priced in gallons). Regress kerosene spot prices changes on heating oil futures changes and show that the hedge ratio is the slope of this regression. If you have to hedge 1,000,000 gallons of kerosene and there are 42,000 gallons per heating oil contract, how may heating oil contracts should you short? 1 Week 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 D Heating Oil F. Kerosene Spot Price Change 1 $ 0.051 $ 0.129 2 $ (0.098) $ (0.112) 3 $ 0.088 $ 0.141 4 $ 0.041 $ 0.201 5 $ (0.001) $ 0.014 6 $ 0.182 $ 0.365 7 $ 0.024 $ (0.162) 8 $ 0.074 $ 0.066 9 $ (0.191) $ (0.270) 10 $ 0.175 $ 0.236 11 $ 0.113 $ 0.142 12 $ (0.101) $ (0.181) 13 $ (0.030) $ (0.019) 14 $ 0.136 $ 0.247 15 $ 0.004 $ 0.002 16 $ (0.050) $ (0.171) 17 $ 0.015 $ (0.097) 18 $ 0.080 $ 0.099 19 $ (0.087) $ (0.161) 20 $ 0.112 $ 0.041 21 $ 0.064 $ 0.185 22 $ 0.201 $ 0.367 23 $ 0.033 $ 0.050 24 $ 0.014 S 0.149 25 $ 0.157 $ 0.164 26 $ (0.060) $ (0.114) 27 $ (0.124) $ (0.221) 28 $ (0.114) $ (0.013) 29 $ (0.026) $ (0.051) 30 $ (0.168) $ (0.239) 31 $ (0.103) $ (0.114) 32 $ (0.056) $ 0.095 33 $ 0.084 $ 0.208 34 $ 0.062 $ 0.043 35 $ (0.032) $ (0.155) 36 $ (0.003) $ 0.051 37 $ (0.040) $ (0.021) 38 $ 0.137 $ 0.232 39 $ 0.026 $ 0.058 40 $ 0.026 $ (0.002) 41 $ (0.044) $ (0.093) 42 $ 0.043 $ 0.005 43 $ (0.108) $ (0.141) 44 $ 0.036 $ 0.172 45 $ 0.018 $ (0.127) 46 $ 0.259 $ 0.321 47 $ 0.074 $ 0.133 48 $ 0.010 $ (0.235) 49 $ 0.003 $ 0.095 50 $ 0.180 $ 0.334 51 $ (0.179) $ (0.295) 52 $ (0.107) $ (0.216) 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 3) You are a refiner who has a commitment to sell kerosene (one of your products) later in 2019, but you think that the kerosene futures market is too illiquid now. Heating oil futures however, are quite liquid, and you believe they are reasonably well correlated with kerosene spot prices. Given the provided time series of weekly changes in heating oil futures and kerosene spot prices, what is appropriate hedge ratio? Use the formula: S h" :p F (both kerosene and heating oil are priced in gallons). Regress kerosene spot prices changes on heating oil futures changes and show that the hedge ratio is the slope of this regression. If you have to hedge 1,000,000 gallons of kerosene and there are 42,000 gallons per heating oil contract, how may heating oil contracts should you short