If you have not, please do the following before you jump in the final project.
1. Go to Canvas week 13 module and watch lecture videos and Excel Demo videos.
2. Set your Excel equipped with Data Analysis Add-Ins. How to get it? Check the last two links of week 13 module.
3. Download Excel practice data file from week 13 module and practice watching Excel Demo videos on the practice data file. Learn how to interpret the output as well.
You are supposed to apply analysis skills you learned in week 13 to this project.
Ready for this project? Download and save both files as yours.
• Download and save the word file named “Problems_FinalProject”. Put your name in front of the file name. For example, “YourName_Problems_FinalProject”. Type in your answers in this problem file.
• Download and save the Excel file named “Data_FinalProject_...”. Put your name in front of the file name. For example, “YourName_Data_FinalProject”. Make your Excel outputs in the file. Your output should be in this file. Please do not bother to import tables and graphs to the problem file. Each file will be graded.
How to submit your project:
To submit** your project, go to your project submission link in Week 15 module,
1. Click "Start Assignment". This means you are starting "uploading/posting your files"
2. Post your Excel Data (including your output) file.
3. Click "add another file". Post your Problem (including your answers) file.
4. Click "Submit".
Your files should be posted at "ONE" submission since the last submission replaces previous submission at multiple submissions. If you submit file 1 and submit file 2, what I receive is only file 2 because the last submission of file 2 replaced file1.
**Please do NOT email your project to instructor. Email submission will not be accepted. All course material should stay within our class Canvas platform, following DLIT internet course regulations.
Following project context is from page 840 of your textbook. But the data and problems have been adjusted by the instructor for your project.
Context:
Managing expenses is often a critical job duty for Chief Financial Officers (CFOs). Sometimes costs are fairly easy to forecast. Items such as office supplies and staffing costs are generally more manageable costs to estimate for budgeting purposes. Other expenses such as raw materials and utility costs can be harder to forecast because the prices can fluctuate widely during a given time period.
Gasoline is another such expense. Gasoline prices are determined in part by the price per barrel of crude oil. There are two predominant types of crude oil used in the industry to determine the price of gas we see at the pump: West Texas Intermediate (WTI) and Brent Blend. WTI is the benchmark used in the United States and Brent Blend is mostly used in Europe and Africa. An interesting side note is that about 20 gallons of gasoline are made from one barrel of crude oil (source: Frequently Asked Questions (FAQs) – U.S. Energy Information Administration (EIA)).
Industries that rely heavily on gasoline to provide their product or service can find it difficult to estimate the gasoline expense from month to month and sometimes even from day to day. Some examples of industries that have fluctuating gas price impacts are transportation, landscaping, and logistics. Being able to reliably forecast the gasoline expenses is a critical component of estimating company profits.
Imagine you are tasked with determining reliable estimates of the price per gallon of gasoline to be used in forecasting company profits. Using the data file answer the following questions to better understand the relationship between the two types of crude oil and their impact on gasoline prices.
The data set includes monthly gasoline and crude oil prices (per gallon) between January 2000 and March 2021 (255 data points).
1. Descriptive Statistics (In Excel, Data >> Data Analysis >> Descriptive Statistics)
1.1 (12 points) In the data file, make the following output and save. (Please do not import your tables here. They will be graded in the Excel file.)
• Descriptive Statistics of WTI price
• Descriptive Statistics of Brent Blend price
• Descriptive Statistics of regular gas price
1.2 (12 points) From your Descriptive Statistics output, you will find many measurements you learned in chapters 4.1 and 4.2. Checking the mean, mode, median, maximum, minimum, range, and standard deviation, briefly discuss data distribution of each variable.
2. Scatter Plot
2.1 (8 points) Create the following two scatter plots. Save them in the Excel data file.
• A scatter plot of [WTI price vs. regular gas price].
• A scatter plot of [Brent Blend price vs. regular gas price]
2.2 (8 points) Review and discuss each scatter plot above. Your discussion should include magnitude and direction of relation between two variables.
3. Correlation Coefficient (In Excel, Data >> Data Analysis >> Correlation)
(10 points) Get correlation coefficient and save the output. Explain how the correlation coefficient supports (or not supports) your scatter plot you got in question 2.
• Correlation coefficient of [WTI price and regular gas price].
• Correlation coefficient of [Brent Blend price and regular gas price]
4. (4 points). You are going to predict “regular gas price per gallon” using “crude oil price per gallon”. Specify dependent variable and independent variable. When you make a simple linear regression model with these two variables, which is X and which is Y?
5. Regression ((In Excel, Data >> Data Analysis >> Regression)
(10 points) Run Regression (simple linear regression) and save the following output in the data file.
• Regression output for predicting Reg Gas price per gallon with Brent Blend crude oil price per gallon.
• Regression output for predicting Reg Gas price per gallon with WTI crude oil price per gallon.
6. Reviewing your regression output, write Simple Linear Regression (SLR) equations.
6.1 (4 points) SLR equation for predicting regular gas price with Brent Blend crude oil price:
6.2 (4 points) SLR equation for predicting regular gas price with WTI crude oil price:
7 Reviewing your regression output, test the slope of each crude oil model to see if they are statistically significant.
7.1 (4 points) Test the slope of Brent Blend crude oil model.
7.2 (4 points) Test the slope of WTI crude oil model.
8. Using your regression output and your answer to question 6, predict the following. Show the calculation.
8.1 (4 points) The price of regular gas price per gallon at the Brent Blend crude oil price
$2.07 per gallon. Show your calculation.
8.2 (4 points) The price of the regular gas price per gallon at the WTI crude oil price
$2.07 per gallon. Show your calculation.
9. (8 points) Define “The Coefficient of Determination” that you learned from chapter 13. Read the value for each model from regression output and interpret the value.
• Coefficient of Determination from regression output of predicting regular gas price per gallon with Brent Blend crude oil price per gallon.
• Coefficient of Determination from the regression output of predicting regular gas price per gallon with WTI crude oil price per gallon.
10. (4 points). Which crude oil would you recommend as the best predictor of regular gasoline prices for your company to use to forecast expenses? Explain the reason.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
