Introduction:

In this assignment, you will collect and analyze time series data to create a number of forecasts. Then, based on the mean absolute error, you will choose the best forecasting model to make a new forecast. Preparing good forecasts is an essential prerequisite to many decisions. The methods that you will practice here are relatively simple to compute in Excel. Please see the example videos in lecture videos and on this assignment page for detailed demonstrations of the techniques that you will use below.

Requirements:

1.Acquire security-related time series data from your workplace, the Internet or other reliable sources. Your data set must contain at least 50 time series observations, and it must be unique to the class. If you have any questions on whether your data set is appropriate,don’t hesitate to ask. If the data are proprietary, please censor the company and identifying headings. Use generic identifiers that indicate the type of company and the time series data. For example, let’s say that you are a security executive at Citibank,and you have access to data on fraudulent usage credit cards in the various states in the US for the last year. You decide to use data from Illinois. Then, it would be sufficient to say that the company is, for example,ABC Credit Card and the data is Fraudulent Usage in the State of Flatlandia. The data would look like the figure at right.

2.Create a line chart and identify the time series components in the time series, and compute the correlation between the time series variable and time using the =CORREL()function. Justify your answer. (Hint: it should be some combination of average or base,cycle, trend, seasonality, and random variation.)

3.Create the following forecasts using as much of the data as possible:

a.2-period moving average.

b. 6-period moving average.

c. 12-period moving average.

d. Exponential smoothing forecasts with alpha = 0.3, alpha = 0.6, and alpha = 0.9.

e. Trend forecast (whether or not there is a trend). Use the =TREND() function in Excel.

IMPORTANT NOTES: When computing your moving average forecasts, do not use the Moving Average Data Analysis tool. This tool will not give you a valid forecast because it uses the current period in the computation. Instead, use the AVERAGE() function. Also, for both the ES and the MA forecasts, do not include the period you are forecasting in the history you are using to compute the forecast.

4. Calculate the MAE for each forecasting model, and choose the best model based on this analysis.

5. Using the best model, make a new forecast for the next period.

6. Based on your analysis and the time series data context, do you believe that this forecast is reliable enough to base a business decision on? Why or why not? Defend your answer! For example, given the forecast and its associated reliability, would you feel confident to base a staffing decision on this result?

Deliverables

Create an email to Dr. XX that describes your analysis and results. Make sure that your answer is error free (if necessary, compose it in Word for spell checking and then paste it

into the email). All students who worked on the analysis should be listed at the end of the email, and everyone should receive the submitted email. Attach your spreadsheet to this

message. Make sure that the spreadsheet is well-organized and easy to read.