Chapter 8
PROJECTIONS I - SIMPLE REGRESSION
1. APPLICATION TO SALES. USE OF THE COMPUTER
a. Potential market
b. Competition level
c. Positioning level
d. General economic situation
e. Available incomes
f. Buyers attitude
g. Substitute products
h. Prices
i. Publicity investments
2. PREPARATION
3. INDICATORS
Each organization must look for the right indicators, to project their sales trying to predict results using the mathematical model, complemented with the executive’s opinion. The indicator (independent value) for being a known value that is used to predict results based on the regression equation.
Example:Kind of Product |
Indicator |
1. School books |
amount of matriculated students |
2. University books |
a number of high-level students |
3. Cars |
familiar income |
4. Food |
population size and familiar income |
5. Gas, tires, batteries |
moving vehicles |
6. Baby products |
nasality index |
7. Petroleum equipment |
wells to drill. (short and long term plan) |
8. Steel |
industrial production |
Regression line: is the one that gives the best adjustment to the available historic facts; using the statistic inference, we project the future results. It's equivalent to thinking that if everything keeps until now, the result can be calculated with the use of a mathematical formula. If X represents the time (independent variable) and Y represents the sales (dependent variable), the equation is a model of simple regression, which has the form; as follows:
A: |
intercept |
B: |
slope |
Y: |
estimated sales |
X: |
years |
In the case of the multiple line regression, the dependent variable Y, change with others that interact together; the mathematical expression is:
Y = A + b_{1}x_{1} + b_{2}x_{2} + b_{3}x_{3} ... b_{n}x_{n} |
The correlation coefficient indicates the percentage of the total sales of the company, which has a line associated with the independent variables.
The determination coefficient indicates the adjustment level of the model, it means that as the closer the value of R2 is to 1, bigger is the amount of the variation that can be explained by the terms that appear in the model, and are calculated by the second potential of the correlation coefficient.
Observations are the number of periods that are taken as a base to make a projection.
4. BUDGETARY MODELS
X |
Y |
XY |
X^{2} |
Y^{2} |
1 |
110 |
110 |
1 |
12100 |
2 |
123 |
246 |
4 |
15129 |
3 |
141 |
423 |
9 |
19881 |
4 |
156 |
624 |
16 |
24336 |
5 |
164 |
820 |
25 |
26896 |
6 |
175 |
1050 |
36 |
30625 |
7 |
186 |
1302 |
49 |
34596 |
8 |
200 |
1600 |
64 |
40000 |
9 |
234 |
2106 |
81 |
54756 |
10 |
254 |
2540 |
100 |
64516 |
11 |
274 |
3014 |
121 |
75076 |
12 |
290 |
3480 |
144 |
84100 |
∑78 |
∑2307 |
∑17315 |
∑650 |
∑482011 |
a. Formula to calculate the slope (B)
b. Formula to calculate the intercept (A)
c. Formula to calculate the correlation coefficient
d. Formula to calculate the determination coefficient
With numbers it would be:
Determination coefficient (r2) = (0.9886)2 = 0.9774
With this the equation is:
EXCEL APPLICATION FOR THE CORRELATION ANALYSIS
Steps to follow:
a. Open excel and enter the original facts as you can see in the picture:
b. Select the icon "functions (fx)" statistics functions (upper circle)
c. Click "accept", watch and answer indicating the range of the matrix as seen in the next picture:
d. Click on finish and you will see the answer 0,98866917
e. Proceed in the same way to calculate the intercept and the slope.
f. With our example, the values are:
C. correlation |
0,98866917 |
Intercept |
86,8181818 |
Slope |
16,2202797 |
C. determination |
0,97746673 |
Equation of regression: Y = 86,82 + 16,22 * X
Note: as you can see, using Excel we obtain the same results as if we were using the traditional formulas, but in a faster and more reliable way. This happens if the facts you enter are the right ones.
g. Let’s see how good is the adjustment:
Matrix to Test the Model |
|||
X |
Y |
Y(estimated) |
(Ye - Y)/Y% |
1 |
110 |
103.04 |
-6.33 |
2 |
123 |
119.26 |
-3.04 |
3 |
141 |
135.48 |
-3.92 |
4 |
156 |
151.70 |
-2.76 |
5 |
164 |
167.92 |
2.39 |
6 |
175 |
184.14 |
5.22 |
7 |
186 |
200.36 |
7.72 |
8 |
200 |
216.58 |
8.29 |
9 |
234 |
232.80 |
-0.51 |
10 |
254 |
249.02 |
-1.96 |
11 |
274 |
265.24 |
-3.20 |
12 |
290 |
281.46 |
-2.9 |
h. Projections: Based on the same facts, calculate the estimated sales for the years 13 and 14.
Y13 = 86,82 + 16,22 * (13) = 297,68
Y14 = 86,82 + 16,22 * (14) = 313,9
i. Graphic illustration:
B. MODEL OF MODIFIED AVERAGES
Steps to follow:
a. Enter the facts
Years (X) |
Sales (Y) |
1 |
110 |
2 |
123 |
3 |
141 |
4 |
156 |
5 |
164 |
6 |
175 |
7 |
186 |
8 |
200 |
9 |
234 |
10 |
254 |
11 |
274 |
12 |
290 |
b. Establish the sales average by biennial, as indicated next:
Years |
Average |
1 - 2 |
116.50 |
2 - 3 |
132.00 |
3 - 4 |
148.50 |
4 - 5 |
160.00 |
5 - 6 |
169.50 |
6 - 7 |
180.50 |
7 - 8 |
193.00 |
8 - 9 |
217.00 |
9 - 10 |
244.00 |
10 - 11 |
264.00 |
11 - 12 |
282.00 |
c. Calculate the sum of the averages, and calculate the participation percentages of each one of them:
Years |
Average |
% Participation |
1 - 2 |
116.50 |
5.5 |
2 - 3 |
132.00 |
6.3 |
3 - 4 |
148.50 |
7.00 |
4 - 5 |
160.00 |
7.60 |
5 - 6 |
169.50 |
8.00 |
6 - 7 |
180.50 |
8.60 |
7 - 8 |
193.00 |
9.20 |
8 - 9 |
217.00 |
10.30 |
9 - 10 |
244.00 |
11.60 |
10 - 11 |
264.00 |
12.50 |
11 - 12 |
282.00 |
13.40 |
Sum |
2107.00 |
100.00 |
d. Calculate the percentage variation of each year dividing the year "n" over (n-1) and apply to the year to project.