BUSI 2342 Course Project
Due Date: May 6th, 2022 11:59 PM
Data Name:
Crude Oil Future, 2000 – 2021 Monthly
NY Mercantile – NY Mercantile Delayed Price. Currency in USD
Data source:
Yahoo Finance: CL=F
The instructions given below are an outline of the steps you need to take to complete your
project. They should be regarded as a guide and not a detailed map of the procedure. Further help
on using Excel is available on for review with the project from Canvas and by clicking on the
Excel Help menu.
Case Description
Crude oil plays a vital role in the global economy. The price of oil affects government plans and
business sectors. The ability to forecast crude oil prices can help companies and the government
better understand trends in energy economics and avoid potential risks. This study will show you
how to use statistical methods to analyze and forecast crude oil prices in the past two decades.
NAME OF FIELD DESCRIPTION
Date Beginning date of each month
Open Opening price of the month
High Highest price of the month
Low Lowest price of the month
Close Close price of the month
Adj Close Adjusted close price of the month
Guideline
1. You will need to have the data file for this project (ProjectData.xlsx) copied onto a disk
or flash drive. Click on the file to download and allow about a half minute for the
downloading to be completed.
2. Open the Microsoft Excel.
3. Click on File Open, choose the address where the data file resides, and select the data file
(ProjectData.xlsx).
4. There are two sheets: “2000-2014” sheet includes monthly oil prices from the year 2000
to 2014, and “2015-2021” sheet includes monthly oil prices from the year 2015 to 2021.
5. The first few rows of the sheet “2000-2014” are shown below
6. The first few rows of the sheet “2015-2021” are shown below
7. Open a new sheet, rename it as “Part A”.
8. Go to sheet “2000-2014”. Copy the adjusted close price (Column F) for the sample of
years 2000-2014 and paste these values into the sheet “Part A”. Rename the first row
(label) at the top of this new column to indicate what the values represent (e.g., Years
2000-2014).
9. Similarly, go to sheet “2015-2021”. Copy the adjusted close price (Column F) for the
sample of years 2015-2021 and paste these values into the sheet “Part A”, beginning at
the second row. Rename the first row (label) at the top of this new column to indicate
what the values represent (e.g., Years 2015-2021).
10. The table below shows what the first ten lines in your two new columns should resemble.
11. Select the Data tab, then Click on Data Analysis (In Excel – Click on Data then Data
Analysis).
12. Select “Descriptive Statistics” and click on OK.
13. Set the cursor in the Input Range box, and then highlight the columns A and B with year
2000-2014 and 2015-2021 data. Here, you can use the range “$A$1:$B$148”. Click the
“Labels in first row” box, and check the ‘summary statistics’ box. Click on OK.
14. Excel will then complete the descriptive statistics analysis and place the output in a new
sheet. Expand the column widths if necessary to reveal all the output without truncation.
Your output might resemble the truncated version of the output shown below:
15. Examine this data and consider its meaning (Important – 1).
16. Once the descriptive statistics are examined, go back to sheet “Part A” and repeat the
steps to get to Excel Data Analysis ToolPak. Select the “t-test: Two-Sample Assuming
Equal Variances”.
17. Set the cursor in the Variable 1 box, and then highlight the column with Year 2000-2014
data. The range should be “$A$1:$A$148”. Repeat with the Variable 2 box and the Year
2015-2021 data. The range should be “$B$1:$B$65”.
18. Click in the labels box to put a checkmark and take the default .05 significance level.
19. Enter 0 (the number zero) in the box for the hypothesized mean difference. Click on OK.
20. Excel will then complete the independent two-sample t-test and place the output in a new
sheet. Expand the column widths if necessary to reveal all the output without truncation.
Your output might resemble the truncated version of the output shown below:
21. Examine this data and consider its meaning (Important – 2).
22. Go back to the sheet “2000-2014”. Conduct the regression analysis with Adj Close as the
dependent variable (Y), and Date (X) as the independent variable. Assuming the level of
significance is 5%.
23. Repeat the steps to get to Excel Data Analysis ToolPak. Select the “Regression”.
24. Set the cursor in the Input Y Range box, and then highlight the column “Adj Close”. The
range should be “$F$1:$F$148”. Repeat with the Input X Range box and the column
“Date”. The range should be “$A$1:$A$148”.
25. Click in the labels box to put a checkmark and take the default .05 significance level.
Click on OK.
26. Excel will then complete the regression analysis and place the output in a new sheet.
Expand the column widths if necessary to reveal all the output without truncation. Your
output might resemble the truncated version of the output shown below:
27. Examine this data and consider its meaning (Important – 3).
Report
Use the given Template to upload a two to three pages report to Canvas by the Due Date. In your
report, include the following:
• Your name at the top
• The following analysis output as described in this handout:
Exhibit 1 (20 points): Screenshot of your “Descriptive Statistics” results.
Exhibit 2 (20 points): Screenshot of your “t-test: Two-Sample Assuming Equal Variances”
results.
Exhibit 3 (20 points): Screenshot of your “Regression” results.
• Answers to the following questions:
Question 1 (20 points): Based on the “Descriptive Statistics” results, what’s the mean value
for Year 2000-2014 data, and what’s the mean value for Year 2015-2021 data?
Question 2 (20 points): Based on the “t-test: Two-Sample Assuming Equal Variances”
results, what’s the mean value for Year 2000-2014 data, and what’s the mean value for Year
2015-2021 data? Are they the same as the values in “Descriptive Statistics” results?
Question 3 (20 points): Based on the “t-test: Two-Sample Assuming Equal Variances”
results, what’s the p-value to compare the mean oil prices for Year 2000-2014 is More Than
the mean oil prices for Year 2015-2021? Assuming the level of significance is 5%, what is
the determination for this t-test?
Question 4 (20 points): Based on the “t-test: Two-Sample Assuming Equal Variances”
results, what’s the conclusion for t-test concerning if the mean oil prices for Year 2000-2014
is More Than the mean oil prices for Year 2015-2021? Assuming the level of significance is
5%.
Question 5 (20 points): Based on the “Regression” results, what’s R-square of the model?
What does it mean? Explain briefly.
Question 6 (20 points): Based on the “Regression” results, what’s the coefficient for the
variable “Date”? How do you think about the relationship between the “Date” and “Adjusted
Close price”?
Question 7 (20 points): Based on the “Regression” results, what’s the 95% Confidence
Interval for the “Date”?

Published by

Dissertations

View all posts