For the data in the Excel file Ohio Prison Population do the following:

a. Develop spreadsheet models for forecasting both male and female populations using single moving average and single exponential smoothing.

b. Using MAD, MSE, and MAPE as guidance, find the best number of moving average periods and best smoothing constant for exponential smoothing.

 

A D E F G H Ohio Plison Population Data 2 Male Male Female Female Total Total 4 Year Intake Population Intake Population Intake Population 1972 4677 8846 236 275 4913 9121 6 1973 4635 8049 253 271 4888 8320 7 1974 5538 7449 8978 367 268 5905 7717 8 1975 7014 442 348 7456 9326 1976 6859 10985 493 436 7352 11421 10 1977 6317 12086 627 542 6944 12628 11 1978 5993 12269 558 577 6551 12846 12 1979 6849 12768 583 582 7432 13350 13 1980 7698 12762 631 598 8329 13360 14 1981 9046 12557 792 581 9838 13138 15 1982 9530 14071 919 725 10449 14796 16 1983 9293 16272 917 875 10210 17147 17 1984 8855 16795 780 971 9635 17766 18 1985 9132 17382 868 969 10000 18351 19 1986 9436 19416 1002 1123 10438 20539 20 1987 9871 20981 1071 1194 10942 22175 21 1988 23943 11170 14673 22659 1296 1284 12466 22 1989 24373 1833 1476 16506 25849 23 1990 15423 28346 1986 1954 17409 30300 24 1991 17450 29605 2196 1896 19646 31501 25 1992 18209 33189 2385 2257 20594 35446 26 1993 17460 35627 2374 2364 19834 37991 27 1994 16546 37715 2346 2538 19198 40253 28 39065 41595 1995 17395 2520 2544 19915 41609 29 1996 16753 2431 2743 19184 44338 30 1997 15589 43183 2011 2779 17600 45962 31 1998 16076 44997 2177 2811 18253 47808 32 1999 16122 45285 2203 2886 18325 48171 33 2000 17334 43813 2387 2806 19721 46619 34 35 A change in policy in 1994 no longer counts inmates AWL to court as prison population