Question

Use Excel to develop a regression model for the Consumer Food Database (using the "Excel Databases.xls" file on Blackboard) to predict Annual Food Spending by Annual Household Income for those living in the Metro area only. Suppose a household in the metro area has an annual income of $60,000. Predict how much they spend on food per year. Write your answer as a number (do not include the $ sign or comma) and round to 2 decimal places.

Answer 1

Step 1 : Create an Indicator Variable for metro cities using formula mentioned in formula bar.

Step 2: Filter the Data on Metro cities i.e. select only those cities with Metro Indicator 1.

Step 3: Paste this filtered data to a new sheet.

Step 4: Go to Data - Data Analysis - Regression

Step 5: Enter the range of Y-variable and X-variable as shown. Select Output range and click on residuals. It will give you Output Summary and the Predicted Values along with Residuals

Please see attachment