8. Projections I - Simple regression

# Chapter 8PROJECTIONS 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. 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
g. Substitute products
h. Prices
i. Publicity investments

2. PREPARATION

A lot of people participate in 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; the 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 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 + b1x1 + b2x2 + b3x3 ... bnxn

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

A. CORRELATION ANALYSIS AND SIMPLE LINE REGRESSION: it is necessary at least seven periods of sales (years), that way the result can be reliable to 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.