Its a corporate finance project
Its a corporate finance project
FIN625.7W2: Corporate Finance
Project
NOTES:
(1) Electronic submission only via Sakai’s Assignments folder.
(2) Due date/time: December 2nd (Sunday), 11:55pm
(3) NO late submission. Submitting beyond the above due time will NOT be accepted and will
result in a grade of zero for this assignment.
(4) You must complete the assignment by using Excel spreadsheet.
(5) You must use “formulas” for ALL of the calculations. No credit will be given if you are
using hand calculation or calculator, or formulas cannot be seen.
(6) Save the final file in either “xls” or “xlsx” format. Do NOT save the file as the “csv” format,
because the formulas will not appear in the “csv” format.
(7) ZERO credit if your file does not contain the formulas in the cells. So, please double check
to make sure there are formulas in the corresponding cells before you submit the file.
(8) Submit ONE file ONLY, with the following THREE spread sheets (“tabs”): (a) Standard
Deviation (S.D.) result of your company and that of S&P500, including original price
columns, return columns, and S.D. columns; (b) Beta estimation result, including the graph
which represents the “characteristic line”; (c) Your answers to the questions (wordings).
(9) 2 points (out of 10) will be deducted if you submit more than one file.
(10) Before submitting, please double check and triple check to make sure the file that you are
going to submit is the correct file. Submitting the incorrect file will result in a 3-point
deduction (out of 10) even if resubmission is allowed.
Part (I): Download the Price Data (2 points)
Go to Yahoo Finance website (finance.yahoo.com) and do the following steps:
(1) Enter the ticker symbol into the “Quote Lookup” box located on the right-side of the screen for
“your company” according to the Company List attached on page 3 below.
(2) Click on “Historical Data” option.
(3) Change the time period to October 2009 to September 2018, and change the frequency to
monthly. Then hit the blue button “Apply”.
(4) Hit “Download Data”. And, then save the downloaded file to a “csv” file (somehow it can only
be saved as a “csv” file at this point.
(5) Save the file to an “xls” or “xlsx” file.
(6) Delete all of the columns except the “Date” and “Adj. Close” columns.
(7) Repeat the steps (1) to (6) above for S&P500 Index.
(8) Copy the S&P500 Index price data onto the above excel file, i.e., combining two files into one
sheet (tab).
(9) Convert price series into return series by using “(P1-P0)/P0”, where P0 and P1 are,
respectively, prices of the previous and the current month (creating a new column for return).
(*** It is essential that you convert the price series into returns, so that you will be using the
RETURN, instead of price, in the regression. You will be losing a lot of points if you use
price series for the regression because it would be a fundamental error.)
2
(10) Re-label each of the columns so that your company’s data is distinguishable from S&P500’s.
Part (II): Calculate the Return Standard Deviation (S.D.) (1 points)
In two adjacent cells, calculate the return standard deviation for both your company and
S&P500 index by using the “STDEV” function.
Part (III): Estimate the Beta (2 points)
(a) Download the series of 3-month US T-bill rate, which is a proxy for the risk-free rate, from
October 2009 to September 2018, from the link below:
https://fred.stlouisfed.org/series/TB3MS/downloadd…
(b) Copy the monthly US T-bill rates series onto the Excel file in Part (II) above, with the months
correctly aligned with the return series of your company and S&P500. Double check to make
sure the monthly sequence has been correctly aligned among those three return columns.
(c) Because the T-bill rates series is an annual rate and is shown in the percentage format, you need
to convert it to monthly rate by dividing it by 12, and then divide that rate by 100 (i.e., divide
the original series by 1200) to make it consistent with the monthly rates of your company and
the S&P500 series. That is, create another column to store the converted series of the adjusted
T-bill rates, which will be used in the following steps.
(d) Create another column (name it “R-Rf”) that contains the rate difference between your
company’s returns and the T-bill rates.
(e) Create another column (name it “RM-Rf”) that contains the rate difference between S&P500
index’s returns and the T-bill rates.
Use regression to estimate beta (β):
(a) You need to first activate the “Data Analysis” function in your Excel, if it is not activated yet.
The step-by-step instructions can be easily found on the internet, such as this one:
http://libanswers.walsh.edu/faq/147605.
(b) You may also find that data analysis activation process AND the regression analysis from this
link:
(c) The only thing missing in the above link is the “graph”. You may simply check the “Line Fit
Plots” box in the regression selection items in a screen such as the one shown below:
