Tutorial on Using Excel to Run OLS



Tutorial on Using Excel to Run OLS

The objective of this reading assignment is to guide you through the mechanics necessary to perform an OLS regression in Excel. Obviously, the first step is to open Excel. Assuming Excel is open, an OLS regression is performed as follows.

Step One - Entering Data

Your data must be in columns. The columns do not have to have a label, but labeling your data will make reading the results easier. Data for the independent variables must be in adjacent columns. It is ok to manipulate your data (such as squaring a variable), but before running the regression, make sure all the independent variables are in adjacent columns. The dependent variable and independent variables do not have to be in adjacent columns.

The data set-up for this example is shown in the following figure. The dependent variable is labeled “output” and the independent variables are labeled “land, labor, capital.” Create a worksheet that has the following data as shown in the worksheet below. The tutorial works best if data is entered in the cells shown. As will be shown later, you do not have to enter the column of ones associated with the intercept.

[pic]

Step Two - Obtaining the Main Regression Menu Pop up Box

Substep one: Click on “Tools” - a pop up box will appear as shown in the following figure. Then click on “Data Analysis”

If the “Data Analysis” ( or “Regression,” see below) line does not appear when you click on tools, be sure to click on the down arrows. If “Data Analysis” still does not appear, it is not installed on your machine. To install, you need to go to “Tools” then “Add-Ins” and install “Analysis ToolPak” and “Analysis ToolPak - VBA.” To install, you must have the Excel software disk. Computers labs on campus should have the add-in installed. If not, see the attendant.

[pic]

Substep Two: After Clicking on “Data Analysis,” the following pop up box appears. Scroll down in this box until you see “Regression.” Highlight “Regression” and then click “OK,” as shown in the following figure.

[pic]

Step Three - Entering Necessary Information

After clicking “OK,” the following main menu regression pop up box appears. In this box, you will provide the necessary input for Excel to run the OLS regression. In this pop up box, there are ranges for inputting the dependent variable, “Y Range”, and the dependent variable, “X Range.” Also, there are spaces to note if labels are included and output options. Each input range is discussed.

[pic]

Y Range To enter the dependent variable, click on the spreadsheet icon next to the “Input Y Range” space. After Clicking on this spreadsheet icon, the following pop up box will appear. To enter your dependent variable range, use the curser to highlight the dependent variable data including the label. This is shown in the following figure by the dotted lines in the first column around cells A5 to A15. (Typing in the cell addresses in the space provided will also input the y data range.) Once your data is highlighted, click on the spreadsheet icon. If your data does not have a label, start by highlighting the first observation. (In this case, highlighting would start in row 6 instead of row 5.)

Clicking on the spreadsheet icon will return you to the main regression input pop up box, obtained in step 2 and shown in step 3.

[pic]

X Range Entering the independent variable range is similar to the procedure used to entering the dependent variable range. Start by clicking on the spreadsheet icon next to “Input X Range.” After Clicking on this spreadsheet icon, the following pop up box will appear. To enter your independent variable range, use the curser to highlight the independent variable data including the labels. This is shown in the following figure by the dotted lines around the cells C5 to E15. (Typing in the cell addresses in the space provided will also input the x data range.) Once your data is highlighted, click on the spreadsheet icon. If your data does not have a label, start by highlighting the first observation. (In this case, highlighting would start in row 6 instead of row 5.)

Clicking on the spreadsheet icon will return you to the main regression input pop up box, obtained in step 2 and shown in step 3.

[pic]

Output Range Output can either be placed in the current worksheet or in a new worksheet. This example places the OLS output in the current worksheet. Because placing the output in a new worksheet is a simpler process, it is not shown but only discussed.

To place output in the current worksheet, click on the circle next to “Output Range.” After clicking on this circle, you will be able to click on the spreadsheet icon. Clicking on this icon gives a pop up box to provide the range for the output. In this box, it is necessary to only put the first cell of the output range. A word of caution, if placing the output in the current worksheet, is be careful to not overwrite any necessary cells.

Clicking on the spreadsheet icon will return you to the main regression input pop up box, obtained in step 2 and shown in step 3.

To place the output in a new worksheet, at the main regression input menu, click on the circle next to “New Worksheet Ply.” A name for the sheet can be typed into the provided space.

[pic]

Step 4 - Running the Regression

Before running the regression, if you have labels on your data; click the box next to “Labels.” If you do not have labels, leave this box blank. To run the regression, click on “OK” in the main regression menu pop up box.

As noted earlier, Excel will automatically create the intercept variable. If you do not want an intercept, click on the box “Constant is Zero.”

[pic]

Step 5 - Obtaining the Regression Output

After clicking “OK” on the main menu pop up box, the following should appear staring with cell H1 in your worksheet. Notice, Excel automatically placed an intercept into your analysis. If your output is not identical to the figure below, the first aspect to check is the data. Carefully, proofread your data, make the 10 data points are correct. The next aspect to check is the main regression menu. Make sure you have the Y and X ranges properly entered. Also, make sure the “Label” box is checked. Finally, check to make sure you have not checked the “Constant Is Zero” box.

When the output is identical, you have successfully run an OLS regression. Congratulations.

The next step is to interpret and use the regression output. See other assignments.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download