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.
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).
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)?
B) How many futures contracts do you need to purchase to hedge your position?
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 3, 2016 and August 17, 2016. Also calculate the basis between June 3, 2016 and August 17, 2016.
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.
E) Plot the basis between June 3, 2016 and August 15, 2016. Label the y-axis as Basis and the xaxis as Date. Comment on the behavior of the basis over time.
F) What is the effective price paid per barrel when the position is closed out?
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.
Attachment:- Data-.rar
Attachment:- Project.rar