QE1 - Dryville - Estimating demand & benefits for a public good

Instructions and question prompts Data and your responses and output Table 1. Data Town Visits Fee ($) 1 168,590 $0.00 2 179,599 $0.00 3 198,595 $0.00 4 206,662 $0.00 5 170,259 $0.00 6 209,995 $0.25 7 172,018 $0.25 8 190,802 $0.25 9 197,019 $0.25 10 186,515 $0.50 11 152,679 $0.50 12 137,413 $0.50 13 158,056 $0.50 14 157,424 $0.50 15 179,490 $0.50 16 164,657 $0.75 17 184,428 $0.75 18 183,822 $0.75 19 174,510 $1.00 20 187,820 $1.00 21 196,318 $1.25 22 166,694 $1.50 23 161,716 $1.50 24 167,505 $2.00 Table 2. Descriptive statistics [INSERT TABLE HERE] You have been asked to estimate the gross social benefits of building a public swimming pool in Dryville (DV), which has a population of 70,230 people and a median household income of $31,500. To construct your estimate, you have already gathered data--shown in Table 1 at right--from 24 nearby towns that already have public swimming pools. You surveyed recreation departments for all 24 towns to find out what "Fee" it charged per visit and how many "Visits" it had during the most recent swimming season. You also gathered each town’s "Population" and median household "Income" from the most recent census. 1. Create a table of descriptive statistics for the four variables in Table 1. Keep only these rows of statistics and delete the rest: Mean, Standard Deviation, Minimum, Maximum, and Count. Clean up the table: (1) delete the repeated row labels for stats so that there's just one set of row labels on the far left of your Table 2 for Mean, Standard Deviation, etc. Your table should have 6 rows and 5 columns (including row and column labels). (2) show a sensible number of decimal places.

[TYPE EQUATION HERE] Table 4. Regression of VISITS on FEE, INCOME, an [INSERT TABLE HERE] [TYPE YOUR ANSWER HERE] regression with respect to the estimated effect of Fee, including the direction of the effect and statistical significance. (1-2 sentences) 6. Write down the regression equation for a regression of Visits on a constant term, Fee, Income and Population. As before, use β_0 (for the constant) and β_1, β_2, etc., for the regression coefficients and ε for the error term.. 7.1. Run the regression specified in Part 6 above. As in Part 5, clean up the regression estimates part of the output (bottom four rows and first four numeric columns). Notice the difference in adjusted R squared between Table 4 and 3. 7.2. A. Discuss the coefficient estimates: (1) what happened to the estimate for the effect of Fee and its standard errror (and thus P-value)? (2) Which of the other explanatory variables are playing a significant role here? Connect this with the correlation statistics (Table 2). What does that mean? (1-2 sentences) B. Summarize the conclusions of this regression with respect to the estimated effect

Income ($) Population Tips: 20,600 36,879 Use the Data Analysis tool in Excel for generating (1) desc 33,400 64,520 In the pop-up window for selecting inputs to your chosen " 39,700 104,123 A. Select the option for "Labels in first row" or "Labels" (de 32,600 103,073 B. For the "Output Range" specify the cell in the upper-left 24,900 58,386 38,000 116,592 26,700 49,945 20,800 79,789 26,300 98,234 35,600 71,762 38,900 40,178 21,700 22,928 37,900 39,031 35,100 44,685 35,700 67,882 22,900 69,625 38,600 98,408 20,500 93,429 39,300 98,077 25,800 104,068 23,800 117,940 34,000 59,757 29,600 88,305 33,800 84,102 Tips: In the Data Analysis tool in Excel for descriptive statistics,

nd POP

criptive statistics; (2) a correlation matrix; and (3) regressions. "Analysis Tool" (e.g. regression, correlation, etc.): epending on the tool) and then when specifying the cells for the "Input Range" include both the label (e.g. F t corner of the blue-shaded output zone. This will instruct Excel to locate the output starting from that cell a select the "Summary statistics" option.

ences (rather than re-typing labels): click on the label box-->click in formula bar and type "="-->click on cell here needed.

Tip: "Input X Range" can include multiple columns.

variable name (as seen here in column C), e.g., "Fee_DV" t statistically significant are dropped before the model is re-estimated and then mple--keep all the variables in the model as estimated originally above.

Fee) and data for a given variable. This will result in the variable labels showing up in the output tables so you and extending down and to the right.

l with desired text

u don't have to figure out where labels go and add them manually.