In: Statistics and Probability
The South Dakota Department of Game and Fish (SDDGF) wants to lengthen the pheasant-hunting season to bring in more tourist revenue. SDDGF’s thinking is that most pheasants are killed during the first 2 weeks of the season; therefore, a longer season will not deplete the bird population. Using sample data from all past hunting seasons, Rodney Ringneck, the SDDGF’s data analyst, regresses the number of birds surviving the season on the length in days in the season.
He finds: Y=547,000-214X; s.e.b = 415; R2 = .15; n = 35.
(a) Interpret the intercept and slope, test the slope for significance, and interpret the R-squared.
(b) Predict how many birds will survive if the season is 60 days long.
(c) Is SDDGF's thinking correct (that a longer season will not significantly deplete the bird population)?
How would I show regression in Excel using the data analysis tool?
A). Y=547,000-214X
Intercept - that means when x is Zero ( at day 0, beginning) number of birds are 547,000
Slope - If the season is 1 day long then number of birds would be killed are 214.
test the slope for significance - T value = -214/415 = 0.515663. By checking the P( >|t|) value you can take the decision
R square = Sum of squared Regression / Sum of square Total. Here value is 0.15 that means only 15% of total variance can be explained by the independent variable.
B). Number of birds birds will survive if the season is 60 days long = 547,000 - 214 *60 = 547,000 - 12,840 = 534,160
C). SDDGF's thinking is not correct, that means a longer season will significantly deplete the bird population because the slope value is 214 and negative. When X will take larger value then Y value will decrease based on the equation.
Regression in Excel using the data analysis tool
You have to select "Regression" in data analysis tool. Please mention "Input Y range" and "Input X range" in the suitable boxes. In the output option mention "Output Range" as well
Please let me know for any doubts. I would be happy to discuss more in details.