LUBS5011 Part I Excel Calculations : Part II Written Report, Assessment v2

LUBS5011 ASSIGNMENT DETAILS

Your assignment consists of two parts: Part I Excel Calculations (40%) and Part II Written Report (60%). You are required to submit both parts of your assignment before the deadline on 12:00 noon, Thursday 12th June 2025.

LUBS5011 Part I. Excel Calculations (40%) - Instructions

Begin by downloading the MS-Excel file “LUBS5011 24-25 TEMPLATE.XLSX”

Rename the file using your Student ID number only (e.g. 123456789.xlsx).

The file contains FIVE worksheets, “Summary Report”, “Data-Yearly” “Data- Monthly” “F-F_Research_Data_Factors” and “Allocation”.

The “Summary Report” worksheet is a template into which you must place your answers to the requirements for Part I. The two data worksheets, “Data-Yearly” and “Data-Monthly”, must be filled with stock prices and further data that relates to the coursework questions. The worksheet “F-F_Research_Data_Factors” contains monthly and annual excess returns on the market (Rm-Rf) and 1 month T-bill rates (Rf- the risk-free rate) from the 1920’s to date. The worksheet “Allocation” contains your unique Student ID and the ticker symbols of two firms, which have been allocated to you.

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 where appropriate (i.e., decimals, %, etc). However, when doing so never move the location of the answering cells.

Use efficient ways to calculate your answers.

LUBS5011 REQUIRED

1.Start from the worksheet “Allocation” and find (next to your unique Student ID) the ticker symbols of the two firms, which have been allocated to you. Go to Yahoo Finance (https://finance.yahoo.com/), (or any source of your preference) and download the monthly and annual closing share prices of the two companies from January 2004 to December 2024. Rename XXXX and YYYY using your allocated ticker symbols across the other worksheets, where applicable. Fill the worksheet “Data-Yearly” with yearly closing stock price data for your allocated ‘XXXX’ and ‘YXYY’ companies from year-end 2004 to year- end 2024. Then use this data to complete the following tasks. You can create additional worksheets for your calculations or include them in this worksheet.

1. Calculate simple returns for both stocks. Format the returns cells to show negative returns in red and positive returns in blue. Create a hyperlink to the “Data-Yearly” worksheet in cell C4 of the “Summary Report” worksheet.
2. Plot both returns series in one chart; place the chart in a separate worksheet named “Yearly Returns Chart”. Use formatting options to improve the presentation of the chart, as illustrated during the lectures. Create a hyperlink to the Yearly Returns Chart worksheet in cell C5 of the “Summary Report” worksheet.
3. Rescale the price data of the two stocks, so that the starting price of each stock is 100, but the yearly returns from rescaled price sequences are equal to the ones from the original price sequences. Plot both Rescaled price series in one chart; place the chart in a separate worksheet named “Yearly Rescaled Price Chart”. Use formatting options to improve the presentation of the chart, as instructed during lectures. Create a hyperlink to the Yearly Rescaled Price Chart worksheet in cell C6 of the “Summary Report” worksheet.
4. Calculate the buy and hold returns from 2004 to 2024 for both stocks. Reference your estimates of the overall gains in cells C9:D9 of the “Summary Report” worksheet.
5. Calculate the compounded annual returns for both stocks. Reference your calculations of these gains in cells C10:D10 of the “Summary Report” worksheet.
6. Count how many years the returns of ‘XXXX’ are higher than those of ‘YYYY’. Reference your calculation in cell C11 of the “Summary Report” worksheet.
7. Create a worksheet named “Market Frontier”, assuming there are only these two stocks in the market and the yearly risk-free rate is 4%, draw the market frontier, find the optimal risky portfolio, draw the capital market line in worksheet “Market Frontier”, and create a hyperlink to this worksheet in cell C12.

2) Fill the worksheet “Data-Monthly” with monthly adjusted price data of ‘XXXX’ and ‘YYYY’ from January-2004 to December-2024.

1. Calculate the monthly excess returns of ‘XXXX’, ‘YYYY’ and copy monthly excess returns on the market (Rm-Rf) and the risk-free rates (Rf) in “Data- Monthly” worksheet. Create a hyperlink to this worksheet in cell C15 of the “Summary Report” worksheet.
2. Calculate the summary statistics of monthly excess returns of ‘XXXX and ‘YYYY’ (Mean, Median, Min, Max, Variance, Standard deviation, Skewness, and Kurtosis). Reference your calculations in cells C18:D25 of the “Summary Report” worksheet.
3. Find which month ‘XXXX’ and ‘YYYY’ have the highest and lowest excess returns. Reference your calculations in cells C26:D27 of the “Summary Report” worksheet.
4. Build frequency distributions of monthly excess returns for ‘XXXX’ and ‘YYYY’. Plot these two distributions in two charts; place the charts in a separate worksheet named “Frequency Distributions Charts”. Use formatting options to improve the presentation of the charts. Create a hyperlink to the Frequency Distributions Chart worksheet in cell C30 of the “Summary Report” worksheet.
5. Estimate the correlations among the monthly excess returns of ‘XXXX’, ‘YYYY’ and the Market (Rm) in the “Data-Monthly” worksheet and reference your results in cells C33:E35 of the “Summary Report” worksheet.
6. Estimate the beta and alpha of ‘XXXX’ and ‘YYYY’ for the whole sample period. Reference betas, alphas and adjusted R-squares in cells C38:D40 of the “Summary Report” worksheet.

LUBS5011 Part II. Written Report (60% - 2,000 words)

Title: Comparing the Stock Performances of ‘XXXX’ and ‘YYYY’.

Prepare a report, comparing the stock performances of ‘XXXX’ and ‘YYYY’. The following equally important points need to be addressed.

1. Description and analysis of stock performances of ‘XXXX’ and ‘YYYY’.
2. Using t-tests to compare the monthly performances of ‘XXXX’ and ‘YYYY’. Check how different sample periods (of your choice) affect your conclusion. How did the two stocks perform during bull and bear market periods?
3. Analysis of ‘XXXX’ and ‘YYYY’ performances with the CAPM. Check how sample periods (of your choice) 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. The readers of this report should be able to understand your arguments without looking at your excel workings. When including the results from your excel analyses, the description of the analyses should be given and tables/figures should be formatted and captioned as illustrated in the lectures. 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.

The maximum word limit for this part of the assignment (part II report) is 2,000 words.

You are required to submit electronic copies of your excel file and your report on Minerva. The electronic submissions of your excel file and your report on Minerva must be submitted no later than 12:00 noon, Thursday 12th June 2025.

CHECK POINTS

• You must download the excel template file from Minerva before attempting the assessed course work. This file provides the coursework template and a “Summary Report” worksheet that must be completed.
• Marks will be awarded for the use of in-built Excel functions and formulae whenever possible.
• Good presentation, style and formatting must be used throughout. Marks will be awarded for the use of headers, borders, shading, etc. However, never move or change the location of the answering cells in the “Summary Report” worksheet.
• Submit the Excel file (name the file ONLY using your Student ID number, e.g. 123456789.xlsx) AND your report (also name the file only using your Student ID number, e.g. 123456789.docx) to Minerva in the submission page.
• Include a front cover in your report.