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.

Answer 1

a) Scatter Plot

Construct scatter plot by selecting room rate and entertainment values and going on Insert tab in the following way:

File Home Insert Page Layout Formulas Data Power Pivot Search Help E-7 3 5 5 Review View L? h? 10.- Recommended Charts BGet Add-ins My Add-ins - do do ti tol V E Illustrations Maps PivotChart Slicer T Pivot Table Recommended Table Pivot Tables Tables Map E Scatter Line Column Win/ Loss Sparklines Add-ins Tours Filte C3 x for 146 B C D J K L M Bubble Hotel ment expense for City Hotel Room Rate ($) Entertainment ($) Boston 159 Denver 103 Nashville 101 New Orleans Phoenix San Diego 119 San Francisco San Jose Tampa 100 101 :: More Scatter Charts... Entertainment Expense ($) 0 20 40 120 140 160 60 80 100 Hotel Room Rate (s)

Hotel rom rate and entertainment expense for each city y = 1.0625x + 14 215 Entertainment Expense ($) 0 20 40 120 140 160 60 80 100 Hotel Room Rate ($)

Right click on any point, select Add Trendline, choose Linear and tick Display Equation on chart.

c) y = 1.0625x + 14.215

Entertainment = 1.0625*Hotel Room Rate + 14.215

Slope = 1.0625

Intercept = 14.215

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

e) Room rate in Chicago = 128

Entertainment Expense for Chicago = 1.0625*Hotel Room Rate + 14.215

= 1.0625*128 + 14.215

= $150.215