AFIN1002 - Corporate Finance and Skills Capital Markets - Macquarie University

Assignment Task

Description

The purpose of this assessment is to apply the skills developed in weeks 4 to 7 on discounted free cash flow models, financial statement modelling, and the cost of capital to value a company listed on the Australian Securities Exchange (ASX) of your choice. In this assessment, you will develop your skills in financial modelling, which is a valuable skill sought by employers

Skills in focus for this assessment

In this assignment, you will develop your skills in building a financial model using Excel, which is a valuable skill sought by employers. Additionally, the assignment will develop your critical thinking, problem-solving and technical skills, as well as your discipline knowledge.

Questions

• If your formulas are correct in Question 5, you can do Question 6.
• You need to find a new WACC in G34, which makes the estimated share price in G45 and the traded market share price in G46 equal.
• Click "Data" - "What-If Analysis" - "Goal Seek".
• In the goal seek dialogue box, “Set cell” G45 “To value” to the numerical value in G46 “By changing” G34. 
• Copy the newly generated WACC in G34, then paste special by value into G49.
• Copy the original WACC in G33, then paste the special by value into G34, to overwrite the hard-copied G32.
• If your formulas are correct in Question 5, you can do Question 7.
• You need to find a new long-run growth rate in G37, which makes the estimated share price in G45 and the traded market share price in G46 equal.
• Click "Data" - "What-If Analysis" - "Goal Seek"
• In the goal seek dialogue box, “Set cell” G45 “To value” to the numerical value in G46 “By changing” G37.
• Copy the newly generated long-run growth rate in G37, then paste special by value into G56.
• Replace the new hard-coped long-run growth rate in G35 with the original long-run growth rate.
• If your formulas are correct in Question 5, you can do
• First, set the top left corner equal to the estimated share price with the calculation formula, that means set "D66=G45.
• Then, set up different long-run growth rates in the yellow line E66-L66. Copy your certain long-run growth rate value G37 into the middle position (H66), then make the growth rates increase from left to right at a percentage that you think is sensible.
• Then, set up different WACCs in the yellow column D67-D78. Copy the WACC value G34 into the middle position (D72), then increase the WACC from top to bottom at a per cent that you think is sensible.
• Next, select the whole yellow table area of Question 8, from the left top corner D66 to the right bottom corner L78.
• Check your Excel settings, Click "Formulas" - "Calculation Options" - tick "Automatic".
• Click "Data" - "What-If Analysis" - "Data Table".
• Set “Row input cell” to G37, and “Column input cell” to G34.
• Contains nice conditional formatting colour shading: select and highlight the whole yellow table area, Click "Home" - "Conditional Formatting" - "Colour Scales" - "GreenYellow-Red Scales".

This AFIN1002 – Accounting and Finance has been solved by our PHD Experts at My Uni Paper.