Pernavik Dairy produces and sells a wide range of dairy products. Because a government regulatory board sets most of the dairy’s costs and prices, most of the competition between the dairy and its...

Pernavik Dairy produces and sells a wide range of dairy products. Because a government regulatory board sets most of the dairy’s costs and prices, most of the competition between the dairy and its competitors takes place through advertising. The controller of Pernavik has developed the sales and advertising levels for the past 52 weeks. These appear in the file P16_60.xlsx. Note that the advertising levels for the three weeks prior to week 1 are also listed. The controller wonders whether Pernavik is spending too much money on advertising. He argues that the company’s contribution-margin ratio is about 10%. That is, 10% of each sales dollar goes toward covering fixed costs. This means that each advertising dollar has to generate at least $10 of sales or the advertising is not cost-effective. Use regression to determine whether advertising dollars are generating this type of sales response. (Hint: The sales value in any week might be affected not only by advertising this week but also by advertising levels in the past one, two, or three weeks. These are called lagged values of advertising. Try regression models with lagged values of advertising included, and see whether you get better results.)