Laboratory Work No.

5
Constructing Multivariate Linear Regression Using the MS Excel Data Analysis Package.
Residual Analysis.
Option 1

1. Initial Data
Labour productivity at enterprises in one of the industrial sectors is studied.
y — labour productivity at the enterprise, tg/person.
x₁ — average monthly salary, tg.
x₂ — share of highly skilled workers, %.
x₃ — investments in fixed assets in the current quarter, thousand tg.
x₄ — investments in fixed assets in the previous quarter, thousand tg.

No. y x₁ x₂ x₃ x₄
1 305 210 10 90 75
2 345 250 15 95 90
3 290 195 8 85 70
4 365 270 18 100 95
5 380 285 20 110 100
6 320 220 12 92 80
7 355 260 17 98 92
8 310 215 11 88 78
9 395 295 22 115 105
10 335 240 14 94 85
11 370 275 19 105 98
12 300 205 9 87 73

2. Step 1: Factor Selection — Pairwise Correlation Matrix

To investigate the feasibility of including factors in the model, we build a pairwise correlation matrix using
the Correlation tool of the MS Excel Data Analysis package (Data tab).

y x₁ x₂ x₃ x₄

y 1,000 0.998 0.999 0.968 0.998

x₁ 1,000 0.998 0.961 0.998
x₂ 1,000 0.966 0.998
x₃ 1,000 0.961
x₄ 1,000
Analysis of the correlation matrix:
1) Correlation of dependent variable y with factors: r(y,x₁) = 0.998, r(y,x₂) = 0.999, r(y,x₃) = 0.968,
r(y,x₄) = 0.998. All factors show a high correlation with the dependent variable (> 0.7).
2) Multicollinearity (between independent variables): r(x₁,x₃) = 0.961 — explicit multicollinearity (> 0.8).
Factor x₃ is closely correlated with x₁, therefore x₃ must be excluded from the model.
3) Similarly: r(x₃,x₄) = 0.961 — high correlation between x₃ and x₄. Factor x₄ (previous-quarter
investments) is also excluded as a duplicate.
Conclusion: Factors x₁ (average monthly salary) and x₂ (share of highly skilled workers) are included in
the model.

3. Step 2: Building the Multiple Linear Regression Equation

3.1. Equation Parameters in Natural Form
Using the Regression tool of the MS Excel Data Analysis package for factors x₁ and x₂, we obtain the
multiple linear regression equation:
ŷ = 174.58 + 0.4132·x₁ + 4.3907·x₂
Interpretation of coefficients:
- with an increase in the average monthly salary by 1 tg, labour productivity increases on average by
0.4132 tg/person, all other factors being equal;
- with an increase in the share of highly skilled workers by 1%, labour productivity increases by 4.3907
tg/person;
- at zero factor values, labour productivity will be 174.58 tg/person.

3.2. Equation Parameters in Standardised Form

Standardised regression coefficients (β-coefficients) are calculated using the formula:
βᵢ = bᵢ · σ(xᵢ)/σ(y)
Parameter Value σ(xᵢ)/σ(y) β
b₁ (x₁) 0.4132 0.9875 0.4081
b₂ (x₂) 4.3907 0.1348 0.5918

ŷ(ст) = 0.408·t(x₁) + 0.592·t(x₂)

Based on the standardised coefficients: β₁ = 0.408 > β₂ = 0.592, therefore factor x₁ (salary) has a greater
influence on labour productivity than factor x₂.

3.3. Elasticity Coefficients

Elasticity coefficients are calculated using the formula: Eᵢ = bᵢ · x̄ ᵢ / ȳ
Factor bᵢ x̄ ᵢ ȳ Eᵢ
x₁ 0.4132 243.33 339.17 0.2965
x₂ 4.3907 14.58 339.17 0.1888
E₁ = 0.296: when x₁ changes by 1% of its level, the result y changes on average by 29.65%, all other
factors remaining unchanged.
E₂ = 0.189: when x₂ changes by 1% of its level, the result y changes on average by 18.88%.
Conclusion: factor x₁ has a greater influence on y by both measures (β and E).

4. Step 3: Model Quality Assessment

Indicator Value Interpretation
Multiple corr. coeff. R 0.9993 High relationship (> 0.7)
Coeff. of determination R² 0.9986 99.9% of variance explained
Adjusted coeff. R²adj 0.9982 High model quality
Fisher F-criterion 3136.93 Ffact >> Ftable(0.05) ≈ 4.26
Significance F (p-value) < 0.0001 Equation is statistically
significant

1) Multiple correlation coefficient R = 0.9993 indicates a high degree of closeness of the linear
relationship between labour productivity and factors x₁, x₂.
2) Coefficient of determination R² = 0.9986: the model explains 99.9% of the variation in the dependent
variable y.
3) Adjusted coefficient R²adj = 0.9982 confirms the high quality of the model.
4) F-criterion: Ffact = 3136.93. At significance level α = 0.05 and df₁ = 2, df₂ = 9, the critical value Ftable
≈ 4.26. Since Ffact >> Ftable, hypothesis H₀ is rejected — the equation is statistically significant.

5. Step 4: Residual Analysis

The examination of residuals eᵢ = yᵢ − ŷᵢ is carried out to verify the fulfilment of the five OLS assumptions.

No. y x₁ x₂ ŷ eᵢ eᵢ² (eᵢ−eᵢ₋₁)²

1 305 210 10 305.27 -0.2678 0.0717 —

2 345 250 15 343.75 1.2489 1.5597 2.3003
3 290 195 8 290.29 -0.2877 0.0828 2.3611
4 365 270 18 365.19 -0.1881 0.0354 0.0099
5 380 285 20 380.17 -0.1682 0.0283 0.0004
6 320 220 12 318.18 1.8184 3.3065 3.9465
7 355 260 17 356.66 -1.6649 2.7720 12.1336
8 310 215 11 311.72 -1.7247 2.9746 0.0036
9 395 295 22 393.08 1.9180 3.6786 13.2690
10 335 240 14 335.23 -0.2279 0.0520 4.6049
11 370 275 19 371.65 -1.6450 2.7061 2.0081
 12 300 205 9 298.81 1.1891 1.4141 8.0325
Σ -0.0000 18.6818 48.6700

5.1. Assumption 1: Random Nature of Residuals

The scatter plot of residuals eᵢ against theoretical values ŷ shows that the points are randomly distributed in
a horizontal band with no discernible pattern. Assumption is FULFILLED.

5.2. Assumption 2: Zero Mean of Residuals

Sum of residuals: Σeᵢ = -0.000000 ≈ 0. Residual plots against factors x₁ and x₂ show a random scatter
pattern. Assumption is FULFILLED.

5.3. Assumption 3: Homoscedasticity

On the residuals-vs-ŷ plot, the variance of residuals remains approximately constant across all values of ŷ
— no heteroscedasticity is observed. Assumption is FULFILLED.

5.4. Assumption 4: No Autocorrelation of Residuals (Durbin-Watson Test)

Durbin-Watson statistic:
DW = Σ(eᵢ − eᵢ₋₁)² / Σeᵢ² = 2.6052
For n = 12, m = 2 and significance level α = 0.05, critical values are: d_L = 0.92; d_U = 1.54.
Autocorr. (+) Uncertain zone No autocorr. Uncertain zone Autocorr. (−)
[0; 0.92) [0.92; 1.54) [1.54; 2.46] (2.46; 3.08] (3.08; 4]

DW = 2.6052 falls within the zone of no autocorrelation [1.54; 2.46]. Hypothesis H₀ about the absence of
autocorrelation is not rejected. Assumption is FULFILLED.

5.5. Assumption 5: Normal Distribution of Residuals

On the Normal Probability Plot produced by the Regression tool, the data points lie along a straight line
from the lower-left to upper-right corner, indicating that the distribution of residuals is approximately
normal. Assumption is FULFILLED.

6. Conclusions
Indicator Result
Regression equation ŷ = 174.58 + 0.4132x₁ + 4.3907x₂
Standardised equation ŷ(std) = 0.408t(x₁) + 0.592t(x₂)
R (multiple correlation) 0.9993
R² (determination) 0.9986
R²adj (adjusted) 0.9982
F-criterion 3136.93 (significant)
DW statistic 2.6052 (no autocorrelation)
All 5 OLS assumptions Fulfilled ✓
The constructed multiple linear regression equation is statistically significant and of high quality. The
model explains over 99% of the variation in labour productivity. Factor x₁ (average monthly salary) has
the greatest influence on the dependent variable according to both the standardised coefficients and the
elasticity coefficients. All five OLS assumptions are fulfilled, confirming the correctness of applying the
least squares method.