The data sources for historical spot and futures prices on crude oil and refined products are available at http://www.eia.gov/petroleum/data.cfm (click on Prices), and current oil futures price data are available at http://www.cmegroup.com/trading/energy/
You work for a major US airline, VUL Air, in its fuel purchasing department. During September 2015, your boss, the VP of fuel purchasing for VUL, purchased 3,000 December 2015 light sweet crude oil contracts traded on the CMEs NYMEX exchange to partially hedge the companys anticipated November 2015 jet fuel consumption of 150 million gallons. The weighted average price of crude oil futures contracts purchased on the NYMEX during September 14 18, 2015, was $46.324 per barrel. Your boss has presented you with the plan for liquidating the 3,000 crude oil futures contracts during November 2015. In fact, you have been provided with a spreadsheet template (see General Files in the course D2L page) that shows the plan for liquidating the futures contracts (see column K of the spreadsheet template). In early December, your boss expects you to submit a written report.
More background information:
One barrel of oil = 42 gallons of oil.
VUL Air uses jet fuel on a daily basis, and there are only minor deviations in the scheduled routes from day-to-day. Thus, you can assume that the airline uses the same amount of fuel during each of the 30 days of November 2015.
VUL Air employs first-in, first-out accounting for any jet fuel held in inventory.
90 million gallons of VUL Airs anticipated jet fuel usage in November 2015 will be purchased at the companys non-hub airports on an as needed basis. Aircraft are fueled immediately upon landing to prepare for the next departure. Jet fuel deliveries at all non-hub airports are priced based on daily spot prices of Gulf Coast jet fuel (reported at the following website http://www.eia.gov/dnav/pet/pet_pri_spt_s1_d.htm). Saturday, Sunday, and holiday purchases of jet fuel are priced at the prior business days spot price. The websites data is updated once per week on Wednesdays (except for Monday holiday weeks in which case the data is updated on Thursdays).

60 million gallons of VUL Airs anticipated jet fuel usage in November 2015 is used at the companys hub airport. The company keeps anywhere from 15 million to 60 million gallons of jet fuel in inventory at oil terminals near its hub airports. All of the hub airport fuel is purchased on the Gulf Coast spot market in bulk quantities (i.e., 250,000 barrels of jet fuel might be a minimum spot market order for this airline). Please assume that as of the end of October 2015, the airline holds existing jet fuel inventories of 40 million gallons at an average cost of $1.341 per gallon. Your boss will inform you of any spot market purchases of spot jet fuel during November 2015 (date and price) on a timely basis. The cost of any November spot purchases of jet fuel will be reflected at the hub airport starting on November 21.
Your boss has informed you that he plans to liquidate 200 crude oil contracts per weekday during the first three weeks of November.
VUL Air typically buys and sells futures contracts just before the settlement period, thus we will assume that each days settlement price for December 2015 crude oil futures on the CME reflects the futures price at which any contracts are liquidated (see http://www.cmegroup.com/trading/energy/crude-oil/light-sweet-crude_quotes_settlements_futures.html). You need to keep these records yourself, but do not despair if you do not want to track settlement prices daily! Historical data on crude oil futures contract settlement prices are updated weekly at http://www.eia.gov/dnav/pet/pet_pri_fut_s1_d.htm. During November, the December 2015 contract is Contract 1 on the EIA website.
Your instructor is NOT your audience, so be careful about your assumptions regarding your readers knowledge base). Your paper will be assessed based on the following factors: 1) correctness of content, 2) completeness of elements below, 3) rigor of analyses and creative judgment employed in designing analyses, and 4) effectiveness of communication (including professionalism):
Facts associated with implementation (Approximate weighting = 25%):
Documentation of futures contracts liquidated (date, quantity, and price).
Profit/loss realized on futures contract liquidation.
Cost of actual jet fuel consumption (based on statements from Background section above).
Total net cost of jet fuel consumed after accounting for effect of hedging profits/losses.
Did the hedge perform better or worse than expected?
Suggestions for improving the hedging strategy employed by your boss (Approximate weighting = 75%):
Your discussion should address (at a minimum):
o What mistake did your boss make in devising the futures contract liquidation strategy? Be specific as to how the liquidation strategy should have been altered.
o What mistakes did your boss make in setting the hedge in September? Make sure you recommend what the hedging strategy should have been (be specific).
Your discussion should contain quantitative analysis of an alternative hedging strategy to the one employed by your boss. In particular, should your boss have considered using an alternative hedging instrument such as futures contracts on gasoline or ultra-low-sulfur-diesel (labeled as heating oil)?
Make sure you consider the following key aspects of hedging with futures when you compare between alternative hedging instruments:
Correlation
Basis risk
Minimum variance hedge ratio
Timing issues of futures contract expiration.
———-
Added on 01.12.2015 21:08
Excel document must be completed along with paper.