INVESTMENT AND PORTFOLIO ANALYSIS
Choose 3 stocks + 1 national stock index. All the three stocks and index have to be from the same stock exchange.
FIRST PART:
Calculate monthly returns over 5 years of each of the 3 stocks ( You can extract closing prices from Yahoo finance. It would be nice if all the 3 stocks are from the same industry, so this way it’s easy to make comparisons.)
Calculate descriptive statistics of each of the 3 stocks(Mean, Median, Variance, Covariance, St. Deviation, Max., Min., Kurtosis, Skewness).
Calculate Co
elation Matrix
Draw Return Graphs
Regression Analysis for Beta
Beta graphs for Stocks
Make interpretation for each table and graph
You can do all these 7 sections (A-G) in one single Excel File
SECOND PART:
Brief information about the three companies you chose, a
ief conclusion (one paragraph ) and a the references.
You can do this part in a Word document
Max. 10 pages, 12 punto, Times New Roman, 1.5 space
Please take a look at the EXCEL files that I have attached in the email. They are some similar examples that I have done previously, and you can refer them to complete the assignment.
In “Excel1 File”:
- Column ‘E’ and ‘F’ co
esponds to section A
- Cells C40 to C48 co
esponds to section B
- The graph on the right side of the excel file co
esponds to section D
- I don’t know how to do or where to find section C (co
elation matrix)on the excel file. This is something you would need to figure out by yourself.
In “Excel2 File”:
- You can find section E (regression analysis for beta) once you open ‘sheet1’
- Section F is the graph that you can find once you open ‘Sayfa1’
BETA CALCULATION
BETA CALCULATION WITH COVARIANCE AND VARIANCE FORMULA
COVARIANCE.S
0,003777
VARIANCE.S
0,002733
BETA
1,381922
BETA CALCULATION WITH SLOPE FUNCTION
BETA
1,381922
BETA CALCULATION WITH REGRESSION ANALYSIS
ÖZET ÇIKIŞI
Regresyon İstatistikleri
Çoklu R
0,638572
R Kare
0,407774
Ayarlı R Kare
0,390355
Standart Hata
0,088332
Gözlem
36
ANOVA
df
SS
MS
F
Anlamlılık F
Regresyon
1
0,182662
0,182662
23,41048
2,78E-05
Fark
34
0,265287
0,007803
Toplam
35
0,447949
Katsayıla
Standart Hata
t Stat
P-değeri
Düşük %95
Yüksek %95
Düşük 95,0%
Yüksek 95,0%
Kesişim
0,01793
0,014766
1,214286
0,233005
-0,01208
0,047939
-0,01208
0,047939
X Değişkeni 1
1,381922
0,285613
4,838438
2,78E-05
0,801486
1,962357
0,801486
1,962357
THE BETA IS POSITIVE. IT INDICATES THAT THE STOK AND THE MARKET, THEY MOVE IN THE SAME DIRECTION. WHEN STOCK MARKET INDEX (S&P 500) DECLINES/INCREASES, SO DOES MICROSOFT. THE BETA IS 1.38, WHICH IS ABOVE 1 BUT LOWER THAN 2. THE STOCK IS RISKY BUT NOT TOO MUCH. BECAUSE, THE BETA INDICATES THE RESPONSIVENESS /SENSITIVITY OF THE STOCK TOWARDS MARKET. IN THIS CASE, IF YOU ARE NOT A RISK-SEEKING INVESTOR, IT WOULD BE FINE TO MAKE INVESTMENT IN MICROSOFT STOCK.
FIGURE 1: BETA CACULATION