# 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]
“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.
 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
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
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
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.
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
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.
returns of i1, i2 and m, which should be available from September 2011.
(i.e. a total of 60 monthly returns should be generated.)

1. 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.
2. 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
3. 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!
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
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.

1. 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.
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.
2. Click the button then highlight your
60 monthly stock returns and
click the button again.
3. Click the button then highlight your
60 monthly market returns and
click the button again.
4. Indicate the cell where you want
5. 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.
6. 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
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
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

1. 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]
2. 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
Line. Label X-axis, Y-axis and intercept. Clearly present values on X- and Y-axis when you locate
3. 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]
4. 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

1. 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

 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
a. Starting with Spreadsheet 1, make one printout as usual showing all your numerical
b. Create a copy of Spreadsheet 1. Make a second printout which must show row and
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.
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
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.
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!

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

### Submit Assignment

Fill in the form below or give us a call and we'll contact you. We endeavour to answer all enquiries within 24-48 hours on business days.