__________________________________________________________________________________________

You want to predict monthly Gas Prices in Colorado from May 2023 to April 2024. You need to do this using Excel (Question 1) and Stata (Question 2), based on the given instructions.

• Download the Gas Prices.xlsx file from the Assignment 2 link on Canvas.

Please create new columns and cells in this data file (in Excel). You must create new columns containing formulas to calculate the Average of all seasons, Average of averages, Seasonal Factor, Deseasonalized Price and Reseasonalized Price, and any additional columns you may need. You will also have to create a few more additional cells (not entire columns) containing some formulas. All your new columns and cells must contain only formulas (except for the column names), i.e., when I double-click on any of these cells, I must be able to see the underlying Excel formula and not a number/value. Save this completed Excel file as Assignment_2.xlsx

• Once again, download the Gas Prices.xlsx file from the Assignment 2 link on Canvas. Then import this file (all 49 rows and 3 columns) into Stata using File…Import and then save this Stata data file as Assignment_2.dta (You can use the pwd command to see where this file has been saved on your laptop.)

Please write commands to create the following new columns in this Stata data file, based on the given information. For each column, the word before the colon must be assigned as the column name.

1. MA:  The (randomly chosen) October 2020 forecast should be the average of 2.277, 2.364, 2.435 and 2.271
2. WMA: The (randomly chosen) October 2020 forecast should assign 20% weight to 2.277 and 80% weight to 2.364
3. ES: α = 0.65; initial forecast is assumed to be the same as initial actual.
4. OVERALLAVG: Overall average of all DollarsPerGallon
5. SEASONALAVG: Average of each season
6. SF: Seasonal Factors
7. DSDPG: Deseasonalized DollarsPerGallon
8. REG1: Predicted linear regression values of the appropriate dependent variable based on Period
9. FINALDPG: Final predicted values of DollarsPerGallon using Time-Series Decomposition

Your final Stata data file must contain at least 12 columns (3 given, plus 9 created, plus any other columns you need to create). Once you have finished creating all these columns, and before closing out of Stata, make sure to select all commands (used to create all these new columns) in the History window and send these to a do-file editor. This do-file must then be saved as Assignment_2.do

Notes:

1. In both the Excel and Stata (columns number 4 through 9 above) questions, you need to forecast using the Time-Series Decomposition method, based on assuming each month as a different season.
2. Even though you have been given only the (randomly chosen) October 2020 forecast for Stata columns number 1 and 2 above, you should calculate all possible forecasts for Stata columns number 1 through 3.

Your deliverable for this assignment is:

1. Assignment_2.xlsx Excel file and Assignment_2.do do-file (to be submitted on Canvas at or before 9 a.m. on Thursday, May 18, 2023). Please do not submit your Assignment_2.dta file.

Get expert help for INTS 4710-2 Spring 2023 Assignment 2 and many more. 24X7 help, plag free solution. Order online now!