FIN 222 Corporate Finance Individual Major Assignment

FIN 222 Corporate Finance Individual Major Assignment
[80 marks]
This manual outlines the followings.
 Objective of the assignment
 Details of questions (Instructions will be provided wherever necessary.)
 Format requirements
 Printing requirements
 Submission requirements
 Objective of the Assignment
In Lecture 5 (CH11 and CH12), you were introduced to the concept of systematic risk,
as measured by beta. Beta is an indicator of an asset’s exposure (or vulnerability) to
systematic risk and evaluates the degree to which an asset’s return is correlated with
the market return. Beta is a key parameter in the Capital Asset Pricing Model
(CAPM) which is most commonly used asset pricing model for the estimation of
required rate of returns on a share (i.e. cost of equity=rE). This assignment consists
of two parts. Part I directs you to work with real financial data in Excel and aims to
take you through the basic exercise of estimating a company beta and computing
statistics which can be evaluated as part of investment decision-making process. Part
II requires you to write a report based on interpretations of the outputs obtained
from the first part. In this manual, you will also find out how to download historical
share price, market index data and interest rates from the web portal.
 Details of Questions
PART 1 [30 marks]
To successfully complete PART 1, please read each section carefully. You must read
“Format and printing requirement” before you produce any outputs. I also
encourage you to read the entire manual before you choose your companies for
analysis.
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
(This manual has been designed for you to do this assignment using Microsoft
Office Excel 2010).
 Be prepared to use Excel worksheet functions if instructed.
 Apart from what is provided in this manual, any further guidance on the use of
Excel functions will not be given. For further help, students should use ‘Help’
function in Excel.
 I would like you to become familiar with notations (or symbols) in [ ] and use
those notations to label your answers (and columns) in this part.
Page 2 of 10

1. Download historical prices for two companies and market index into Excel.
   [3 marks]
   Instructions!
   a. Choose two companies [i] from TWO different industries listed on the
   Australian Securities Exchange (ASX). To view ASX listed companies, go to
   http://www.asx.com.au/asx/research/listedCompanies.do
   b. To download the data, go to http://finance.yahoo.com
   c. Find a search box titled “Get Quotes” & type the name of your first
   company [i1]. Then click “Get Quotes”.
   d. See the menu on the left-hand side. Click “Historical Prices” under
   “Quotes”.
   e. See “Set Date Range”. Set Start date as 26th August 2011 and End date as
   26th August 2016 (sample period). Click on “Monthly” button. Then Click
   “Get Prices”.
   f. 61 month-end price 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
   26/08/2011). Don’t worry about them. They are actually month-end data.
   g. You will see seven columns in the saved spreadsheet. All we need is Date
   and Adj. close price [P]. Delete other columns. Currently, the data is in
   descending order. Go to “Data” tab and click “Sort”. Select “Date” for Sort
   by and “Oldest to Newest” for order. Click “OK”. The data should now be in
   ascending order. 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/ASX200” is to be used. Symbol to be
   entered to “Get Quotes” box 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]. Then save the file in Excel
   format. At this point, you should have four columns and 62 rows (The
   company names do not need to be presented here. Introduce your
   company names in your report in Part II.)
   Page 3 of 10
2. Calculate the monthly realised returns,[Ri1, Ri2, Rm], for your stocks and
   market index. [2 marks]

Instructions!
a. We learnt in Lecture 5 that realised return at t+1 can be calculated as


    t 1 t
t 1
t
t 1 Div P P
R
P
b. As you are using prices adjusted for dividends, you do not need to consider a
dividend component in return calculation if you have chosen a dividend paying
stock.
c. Three additional columns should be created for your monthly realised
returns of i1, i2 and m, which should be available from September 2011.
(i.e. a total of 60 monthly returns should be generated.)
d. Label your answers correctly.

3. Compute the following for your sample period. [3 marks]
   3.1. Monthly average returns [ i1 i2 m RR R , , ]
   3.2. Variance [ i1 i2 Var ,Var , m Var ] of monthly returns
   3.3. Standard Deviation [ i1 i2 SD ,SD , m SD ] of monthly returns
   Instructions!
   a. To compute the monthly average returns, use Excel function
   =AVERAGE (data range)
   b. To compute the variance, use Excel function, =VAR(data range)
   c. To compute the standard deviation, use Excel function, =STDEV(data
   range)
   d. This might help. You are meant to arrive at one value for Ri1 and for all
   the rest.
   e. Label your answers correctly.
4. Compute the Annual average return [ A A A
   i1 i2 m RR R , , ] using 5 annual
   realised returns. For each year, you will calculate the realised return using
   PAugust-end at year t and PAugust-end at year t+1. (Please disregard your monthly
   return data. You will have to compute new return figures using price data.)
   [3 marks]
   Page 4 of 10
5. Compute the followings. [9 marks]
   5.1. Covariance [Cov] and Correlation coefficient [Corr] between your first
   stock’s monthly returns and market returns. Then compute the beta for the 1st
   stock [i1]. Your goal is to arrive at one value for each of the three.

5.2. Covariance [Cov] and Correlation coefficient [Corr] between your second
stock’s returns and market returns. Then compute the beta for the 2nd stock
[i2 ]. Your goal is to arrive at one value for each of the three.

5.3. Covariance [Cov] and Correlation coefficient [Corr] between your 1st
stock’s returns and 2nd stock’s returns. Your goal is to arrive at one value for
each of the two.

Instructions!
To answer 5.1,
a. Open a new spreadsheet in the same file, copy date, your monthly return
observations, [Ri1, Rm] and paste these (using “Paste special” and choosing
“Value” button) into the new worksheet.
b. To compute the covariance, use the following formula.
         i1,m i1,1 i1 m,1 m i1,T i1 m,T m
1 Cov (R R )x(R R ) … (R R )x(R R ) T 1
(Note: If unsure, learn a mini covariance example from Lecture 5 note
first.)
c. Three additional columns should be created for outputs of  i1,T i1 (R R ),
 m,T m (R R ) and   i1,T i1 m,T m (R R )x(R R )
d. For summation, use Excel function =SUM(data range)
e. To compute the correlation coefficient, use the following formula.
 i1,m
i1,m
il m
Cov
Corr
SD xSD
f. To compute the beta, use the following formula.
  i1,m
i1
m
Cov
Var
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.)
Page 5 of 10
At this point, you should have produced a total of 4 spreadsheets with all your
outputs and answers labelled as instructed.
 Spreadsheet 1 should contain outputs for Q1-Q4.
 Spreadsheet 2 should contain outputs for Q5.1.
 Spreadsheet 3 should contain outputs for Q5.2.
 Spreadsheet 4 should contain outputs for Q5.3.

6. Additionally, compute the beta by regressing stock returns on market returns.
   6.1. Compute [i1] using a regression analysis. [3marks]
   6.2. Compute [i2 ] using a regression analysis. [3marks]
   Instructions!
   A beta can also be computed using a regression analysis. To do this, you will
   only need to work with 60 monthly stock returns and 60 monthly market
   returns.
   To answer 6.1,
   a. Open a new worksheet in the current file, copy and paste date, your
   monthly stock returns and monthly market returns [Ri1, Rm] as in step a in
   the previous question. Then go to “Data” tab  “Data analysis” on the far
   right  “Regression” then click “OK”
   (If you don’t see ‘Data analysis’ under ‘Data’ tab then click ‘Options’ under
   ‘File’ tab. Go to ‘add-ins’ from the left-hand side menu. On the bottom, you
   will see a drop-down menu next to ‘Manage’. Select ‘Excel Add-ins’ then Click
   ‘Go’ and tick ‘analysis toolpak’ and click ‘OK’. ‘Data analysis’ should now
   appear under ‘Data’ tab.)
   b. You will see the following pop-up monitor.
   Proceed as follows.
   c. Summary output will be displayed. Under ANOVA, the coefficient on X
   variable represents a beta of your stock i1[Note: See Figure 12.7 on Page
   374 of our text book. The coefficient on X variable (=beta) represents the
   slope of this line]. This beta estimate obtained from regression analysis
   should be identical to the beta from Question 5.1.
7. Click the button then highlight your
   60 monthly stock returns and
   click the button again.
8. Click the button then highlight your
   60 monthly market returns and
   click the button again.
9. Indicate the cell where you want
   your output to be displayed.
10. Then click OK.
    Page 6 of 10
    d. To answer 6.2, replicate steps a to b. (Make sure you replace il with i2
    wherever relevant.)
    e. Summary output will be displayed. Under ANOVA, the coefficient on X
    variable represents a beta of your stock i2. This beta estimate obtained
    from regression analysis should be identical to the beta from Question 5.2.
11. Estimate the required rate of return for your stocks [E(Ri1), E(Ri2)] as at 26th
    August 2016 using the CAPM. Use 6% as a market risk premium and the 10-
    year Australian Government bond rate as at 26th August 2016 as a risk-free
    rate [rf]. For 7.1 and 7.2, clearly present the following variables:
    rf =?, E(rMkt – rf) =?, beta=? And show the equation which led you to the final
    answer.
    7.1. Establish the CAPM equation and compute the required rate of return for
    the first stock [E(Ri1)] on the 5th spreadsheet in which you produced [i1]
    using a regression analysis. [3 marks]

7.2. Compute the required rate return for the second stock [E(Ri2)] on the 6th
spreadsheet in which you produced [i2 ] using a regression analysis. [1 mark]
Instructions!
a. 10-year Government bond rates can be obtained from
http://www.rba.gov.au/statistics/tables/index.html#interest-rates. See
“Capital market yields – Government bonds-Daily” under Interest Rates.
 Spreadsheet 5 should contain outputs for Q6.1 and Q7.1.
 Spreadsheet 6 should contain outputs for Q6.2 and Q7.2.
At this point, you should have produced a total of 6 spreadsheets with all your
outputs and answers labelled properly.
Page 7 of 10

PART II [40 marks]

1. Demonstrate your understanding of total risk, systematic risk and unsystematic risk. The following
   points should be discussed. [5 marks]
    Definition
    When unsystematic risk becomes unimportant
    Which risk is important in determining the required rate of return for an asset, and why?
2. Import your data from Part I into the following table. [1 mark]
   Stock 1 Stock 2 ASX200
   Monthly average return
   Annual average return
   Standard deviation
   Variance
   Stock 1 Stock 2
   ASX200 Cov=
   Corr=
   Beta=
   Cov=
   Corr=
   Beta=
   Stock 2 Cov=
   Corr=

Required rate of return

3. Suppose that you have an option of investing in one single asset: your first stock, second stock or
   ASX200. Which asset would be most vulnerable to changes in economic condition and why? Justify
   your answer by referring to an appropriate measure computed in Part I. [4 marks]
4. Suppose that you consider forming a three-asset portfolio by investing 25% of your wealth in your
   first stock, 25% in your second stock, and 50% in the market index, ASX200.
   4.1. Calculate the portfolio beta using   11 22     … p ww wn n . [2 marks]
   4.2. Calculate the required rate of return for your portfolio. [2 marks]
   4.3. Calculate the expected rate of return (measured by realised returns) for your portfolio.[2 marks]
   4.4. Is your portfolio overpriced or underpriced and why? Consequently, which recommendation
   would you make, “Buy/Hold” or “Sell/don’t buy”? [9 marks]
    Please show all your relevant calculations and justify your decision using the Security Market
   Line. Label X-axis, Y-axis and intercept. Clearly present values on X- and Y-axis when you locate
   your portfolio in the graph.
5. Suppose that you currently have 100% of your wealth invested in the market index, ASX200. If you
   consider adding either the first or second stock to form a two-asset portfolio (40% in ASX200
   afterwards), adding which stock will lead to a two-asset portfolio with lower total risk? [8 marks]
6. Which pair of the assets below will have the greatest diversification benefit and why? [3 marks]

 Stock 1 and ASX200
 Stock 2 and ASX 200
 Stock 1 and Stock 2

7. In this assignment, you have learnt how to estimate rE. Please comment on the usefulness and
   importance of rE. [4 marks]
   Page 8 of 10

 Format/Printing /Submission Requirements [10 marks]
 Format Requirements
PART I

1. Generate all your outputs in 6 separate spreadsheets as follows.
    Spreadsheet 1 should contain outputs for Q1-Q4.
    Spreadsheet 2 should contain outputs for Q5.1.
    Spreadsheet 3 should contain outputs for Q5.2.
    Spreadsheet 4 should contain outputs for Q5.3.
    Spreadsheet 5 should contain outputs for Q6.1 and Q7.1.
    Spreadsheet 6 should contain outputs for Q6.2 and Q7.2.
2. Font for both numerals and words should be Arial size 8.
3. i) covariance and ii) betas should show full decimal places. For others, keep 4 decimal points.
4. In answering Q5, you must use the method instructed in this manual.
5. Label your final outputs using notations suggested. The example follows.
   3.3 i1 i2 SD ,SD , m SD 0.XXXX0.XXXX0.XXXX
   3.3 i1 i2 Var ,Var , m Var 0.XXXX0.XXXX0.XXXX
6. I do not mind you drawing symbols (notations) by hand.
7. In answering Q5, the use of the following excel functions is not allowed: COVAR, CORREL,
   SLOPE.
8. The use of appendix is not allowed.
   PART II
9. Your written report can be typed in any preferred font style.
10. The report should be typed in size 12 with no spacing or margin requirement.
11. When answers are discussed, question numbers (i.e. 1,2,3…) are to be clearly presented.
12. Use your own words. Reference wherever necessary.
13. Use a Harvard system of referencing.
14. Reference (or bibliography) should be provided at the back of your report.
15. The use of appendix is not allowed.
    Page 9 of 10
     Printing Requirements
16. For Spreadsheets 1-4, print out each spreadsheet twice.
    a. Starting with Spreadsheet 1, make one printout as usual showing all your numerical
    answers.
    b. Create a copy of Spreadsheet 1. Make a second printout which must show row and
    column headings and your cell formulas used to generate your calculation. First, to make
    formulas visible, on a copy of Spreadsheet 1, go to “Formulas” tab Click “Show
    Formulas” within “Formula Auditing” section. Second, to show row and column
    headings, go to “Page Layout” tab  within “Sheet Options” section  under “Headings”
     tick “Print” box.
    In the second printout, the above sample output should appear as below.
    A B C D
    63 3.3 i1 i2 SD ,SD , m SD =STDEV(B3:B62)=STDEV(C3:C62)=STDEV(D3:D62)
    64 3.3 i1 i2 Var ,Var , m Var =VAR(B3:B62) =VAR(C3:C62) =VAR(D3:D62)
    c. Repeat a and b for Spreadsheets 2-4.
17. For Spreadsheets 5 and 6, print out each spreadsheet once.
    a. Make one printout as usual showing all your numerical answers.
    b. A separate spreadsheet with formula view is NOT required as you were required to
    present the used formula on the numerical spreadsheet.
18. Before you print, make sure your cell size is appropriate (so that it does not take up too much
    space). To see how to change column width and row height, see
    http://office.microsoft.com/en-us/excel-help/change-the-column-width-and-row-heightHP001216383.aspx#BMmouse. To fit your spreadsheet into one page, you will need to reduce
    margins. Go to “File” tab  “Print”. The “Print Preview” is shown on the right of the window.
    Activating the first button at bottom right-hand corner will allow you to reduce margins for
    both left/right and top/bottom. (DO NOT USE “FIT SHEET ON ONE PAGE” BUILT-IN
    FUNCTION UNDER SCALING. IT’S GOING TO MAKE YOUR FONT EXTREMELY SMALL
    and DIFFICULT TO BE READ.)
19. The spreadsheet with numbers is to be followed by the spreadsheet with corresponding formula
    view. For example, spreadsheet 1 should be followed by the spreadsheet with formula view then
    spreadsheet 2 followed by the spreadsheet with formula view and so forth.
20. Your written report will then follow this set of spreadsheets.
    Page 10 of 10
     Submission Requirements
21. The assignment is due by 4pm on Wednesday, 5th October 2016 in Week 10.
22. Do not wait until the last minute to submit your work. Server problems, network outrages,
    computer breakdowns, hard disc failures, and the like will not be considered for late
    submissions.
23. Assignment handed in late will be penalised by a deduction of 20% for each day late (i.e. 2
    marks deduction (out of 20) per day late). Any late assignment should be submitted to Aelee
    Jun directly.
24. The marked assignment will be returned in Week 12 tutorial.
25. FIN222 students are required to submit both soft-copy and hard-copy of the completed
    assignment.
    For the submission of the soft-copy,
    o go to “Turnitin: FIN222 individual major assignment” on FIN222 Moodle site.
    o Submit a single excel spreadsheet under the tab “Part I Excel Spreadsheet”.
    o Submit a single word file under the tab “Part II Written Report”.
    o Under each tab, click “Submit to Turnitin” button.
    o You do not need to print your originality report. We will check online.
    o Under Submission Title, please use the format
    First Name_Tutorial time (eg. Aelee_Thu 4:30)
    o Failure to submit a soft-copy of your assignment will result in a zero mark for the part
    unsubmitted.
    For the submission of the hard-copy, please see Assessment submission under Assessment 3 in
    FIN222 subject outline.
26. You are not allowed to collaborate with anyone on this assignment. Sign the declaration stating
    that the submitted work is your own on a coversheet.
27. If you fail to meet format/printing/submission requirements, you will be penalised.
    The total deductible marks will not exceed 10 marks (out of 80).
    Good luck!

 Frequently Asked Question
Q.My betas from 5.1 (or 5.2) and 6.1 (or 6.2) do not match.
For the calculation of betas in Q5,
 You didn’t miss any data point?
 Did you use formulas correctly?
 When you need to import the previously calculated measures from Q1-Q4, please make sure you
import the full digits by referencing the cell. The use of rounded numbers can be the cause of
mismatch.
Any more questions? Please post your question up onto FIN222 Moodle site!

Chat with us to get the full answer to the question and also email us at help@d2qud7ra4nbef7.cloudfront.net