Column A
Survey Number
This records the survey number that should also be written on the survey at the corner to enable the analyst to go back and check something if need be.
Column B
Attend
Yes 1
No 0
Column F
Zipcode
Actual zipcode entry
City of Cincinnati zip codes are:
45201 — 45299.
One way to create a new variable called “Local” is to (1) insert a
column, (2) highlight the entire data set by clicking the grey box
northwest of cell A1 and, (3) sort (Data/Sort) all of the data by zipcode
and then go to the zip code range for Cincinnati listed above and put a
“1” for all surveyees in Cincinnati and a “0” for those from out of town.
How long will you be visiting Cincinnati? (enter numerical value)
Column G Days Enter numerical values
Column H Nights Enter numerical values.
Survey # Attend Age Gender
Inco
me
Zipcode
Visitors
Days
Incremen
tal
visitors
Days
Increment
al visitors
Days*Part
y
Nights
Incremental
visitors
Nights*Part
y
Party
Attend, Vis,
Party Size
Attend, Local,
Party Size
Lodging
Lodging per
incremental
visitor per day
Transpo
rtion
Event
Incremental
visitor event
spending per
day
Food
Entertain
ment
Shoppi
ng
Other
Spending per
incremental
visitor per day
(not lodging)
Casual
Cas,
Attend,
Vis,
Party
TS
TS,
Attend,
Visitors,
Party
Both TS
and
Casual
1 1 2 0 3 08055 1 4 4 12 3 9 3 3 140 46.67 55 125 42 130 75 120 20 175.00 0 0
2 0 2 1 1 45239 0 1 0 3 0 4 0 134 49 90 7 1 0
3 1 1 0 1 02147 1 2 2 4 1 2 2 2 154 77.00 8 44 22 116 32 45 5 124.88 1 2 0
4 1 1 1 1 93676 1 1 1 1 1 1 1 1 112 112.00 4 13 13 60 16 21 2 115.63 0 0
5 1 1 1 1 45209 0 1 0 2 2 0 2 60 029 46 3 1 0
6 1 3 0 1 45209 0 4 3 4 4 0 2 116 268 66 017 1 0
7 1 2 1 60863 1 3 3 9 2 6 3 3 185 61.67 14 102 34 222 44 85 11 159.25 0 0
8 1 1 1 3 08499 1 3 3 3 2 2 1 1 84 84.00 4 30 30 67 24 26 2 153.25 0 0
25 1 2 1 3 62376 1 1 1 3 1 3 3 3 176 58.67 9 144 48 336 47 42 7 195.04 0 0
26 1 1 1 3 45217 0 2 1 1 1 0 4 28 65 21 23 4 1 0
27 1 4 1 4 38536 1 2 2 4 1 2 2 2 83 41.50 21 108 54 300 57 85 11 290.75 0 0
28 1 2 0 4 87242 1 3 3 6 2 4 2 2 184 92.00 11 106 53 219 45 34 9 212.13 0 0
29 1 3 0 4 45242 0 1 0 3 3 0 2 174 289 60 87 15 1 0
30 0 1 1 4 63665 1 3 2 2 162 11 0147 35 34 7 0 1
31 1 3 1 4 45240 0 1 0 2 2 0 4 96 201 45 43 9 1 0
32 1 1 0 4 92813 1 1 1 1 0 0 1 1 30 30.00 5 37 37 84 26 27 4 183.13 0 1 1
33 0 2 0 4 32445 1 1 1 2 154 9 0 169 46 71 7 1 0
34 1 2 1 5 96090 1 1 1 4 0 0 4 4 30 7.50 8 176 44 403 84 78 23 192.88 0 0
35 1 2 1 5 45214 0 3 2 3 3 0 4 144 296 72 116 15 1 0
36 1 4 1 5 08743 1 2 2 4 1 2 2 2 179 89.50 23 148 74 278 50 56 13 283.88 0 0
37 1 4 0 5 75479 1 4 4 4 3 3 1 1 70 70.00 19 64 64 168 30 28 6 314.88 0 0
38 1 5 0 5 45234 0 4 3 4 4 0 2 272 707 108 158 30 1 0
39 1 3 1 5 75530 1 4 4 8 3 6 2 2 156 78.00 12 110 55 291 45 55 12 262.63 0 0
58 1 2 0 3 56453 1 3 3 6 2 4 2 2 127 63.50 12 70 35 169 33 64 5 176.25 0 0
59 1 2 0 4 45219 0 2 1 4 4 0 2 220 376 88 75 16 1 0
60 1 3 1 3 45224 0 3 2 5 5 0 2 250 627 93 97 21 1 0
61 1 5 1 3 45226 0 3 2 3 3 0 2 192 356 82 96 19 1 0
62 1 1 1 3 52036 1 1 1 2 0 0 2 2 30 15.00 9 78 39 121 27 70 4 154.25 0 1 2
63 1 2 1 4 45233 0 3 2 3 3 0 4 135 348 61 83 9 1 0
64 1 2 1 3 45243 0 1 0 3 3 0 2 99 275 52 74 12 1 0
65 1 2 1 3 98301 1 3 3 9 2 6 3 3 106 35.33 14 144 48 241 52 76 11 179.21 0 0
66 1 2 0 3 45202 0 1 0 1 1 0 4 33 105 28 0 3 1 0
67 1 1 0 4 47315 1 1 1 1 0 0 1 1 30 30.00 5 32 32 65 24 31 4 160.88 0 1 1
82 1 4 0 6 00056 1 2 2 8 1 4 4 4 148 37.00 19 264 66 572 121 116 21 278.25 0 0
83 1 4 1 4 45205 0 2 1 6 6 0 2 414 766 127 166 34 1 0
84 1 2 1 4 56681 1 4 4 16 312 4 4 209 52.25 9 172 43 475 87 90 13 211.38 0 0
85 1 1 0 3 45202 0 1 0 2 2 0 4 50 121 30 48 4 1 0
86 1 2 0 4 87577 1 1 1 1 0 0 1 1 30 30.00 9 43 43 107 28 32 5 223.63 0 0
87 1 4 0 5 45238 0 1 0 6 6 0 4 402 940 156 292 37 1 0
88 0 1 0 4 14020 1 1 0 2 30 5 0 147 35 53 5 1 0
89 1 5 0 3 45232 0 2 1 1 1 0 2 57 156 29 45 6 1 0
90 0 2 0 3 57999 1 3 2 3 155 11 0336 56 42 14 1 0
91 1 4 0 3 45212 0 3 2 2 2 0 2 108 259 50 53 8 1 0
92 0 5 1 6 07856 1 1 0 2 30 21 0340 59 100 14 1 0
93 1 2 1 3 45231 0 3 2 1 1 0 2 38 83 25 26 4 1 0
94 1 1 0 4 88546 1 2 2 2 1 1 1 1 65 65.00 5 37 37 76 23 25 3 168.75 0 0
95 1 3 1 4 45227 0 3 2 2 2 0 2 92 233 43 48 10 1 0
96 1 2 0 3 45220 0 3 2 2 2 0 4 68 184 39 61 9 1 0
97 0 2 1 3 62203 1 1 0 2 30 8 0 169 37 36 9 1 0
98 1 4 0 4 43950 1 2 2 8 1 4 4 4 182 45.50 22 208 52 502 109 203 26 267.50 0 0
120 1 4 1 4 21071 1 2 2 2 1 1 1 1 110 110.00 18 60 60 152 33 30 5 297.88 0 0
121 1 2 1 4 60169 1 2 2 2 1 1 1 1 104 104.00 11 35 35 110 27 18 4 204.50 0 0
122 1 1 1 4 45243 0 1 0 2 2 0 2 70 139 43 41 7 1 0
123 1 3 0 4 28660 1 4 4 12 3 9 3 3 206 68.67 16 186 62 323 76 98 12 237.13 0 0
124 1 2 0 4 45225 0 2 1 2 2 0 5 94 174 36 33 8 1 0
125 0 5 0 4 13945 1 2 1 1 146 26 0147 36 53 6 0 1
126 1 3 0 4 61459 1 4 4 16 312 4 4 193 48.25 14 232 58 492 85 78 18 229.75 0 0
127 1 3 0 1 45207 0 3 2 3 3 0 2 132 281 42 67 9 0 0
128 0 4 1 6 45203 0 3 2 2 0 5 0 336 54 101 12 1 0
129 1 3 1 1 45222 0 2 1 1 1 0 4 42 94 23 26 3 1 0
145 0 4 0 6 45222 0 2 1 4 0 2 0 689 120 128 29 1 0
146 1 3 0 5 87823 1 1 1 3 0 0 3 3 30 10.00 15 183 61 409 73 96 18 264.67 0 1 3
147 0 5 1 4 69042 1 3 2 7 217 25 01065 173 288 40 1 0
148 1 2 0 1 57663 1 1 1 2 1 2 2 2 172 86.00 12 54 27 161 27 76 4 167.13 0 0
149 1 1 0 4 45206 0 3 2 2 2 0 2 84 169 39 48 8 1 1
150 1 4 0 5 54182 1 2 2 6 1 3 3 3 146 48.67 16 189 63 416 71 103 16 270.25 0 0
151 0 1 0 4 10695 1 2 1 1 92 8 0 102 23 38 4 1 0
152 1 2 0 3 54158 1 2 2 6 1 3 3 3 203 67.67 14 111 37 323 62 128 11 216.25 0 0
153 0 4 0 3 63968 1 1 1 3 220 23 0424 62 101 13 1 0
154 0 3 0 5 45210 0 4 3 3 0 4 0 430 81 126 17 1 0
155 0 3 0 3 45216 0 2 1 5 0 4 0 593 104 228 15 1 0
156 0 3 0 5 40500 1 1 0 4 30 18 0520 108 131 19 1 0
157 1 2 0 4 51070 1 2 2 2 1 1 1 1 157 157.00 11 46 46 96 29 44 5 230.75 0 0
158 1 3 0 4 54844 1 1 1 4 0 0 4 4 30 7.50 12 188 47 430 79 191 17 229.25 0 0
182 1 1 0 3 45211 0 2 1 1 1 0 2 32 73 18 36 2 1 0
183 1 1 4 4 8 3 6 2 2 102 51.00 9 50 25 147 29 41 7 141.38 0 0
184 1 1 0 3 45238 0 1 0 1 1 0 2 36 78 24 37 4 1 0
185 1 2 1 3 59075 1 4 4 8 3 6 2 2 176 88.00 9 86 43 169 45 33 9 175.63 0 0
186 1 3 0 5 93761 1 1 1 4 0 0 4 4 30 7.50 14 204 51 483 99 146 26 242.88 0 0
187 0 2 0 2 45230 0 1 0 2 0 4 0 129 33 53 4 1 0
188 1 2 1 4 45227 0 4 3 2 2 0 2 94 241 48 86 10 1 0
189 0 1 0 4 66406 1 1 0 2 30 9 0 169 37 81 8 1 0
190 1 2 0 3 45211 0 1 0 3 3 0 5 108 201 42 56 10 1 0
204 0 4 0 5 45218 0 2 1 2 0 5 0 246 50 49 11 1 0
205 1 3 0 5 99040 1 4 4 8 3 6 2 2 107 53.50 14 110 55 214 45 75 11 234.63 0 0
206 1 1 1 2 45206 0 1 0 2 2 0 2 70 166 24 52 4 1 0
207 1 1 0 3 45218 0 1 0 1 1 0 2 26 019 14 3 1 0
208 1 1 0 3 45194 1 1 1 2 0 0 2 2 30 15.00 4 66 33 129 29 64 6 148.88 0 0
209 0 5 0 6 74169 1 1 0 2 30 26 0363 70 100 13 1 0
210 1 5 0 5 46192 1 1 1 2 0 0 2 2 30 15.00 21 146 73 323 57 56 15 308.75 0 0
211 1 1 0 4 45206 0 1 0 1 1 0 2 38 105 22 36 3 1 0
212 1 1 1 3 45218 0 3 2 1 1 0 2 26 54 22 38 2 0 0
213 1 2 1 2 01074 1 3 3 6 2 4 2 2 157 78.50 11 54 27 129 28 51 6 139.50 1 2 0
214 1 2 1 4 31094 1 2 2 2 1 1 1 1 162 162.00 15 50 50 102 23 36 3 229.13 0 0
215 0 1 1 4 08381 1 2 1 2 157 9 0 211 36 51 6 1 0
216 1 5 0 6 45230 0 2 1 2 2 0 5 158 363 70 112 13 1 0
217 1 2 0 3 12669 1 1 1 2 0 0 2 2 30 15.00 8 72 36 184 42 38 7 175.63 0 0
218 1 2 0 2 45207 0 1 0 2 2 0 2 88 161 36 78 7 0 0
219 1 3 0 5 00136 1 1 1 3 0 0 3 3 30 10.00 16 162 54 382 79 78 13 243.21 0 0
220 1 4 0 2 73036 1 4 4 12 3 9 3 3 209 69.67 23 123 41 369 70 130 15 243.21 1 3 0
244 1 1 0 4 58091 1 4 4 4 3 3 1 1 120 120.00 9 40 40 68 20 40 3 180.13 0 0
245 0 4 1 2 45219 0 3 2 2 0 2 0 201 49 61 10 1 0
246 0 1 1 3 20889 1 3 2 1 140 2 0 67 22 15 2 0 0
247 1 3 1 5 45240 0 3 2 4 4 0 5 232 528 106 168 26 1 0
248 1 3 1 3 02655 1 2 2 10 1 5 5 5 210 42.00 15 250 50 459 83 161 22 198.03 0 0
249 1 1 0 3 45224 0 1 0 2 2 0 2 60 169 28 68 6 1 0
250 1 3 0 3 68295 1 2 2 10 1 5 5 5 259 51.80 12 215 43 593 98 168 22 221.63 0 0
251 1 4 0 6 54907 1 1 1 5 0 0 5 5 30 6.00 23 400 80 795 123 258 33 326.30 0 0
252 1 3 0 3 45201 0 1 0 4 4 0 4 188 465 73 020 1 0
269 1 2 1 4 57320 1 1 1 3 0 0 3 3 30 10.00 9 111 37 248 63 78 12 173.63 0 0
270 0 1 1 3 54662 1 2 1 2 126 11 0116 27 63 8 0 0
271 1 4 1 6 45230 0 3 2 4 4 0 4 272 582 112 216 28 1 0
272 0 3 0 4 45233 0 2 1 5 0 2 0 0 85 180 26 1 0
273 1 4 0 6 62153 1 3 3 12 2 8 4 4 203 50.75 22 252 63 654 114 153 24 304.63 0 0
274 1 3 0 5 45238 0 1 0 3 3 0 5 180 390 69 67 18 1 0
275 1 5 0 6 25233 1 3 3 6 2 4 2 2 168 84.00 22 172 86 326 66 83 15 342.13 0 0
276 1 3 0 2 00907 1 3 3 9 2 6 3 3 131 43.67 15 102 34 342 64 81 15 206.33 0 0
277 1 2 0 2 45234 0 1 0 2 2 0 2 68 192 28 64 4 1 0
278 1 2 1 4 25541 1 4 4 12 3 9 3 3 149 49.67 12 147 49 342 51 123 15 229.83 0 0
279 1 1 1 3 31178 1 4 4 8 3 6 2 2 108 54.00 4 60 30 152 31 61 8 158.00 0 0
280 1 1 1 2 64994 1 2 2 4 1 2 2 2 176 88.00 7 56 28 116 31 53 5 134.00 0 0
281 0 2 0 3 13445 1 3 2 2 198 7 0 192 35 61 7 1 0
282 1 2 0 2 45204 0 1 0 1 1 0 4 34 68 21 19 3 1 0
283 1 2 0 4 13036 1 1 1 2 1 2 2 2 101 50.50 8 96 48 179 35 38 7 181.38 0 0
284 1 1 0 2 45225 0 2 1 2 2 0 5 54 129 33 63 7 1 0
285 1 2 0 5 51835 1 2 2 4 1 2 2 2 120 60.00 14 92 46 256 48 79 11 250.13 0 0
306 0 4 0 3 45212 0 2 1 2 0 2 0 224 42 75 12 1 0
307 1 1 0 3 86255 1 2 2 4 1 2 2 2 80 40.00 4 78 39 112 34 32 5 132.50 0 0
308 1 5 1 6 61182 1 1 1 3 0 0 3 3 30 10.00 22 231 77 537 97 23 17 308.88 0 0
309 1 3 0 4 45194 1 4 4 20 315 5 5 260 52.00 14 270 54 638 96 38 23 215.85 0 1 5
310 1 1 0 3 45224 0 1 0 2 2 0 2 60 169 28 27 6 1 0
311 1 5 1 4 00381 1 3 3 9 2 6 3 3 164 54.67 23 198 66 416 76 78 16 269.13 0 1 3
312 0 1 0 2 45208 0 2 1 1 0 2 0 76 20 38 4 1 0
313 1 3 0 5 93761 1 1 1 4 0 0 4 4 30 7.50 14 204 51 483 99 13 26 209.63 0 0
Answers to Case Study for Chapter 12 — Measuring the Economic Impact of the MLB All-Star Game
Note that the survey in Exhibit 12.14 is to be used for this case study. Also, note that the first row (survey respondent) of the data set corresponds to that filled-out survey.
Note that Survey #183 has no zipcode on purpose to see how the student responds. There are two ways to handle this. One is to throw it out or notice that they have significant lodging spending so they likely are visitors. This answer here chose to keep it in and label it a Visitor.
Also, Surveys #200 and #290 have the wrong number of days (data entry error is simulated). I have fixed those for these answers. Most students ask about it and I put it back on them to figure out what to do.
Also, Survey #226 has 11 for Attend instead of 1. One thing to teach the students is to “clean” the data. The easiest way to catch typos is to Filter the Data (under Data/Filter/Autofilter) and look at the choices given on the pulldown.
Also, Survey #175 has 11 for Casual, where it likely should be a 1.
Question Answer Explanation
Question 1 42,059 The game sold out and seats 42,059. An assumption is that there were not any no-shows – somebody sat in every seat.
Question 2
Here is one method: First, insert a column and label it “Visitors”. Place a 1 for every visitor and a 0 for every local resident. Either create an
Excel formula or sort the entire data set by zipcode and it will make it easier to enter the 0s and 1s in groups. Second, note that each survey
represents more than one person (usually a family or group). Each of these people will take up a seat at the stadium if they are attending. In
order to count the number of visitors (versus locals) who attended the game, we must account for the Party size (Q7 on the survey), and
whether they attended the game (Q1), and whether they are a Visitor (column you just created). Third, insert a column and either use the
formula that is in there or do it by hand. Put in this column either the party size for visitors who attend the game or a blank. Once that’s
Question 6
$222.78,
$53.07
The survey asks the question a spending per DAY for the GROUP. This question wants the spending per DAY for each PERSON. For non-
lodging, the daily spending per person is $222.78. For lodging, it is $53.07. This is for the people in Question 5 — those who are now called
“incremental” visitors. WE ASSUME THAT THE RESPONDENT ANSWERED THE QUESTION PER NIGHT STAYED, NOT MAKING ANY
ADJUSTMENT FROM DAYS TO NIGHTS.
Question 7
2.26,
1.29
Taking the average (only for the incremental visitors) of the number of days not accounting for party size leads to 2.25 days. When
weighting the number of days by party size, it is 2.26 (so not an important difference). For nights, the weighted average is 1.29.
Question 8 $571.77
Since Q6 asks for per person spending per day and Q7 asks for the number of days, then multiplying Q6 and Q7 gives the answer to Q8.
For non-lodging, it is $503.22. For lodging it is $68.55. The total, then, is $571.77. Again, depending on how one does the rounding, it could
be off by something.
Question 9 $6.97 million
Total EI = Direct EI + Indirect EI. Indirect EI = Direct EI*multiplier. Here the multiplier is given as 1.6 (usually it is a matrix for each spending
category). Thus, Total EI = 1.6*Q9 = $11.16 million
typically be 14,280 spots open in hotel rooms in Houston. Given that 19,715 incremental visitors showed up, they blocked or crowded out
5,435 visitors (19,715-14,280). Note that this calculation is based on incremental visitors, not total visitors because a Casual Visitor (for
instance) staying in a hotel is a typical tourist anyway. Even if they stop another tourist from coming, they are here for another reason (not
the game), so that is not part of the calculation of economic impact.
by the crowded out visitors. This is possibly a strong assumption because these All-Star Game visitors spend more than a typical visitor
does. In fact, a major sporting event visitor spends about twice as much as a typical visitor (based on comparisons to CVB numbers).
Anyway, we don’t have the CVB information, so re-answer the previous Questions 9 and 10 with 14,280 for incremental visitors.
This is where all of the pieces get put together. Q8 multiplied by Q5 gives the direct spending that comes from incremental visitors
($11,272,504). Also, 60% of LOC funding came from outside of the city ($2.7 million). This is assumed to be new incremental spending in
the city that would not have occurred otherwise. Also, Minute Maid took the opportunity to activate its sponsorship of the stadium by
spending an additional $1 million. Again, this is assumed to additional net new spending by Minute Maid. However, the City spent $8 million
hosting the event. It is assumed that this money could have been spent elsewhere and is thus a cost of the event to be subtracted off of the
$11,272,504 2.7 1 8 $6.97
should be 247. That means that of the people surveyed, 672 attended the game (425 visitors and 247 locals). This is only for the SAMPLE.
Some students will think that this is the answer. They might need to reminded (prior to starting) to be sure to do their calculations for the
entire POPULATION, not just the SAMPLE. Assuming a random sample of attendees, about 63% (425/672) of the game attendees are
visitors. So, multiply that ratio by 40,950 to get the answer: 26,600.
This is similar, but for Casual Visitors. There are 51 Casual Visitors who attended the game in the SAMPLE. So, 51/425 is the percentage of
visitors attending the game who are Casual. Multiply that by 26,600 to get: 3,108
BUT STAYED OUTSIDE (PERHAPS IN A LOCAL PUB WATCHING THE GAME). THIS CASE STUDY DOES NOT INCLUDE THEM
(ALTHOUGH THEY ARE IN THE DATA SET AND COULD BE INCLUDED).
Question 13 $1.026 million
The fiscal impact consists of the sales taxes collected, the hotel taxes collected, and the city’s portion of the stadium revenue from the event.
In most cities, some goods and services are taxed, but not all of them. Without specific information, it is simplest in this case to assume that
all spending is subject to sales taxation and lodging is subject to a hotel tax. The Event spending category will be subject to sales taxation,
but also 20% of it will go to the City. Q8 showed that lodging spending is $68.55 per person per stay. Based on 14,280 (incremental visitors
accounting for hotel capacity constraints), the hotel taxes collected are $68.55*14,280*0.15=$146,834. Non-lodging spending was $503.22
(Q8). Using a sales tax rate of 7.75%, we get $503.22*14,280*.0775=$556,914.
$146,834 $556,914 $321,889