The goal of this project is to explore the topic of the basis and the changes to the spot prices and futures prices over time, using real futures data. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word with an introduction (description of the mini project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Question A and B are worth 5 points each. Question C is worth 20 points. Questions D and E are worth 10 points each and question F is worth 5 points. The report and the excel file are worth 10 points each. The crude oil futures contract is utilized as the contract of interest. For more information about the contract, visit http://www.cmegroup.com/trading/energy/crude-oil/light-sweet-crude_contract_specifications.html. If you employ external references, please cite them in a bibliography section. Basis Part 1: Download the Crude Oil Spot and Futures Data A) Go to http://www.eia.gov/ B) Select Sources & Uses at the top of the page C) Click on Petroleum & Other Liquids D) Select the DATA tab E) Click on Prices F) Click on Spot Prices G) Select Daily Period H) Click on Download Series History and save the excel file as Spot Prices I) Select the DATA tab J) Click on Prices K) Click on Futures Prices (NYMEX) L) Select Daily Period M) Click on Download Series History and save the excel file as Futures Prices Part 2: Explore the Data A) The Spot Prices excel file contains spot prices for crude oil and other petroleum products B) Open the Spot Prices excel file C) Click on Data 1 to go to the Crude Oil spot price section D) Column A lists the dates and Column B lists the spot prices beginning on January 2, 1986 E) The Futures Prices excel file contains futures prices for crude oil and other energy products F) Open the Futures Prices excel file G) Click on Data 1 to go to the Crude Oil futures price section H) Column A lists the dates beginning on March 30, 1983 I) The next four columns list prices for the closest four delivery months (expirations). According to the CME Group website, Crude oil futures are listed nine years forward using the following listing schedule: consecutive months are listed for the current year and the next five years; in addition, the June and December contract months are listed beyond the sixth year. Column B lists the nearby contract or the next futures contract to expire. Columns C, D, and E list the next three contracts expiring. For example, If Column B lists the January 2013 delivery contract, then Column C lists the February 2013 delivery, Column D lists the March 2013 delivery, and column E lists the April 2013 delivery contract. J) When one contract expires, the nearby contract shifts over to the next available contract with the closest delivery. According to the CME Group website, the expiration procedure for oil futures is as follows: Trading in the current delivery month shall cease on the third business day prior to the twenty-fifth calendar day of the month preceding the delivery month. If the twenty-fifth calendar day of the month is a non-business day, trading shall cease on the third business day prior to the last business day preceding the twenty-fifth calendar day. For example, the March 2013 futures cease trading on the third business day (February 20) prior to the twenty-fifth calendar day of the month preceding the delivery month (February). The following table lists the last trading day for several expirations: Contract Last Trading Day June 2016 5/20/2016 July 2016 6/21/2016 August 2016 7/20/2016 September 2016 8/22/2016 October 2016 9/20/2016 Part 3: Basis and Effective Price Paid A) According to the textbook (page 55), the basis is defined as follows: Basis = Spot price of asset to be hedged Futures price of contract used. The effective price paid is provided on page 56. Part 4: Assignment Assume that it is June 3, 2016. You work for an oil refining plant and know that you will need to purchase 100,000 barrels of oil for delivery in August 2016. You decide to hedge your position and take a long position in the September 2016 oil futures contract. You decide to close out your position on August 17, 2016. A) What is the contract size of one futures contract (in barrels per contract)? (5 points) B) How many futures contracts do you need to purchase to hedge your position? (5 points) C) Make a table of the daily price series for the spot crude oil and futures crude oil (make sure to consider the September 2016 contract and roll over when it expires) between June 2, 2016 and August 17, 2016. Also calculate the basis between June 3, 2016 and August 17, 2016. (20 points) D) Plot the spot prices and futures prices in one graph between June 3, 2016 and August 17, 2016. Label the y-axis as Price and the x-axis as Date. Set the y-axis range between $40 and $65. Label the spot and futures prices in a legend. Comment on the behavior of the spot and futures prices depicted in the plot. (10 points) E) Plot the basis between June 3, 2016 and August 15, 2016. Label the y-axis as Basis and the x-axis as Date. Comment on the behavior of the basis over time. (10 points) F) What is the effective price paid per barrel when the position is closed out? (5 points)

FINC 6365 - Project 1 - Basis
Last Name	First Name
A)	What is the contract size of one futures contract (in barrels per contract)?
B)	How many barrels of oil are you trying to hedge?
	How many futures contracts do you need to purchase to hedge your position?
C)	Table of spot and futures prices
	Date	Spot Price	Futures Price	Basis		Hint: In the Futures Prices excel file, Column B represents the nearby (soonest to expire)
	Jun 03, 2016					contract, Column C the next expiration, Column D the following expiration, and Column E the
	Jun 06, 2016					next expiration. The June 2016 futures contract stops trading on 5/20/2016. Therefore, on 5/21/2016,
	Jun 07, 2016					the July 2016 contract becomes the nearby contract. Hence on this day, the values in column B are
	Jun 08, 2016					for the July 2016 contract, the values in column C are for the August 2016 contract, the values in
	Jun 09, 2016					column D for the September 2016 contract, and the values in column E for the October 2016 contract.
	Jun 10, 2016					Keep in mind that we are interested in the September 2016 contract prices since we are using it to hedge.
	Jun 13, 2016
	Jun 14, 2016
	Jun 15, 2016					Hint: Be sure to remove the date 7/4/16 from the spot prices so that dates match with the futures.
	Jun 16, 2016
	Jun 17, 2016
	Jun 20, 2016
	Jun 21, 2016
	Jun 22, 2016
	Jun 23, 2016
	Jun 24, 2016
	Jun 27, 2016
	Jun 28, 2016
	Jun 29, 2016
	Jun 30, 2016
	Jul 01, 2016
	Jul 05, 2016
	Jul 06, 2016
	Jul 07, 2016
	Jul 08, 2016
	Jul 11, 2016
	Jul 12, 2016
	Jul 13, 2016
	Jul 14, 2016
	Jul 15, 2016
	Jul 18, 2016
	Jul 19, 2016
	Jul 20, 2016
	Jul 21, 2016
	Jul 22, 2016
	Jul 25, 2016
	Jul 26, 2016
	Jul 27, 2016
	Jul 28, 2016
	Jul 29, 2016
	Aug 01, 2016
	Aug 02, 2016
	Aug 03, 2016
	Aug 04, 2016
	Aug 05, 2016
	Aug 08, 2016
	Aug 09, 2016
	Aug 10, 2016
	Aug 11, 2016
	Aug 12, 2016
	Aug 15, 2016
	Aug 16, 2016
	Aug 17, 2016
D)	Plot of the spot and futures prices
E)	Plot of the basis
F)	What is the effective price paid per barrel of oil when the position is closed out?
	F1				S2
	b2				F1
	Effective Price				F2
					Effective Price