Assignmentstore is an one stop solution for all your assignment needs with 100% unique solutions from high qualified Instructors.

Get HelpNow





    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.

    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.
      e. Label your answers correctly.
    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!
    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.

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

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

    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@assignmentstore.com