Describe the business of the company assigned to you. (1) For ratios that measure profitability, utilization efficiency, leverage and liquidity, analyze the performance of the company over time and also compare its values to the peer averages. (2) Plot the stock prices from the start of 2017 till the end of 2021. How did the change in performance affect stock prices?

Important notes:
1. Please submit all your files on time. Submission on the first day after the
deadline incurs a 10% penalty and submission on the second day after the
deadline incurs a 20% penalty. Submissions made two days after the deadline
will not be accepted.
2. You can submit your work two times in Blackboard. However, we will NOT
accept new submissions after two times regardless of the submission time.
3. This is an individual project and you must work independently. Suspected
plagiarism cases will be reported to the disciplinary committee of the Faculty of
Engineering.
4. You can ONLY use Excel to solve the problems.
5. Please include both your name and student ID in your report.
6. The TA responsible for this project is WU Bo.
7. Grading is based on correctness, description and explanation of results, innovative
aspects and clarity.
8. Some problems are open-ended.
Data
Refer to Column “STOCK” in the Blackboard grade center and you will find the
ticker symbol for the stock assigned to you. You must analyze the assigned stock.
1. Collect the daily price data from the beginning of year 2017 to the end of
October in year 2022 from Yahoo Finance for the following assets: (1) the
stock assigned to you; (2) Vanguard S&P500 ETF (ticker symbol VOO); (3)
iShares iBoxx investment grade corporate bond ETF (ticker symbol LQD).
2. Use the database Mergent Online to obtain the data for various financial ratios
of the company assigned to you for years 2017 to 2021. Make sure you also
output the peer averages of these ratios. The list of ratios for analysis should
contain all the ones in the lecture slides (check the summary table) that are
available in the database. Please use “EBITDA margin” (=EBITDA/sales) in
the database as a substitute for “return on sales”. There is no need to consider
ratios that do not appear in the lecture slides.
Problems
1. Describe the business of the company assigned to you. (1) For ratios that
measure profitability, utilization efficiency, leverage and liquidity, analyze the
performance of the company over time and also compare its values to the peer
averages. (2) Plot the stock prices from the start of 2017 till the end of 2021.
How did the change in performance affect stock prices?
2. You will invest in the stock assigned to you at the beginning of 2022 for one
year. Suppose that you are also considering investing in another asset from the
two ETFs. Use data from the start of 2017 to the end of 2021 to forecast the
2
mean returns, volatilities and correlations (between the stock and the ETFs)
for 2022. Suppose you cannot short sell the stock but short selling the ETFs is
possible. Which ETF would you choose? Plot the investment opportunity sets
for the two pairs in the same mean-stdev diagram. Find out the optimal risky
portfolio for each pair and explain your choice of ETF.
3. Use the data in 2022 to estimate the mean returns, volatilities and correlations
(between the stock and the ETFs) of 2022. How accurate are your estimates in
Problem 2 compared with the estimates from 2022 data? If you use the
estimates from 2022 data, does the choice of ETF change in Problem 2?
4. Did VOO track S&P500 well from 2017 to 2021? Use statistical analysis to
justify your answer.
5. Estimate the beta of the stock in 2017, 2018, 2019, 2020 and 2021. What do
you observe? Also explain how you would forecast the beta of the assigned
stock in 2022.
Note: For simplicity, we use the average 3-month T-bill rate in a year to approximate
the risk-free rate in that year. The annual risk-free rate in 2017, 2018, 2019, 2020,
2021 and 2022 are 0.9341%, 1.9363%, 2.0625%, 0.3547%, 0.0450% and 1.5126%,
respectively. The daily risk-free rate = annual risk-free rate / 252.
Excel Workbook
You must solve all the problems in excel (you cannot use other software). We
expect to see the raw data, the daily return series, and all quantities you need to
estimate or calculate. By clicking on the cell that stores the value of a quantity, we
also expect to see the formula that calculates it. The regression results for each year
should be reported in standard excel regression output format.
Please create three excel workbooks, one for problem 1, one for problems 2 and 3,
and one for problem 4 and 5. You can decide the number of worksheets in each
workbook.
Report

Prepare a report to answer all the questions. In your solution, please write down the
formulas or the approach you used and include graphs and tables when necessary.
You must also have an appendix in the report, which explains what each excel
worksheet is for (this makes it easier for us to understand what you are doing in excel).
The report must be written in English and should not exceed 15 A4 pages with font
size 12, single-line spacing, 1-inch margin all-around. Violation of the standard
format receives a 10% penalty.
Note: By adopting a 15-page limit, there is no implication that a good report should be
15 pages long. However, it is true that a well-written report must contain adequate
explanations and discussions.
Submission
3
1. Report (in .docx or .pdf format). Name your file as “report-last name-first
name”.
2. Three Excel workbooks (in .xlsx format). Name your file as “WBx-last namefirst name” and replace x by the index of the workbook.
You should submit all your files through the blackboard system. When you
submit online, please directly submit the report and the excel workbook. Please do
not zip them and upload the zipped folder.