Multivariate Econometrics Problems and Excel

Multivariate Econometrics Problems and Excel Most economics departments require second or third year undergraduate students to complete an econometrics project and write a paper on their findings. Years later I remember how stressful my project was, so Ive decided to write the guide to econometrics term papers that I wish I had when I was a student. I hope that this will prevent you from spending many long nights in front of a computer. For this econometrics project, Im going to calculate the marginal propensity to consume (MPC) in the United States. (If youre more interested in doing a simpler, univariate econometrics project, please see How to Do a Painless Econometrics Project) The marginal propensity to consume is defined as how much an agent spends when given an extra dollar from an additional dollars personal disposable income. My theory is that consumers keep a set amount of money aside for investment and emergency, and spend the rest of their disposable income on consumption goods. Therefore my null hypothesis is that MPC 1. Im also interested in seeing how changes in the prime rate influence consumption habits. Many believe that when the interest rate rises, people save more and spend less. If this is true, we should expect that there is a negative relationship between interest rates such as the prime rate, and consumption. My theory, however, is that there is no link between the two, so all else being equal, we should see no change in the level of the propensity to consume as the prime rate changes. In order to test my hypotheses, I need to create an econometric model. First well define our variables: Yt is the nominal personal consumption expenditure (PCE) in the United States.X2t is the nominal disposable after-tax income in the United States. X3t is the prime rate in the U.S. Our model is then: Yt b1 b2X2t b3X3t Where b 1, b 2, and b 3 are the parameters we will be estimating via linear regression. These parameters represent the following: b1 is the amount the level of PCE when nominal disposable after-tax income (X2t) and the prime rate (X3t) are both zero. We do not have a theory about what the true value of this parameter should be, as it holds little interest to us.b2 represents the amount PCE rises when the nominal disposable after-tax income in the United States rises by a dollar. Note that this is the definition of the marginal propensity to consume (MPC), so b2 is simply the MPC. Our theory is that MPC 1, so our null hypothesis for this parameter is b2 1.b3 represents the amount PCE rises when the prime rate increases by a full percent (say from 4% to 5% or from 8% to 9%). Our theory is that changes in the prime rate do not influence consumption habits, so our null hypothesis for this parameter is b2 0. So we will be comparing the results of our model: Yt b1 b2X2t b3X3t to the hypothesized relationship: Yt b1 1*X2t 0*X3t where b 1 is a value that does not particularly interest us. To be able to estimate our parameters, well need data. The excel spreadsheet Personal Consumption Expenditure contains quarterly American Data from the 1st quarter of 1959 to the 3rd quarter of 2003. Â  All data comes from FRED II - The St. Louis Federal Reserve. Its the first place you should go for U.S. economic data. After youve downloaded the data, open up Excel, and load the file called aboutpce (full name aboutpce.xls) in whatever directory you saved it in. Then continue to the next page. Be Sure to Continue to Page 2 of How to Do a Painless Multivariate Econometrics Project Weve got the data file open we can start to look for what we need. First we need to locate our Y variable. Recall that Yt is the nominal personal consumption expenditure (PCE). Quickly scanning our data we see that our PCE data is in Column C, labeled PCE (Y). By looking at columns A and B, we see that our PCE data runs from the 1st quarter of 1959 to the final quarter of 2003 in cells C24-C180. You should write these facts down as youll need them later. Now we need to find our X variables. In our model we only have two X variables, which are X2t, disposable personal income (DPI) and X3t, the prime rate. We see that DPI is in the column marked DPI (X2) which is in Column D, in cells D2-D180 and the prime rate is in the column marked Prime Rate (X3) which is in column E, in cells E2-E180. Weve identified the data we need. We can now compute the regression coefficients using Excel. If you are not restricted to using a particular program for your regression analysis, Id recommend using Excel. Excel is missing a lot of the features a lot of the more sophisticated econometrics packages use, but for doing a simple linear regression it is a useful tool. Youre much more likely to use Excel when you enter the real world than you are to use an econometrics package, so being proficient in Excel is a useful skill to have. Our Yt data is in cells E2-E180 and our Xt data (X2t and X3t collectively) is in cells D2-E180. When doing a linear regression we need every Yt to have exactly one associated X2t and one associated X3t and so on. In this case we have the same number of Yt, X2t, and X3t entries, so were good to go. Now that we have located the data we need, we can calculate our regression coefficients (our b1, b2, and b3). Before continuing you should save your work under a different filename (I chose myproj.xls) so if we need to start over we have our original data. Now that youve downloaded the data and opened Excel, we can go onto the next section. In the next section we calculate our regression coefficients. Be Sure to Continue to Page 3 of How to Do a Painless Multivariate Econometrics Project Now onto the data analysis. Go to the Tools menu on the top of the screen. Then find Data Analysis in the Tools menu. If Data Analysis is not there, then youll have to install it. To install the Data Analysis Toolpack see these instructions. You cannot do regression analysis without the data analysis toolpack installed. Once youve selected Data Analysis from the Tools menu youll see a menu of choices such as Covariance and F-Test Two-Sample for Variances. On that menu select Regression. The items are in alphabetical order, so they shouldnt be too hard to find. Once there, youll see a form that looks like this. Now we need to fill this form in. (The data in the background of this screenshot will differ from your data) The first field well need to fill in is the Input Y Range. This is our PCE in cells C2-C180. You can choose these cells by typing $C$2:$C$180 into the little white box next to Input Y Range or by clicking on the icon next to that white box then selecting those cells with your mouse. The second field well need to fill in is the Input X Range. Here we will be inputting both of our X variables, DPI and the Prime Rate. Our DPI data is in cells D2-D180 and our prime rate data is in cells E2-E180, so we need the data from the rectangle of cells D2-E180. You can choose these cells by typing $D$2:$E$180 into the little white box next to Input X Range or by clicking on the icon next to that white box then selecting those cells with your mouse. Lastly well have to name the page our regression results will go on. Make sure you have New Worksheet Ply selected, and in the white field beside it type in a name like Regression. When thats completed, click on OK. You should now see a tab on the bottom of your screen called Regression (or whatever you named it) and some regression results. Now youve got all the results you need for analysis, including R Square, coefficients, standard errors, etc. We were looking to estimate our intercept coefficient b1 and our X coefficients b2, b3. Our intercept coefficient b1 is located in the row named Intercept and in the column named Coefficients. Make sure you jot these figures down, including the number of observations, (or print them out) as you will need them for analysis. Our intercept coefficient b1 is located in the row named Intercept and in the column named Coefficients. Our first slope coefficient b2 is located in the row named X Variable 1 and in the column named Coefficients. Our second slope coefficient b3 is located in the row named X Variable 2 and in the column named Coefficients The final table generated by your regression should be similar to the one given at the bottom of this article. Now youve got the regression results you need, youll need to analyze them for your term paper. We will see how to do that in next weeks article. If you have a question youd like answered please use the feedback form. Regression Results Observations Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept X Variable 1 X Variable 2 -13.71941.4186-9.67080.0000-16.5192-10.9197