Hello, I need help with an assignment for my finance class. A big portion of the assignment needs to be completed by Excel. Please refer to the documents that I have attached below. It would be nice if I can get receive the assignment within the next 36 hours.The asignment can be done in a couple of hours, as long as the assigned tutor is familiar with simple statistics, simple excel, and knows a bit about stocks and portfolio analysis.
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 Correlation 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 brief 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’ corresponds to section A - Cells C40 to C48 corresponds to section B - The graph on the right side of the excel file corresponds to section D - I don’t know how to do or where to find section C (correlation 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ılar 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