A bank would like to develop a model to predict the total sum of money customers withdraw from automatic teller machines (ATMs) on a weekend based on the following model:
where = Withdrawal amount ($000)
= Median assessed value of houses in the vicinity of the ATM ($000)
= 1 if the ATM is located in a shopping center
0 otherwise
A random sample of 15 ATMs is selected with the following results.
Median
Withdrawal Assessed Value
ATM# Amount($000) of Houses ($000) Location of ATM
1 12.0 225 1
2 9.9 170 0
3 9.1 153 1
4 8.2 132 0
5 12.4 237 1
6 10.4 187 1
7 12.7 245 1
8 8.0 125 1
9 11.5 215 1
10 9.7 170 0
11 11.7 223 0
12 8.6 147 0
13 10.9 197 1
14 9.4 167 0
15 11.2 210 0
The following steps describe how to use the Regression tool in Microsoft Excel.
1. Arrange the data in columns: The two (or more) explanatory variables must be in adjacent columns. Label the data in the top cell of each column (for example, ATM#, Y, X1, and X2) and enter the data under the corresponding labels. (You might like to center the data in each column, including the label, by clicking the appropriate icon on top.)
2. If you have Excel 2010/13/16, go to the Data tab and locate the Data Analysis option under the Analysis category. If it is not listed there, click the File button, then go to Options. Click on the Add-Ins option and you will see Analysis ToolPak option in the list. Hit the Go button at the bottom. Now select the Analysis Toolpak option and click OK to complete the configuration. In the Data Analysis dialog box, scroll the list box, select Regression, and choose OK.
3. Input Y Range: Point to or enter the reference for the range containing values of the dependent variable ($B$1: $B$16). Include the label above the data.
4. Input X Range: Point to or enter the reference for the range containing values of the two explanatory variables ($C$1: $D$16). Include the labels above the data.
5. Labels: Select this box, because the labels at the top of the Input Y Range and Input X range were included in those ranges.
6. Constant is Zero: Do not select this box. Select this box only if you want to force the regression line to pass through the origin (0, 0).
7. Confidence Level: Select this box and erase 95. Otherwise, Excel automatically includes 95% confidence intervals twice. (For an additional confidence interval, select this box and enter the level in the Confidence Level box.)
8. Output location: Click the Output Range button and point to or type a reference for the top-left corner cell of a range sixteen columns wide where the summary output should appear. (For example, $A$20)
9. Click OK. The summary output appears.
10. Optional: To change column width so that all summary output labels are visible, select the cell containing the Adjusted R Square label. Hold down the Control key while clicking the following cells: Significance F, Coefficients, Standard Error, and Upper 95%. Go to Home tab. From the Format menu, choose AutoFit Column Width.
0 comments