a. Prepare line graphs of each of the six expenditure categories. Indicate whether each category appears to be stationary or nonstationary.

b. Use Holt’s method to create models for each expenditure category. Use Solver to estimate the values of α and β that minimize the MSE. What are the optimal values of α and β and the MSE for each model? What is the forecast for next year for each expenditure category?

c. Estimate linear regression models for each expenditure category. What is the estimated regression equation and MSE for each model? What is the forecast for next year for each expenditure category?

d. Fysco’s Vice President of Marketing has a new idea for forecasting market demand. For each expenditure category, she wants you to estimate the growth rate represented by g in the following equation: Ŷt+1 = Yt(1 + g). That is, the estimated value for time period t + 1 is equal to the actual value in the previous time period (t) multiplied by one plus the growth rate g. Use Solver to identify the optimal (minimum MSE) growth rate for each expenditure category. What is the growth rate for each category? What is the forecast for next year for each expenditure category?

e. Which of the three forecasting techniques considered here would you recommend that Fysco use for each expenditure category?

Fysco Foods, Inc. is one of the largest suppliers of institutional and commercial food products in the United States. Fortunately for Fysco, the demand for “food away from home” has been growing steadily over the past 22 years as shown in the following table (and the file FyscoFoods.xls on your data disk). This table breaks the total expenditures on food away from home (shown in the final column) into six component parts (e.g., eating & drinking places, hotels & motels, etc).

As part of its strategic planning process, each year Fsyco generates forecasts of the total market demand in each of the six food away from home expenditure categories. This helps the company allocate its marketing resources among the various customers represented in eachcategory.

Transcribed Image Text:

Total Food Away from Home Expenditures (in millions) Retail Drinking Hotels &Direct Recreational Schools & Year Places Motels Selling P3 Colleges4 All Other Total 1 75,883 5,9068,158 3,040 16,194 120,296 11,357 17,751 130,914 11,692 18,663 139,776 12,338 19,077 150,883 12,950 20,047 161,046 20,133 168,831 20,755 181,695 14,300 21,122 209,127 14,929 22,887 228,930 15,728 24,581 244,236 16,767 26,198 262,326 27,108 270,584 18,983 27,946 265,338 280,124 28,208 292,943 22,093 28,597 304,066 28,981 314,043 24,071 30,926 328,760 25,141 31,926 346,530 26,25633,560 363,730 27016 34,508 382427 28,012 35,004 400,259 283,358 6,639 8,830 3 90,390 6,888 9,256 4 98,710 7,660 9,827 5 105,836 8,409 10,315 6 111,760 9,168 10,499 7 121,699 9,665 11,116 4,059 14,401 8 146,194 11,117 12,063 9 160,855 11,905 13,211 10 171,157 12,179 14,440 11 183,484 12,508 16,053 12 188,228 12,460 16,750 13 183,014 13,204 13,588 14 195,835 13,362 13,777 15 205,768 13,880 14,210 16 214,274 14,195 14,333 10,574 17 221,735 14,504 14,475 11,354 18 235,597 15,469 14,407 19 248,716 15,800 15,198 20260,495 16,623 16,39710,400 21 275,695 17,440 16,591 22 290,655 7,899 16,881 6,151 9 17,959 9,275 9,791 19,844 28,031 21,086 22,993 11,809 1 Includes tips. 2Includes vending machine operators but not vending machines operated by organization. Motion picture theaters, bowling alleys, pool parlors, sports arenas, camps, amusement parks, golf and country clubs. Includes school food subsidies. Military exchanges and clubs; railroad dining cars; airlines; food service in manufacturin plants, institutions, hospitals, boarding houses, fraternities and sororities, and civic and social organizations; and food supplied to military forces. Computed from unrounded data