Pharmaceuticals is a small to mid size pharmaceutical company. The accounting department began extending its historical records by recording the sales volume for each month over the past four years, starting in January.

The company engages in various kinds of direct to consumer advertising and, until recently, has never studied the effect its advertising dollars have on sales, although some data had also been collected. Their hope was that after several months the collected data could be examined to possibly reveal any relationships that would help in future advertising expenditures.

The accounting department besides extending its historical records by recording the sales volume for each month, they also collected advertising dollars for both magazine and TV spots. They also recorded both these values lagged one and two months. This was done because some people on the executive committee thought that sales might lag advertising expenditures rather than respond in the month the ads appeared. It was also believed that sales experienced a seasonal effect and management further wondered about any trend in the sales volume. Finally, it was believed that Alomega’s advertising dollars might have an effect on its major competitors’ advertising budgets the following month. For each following month it was decided that competitors’ advertising could be classified as: (1): little; (2): a moderate amount, or; (3): a great amount.

After a few months of data collection and analysis of past records, the accounting department completed an Excel file using the following variables:

Sales dollars

Magazine advertising dollars

TV advertising dollars

Code 1, 2, or 3 to indicate competitors’ advertising efforts the following month.

The data is in thetable below.

1.I build a 3-period moving average model.

2. build an exponential model, using α = .1.

3. Evaluate the two above models, i.e., which one is the best and explain why.

4. build a regression model with only the time series components—trend and seasonal components.

5., evaluate the above model, i.e., is it a good model? If so, why, or if not, why? Consider all the appropriate tests, α = 0.05. (explain your conclusions).

6. Using the model you developed in 1 (of part 2), i forecast the next 12 months (in the future).

7.Build a regression model with only the causal variables, paper advertising, tv advertising, and competitors’ advertising (complete).

8. evaluate the above model, i.e., is it a good model? If so, why, or if not, why? Consider all the appropriate tests, α = 0.05. (explain your conclusions).

9. Compare the models, moving average, exponential smoothing, classical decomposition, regression with time series and regression with causal variables, which one is the best and explain why.

Transcribed Image Text:

Sales 425075 315305 432101 357191 347874 435529 299403 296505 426701 329722 281783 166391 629404 263467 468612 313221 444404 386986 414314 253493 484365 305989 315407 182784 655748 270483 429480 260458 528210 379856 472058 254516 551354 335826 320408 276901 455136 247570 732005 357107 453156 320103 451779 249482 744583 421186 397367 269096 Paper 75253 15036 134440 119740 135590 189636 9308 41099 9391 942 1818 672 548704 52819 2793 27749 21887 1110 436 1407 376650 122906 15138 5532 544807 43704 5740 9614 1507 13620 101179 80309 335768 91710 9856 107172 299781 21218 157 12961 333529 178105 315564 80206 5940 36819 234562 71881 TV Compete Month January 114433 Weak 63599 Moderate February 64988 Moderate 66842 Weak 39626 Moderate 107503 Strong 97129 Strong 56404 Moderate August 260576 Strong 243523 Strong 75473 Strong 19037 Moderate 0 Moderate 78799 Moderate September October November December January February March 175906 Weak 49616 Weak April 119720 Moderate May 114293 Moderate June 177370 Strong 11345 Moderate 7020 Weak 27880 Moderate 66800 Strong 88576 Strong 166178 Moderate 134206 Moderate 137890 Weak 37520 Weak March April May June July 7593 Strong 37581 Strong 73261 Strong 40697 Moderate 8133 Strong 5867 Moderate 1125864 Moderate July August 145200 Weak 108770 Moderate 90286 Moderate July 24461 Moderate 1217488 Weak 94139 Weak 94139 Weak 138134 Weak September October November December January February March April May June August September October November December January February March April May June 125226 Weak 0 Weak 0 Moderate 11526 Moderate July 23063 Moderate August September October November December Sales 425075 315305 432101 357191 347874 435529 299403 296505 426701 329722 281783 166391 629404 263467 468612 313221 444404 386986 414314 253493 484365 305989 315407 182784 655748 270483 429480 260458 528210 379856 472058 254516 551354 335826 320408 276901 455136 247570 732005 357107 453156 320103 451779 249482 744583 421186 397367 269096 Paper 75253 15036 134440 119740 135590 189636 9308 41099 9391 942 1818 672 548704 52819 2793 27749 21887 1110 436 1407 376650 122906 15138 5532 544807 43704 5740 9614 1507 13620 101179 80309 335768 91710 9856 107172 299781 21218 157 12961 333529 178105 315564 80206 5940 36819 234562 71881 TV Compete Month January 114433 Weak 63599 Moderate February 64988 Moderate 66842 Weak 39626 Moderate 107503 Strong 97129 Strong 56404 Moderate August 260576 Strong 243523 Strong 75473 Strong 19037 Moderate 0 Moderate 78799 Moderate September October November December January February March 175906 Weak 49616 Weak April 119720 Moderate May 114293 Moderate June 177370 Strong 11345 Moderate 7020 Weak 27880 Moderate 66800 Strong 88576 Strong 166178 Moderate 134206 Moderate 137890 Weak 37520 Weak March April May June July 7593 Strong 37581 Strong 73261 Strong 40697 Moderate 8133 Strong 5867 Moderate 1125864 Moderate July August 145200 Weak 108770 Moderate 90286 Moderate July 24461 Moderate 1217488 Weak 94139 Weak 94139 Weak 138134 Weak September October November December January February March April May June August September October November December January February March April May June 125226 Weak 0 Weak 0 Moderate 11526 Moderate July 23063 Moderate August September October November December