Due 11/12
PADM 702
Research Paper: Simple Time Series Analysis Assignment Guide
I. Getting Started
I. Getting Started:
· To use the Exponential Smoothing feature in Excel, the Data Analysis ToolPak must be activated.
· To see if Data Analysis ToolPak has been activated:
1) Look at the top middle of the excel screen and select Data.
2)
If you see this, you do have the Data Analysis ToolPak activated. Please proceed to the next section: II. Calculating a Simple Time Series Analysis (Exponential Smoothing)
If you do not see this: Install the Data Analysis ToolPak by following the instructions outlined below.
|
Step 1: Click on File and select Options |
|
|
Step 2: Select Add-ins from the left sidebar.
|
|
|
Step 3: Under Add-Ins select Excel Add-Ins under the Manage options and click on Go…
|
|
|
Step 4: Now from the Add-ins window select Analysis ToolPak and click on OK to enable “Data Analysis.”
|
|
|
Now you should see “Data Analysis” under the “DATA” tab in Excel. |
If you have any difficulties, contact me immediately by email or phone. I will walk you through the process.
II. Calculating a Simple Time Series Analysis (Exponential Smoothing):
Step 1:
Open and save the Crude Oil Data located in the Assignment Resource Crude Oil Data Worksheet. This can be found in the Assignment Instructions folder.
Follow the instructions below using the Crude Oil data
Step 2: Click on the Data tab and Data Analysis.
Step 3: Select the Exponential Smoothing option.
Step 4: For the Input Range, specify the available data points. The data range in this example is B1:B11.
Our data range is B2:B38.
Step 5: Set the Damping Factor to 0.3. This shows for recent values (recent years revenue values) has given a weight of 70% and for relatively old values has a weight of 30%. (The more recent the data, the more important it is to the trend.)
Step 6: Since the column heading was selected in the Input Range, select the checkbox Labels.
Step 7: Now select the range where to display the output range. Select an empty column, i.e., C2.
Step 8: Now, select Chart Output.
Step 9: Click on Ok to get the results. The chart will look like this:
Do not panic!
Place cursor on the chart and click You will see a round white dot, just below the term Data Point and drag down until the chart look like this:
Wala!
Now, select Chart Design at the top middle of the excel screen, then
select Add Chart Element
Select Chart Title > Above Chart> type in Crude Oil Prices, Brent Crude
Then the chart looks like this:
To Change Vertical Axis Values -- Change Value to Closing Price per Barrel
To Change Horizontal Axis Values -- Change Data Point to Years
To change Data Point to Years:
Select the Chart that you have created and navigate to the Axis you want to change.
Right-click the axis you want to change (Horizontal Axis) and navigate to Select Data and the Select Data Source window will pop up,
In the Horizontal (Category) Axis Label box, select Edit and place cursor in Axis label range, navigate to data sheet and click and drag A3 to A38
click OK
Then the chart looks like this:
Chart 1
III. How to Interpret the Key Results for Time Series Plot
|
Step 1: Look for Outliers and Sudden Shifts: Use knowledge to determine whether unusual observations or shifts indicate errors or a real change in the process. ·
· Look for unusual observations, also called outliers. Outliers can have a disproportionate effect on time series models and produce misleading results. Try to identify the cause of any outliers and correct any data-entry errors or measurement errors. Consider removing data values that are associated with abnormal, one-time events, which are also called special causes. ·
Figure 1
· Sudden Shifts · Look for sudden shifts in the series or sudden changes to trends. Try to identify the cause of such changes. · For example, the following time series plot (Figure 2) shows a drastic shift in the cost of a process after 15 months. You should investigate the reason for the shift. Figure 2 |
|
Step 3: Look for Trends. ·
· The following time series plot shows a clear upward trend. There may also be a slight curve in the data because the increase in the data values seems to accelerate over time.
Source: 1 Source: https://support.minitab.com 1 · Once the plot is completed, write a four-page report, including chart(s), interpreting the findings. The format should consist of (1) a couple of pages describing time series analysis and how it can be used to analyze budgets, (2) present the table, and (3) analyze the table based on the information provided in section How to Interpret the key results for Time Series Plot. Figure 3
|
II. Interpreting the Key Results for Time Series Plot
|
Step 1: Look for Outliers and Sudden Shifts: Use knowledge to determine whether unusual observations or shifts indicate errors or a real change in the process. · Outliers · Look for unusual observations, also called outliers. Outliers can have a disproportionate effect on time series models and produce misleading results. Try to identify the cause of any outliers and correct any data-entry errors or measurement errors. Consider removing data values that are associated with abnormal, one-time events, which are also called special causes. · The following time series plot (Figure 4) shows an outlier that was caused by a data-entry error. A technician accidentally entered the value 4 in the worksheet instead of 40. Figure 4
· Sudden Shifts · Look for sudden shifts in the series or sudden changes to trends. Try to identify the cause of such changes. · For example, the following time series plot (Figure 5) shows a drastic shift in the cost of a process after 15 months. You should investigate the reason for the shift. Figure 5 |
|
Step 2: Look for Trends. · A trend is a long-term increase or decrease in the data values. A trend can be linear, or it can exhibit some curvature. If your data exhibits a trend, you can use a time series analysis to model the data and generate forecasts. For more information on which analysis to use, go to Website: Which Time Series Analysis Should I Use?
· The following time series plot shows a clear upward trend. There may also be a slight curve in the data because the increase in the data values seems to accelerate over time.
Source: 1 Source: https://support.minitab.com 1
Figure 6
|
Once the plot is completed, write a four-page report, including (1) at least two of pages describing time series analysis and how it can be used to analyze budgets, (2) display a professionally formatted chart, and (3) analyze the charts based on the information provided below.
How to analyze Time Series charts—Address the research questions
Research Questions:
· The analysis should be related to the guidelines listed above. After doing so, the question to be asked is, why did the pattern(s) occur or not occur?
· What occurred beginning in 2005 that increased the price of oil and why did it continue to increase until 2008?
· What occurred beginning in 2009 that increased the price of oil and why did it continue to increase until 2012?
· What occurred beginning in 2013 that decreased the price of oil and why did it continue to decrease until 2017?
· What occurred beginning in 2020 that increased the price of oil?
To make a forecast ( for information only, not part of the assignment):
If the data were linear, exponential smoothing would be a good way to make a forecast. However, the data are not linear; therefore, a more robust set of computations must be used.
Regression based analysis can be used to make a precise forecast. One type of regression-based forecasting uses polynomial equations. (There are many ways to forecast.) Simply put, polynomial equations bend the trend line to better fit the data and thus make more precise forecasts. In essence, a first order polynomial is straight line. A second order polynomial bends the line once, A second order polynomial bends the line twice, and so forth. The easiest way to determine which order polynomial best fits the data and thus will give the best forecast is to look at the coefficient of determination (R2). The Brent Crude Oil Prices chart below is a fifth order polynomial.
The time series chart followed by a 5th order polynomial computation:
The interpretation is a bit complex. However, I mention this to show you the value of beginning an examination of trends with a visual time series analysis, followed by a forecasted analysis.
Page 2 of 2