1. What variables do you think would affect shipments of Treat?

• Trend
• Season (Month)
• Consumer pack promotion (this month and two prior months)
• Dealer allowance (this month and two previous months)

2. From the point of view of management, what model(s) do you think make(s) most sense for predicting monthly shipments of Treat cereal?

3. After testing those models (using Excel) that you think are most attractive for forecasting purposes in this situation, select the model that you think is best and use that to project shipments for Jan-Dec 1988. What do you need to know in order to use this model to prepare these forecasts?

4. What can you learn from an examination of the coefficients in your model as to the relative impact of consumer promotions versus dealer promotions?

• For this question, you may check this YouTube to see how you can perform a multi-variable regression model and answer this question. Using Multiple Regression in Excel for Predictive Analysis - YouTube

Exhibit 1 Case Shipments, Consumer Packs, Dealer Allowances, 1983-1987 Con- Con- Case summer Dealer Case summer Dealer Ship- Packs Allow- Ship- Packs Allow- Month ments cases)a ance Month ments cases ance Jan-83 #N/A 0 $396,776 Jan-86 655,748 544,807 $664,712 Feb-83 #N/A $152,296 Feb-86 270,483 43,708 $536,824 Mar-83 #N/A $157,640 Mar-86 365,058 5,740 $551,560 OO Apr-83 #N/A $246,064 Apr-86 313,135 9,614 $150,080 May-83 #N/A 15,012 $335,716 May-86 528,210 1,507 $580,800 Jun-83 #N/A 62,337 $326,312 Jun-86 379,856 13,620 $435,080 Jul-83 #N/A 4,022 $263,284 Jul-86 472,058 101, 179 $361, 144 Aug-83 #N/A 3,130 $488,676 Aug-86 254,516 80,309 $97,844 Sep-83 #N/A 422 $33,928 Sep-86 551,354 335,768 $30,372 Oct-83 #N/A 0 $224,028 Oct-86 335,826 91,710 $150,324 Nov-83 #N/A $304,004 Nov-86 320,408 9,856 $293,044 OO Dec-83 #N/A $325,872 Dec-86 276,901 107,172 $162,788 Jan-84 425,075 75,253 $457,732 Jan-87 455, 136 299,781 $32,532 Feb-84 315,305 15,036 $254,396 Feb-87 247,570 21,218 $23,468 Mar-84 367,286 134,440 $259,952 Mar-87 622,204 157 $4,503,456 Apr-84 429,432 119,740 $267,368 Apr-87 429,331 12,961 $500,904 May-84 347,874 135,590 $158,504 May-87 453, 156 333,529 $0 Jun-84 435,529 189,639 $430,012 Jun-87 320, 103 178, 105 $0 Jul-84 299,403 9,308 $388,516 Jul-87 451,779 315,564 $46, 104 Aug-84 296,505 41,099 $225,616 Aug-87 249,482 80,206 $92,252 Sep-84 426,701 9,391 $1,042,304 Sep-87 744,583 5,940 $4,869,952 Oct-84 329,722 942 $974,092 Oct-87 421, 186 36,819 $376,556 Nov-84 281,783 1,818 $301,892 Nov-87 397,367 234,562 $376,556 Dec-84 166,391 672 $76, 148 Dec-87 269,096 71,881 $552,536 Jan-85 629,402 548,704 $0 Feb-85 263,467 52,818 $315, 196 Mar-85 398,320 2,793 $703,624 Apr-85 376,569 27,749 $198,464 May-85 444,404 21,887 $478,880 Jun-85 386,986 1,110 $457,172 Jul-85 414,314 436 $709,480 Aug-85 253,493 1,407 $45,380 Sep-85 484,365 376,650 $28,080 Oct-85 305,989 122,906 $111,520 Nov-85 315,407 15, 138 $267,200 Dec-85 182,784 5,532 $354,304 a 1 case contains 24 packs.Harmon Foods, Inc. Exhibit 2 Seasonal Effects on Breakfast Cereals Shipments Seasonal Indices for Treat Breakfast Cereal Shipments Month Index January 113 February 98 March 102 April 107 May 119 June 104 July 107 August 81 September 113 October 97 November 95 December 65