Hotel Site Determination

Step 2: Create a correlation table

Using the Data Analysis > Correlation tool, make a correlation table for all of the quantitative variables.

Highlight the correlation values for operating margin related to each explanatory variable.

Briefly describe what each of the highlighted correlation values tell you about the relationship between operating margin and each explanatory variable.

Step 3: Create two scatter plots

Choose the two explanatory variables that have the highest correlation values (negative or positive) with Operating Margin.

Create a scatterplot with operating margin on the vertical axis and one of the explanatory variables you picked on the horizontal axis.

Now make another scatterplot with operating margin on the vertical axis and the other explanatory variables you picked on the horizontal axis.

Make sure to include a trendline and choose Display Equation for each scatterplot.

Does the information contained in the scatterplots support the results from Step 2?

Step 4: Create a linear regression for one explanatory variable

Using the Data Analysis > Regression tool in Excel, perform a regression analysis for operating margin using one of the two variables you worked with in Step 3.

Check to make sure that the regression coefficients are the same as the equation in the scatterplot you created.

What does the R square tell you about the model?

What does the coefficient tell you about the model?

What does the p-value tell you about the coefficient?

Step 5: Create a linear regression for multiple explanatory variables

Using the Data Analysis > Regression tool in Excel, perform a regression analysis for operating margin using all of the quantitative variables as the explanatory variables.

How does this model perform compared to the one you used in Step 4 based on R²?

Look at the p values for the coefficient of each explanatory variable. Do you have any reason to believe that any of the estimates lack sufficient evidence to support that variables relationship with operating margin?

Interpret the coefficient of each explanatory variable as it relates to its impact of operating margin. For example, If the distance from downtown increases by 1 mile, the operating margin increases/decreases by _________ percentage points.

Finally, compare the coefficient values to the correlation values in Step 2. Do the results from the regression support the results you found in Step 2?