1

Table of Contents
Executive Summary ................................................................................................... 3 Challenge
................................................................................................................... 3 Data Analysis
............................................................................................................. 3
Variables identification .......................................................................................................................... 3
Scatter Plots .......................................................................................................................................... 4
Correlation ............................................................................................................................................. 4
Regression ............................................................................................................................................ 4
Answers to Questions ................................................................................................ 6

Appendices................................................................................................................. 7
Appendix 1: Scatter Plots ...................................................................................................................... 7
Appendix 2: Correlation ......................................................................................................................... 8

Executive Summary
Sally Franklin, who has recently joined AFP (amalgamated Food Products) as a meat loaf brand manager,
is asked to prepare a sales forecast and a budget for promotion and advertising for the next year. She
collected historical data over the past 24 quarters, which illustrates high sales variability as well as
expenditures variations, as a consequence of many factors: general economic index, seasonality,
advertising and promotion budget and planning

Challenge
She has to define as accurately as possible what is the right expenditures mix and timing that would lead
the company to a more clear marketing policy and avoid, by the way, all the confused ideas existing
among the companys stakeholders.

Data Analysis
Variables identification
In our approach, we start by identifying the variables to be used to build the best model. In addition to
what was provided initially within the case, we introduce a dummy variable to take into account the
Delayed effect of both promotion and advertising expenditures on sales. The table 1 exhibits all chosen
variables that we will use during our data analysis. Variable name Sales Description quarterly sales of
Quality Kitchens meat loaf mix (thousands of dollars) Promotion; funds spent on promotion activities in
the quarter (thousands of dollars) Advertising; funds spent on advertising during the quarter (thousands
of dollars) Delayed prom Delayed adv Delayed promotion, Delayed advertising Comment Dependant
Variable

Prom Adv

Direct effect of promotion investment on sales. Direct effect of advertising investment on sales.

As described within the case advertising and promotion could have a delayed effect on sales. We
introduce the variables delayed promotion and delayed advertising that represent the effect on sales

one quarter later Counter cyclical aspect: Sales go up in bad timesThis looks paradoxical, but needs to
be kept into account: it is the Economist point of view! The warm seasons are: Q3 and Q2 The cold
seasons are: Q1 and Q4

Index

Economic index of general economic conditions in Quality Kitchens market area Seasonality

Q1, Q2, Q3, Q4

Table 1

Scatter Plots
We take a look at scatter plots in order to identify any obvious non-linearity. We see that there is a linear
relationship between Sales & Promotion (Appendix 1-Figure 1). A low linearity characterizes the
relationship between Sales & Index (Appendix 1-Figure 2). This will suggest that a linear regression may
well be appropriate. In the other hand, we notice that there is a non-linear distribution between Sales &
Advertising (Appendix 1-Figure 3) which would lead us to build our regression model using the squared
value of advertising as well as delayed advertising..

Correlation
Before going forward and do additional analysis, we might consider looking at the multi-colinearity
issues if any. In fact, the multi-collinearity problem does not result in biased coefficient estimates, but
does increase the standard error of the estimates and thus reduces their reliability. According to
(Appendix 2- Correlation), we notice a low correlation between explanatory variables: So there is no
multi-collinearity issue. We keep all the presented independent variables and we carry out the
regression so as to be able to go further in the analysis. Regression is very likely to be reliable after this
correlation analysis.

Regression
We might now consider doing some regression analysis. In fact, the main objective of regression analysis
is to explain variability of the dependent variable by means of one or more of independent or control
variables. Regression 1 SUMMARY OUTPUT Regression Statistics Multiple R 0.95 R Square 0.90 0.78
Adjusted R Square Standard Error 52.12 Observations 23 Standard Error 239.216 0.806 0.785 0.727 0.749
2.329 34.354 0.000 35.727 33.706 Upper 95% 1260.099 8.238 -1.726 4.196 4.382 0.073 155.288 0.000
85.222 133.538

Intercept prom DelayedProm adv DelayedAdv index q1 q2 q3 q4

Coefficients 747.031 6.509 -3.410 2.637 2.775 -4.922 81.605 0.000 8.595 61.245

t Stat 3.123 8.074 -4.343 3.628 3.704 -2.113 2.375 65535.000 0.241 1.817

P-value 0.007 0.000 0.001 0.003 0.002 0.053 0.032 #NUM! 0.813 0.091

Lower 95% 233.964 4.780 -5.094 1.078 1.168 -9.918 7.922 0.000 -68.033 -11.048

5 We can notice that we got a quite high Adjusted R square which indicate that our model is good but
could be enhanced. So we decide to keep the seasonal effects but we are going to take out some
variables and try other regressions. Regression 2:

Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations

Intercept prom DelayedProm adv DelayedAdv index q1 q2 q3 Coefficients Standard Error 736.00 243.24
6.47 0.83 -3.44 0.81 2.56 0.75 2.61 0.76 -4.18 2.33 8.57 32.56 -59.36 34.63 -49.93 34.05

0.94 0.89 0.83 53.60 24

t Stat 3.03 7.81 -4.26 3.44 3.43 -1.79 0.26 -1.71 -1.47 P-value Lower 95% Upper 95% ower 95.0% Upper
95.0% 0.01 217.54 1254.46 217.54 1254.46 0.00 4.70 8.23 4.70 8.23 0.00 -5.16 -1.72 -5.16 -1.72 0.00
0.97 4.15 0.97 4.15 0.00 0.99 4.23 0.99 4.23 0.09 -9.15 0.78 -9.15 0.78 0.80 -60.82 77.97 -60.82 77.97
0.11 -133.18 14.47 -133.18 14.47 0.16 -122.51 22.64 -122.51 22.64

The Figure above (Regression 2) drives us to conclude that the variable Q1 should be deleted in the next
regression because of the low t-stat value (5%). Regression 3: As explained previously, the sales Vs
advertising scatter plot (Figure 3- Appendix 1) showed nonlinearity. This leads us to introduce the
squared values of both advertising and delayed advertising variables.
SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error
Observations

Adj R Sqr = 89% !

0.96 0.93 0.89 44.52 23

Sales = 964.17+6.92*Prom -3.19 *Delayed Prom +0.05*Adv^2+ 0.05* DelayedAdv^2 -6.02*index 61.39*q2- 58.04*q3

Intercept prom DelayedProm adv^2 DelayedAdv^2 index q2 q3

Coefficients 964.1698 6.9179 -3.1918 0.0461 0.0530 -6.0211 -61.3932 -58.0370

Standard Error 207.9185 0.6725 0.6548 0.0111 0.0112 2.0106 24.9686 24.4831

t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 4.6372 0.0003 521.0021 1407.3375
521.0021 1407.3375 10.2871 0.0000 5.4845 8.3513 5.4845 8.3513 -4.8746 0.0002 -4.5874 -1.7962 4.5874 -1.7962 4.1629 0.0008 0.0225 0.0697 0.0225 0.0697 4.7087 0.0003 0.0290 0.0769 0.0290 0.0769
-2.9946 0.0091 -10.3067 -1.7356 -10.3067 -1.7356 -2.4588 0.0266 -114.6127 -8.1738 -114.6127 -8.1738 2.3705 0.0316 -110.2214 -5.8526 -110.2214 -5.8526

In the Figure above (Regression 3), we obtain significant t-stat and high adjusted R square (89%). It is our
best model. Our best model equation is the following:

Sales = 964.17+6.92*Prom -3.19 *Delayed Prom +0.05*Adv^2+ 0.05* DelayedAdv^2 -6.02*index 61.39*q2- 58.04*q3 + error

Answers to Questions
Q1-a Taking on consideration the fact that promotion and advertising might have some delayed effects
on sales, we should calculate the impact on both the actual and next quarter.
Based on our best model equation and the slopes values of promotion and advertising, we can conclude
that it will be more interesting to invest in promotion rather than in advertising.

Sales = 964.17+6.92*Prom -3.19 *Delayed Prom +0.05*Adv^2+ 0.05* DelayedAdv^2 -6.02*index 61.39*q2- 58.04*q3
P.S: please note that, In addition to these calculations, computing the same values for higher budgets1
let us conclude that we should choose advertising rather than promotion. In fact, the advertising effects
become much more important. (See below examples for 50 and 100 thousands $).

Q1-b The effect of investing 1000 dollars in Promotion will be: Sales (1000 $ investment) =
964.17+6.92*1 -3.19 *1 = 967.9 $
The effect of investing 1000 dollars in Advertising will be:

Sales (1000 $ Advertising) = 964.17+ 0.05*1+ 0.05*1 = 964.27 $ Q2/ By examining the equation of our
best model, we can notice a negative value for the index' slope.
This means that the sales trend is opposite to the markets one.

Sales = 964.17+6.92*Prom -3.19 *Delayed Prom +0.05*Adv^2+ 0.05* DelayedAdv^2 -6.02*index 61.39*q2- 58.04*q3 + error
So, we do agree with the economic analyst

Q3/ scrutinizing the results of our best model equation, we notice that the slopes of Q2 and Q3 are
significant which helps us to conclude that there are significant seasonal effects associated with meat
loaf mix sales. Since the slope is negative for q2 and q3 (the hot season), we conclude that the sales
decrease during the hot season and increase during the cold one.
1

Growing budgets scenario:

Added Delayed effect - 3.19 0.05 Added Delayed effect -159.50 125 Added Delayed effect - 319 500 Total
added effect 3.73 0.1 Total added effect 186.50 250 Total added effect 373 1000

For 01 thousand $ Added immediate effect Promotion 6.92 Advertising 0.05 But let us take a look for
higher investing values: For 50 thousands $ Added immediate effect Promotion 346.00 Advertising 125
For 100 thousands $ Promotion Advertising Added immediate effect 692 500

Appendices
Appendix 1: Scatter Plots

sales & promotion

800 700 600 500 400 300 200 100 0 0 10 20 30 40 50 60

Figure 1

sales & index

800 700 600 500 400 300 200 100 0 95 100 105 110 115

Figure 2

Figure 3

Appendix 2: Correlation

sales prom DelayedProm adv DelayedAdv index q1 q2 q3 q4

sales 1,000 0,644 -0,086 0,428 0,569 -0,004 0,083 0,090 0,028 -0,200

prom 1,000 0,457 0,072 0,270 0,228 -0,135 0,132 0,181 -0,177

DelayedProm

adv

DelayedAdv

index

q1

q2

q3

q4

1,000 -0,024 0,072 0,255 -0,177 -0,135 0,132 0,181

1,000 0,251 0,155 -0,139 0,349 -0,172 -0,038

1,000 0,132 -0,038 -0,139 0,349 -0,172

1,000 -0,158 -0,024 0,091 0,091

1,000 -0,333 -0,333 -0,333

1,000 -0,333 -0,333

1,000 -0,333

1,000