# 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

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

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

- 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. - Click the button then highlight your

60 monthly stock returns and

click the button again. - Click the button then highlight your

60 monthly market returns and

click the button again. - Indicate the cell where you want

your output to be displayed. - 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. - 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]

- 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? - 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

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

- 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

- 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. - Font for both numerals and words should be Arial size 8.
- i) covariance and ii) betas should show full decimal places. For others, keep 4 decimal points.
- In answering Q5, you must use the method instructed in this manual.
- 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 - I do not mind you drawing symbols (notations) by hand.
- In answering Q5, the use of the following excel functions is not allowed: COVAR, CORREL,

SLOPE. - The use of appendix is not allowed.

PART II - Your written report can be typed in any preferred font style.
- The report should be typed in size 12 with no spacing or margin requirement.
- When answers are discussed, question numbers (i.e. 1,2,3…) are to be clearly presented.
- Use your own words. Reference wherever necessary.
- Use a Harvard system of referencing.
- Reference (or bibliography) should be provided at the back of your report.
- The use of appendix is not allowed.

Page 9 of 10

Printing Requirements - 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. - 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. - 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.) - 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. - Your written report will then follow this set of spreadsheets.

Page 10 of 10

Submission Requirements - The assignment is due by 4pm on Wednesday, 5th October 2016 in Week 10.
- 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. - 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. - The marked assignment will be returned in Week 12 tutorial.
- 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. - 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. - 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**