QUANTITATIVE BUSINESS ANALYSIS US ENERGY INFORMATION ADMINISTRATION.

You have recently taken on work in the business analytics group for the US Energy Information Administration. The administration wants to use data analytic methods to better understand the variation and movements over time in residential prices for natural gas. They have supplied you with a dataset that consists of monthly residential natural gas prices, averaged over the entire US, in units of dollars per thousand cubic feet, from 2008-2012. Also included in the dataset is the average natural gas “Wellhead price”. The wellhead price represents the price of the gas immediately upon extraction, i.e. at the mouth of the well, which excludes the transportation and delivery costs; i.e. the cost of getting the gas to the consumers for their energy use is excluded. Currently, the Administration has some fairly adhoc understandings about residential prices, which are used to form estimates and forecasts and help form various policies and reports for government as well as those in the natural gas industry. However, using their recently acquired data, and having hired you to help analyse it, they are hopeful to improve their estimation processes and understanding. The data are in the file “Natural Gas_asst.xlsx”, available on the BUSS1020 Blackboard site. The data consists of 60 months of two measurements on natural gas prices. Variables include the date, the average residential natural gas price and the average wellhead price for natural gas, across the US. You are required to present and explain all your assumptions and discuss whether they could be true, or not, in answering each of the questions below. Make sure to note that the data are recorded consecutively over time. Task 1 (10 marks) Data Summary and Visualization The point of this task is for you to perform and present a summary of an initial exploratory data analysis (EDA); one that is relevant to the goals of the assignment. Please read Tasks 2-4 now, and as you do decide what are the most appropriate things to include in an exploratory data analysis. Then, explore and present an appropriate EDA for this data set, focusing on the graphs and summary measures that are most relevant to t h e o b j e c t i v e s i n Tasks 2- 4 . Discuss these graphs and statistics in b o t h a general and a business context, as well as how they relate or inform the issues in Tasks 2-4. Task 2 (10 marks) The Administration has always estimated or predicted average residential natural gas prices differently, based on the time of the year. Specifically, the Administration usually estimates that residential natural gas prices will be higher, by 4 dollars per thousand cubic feet, for the months of June, July, August and September, compared to the remainder of the year. Fully investigate whether residential natural gas prices are higher during the months of June – September, compared to the remainder of the year, giving a plain-English business context conclusion to your investigation. Then, fully assess the Administration’s adhoc method of predicting residential natural gas prices described above. Page 3 of 3 Task 3 (15 marks) An analytics department colleague suggests that a regression model be considered to estimate natural gas prices, employing the wellhead price as an explanatory variable. In the past, the Administration has estimated average residential prices as 10 + 75% of the wellhead price (in dollars per thousand cubic feet). Using a regression analysis, fully examine the relationship between the wellhead prices and the residential prices; then properly assess whether the Administration’s adhoc estimation method is accurate, or not. Task 4 (15 marks) The head of business intelligence and analytics suggests that more explanatory variables may be included in a regression model to estimate residential natural gas prices. You suggest adding dummy variables for each grouping of months that has a “different” level of residential natural gas price. Using multiple regression analysis, fully examine the following questions: What is the best of grouping of months that employs the minimum number of groups (and thus dummy variables) and achieves an acceptably accurate fit to the data? Compare (at least) the fit of the competing models that respectively employ 2 and 3 groups of months. Fully analyze, diagnose and discuss the best model you find. Compare the best model with the two adhoc approaches to estimating residential natural gas in Tasks 2 and 3. Task 5 (5 marks) Write an executive summary (no more than 500 words) for the management of t h e US Energy Information Administration regarding your analyses, their assumptions, your recommendations and your conclusions regarding methods to estimate average US residential natural gas prices. Include a recommendation on which model or method they should best use, and why. Use plain English language as much as possible.