Reference no: EM132418642 , Length: word count:1800
LUBS5011M - Applied Finance Assignment Help and Solution, University of Leeds, UK
Your assignment consists of two parts: Part I Excel Calculations and Part II Written Report. You are required to submit both parts of your assignments.
Part I - Excel Calculations - Instructions
Begin by downloading the MS-Excel file.
The file contains three worksheets, "Summary Report", "Data-Yearly" and "Data- Monthly". The two data worksheets contain the data that relates to the coursework questions, whilst the "Summary Report" is a template into which you must place your answers to the requirements for Part I.
You should insert additional worksheets into the file, within which you carry out the calculations necessary to meet the requirements below.
When performing the necessary calculations never manually retype any of the information contained in the data worksheet, always use cell references, functions and/or formulae. Use built-in Excel functions whenever possible.
When completing the "Summary Report" worksheet never manually retype figures contained in other worksheets, always use cell references, functions and/or formulae.
You are encouraged to format the "Summary Report" worksheet. However, when doing so never move the location of the answering cells.
Use efficient ways to calculate answers.
REQUIRED -
1) The "Data-Yearly" worksheet contains yearly stock price data for 'XXX Corp' and 'YYY Inc' from 1986 to 2015. Use the data in the worksheet to complete the following requests. You can create additional worksheets for your calculation or do it on the data worksheet.
a) Calculate simple return for both stocks and format the return to show negative return in blue and positive return in red. Create a hyperlink to the "Data-Yearly" worksheet in cell C4 of the "Summary Report" worksheet.
b) Plot both return series in one chart; place the chart in a separate worksheet named "Yearly Return Chart". Use formatting options to improve the presentation of the chart. Create a hyperlink to the Yearly Return Chart worksheet in cell C5 of the "Summary Report" worksheet.
c) Rescale the price data of two stocks, so that the starting price of each stock is 100, but the yearly returns from rescaled price sequences are equal to ones from original price sequences. Plot both Rescaled price series in one chart; place the chart in a separate worksheet named "Yearly Price Chart". Use formatting options to improve the presentation of the chart. Create a hyperlink to the Yearly Price Chart worksheet in cell C6 of the "Summary Report" worksheet.
d) Calculate the overall gains (buy and hold return) of 1986-2015 for both stocks. Reference your estimates of the overall gains in cells C9:D9 of the "Summary Report" worksheet.
e) Calculate the Compounded Annual Gains. Reference your estimates of these gains in cells C10:D10 of the "Summary Report" worksheet.
f) Count how many years the returns of 'XXX Corp' are higher than those of 'YYY Inc'. Reference your final estimation in cell C11 of the "Summary Report" worksheet.
g) Create a worksheet named "Market Frontier", assuming there are only these two stocks in market and yearly risk free rate is 5%, draw market frontier, find out optimal risky portfolio, draw capital markets line in worksheet "Market Frontier", and create a hyperlink to this worksheet in cell C12 (use historical estimation of mean, variance and correlation of stock returns as expected mean, variance and correlation of stock returns).
2) The "Data-Monthly" worksheet contains monthly adjusted close price data of 'XXX Corp' and 'YYY Inc' from March 1986 to December 2015. It also contains monthly market index (SP500 index) and 1 month T-bill rates (risk free rate).
a) Calculate the monthly excess returns of 'XXX Corp', 'YYY Inc' and Market in "Data-Monthly" worksheet. Create a hyperlink to this worksheet in cell C16 of the "Summary Report" worksheet.
b) Calculate the basic statistics of monthly excess returns of 'XXX Corp' and 'YYY Inc' (Mean, Median, Min, Max, Variance, Standard deviation, Skewness, and Kurtosis). Reference your final estimates in cells C19:D26 of the "Summary Report" worksheet.
c) Find which month 'XXX Corp' and 'YYY Inc' have the highest and lowest excess returns. Reference your final estimates in cells C27:D28 of the "Summary Report" worksheet.
d) Build frequency distributions of monthly excess returns for 'XXX Corp' and 'YYY Inc'. Plot these two distributions in two charts; place the charts in a separate worksheet named "Frequency Distributions Chart". Use formatting options to improve the presentation of the chart. Create a hyperlink to the Frequency Distributions Chart worksheet in cell C31 of the "Summary Report" worksheet.
e) Estimate the correlations among the monthly excess return of 'XXX Corp', 'YYY Inc' and Market in the "Data-Monthly" worksheet and reference your results in cells C34:E36 of the "Summary Report" worksheet.
f) Estimate the betas and alphas of 'XXX Corp' and 'YYY Inc' for the whole sample period. Reference beta, alphas and adjusted R squares in cells C39:D41 of the "Summary Report" worksheet.
Part II - Written Report
Title: Comparing the Stock Performances of 'XXX Corp' and 'YYY Inc'.
Prepare a report, comparing the stock performances of 'XXX Corp' and 'YYY Inc'.
The following equally important points need to be addressed.
1. Description and analysis of stock performances of 'XXX Corp' and 'YYY Inc' over time.
2. Using T-tests to compare the monthly performances of 'XXX Corp' and 'YYY Inc'. Check how sample periods affect your conclusion.
3. Analysis of 'XXX Corp' and 'YYY Inc' performances with the CAPM. Check how sample periods affect your conclusion.
You can refer your discussion to the relevant excel analyses in Part I. However, the report is a standalone piece of work. Readers should be able to understand your arguments without looking at your excel workings. When including the results from your excel analyses, descriptions of the analyses should be given and tables/figures should be formatted and captioned. You are encouraged to use additional analysis to support your arguments. When doing so, please clearly label the worksheet including any additional analysis in excel and include the analysis in your writing.
Total Word - 1800. And Reference - APA style. Using data in excel analyze the performance of stock price of two companies.
Attachment:- Applied Finance Assignment Files.rar