2. Use Holt's method to create models for each expenditure category. Use Solver to estimate the values of a and that minimize the MSE. What are the optimal values of a and and the MSE for each model? What is the forecast for next year for each expenditure category? Year Total Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Eating & drinking places EATDRINK 75,883 83,358 90,390 98,710 105,836 111,760 121,699 146,194 160,855 171,157 183,484 188,228 183,014 195,835 205,768 214,274 221,735 235,597 248,716 260,495 275,695 290,655 Hotels & motels HOTEL 5,906 6,639 6,888 7,660 8,409 9,168 9,665 11,117 11,905 12,179 12,508 12,460 13,204 13,362 13,880 14,195 14,504 15,469 15,800 16,623 17,440 17,899 Retail stores, direct selling RETAIL 8,158 8,830 9,256 9,827 10,315 10,499 11,116 12,063 13,211 14,440 16,053 16,750 13,588 13,777 14,210 14,333 14,475 14,407 15,198 16,397 16,591 16,881 Recreational places RECREATION 3,040 2,979 2,887 3,271 3,489 3,737 4,059 4,331 5,144 6,151 7,316 8,079 8,602 9,275 9,791 10,574 11,354 8,290 9,750 10,400 11,177 11,809 Schools & colleges SCHOOL 11,115 11,357 11,692 12,338 12,950 13,534 14,401 14,300 14,929 15,728 16,767 17,959 18,983 19,844 21,086 22,093 22,993 24,071 25,141 26,256 27,016 28,012 All other OTHER 16,194 17,751 18,663 19,077 20,047 20,133 20,755 21,122 22,887 24,581 26,198 27,108 27,946 28,031 28,208 28,597 28,981 30,926 31,926 33,560 34,508 35,004 TOTAL 120,296 130,914 139,776 150,883 161,046 168,831 181,695 209,127 228,930 244,236 262,326 270,584 265,338 280,124 292,943 304,066 314,043 328,760 346,530 363,730 382,427 400,259 2. Use Holt's method to create models for each expenditure category. Use Solver to estimate the values of a and that minimize the MSE. What are the optimal values of a and and the MSE for each model? What is the forecast for next year for each expenditure category? Year Total Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Eating & drinking places EATDRINK 75,883 83,358 90,390 98,710 105,836 111,760 121,699 146,194 160,855 171,157 183,484 188,228 183,014 195,835 205,768 214,274 221,735 235,597 248,716 260,495 275,695 290,655 Hotels & motels HOTEL 5,906 6,639 6,888 7,660 8,409 9,168 9,665 11,117 11,905 12,179 12,508 12,460 13,204 13,362 13,880 14,195 14,504 15,469 15,800 16,623 17,440 17,899 Retail stores, direct selling RETAIL 8,158 8,830 9,256 9,827 10,315 10,499 11,116 12,063 13,211 14,440 16,053 16,750 13,588 13,777 14,210 14,333 14,475 14,407 15,198 16,397 16,591 16,881 Recreational places RECREATION 3,040 2,979 2,887 3,271 3,489 3,737 4,059 4,331 5,144 6,151 7,316 8,079 8,602 9,275 9,791 10,574 11,354 8,290 9,750 10,400 11,177 11,809 Schools & colleges SCHOOL 11,115 11,357 11,692 12,338 12,950 13,534 14,401 14,300 14,929 15,728 16,767 17,959 18,983 19,844 21,086 22,093 22,993 24,071 25,141 26,256 27,016 28,012 All other OTHER 16,194 17,751 18,663 19,077 20,047 20,133 20,755 21,122 22,887 24,581 26,198 27,108 27,946 28,031 28,208 28,597 28,981 30,926 31,926 33,560 34,508 35,004 TOTAL 120,296 130,914 139,776 150,883 161,046 168,831 181,695 209,127 228,930 244,236 262,326 270,584 265,338 280,124 292,943 304,066 314,043 328,760 346,530 363,730 382,427 400,259