PRR 475 Extra Credit Exercise - Lansing Riverfront Trail Use EstimatesPURPOSE: This exercise will provide further practice in using formulas and Pivot tables in Excel to compute somesimple statistics. It also follows up on previous exercise to illustrate the procedure for expanding from a sample of observations to the population using either a simple random sample or stratified sample. The data were gathered by previous PRR 475 students using observations along the Lansing Riverfront Trail. We will use the 1996 observations. Using the 30 observations at Aurelius and Kalamazoo access points on Lansing Riverfront Trail (in Trail96.xls file on Count96 page), answer the following questions: 1. How much use did the eastern section of the trail receive in October of 1996? 2. Where and when was use heaviest? How was use distributed across the two access sites and by time of day and day of week?3. Who uses the trail? Report the percentage of use by type of use, user characteristics, etc.MATERIALS:a. HINTS page suggests how to do this exercise. Try it without consulting the hints first.b. Original handout on trail summarizes purposes and study approach. Note especially the sampling strata -only 30 of the planned observations were completed.c. Observation form(s) including observation instructions, coding of variables and how use was operationally defined and measured. The data file has the hourly sums that were entered on the final "Summary Counts" form plus the variables in box at top of the INSTRUCTION sheet.d. Hourly observation counts. Available in an Excel file available in the course AFS space -- msu/course/prr/475/Stynes/trail96.xls. Use the 1996 data by selecting the 1996 tab at bottom. After completing 1996, you are welcome to try other years or combining data across years. We didthis in 1995 and 1993 also.Format of your report: (Shouldn’t take more than 2 pages). 1. For each of the three questions /objectives, briefly explain how you generated the answers - call this section METHODS.2. For each question/objective summarize the answer in a brief text, table, figure or combination of these - put thesein a RESULTS section.HINTS ON THE ANALYSIS1. ESTIMATING OCTOBER USE. There are many ways to do this, some right some wrong, and many better or worse. Note that the sampling design suggests the recommended approach using the 6 strata. a. Use the sample to obtain an average use per hour (note the PEOPLE column has counts of observed trail users per hour. You can get an overall average (all observations) by using the AVERAGE function to averagethe PEOPLE column in Excel OR get averages by strata using the Pivot table command (use STRATA as row variable, LOCation as column and Average of PEOPLE in cells).b. Expand from the sample to population. Figure out the total number of daylight hours in October (12 hour days * 31 days) or the number that fall into each of the six strata. Multiply the number of hours by the average use per hour computed in step a. Sum across strata if you compute by strata to get total October use. 2. ESTIMATING USE DISTRIBUTION AND TYPES OF USERS. You could repeat the procedures in (1) using numbers from the other columns on the data sheet in place of the PEOPLE column. For example, using the WHITE column would estimate number of "white" users. This would be very tedious and time consuming. A quicker method is to assume the visitor characteristics and types of use do not vary much by strata and simply total the observations directly (essential assuming the 30 observations are a representative sample). On spreadsheet, sum the columns to get total counts of all variables in the sample. Then simply divide by appropriate totals to estimate percent white vs non-white, male vs female, by age, etc. Be careful to make percents sum to 100% (due to some missing data, not all fields will sum to same total as PEOPLE column). For example, in getting percents by age divide each age group total by the sum of totals across all age groups. Note that you can put formulas at bottom of one column and simply copy them across to quickly compute all column sums or averages. EXERCISE DUE: December 2Follow-up on statistical matters: 3. Comment on the accuracy of your results (confidence intervals? or discussion of errors).HINTS ON ERROR AND CONFIDENCE IN RESULTS. You should try to convey to the client some idea of accuracy/error in the estimate of use. Some ideas here are: a) commenting on representativeness of sample (did we get a typical mix of weather patterns and reasonable time distribution within the strata), b) noting the amount of variation in use counts (highs and lows) and hence sensitivity to which periods we sampled, or c) a formal estimate of confidence using standard errors. (C) can be done by strata (need at least two observations in a stratum to estimate variance from sample). Some strata will have higher variances than others, so estimates of means for those strata will have more error. If you want to take a crack at a confidence interval, I'd suggest trying it with sample as a whole first, ignoring strata. To do this, in Excel compute the standard deviation of entries in the PEOPLE column (STDDEVP function), then calculate standard error of mean as standard deviation/sqrt(n), n=30. Convert this to a percent of the mean by dividing the standard error by the PEOPLE mean. Now apply this percent error to your (expanded) estimate of October use from step 1. Remember two standard errors either side of mean is a 95% confidence interval, so take two "Pct errors" either side of mean. For example: If mean is 100 and standard error is 10, the percent error is 10% (10/100) . Two standard errors is 20% either side of mean. If expanded use estimate is 25,000, then a 95% CI is 20% either side of 25,000 or between 20,000 and 30,000. If this makes no sense to you at all, disregard and restrict your comments about error to a or b. A better, but messier approach is to compute standard errors of means for each stratum and sum confidence intervals across strata. - for this use same Pivot Table as in (1) above. First copy the Pivot table below using Paste Special - Values to copy the values in table. Then re-enter Pivot Table wizard and change middle to STDEVP of PEOPLE rather than AVERAGE - copy this next to averages below; finally change middle to COUNT of PEOPLE. Now divide standard deviation by square root of the count for each cell to compute