Question

Concur Technologies, Inc., is a large expense-management company located in Redmond, Washington.  The Wall Street Journal asked Concur to examine the data from 8.3 million expense reports to provide insights regarding business travel expenses. Their analysis of the data showed that New York was the most expensive city, with an average daily hotel room rate of $198 and an average amount spent on entertainment, including group meals and tickets for shows, sports, and other events, of $172. In comparison, the U.S. averages for these two categories were $89 for the room rate and $99 for entertainment. The table in the Excel Online file below shows the average daily hotel room rate and the amount spent on entertainment for a random sample of 9 of the 25 most visited U.S. cities (The Wall Street Journal, August 18, 2011). Construct a spreadsheet to answer the following questions.

1. Develop the least squares estimated regression equation.

   Entertainment = (___)+(___) Room Rate ( to 4 decimals)

2. Provide an interpretation for the slope of the estimated regression equation (to 3 decimals).

   The slope of the estimated regression line is approximately (____) . So, for every dollar increase in the hotel room rate the amount spent on entertainment increases by $ (___).

3. The average room rate in Chicago is $128, considerably higher than the U.S. average. Predict the entertainment expense per day for Chicago (to whole number).

   $ (___)
   
   

   Hotel Room Rate ($)	Entertainment ($)
   152	162
   96	104
   87	103
   113	141
   92	98
   103	121
   133	167
   88	140
   81	96

Answer 1

using excel>data>data analysis>Regression

we have

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.843804
R Square	0.712005
Adjusted R Square	0.670862
Standard Error	15.86732
Observations	9
ANOVA
	df	SS	MS	F	Significance F
Regression	1	4357.152	4357.152	17.30595	0.004242
Residual	7	1762.404	251.7719
Total	8	6119.556
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	22.45778	25.39323	0.8844	0.405831	-37.5877	82.50321	-37.5877	82.50321
Hotel Room Rate ($)	0.984	0.236536	4.160042	0.004242	0.424681	1.543319	0.424681	1.543319

1. Develop the least squares estimated regression equation.

   Entertainment = 22.458+0.9840*Room Rate

2. Provide an interpretation for the slope of the estimated regression equation (to 3 decimals).

   The slope of the estimated regression line is approximately 0.984. So, for every dollar increase in the hotel room rate the amount spent on entertainment increases by $ 0.984

3. The average room rate in Chicago is $128, considerably higher than the U.S. average, the entertainment expense per day for Chicago is 22.458+0.9840*128 = 148.41 = $ 148