Get Adobe Flash player

Chapter 8

PROJECTIONS I - SIMPLE REGRESSION

 

by josavere

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

X2

Y2

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
[n + (n + 1)] / 2

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
[ n + (n + 1)] / 2

% 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.

 

Advertise Here