Project II:

Regression Analyses and Forecasting

Christine Joy Ignacio (2343170)

Ahmed, Giuliana Andreina (2327392)

Jorge, Dragoneti Lucas (2325776)

Ramirez, Cristian Camilo (2343075)

University Canada West - Vancouver, British Columbia, Canada

MBAF502-15

Quantitative Reasoning & Analysis

Professor: Abeba N. Turi

June 16, 2024

 Table of Contents

Title Page

Table of Contents

Abstract

I. Introduction

1.1. Introduction………………………………………………………………….1

II. Methodology

2.1 Data Collection…….……………………………………………………………2

III. Data Analysis

3.1 Time Series Data and Forecasting…………………………………………3

3.2 Regression Analysis………………………………………………………..13

IV. Conclusion
 Executive Summary

This second phase of the study was accomplished to examine the correlation and

significance of regression models in data forecasting. The data extracted from the phase

1 of this study was utilized to apply linear regression analyses and forecasting. The

visualization of the Microsoft Company’s close prices initially initiated the utilization of

varied smoothing techniques to acquire the desired numerical values of the regression

models. Furthermore, a correlation analysis was performed to show relationship between

the stock market and Microsoft Company.

 1

Introduction

To show the relationship between a company and the stock market, a comparative

technique and analysis will be conducted to help researchers to analyze and conclude

with the desired results acquired. As the acquisition of odd volatility of the company, the

researchers are interested in the said stationary of the data to further analyze the major

events and depict the trends and volatilities. Furthermore, the researchers wanted to

know the relationship between the stock market and the company itself through

correlation analysis.
 2

Methodology

Data Collection

This second phase project administered the previous data of Microsoft Company’s

close prices and a stock market sourced from Yahoo Finance. For researchers to ensure

the paper's desired results and credibility, it is vital to utilize the most suitable method as

it improves the paper's quality (Stones, 2021).

 3

Data Analysis

Time Series Data and Forecasting

The Microsoft Company’s close prices, as shown in the Figure 1, have a low

volatility throughout the time frame. The mean of the data is not equal to 0 as it obviously

shifted on April 2024, the standard deviation of the data has a numerical value of 314.73

that is constant with the time, and the correlation between the lags is equal to zero in

which the sets of the data in the series were associated correspondingly.

Figure 1.

Microsoft Close Price.

Microsoft Close Price

$500.00
$450.00
$400.00
$350.00
$300.00
$250.00
$200.00
$150.00
$100.00
$50.00
$-

Note: From “Microsoft Corporation” by Yahoo Finance,

https://finance.yahoo.com/quote/MSFT/history. Copyright by Yahoo Finance.

 4

In the Augmented Dickey-Fuller (ADF) Test, as shown in Table 1, the p-value of

the intercept and lagged differencing are less than 1. Therefore, the data is stationary.

Table 1

Statistical Summary of the Microsoft Corporation’s Close Prices.

SUMMARY OUTPUT

Regression Statistics
Multiple R 0.6904
R Square 0.4766
Adjusted R Square 0.4720
Standard Error 24.4881
Observations 229

ANOVA
df SS MS F Significance F
Regression 2 123423.0645 61711.5322 102.9094 0.0000
Residual 226 135525.1363 599.6687
Total 228 258948.2007

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 1.9183 16.4452 0.1166 0.9072 -30.4873 34.3239 -30.4873 34.3239
Lagged 0.0040 0.0448 0.0896 0.9287 -0.0843 0.0923 -0.0843 0.0923
Diff. Lagged 0.4970 0.0346 14.3452 0.0000 0.4288 0.5653 0.4288 0.5653

Note: Regression Analysis on Excel. From Excel.

 5

As shown in Figure 2, trends, volatilities, and changes of the data are depicted

upon execution of the unit root test and differencing method.

Figure 2

Unit Root Test on Microsoft Company’s Close Prices.

Unit Root Test

$700.00
$600.00
$500.00
$400.00
$300.00
$200.00
$100.00
$-
1
8

29

148

190
15
22

36
43
50
57
64
71
78
85
92
99
106
113
120
127
134
141

155
162
169
176
183

197
204
211
218
225
$-100.00
$-200.00

Series1 Series2 Series3

Note: Unit root test on Microsoft Company Close Prices, series 1, differencing, series 2,

lag differencing, series 3, lag. From “Microsoft Corporation” by Yahoo Finance,

https://finance.yahoo.com/quote/MSFT/history. Copyright by Yahoo Finance.

Differencing method. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 6

Figure 3

Stationary of the Microsoft Company’s Rate of Returns.

Returns
2.0000%

1.0000%

0.0000%
1
8

29

78

127

176

225
15
22

36
43
50
57
64
71

85
92
99
106
113
120

134
141
148
155
162
169

183
190
197
204
211
218
-1.0000%

-2.0000%

-3.0000%

Note: Rate of returns. From Excel, https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.

As shown in Table 2, is the 10-day forecast of the Microsoft Company’s Close

Prices. Upon the acquisition of the rate of returns from the company, further forecasting

methods were utilized, mainly the Exponential Smoothing, Holt’s Method, and SMA

(Smooth Moving Average) to acquire the necessarily desired data results on Regression

Models such as MAD (Mean Absolute Deviation), MSE (Mean Squared Error), RMSE

(Root Mean Squared Error), and (Mean Absolute Percentage Error).

 7

Table 2

10-day Forecast on Microsoft Company’s Close Prices.

1 02-05-2024 $ 5,123.19
2 03-05-2024 $ 5,127.22
3 04-05-2024 $ 5,131.25
4 05-05-2024 $ 5,135.28
5 06-05-2024 $ 5,139.31
6 07-05-2024 $ 5,143.34
7 08-05-2024 $ 5,147.37
8 09-05-2024 $ 5,151.40
9 10-05-2024 $ 5,155.44
10 11-05-2024 $ 5,159.47
Note: May 02, 2024 to May 11, 2024 Forecasted Values.

Figure 4

Linear Trend Equation and Visualization on Estimated Future Time Periods and Values.

Microsoft
$6,000.00
$5,000.00
$4,000.00
R² = 0.7354
$3,000.00 y = 2.7796x - 121110

$2,000.00
$1,000.00
$-

Note: Linear Trendline Prediction on Microsoft Company’s Forecasted Prices. From

excel. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 8

In Figure 5, it shows the exponential smoothing on Microsoft Company’s close

prices with the exponential constant of 0.3 and 0.7.

Figure 5

Exponential Smoothing of the Company from June 01, 2023, to May 01, 2024.

Exponential Smoothing on Microsoft Company

$6,000.00
$5,000.00
$4,000.00
$3,000.00
$2,000.00
$1,000.00
$-
1
9
17
25
33
41
49
57
65
73
81
89
97

113

225
105

121
129
137
145
153
161
169
177
185
193
201
209
217
ExC 0.3 ExC 0.7

Note: Microsoft Company’s Close Prices. From Excel, https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 9

Figure 6

3-day Smooth Moving Average on Close Prices Actual Prices’ Amounts.

Note: Microsoft Company’s Close Prices on Yt, 3-day Moving Total, and Moving Average

from Microsoft, Line Graph created from Gretl. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 10

Figure 7

4-day Smooth Moving Average on Close Prices Actual Prices’ Amounts.

Note: Microsoft Company’s Close Prices on Yt, 4-day Moving Total, and Moving Average

1 and 2 from Microsoft, Line Graph created from Gretl. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 11

Figure 8

Smooth Moving Average on 3-day and 4-day Linear Regression Forecast.

Note: Smooth Moving Average. From excel. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.

In Table 3, the 10-day forecasted value of the Microsoft Company’s Close Prices

was utilized for smoothing, specifically, Holt’s Method with a constant of 0.7 and 0.5 to

acquire the MAD (Mean Absolute Deviation), MSE (Mean Squared Error), RMSE (Root

Mean Squared Error), and (Mean Absolute Percentage Error).

 12

Table 3

Holt’s Method Smoothing on Forecasted 10-day Prices.

Date y yf MAD Absolute MSE

02-05-2024 $ 5,123.19 5002.686924 $ 120.50 $ 2.35 $ 1.21
03-05-2024 $ 5,127.22 4977.063573 $ 150.16 $ 2.93 $ 1.50
04-05-2024 $ 5,131.25 4951.440222 $ 179.81 $ 3.50 $ 1.80
05-05-2024 $ 5,135.28 4925.816871 $ 209.46 $ 4.08 $ 2.09
06-05-2024 $ 5,139.31 4900.19352 $ 239.12 $ 4.65 $ 2.39
07-05-2024 $ 5,143.34 4874.570168 $ 268.77 $ 5.23 $ 2.69
08-05-2024 $ 5,147.37 4848.946817 $ 298.43 $ 5.80 $ 2.98
09-05-2024 $ 5,151.40 4823.323466 $ 328.08 $ 6.37 $ 3.28
10-05-2024 $ 5,155.44 4797.700115 $ 357.74 $ 6.94 $ 3.58
11-05-2024 $ 5,159.47 4772.076764 $ 387.39 $ 7.51 $ 3.87
$ 2.54

Holt's MAD MSE MAPE RMSE SMA

w=0.7,
v=0.5 387.3901238 2.539457267 7.508336273 1.593567466
Note: Holt’s Method Smoothing. From excel. https://guscanada-

my.sharepoint.com/:x:/g/personal/christine_ignaci3170_myucw_ca/EVyvxwHzHKhLrFM

zIV9WqJgBcEifpVo7_wy4LyuBFAp_XA?e=1H7CIp.
 13

Regression Analysis

This section demonstrates the use of regression analysis to examine the gathered

data thoroughly as illustrated in Table 4. The goal is to assess and contrast two separate

variables, the dependent S&P 500 and independent close prices of Microsoft Corporation,

to visualize the significance, relations, stationarity, and trends. Moreover, it supports

investors in making decisions, evaluating objectives and risks, prioritizing, promoting

informed innovation, and achieving continual enhancements (Appinio, 2024).

Table 4.

Regression Statistics on Stock Market Index and Microsoft Company’s Close Prices.

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.935314734
R Square 0.874813652
Adjusted R Square 0.874266986
Standard Error 111.8418688
Observations 231

ANOVA
df SS MS F Significance F
Regression 1 20017179.93 20017179.93 1600.272942 2.6339E-105
Residual 229 2864470.231 12508.60363
Total 230 22881650.16

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 1670.943053 74.97199188 22.28756382 2.78645E-59 1523.219944 1818.666163 1523.219944 1818.666163
X Variable 1 8.164038045 0.204083545 40.00341163 2.6339E-105 7.761916464 8.566159625 7.761916464 8.566159625

Correlation Analysis

Correlation assesses the relationship between the closing prices of Microsoft and

the stock market index. This demonstrates the extent of the correlation between the two

variables. The correlation coefficient r is always between -1 and +1: -1 ≤ r ≤ 1. In the

diagram, a value of r from 0 to 0.935314734 indicates a strong positive correlation as it is

closer to 1 in numerical terms. The calculated r2 value is 0.874813652. Furthermore, it

 14

can be seen from the estimated regression equation that the optimal line is defined by ŷ

= 8.164x + 1670.9. Therefore, the variability in the changes in Microsoft Company's

closing prices accounts for approximately 88% of the variability in the S&P 500 changes

based on the optimal regression line.

The correlation between Microsoft Company's closing prices and the S&P500

stock market shows a high positive slope of y=8.16x and a strong best-fit line represented

by R2=0.8748, indicating a strong 87% relationship between the two variables.

Figure 9

Linear Regression in a Scatter Plot between Stock Market and Microsoft.

$6,000.00
Linear Regression of the Company and Market Stock
y = 8.164x + 1670.9
$5,000.00 R² = 0.8748

$4,000.00

$3,000.00

$2,000.00

$1,000.00

$-
$- $50.00 $100.00 $150.00 $200.00 $250.00 $300.00 $350.00 $400.00 $450.00 $500.00

Stock market y-axis from “E-Mini S&P 500 Jun 24 (ES=F)” by Yahoo Finance,

(https://ca.finance.yahoo.com/quote/ES%3DF/history). Copyright by Yahoo Finance.

 15

The slope of the line represents the typical increase in the y variable for every

increase of one unit in the x variable. The slope of the line is 8.164 times x. Typically,

when the company's stock goes up by one point, the market stock also goes up by an

average of 8.164 points.

 Conclusion

Data smoothing is an advantageous method to remove the white noise,

expose the volatilities in a complex data pattern, and assist in predicting a forecast value.

Furthermore, it gives significance to the data observations as it deals with the differences

between actual events and forecasts accurately. Calculating regression models will help

the researchers in predicting results and shifts in the data extracted.