Problem: The children\'s museum found that annual visits (in 1,000) are associat
ID: 361741 • Letter: P
Question
Problem: The children's museum found that annual visits (in 1,000) are associated with kindergarten enrollment (in 1,000) one year prior in the community. They gathered data of 8 years as below. kindergarten enrollment Year visits 50 48 2 4 62 58 72 70 Please use appropriate functions in spreadsheet to address the following questions. 1. How would you describe the relationship between the annual visits of the children's museum and kindergarten enrollment? Does it make sense to use the linear regression method to forecast the annual visits of the children's museum given above data? Justify your answer using appropriate plot or measurement. (reorganize above dataset if necessary in spreadsheet)Explanation / Answer
It is mentioned that annual visits of the children's mueseum is related to the kindergarten enrollment and required to use the linear regression method to forecast the annual visits of the children's museum with the given data.
Visits (in thousands) are represented by Y and enrollments by X
Therefore linear regression is Y = a + bX where a, and b are constants
Please note that given data is assumed to be as per the relation mentioned in the question, otherwise one may be considering to shift the data for y or X by one year assuming previous year enrollment having impact on current year's visits. Anyway procedure remains the same, may be change in values of a, and b constants.
Correlation coefficient is just above .5 not that much strong as in case of .9 or .8
Irrespective of value of enrollments, number of visits are assumed to be approx 49 thousands.
Ans. 2
In excel the function is LINEST(known-values Y, known-values X, constant, stats)
Using above function we get Constant (a) = 49.16949 and constant (b) = 2.254237 similiar to solution1.
With shift in the values, say year1 enrollment against year2 visits and so on we have seven years data (1,2,3,4,5,6,7 for X) and corresponding (2,3,4,5,6,7,8 for Y) LINEST function provides a=41.46808, b=4.98936
appears to be better related than earlier.
Answer 3
One may use the equation Y = 41.468 + 4.99X for any year where X is number of enrollments in the previous year.
Visits(Y) enrollment(X) x^2 x * y Forecast Error |Error| Error^2 |Pct Error| year1 50 3 9 150 55.9322 -5.9322 5.9322 35.191 11.86% year2 55 2 4 110 53.678 1.322 1.322 1.7478 2.40% year3 48 4 16 192 58.1864 -10.1864 10.1864 103.7635 21.22% year4 55 4 16 220 58.1864 -3.1864 3.1864 10.1534 5.79% year5 62 2 4 124 53.678 8.322 8.322 69.2563 13.42% year6 58 6 36 348 62.6949 -4.6949 4.6949 22.0422 8.09% year7 72 5 25 360 60.4407 11.5593 11.5593 133.6179 16.05% year8 70 8 64 560 67.2034 2.7966 2.7966 7.8211 4.00% TOTALS 470 34 174 2,064 0 48 383.5933 82.85% AVERAGE 58.75 4.25 21.75 258 0 6 47.9492 10.36% (Bias) (MAD) (MSE) (MAPE) Intercept 49.1695 Std err 7.9958 Slope 2.2542Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.