Chapter 8
1. APPLICATION TO SALES. USE OF THE COMPUTER
The sales budget constitutes the departure point for the planning of a company, and to elaborate a plan that generates value. Its preparation corresponds to the marketing and sales executive, but the financial division has to participate also openly in its elaboration, in order to have a solid base with the purpose of making a simulation analysis, until it gets to the point of defining a plan that generates value (josavere). The financial executive participates as an advisor, delivering elements of analysis to the management.The sales depend on a great number of variables. Among them we can list:
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
A lot of people participate on its elaboration, all of them in different levels of the organization under the orientation of the marketing executive who has to answer to the management for their final approval.a. The general management: is in charge of the long range decisions, such as defining the publicity budget; approval of the new products; defining the strategies; goal of the company according to the economic tendencies; analysis looking for export possibilities; check and give the final approval of the budget and execute the corrections that the periodical control indicates that should be implemented.
b. The marketing executives: prepare the market investigation, actual, and potential, check the behavior of the demand and analyze the plant capacity; look for projection indicators; make flexible calculus and relative variation of the sales, according to variables like the price, the investment publicity, etc. and study alternatives that allow the company to fulfill the goals the organization has set.
c. Salesmen estimation: they are good elements like specific knowledge about the clients and their individual objectives to complete the plans, based on mathematical models.
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 |
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. Its equivalent to think 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:
The correlation coefficient indicates the percentage of the total sales of the company, which has a line association with the independent variables.
The determination coefficient indicates the adjustment level of the model, it means that as 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
A. CORRELATION ANALYSIS AND SIMPLE LINE REGRESSION: it is necessary at least seven periods of sales (years), that way the result can be reliable for build a matrix, just as the next example indicates:The X, the independent variable (years) and Y, is the dependent variable (real sales in units)
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.