An ocean beach attracts visitors from 10 nearby cities and towns. There is no entry fee. The round-trip cost of beach trips from the different towns serves as a proxy for a beach "price." The towns' populations, average round-trip cost per person and average summer Wednesday and Saturday visit rates are listed in the dataset

1. Copy and Paste Special (as text) these data into an Excel worksheet. Multiply the visit rates by town populations to obtain total Wednesday visits from each town. Sum the visits from all towns.

2. Create an XY-scatterplot (points) of Wednesday trips per person (y-axis) versus trip cost per person (x-axis). Right-click the datapoints and add a Linear Trendline, checking the options to display the trendline Equation and R-square on the chart.

3. Use the trendline equation to estimate predicted Wednesday visit rates from each town based on the respective trip costs. Multiply these by the town populations to predict total Wednesday visits for each town. Sum the predicted visits from all towns. It should match the actual total pretty closely.

4. Now suppose the beach starts charging a $1 entry fee. Use the trendline equation to estimate predicted visit rates from each town based on the respective trip costs incremented by $1. (Since you can't have negative visits, convert any negative predicted rate to zero.) Multiply these estimated visit rates by the town populations to predict total visits for each town. Sum the predicted Wednesday visits from all towns under the $1 cost increment. (You can check this example to see Excel formulas that will get you to this point.)

5. Predict (non-negative) Wednesday visit rates for each town, and total beach visits from all towns, under a hypothetical entry fee of $2 per trip.
   Predict (non-negative) Wednesday visit rates for each town, and total beach visits from all towns, under hypothetical entry fees of $3 per trip up to $15 per trip in one-dollar increments. (A $15 cost increment should choke off all demand.)

6. Create an XY-plot (line) showing the total Wednesday visits you would expect at the site under entry fees ranging from $0 to $15. This is the midweek per-day travel-cost demand schedule for the beach.

7. Calculate the total area under this constructed demand schedule--the midweek per-day consumer surplus derived from the beach.

8. Perform the same analysis using the Saturday visit rate data.
   Use hypothetical entry fees ranging from $0 to $12 to construct the weekend per-day travel-cost demand schedule for the beach.
   Create an XY-plot (line) showing the total Saturday visits you would expect at the site under entry fees ranging from $0 to $12.
   Calculate the Saturday consumer surplus derived from the beach.

9. Copy the Saturday demand plot onto the Wednesday demand plot. Notice that the Wednesday demand choke price is higher than the Saturday demand choke price because of congestion on Saturdays. Once the crowd exceeds 20,000 people, all beach-goers start experiencing rising congestion externalities. Without congestion, Saturday demand would be proportionately greater than midweek demand because proportionately more people are free on weekends.

   Create an XY-scatterplot of towns' Saturday visit rates (y-axis) versus Wednesday visit rates (x-axis). Add a Trendline with the Equation displayed on the chart. Assume the slope coefficient represents the proportional increase in potential weekend vs. weekday visitors, while the intercept coefficient accounts for the congestion effect. You can infer what the Saturday demand would be without congestion: simply scale up the Wednesday demand by the slope coefficient. This approximates the true Saturday site demand schedule up to the congestion threshold of 20,000 beach-goers. Once the crowd exceeds 20,000, congestion externalities shift the entire demand schedule downward.

   Suppose the beach sets a Saturday entry fee to reduce congestion and give beach-goers a better experience. What entry fee would reduce the Saturday crowd to 20,000? What total revenue would this fee generate? Calculate the consumer surplus that beach-goers would realize under this entry fee.