Why do use the two y-values? I'm not sure I understand that piece. Does this compare first differences - I'm not too familiar with the Excel lingo (regression pack) so I don't know what it is doing once I give it the information. I suppose that would be a useful Rachael/Jacob discussion NEAS can post - How does Excel interpret/use data to perform regression.
JR
Excel Regression Built-in Function
[NEAS: An extract from a posting for the regression analysis student project:]
Jacob: Do we solve for the ordinary least squares estimators by the equations in the textbook?
Rachel: Excel provides a built-in regression function. If we use a hundred simulation to test the accuracy of the estimators, we code a VBA macro or an Excel table with the formulas from the textbook. For the student project, it is easier to use the regression built-in function.
Jacob: Where is the Excel regression built-in function?
Rachel: Choose the tools menu from the menu bar. From the menu, choose data analysis. You may have to include the data analysis add-in to your version of Excel. From data analysis, choose regression.
Jacob: Simpler Excel built-in functions determine a linear trend line using regression. Can we use those built-in functions?
Rachel: Those Excel functions use a single independent variable. For two or more independent variables, we need the Excel add-in.
Jacob: How do we include the data analysis add-in?
Rachel: Check to see if the add-in is already installed. Some actuarial departments have the add-in installed.
If the add-in is not installed, choose add-ins… from the tools menu. From the menu that appears, choose analysis toolpak. To work with VBA, include also the analysis toolpak vba.
Your version of Excel may differ. If you can’t find the regression built-in function, post a question on the discussion forum, listing your version of Excel and of windows.
Jacob:
Are we doing regression analysis or time series? We speak about an ARIMA(2,1,0) model and then use the Excel built-in regression function.
Rachel: We have three elements:
If the time series has an order of homogeneity greater than 0, we take first or second differences. This is spreadsheet arithmetic; you don’t need sophisticated functions.
For an autoregressive model, we use linear regression with the Excel built-in regression function. If you want, you can add your own functions or VBA macros.
A moving average model is harder to code in Excel, since it uses non-linear regression. The textbook explains how to identify a moving average process by spikes in the sample autocorrelation function that are followed by white noise. In practice, it is hard to identify these spikes. You may comment whether the sample autocorrelation function suggests a moving average term, but you do not have to model this term. Moving average terms are not common.