|
论文题目：Major Assignment
论文语种：英文
您的研究方向：ＦＩＮＡＮＣＥ
是否有数据处理要求：否
您的国家：澳大利亚
您的学校背景：５星级大学
要求字数：没有字数要求 文章包括２个部分，一共１７页
论文用途：本科课程论文 BA Assignment
是否需要盲审（博士或硕士生有这个需要）：[email protected](1455780998)
补充要求和说明：附件里是format/printing & submission requirements (很重要)
FIN 221 Introductory Business Finance
Major Assignment
This manual outlines the followings.
•Objective of the assignment
•Details of questions (Instructions will be provided wherever necessary.)
•Format requirements
•Printing requirements
•Submission requirements
Objoctive of the Assignment
In Lecture 4 (CH7), you were introduced to the concept of “beta” which was to be used as a measurement of “Systematic risk”. We also defined it as the “sensitivity of stock returns to market returns”. A beta is a critical component in the estimation of required returns for shareholders as can be seen in the Capital Asset Pricing Model (CAPM). This assignment consists of two components. The first part of the assignment requires you to work with Excel and aims to take you through to the basic exercise of estimating a company beta and computing statistics which are relevant to your investment decision. The second part of the assignment requires you to write a report based on the outputs obtained from the first part and current events. Along the way, you will also find how to download historical share price and index data from online resources. To successfully complete this assignment, you are also expected to search actively for relevant current market information using various sources.
Details of Questions
PART 1
To successfully complete PART 1, please read each section carefully. You must read “Format and printing requirement” before you produce any outputs.
If you have read “Format and printing requirement”, then proceed.
•Provide your answers (or outputs) for the following questions.
•All the calculations and computations are required to be produced in Excel.
•Be prepared to use Excel worksheet functions if advised.
•Apart from what is provided here, any further guidance on the use of Excel functions will not be provided. For further help, students should use ‘Help’ function in Excel.#p#分页标题#e#
•I would like you to become familiar with notations (or symbols) in [ ] for variables to be used (or computed) and use those notations to label your answers (or columns) in this part.
1. Download historical prices for two companies and S&P/ASX 200 index into Excel.
Instructions!
a.
Choose two companies [i] listed in the Australian Security Exchange (ASX) in the same industry in which you are interested in investing.
(For detailed information on companies listed on the ASX and their ASX code,
Go to c.
Find a box titled “Enter symbol(s)” & type in the ASX code of your first company [i1]. (Use “Symbol Lookup” if the ASX code doesn’t work.) Then click “GO”.
d. See the menu on the left-hand side. Click “Historical Prices” under “Quotes”.
e. See “Set Date Range”. Set start date as 30th April 2004 and End date as 27th March 2009. Click on “Monthly” button. Then Click “Get Prices”.
f. 60 month-end data should now be visible on the screen. Scroll down until you see “Download to Spreadsheet”. Save the data to a file. (You will see that date on the spreadsheet is shown as month-start except for 30/04/2004. Don’t worry about them. They are actually month-end data. But make sure the company you have chosen has a sufficient data i.e. 60 month-end data and does not exhibit a sudden price increase or fall.)
g. You will see six columns in the spreadsheet. All we need is Date and Adj. close price [P]. Delete other columns. Currently, the data is in descending order. Click “Date” and go to “Data” on the toolbar and choose “Sort”. Under “Sort by”, make sure “ascending” button is chosen. Click “OK”. Save the file again.
h. Replicate steps b to g to download data for your second company [i2].
i. Replicate steps b to g to download market index data [m]. For this assignment, the market index S&P/ASX 200 is to be used. Symbol for S&P/ASX 200 is ^AXJO.
j. Display the following on the same spreadsheet, i) date, ii) Adj. close price for your first stock [Pi1] , iii) Adj, close price for your second stock [Pi2] and iv) Adj. close price for the market index [Pm]. At this point, you should have four columns and 61rows (Company names need not to be presented here. You should address your company names in your report.)
2. Calculate the monthly returns,[Ri1, Ri2, Rm], for your stocks and market index.
Instructions!
a.
Return for month t (Rt) can be calculated as tt1tt1PPRP−−−=
b. As you are using price adjusted for dividends, you don’t need dividend component in return calculation (if your stock pays dividends).#p#分页标题#e#
c. Extra three columns should be created for your monthly return outputs which will be available from May 2004. (i.e. a total of 59 monthly returns should be generated.)
d. Label your answers correctly.
3. Compute the following for your sample period.
3.1. Monthly expected returns [] i1i2mE(R),E(R),E(R)
3.2. Annual expected returns [AAAi1i2mE(R),E(R),E(R)]
3.3. Variance [22i1i2,σσ,2mσ ] of monthly returns
3.4. Standard Deviation [i1i2,σσ,mσ] of monthly returns
Instructions!
a. To compute the monthly expected returns, use Excel function, =AVERAGE (data range)
b.
Annual expected return is calculated as monthly expected return*12
c.
To compute the variance, use Excel function, =VAR(data range)
d.
To compute the standard deviation, use Excel function, =STDEV(data range)
e.
Label your answers correctly.
4. Compute the Coefficient of Variation for your first [CVi1] and second stock [CVi2]. You may like to pay a particular attention to the choice of return figure.
5. Compute the followings.
5.1. Covariance [Cov(Ri1,Rm)] and Correlation coefficient [(Ri1,Rm)] between your first stock’s monthly returns and market returns. Then compute beta for the 1st stock [ρi1β].
5.2. Covariance [Cov(Ri2,Rm)] and Correlation coefficient [(Ri2,Rm)] between your second stock’s returns and market returns. Then compute beta for the 2nd stock []. ρi2β
5.3. Covariance [Cov (Ri1,Ri2)] and Correlation coefficient [(Ri1,Ri2)] between your 1st stock’s returns and 2nd stock’s returns ρ
Instructions!
To answer 5.1,
a.
Open a new spreadsheet, copy your monthly return variables, [Ri1, Rm] and paste these (using “Paste special” and choosing “Value” button) into the new sheet.
b.
To compute the covariance, use the following formula. nt1i1mi1,tm,ti1mCov(R,R)(RE(R))*(RE(R))n1==−−−Σ
(Learn covariance example from the Lecture 4 note first!)
c. Extra three columns should be created for outputs ofi1,til(RE(R))−, and *m,tm(RE(R))−i1,ti1(RE(R))−m,tm(RE(R))−
d. For summation, use Excel function =SUM(data range)
e. To compute the correlation coefficient, use the following formula. i1mi1mi1mCov(R,R)(R,R)*ρ=σσ
f. To compute the beta, use the following formula. i1mi1m2mCov(R,R)
(R,R)β=σ
g.
Label your answers correctly.
h. To answer 5.2, replicate steps a to g. (Make sure you replace il with i2 wherever relevant.)
i. To answer 5.3, replicate steps a to e and g. (Make sure you replace m with i2 wherever relevant.)
At this point, you should have produced a total of 4 spreadsheets with all your outputs and answers labelled as instructed.
6. Additionally, compute the beta by regressing stock returns on market returns.
6.1. Compute [] using a regression analysis. i1β#p#分页标题#e#
6.2. Compute [] using a regression analysis. i2β
Instructions!
A beta can also be computed using a regression analysis. To do this, you will only need to work with 59 monthly stock returns and 59 monthly market returns.
To answer 6.1,
a.
Open a new worksheet, copy and paste your monthly stock returns and monthly market returns [Ri1, Rm] as in step a in the previous question. Then go to “Tool”
|
