Coverage: Topics 2-5
Due date: May 17, 2021 at 11.59 p.m.
Weight: 40%
1. The purpose of this project is to explore the dynamics of corporate valuation used in M&As and understand the information retrieval process, the limitations of valuation methods, and the impact of uncertainty on corporate valuation. The project is designed to help you build research abilities, analytical and time management skills.
2. Marks: 40 – You must have the correct answer and a correct explanation to gain the full marks allocated to each question. Failure to do so will result in a mark of zero for the question. Be sure to format your Excel file in a reasonably clear way, so that someone who was given the same job after you would be able to understand your working and replicate what you have done. Ambiguous answers and contradictory statements will be penalised.
3. The project must be done in Excel. Use the Excel template provided. Do all the assignment with explanations in excel and upload one file. Type your explanation in text boxes.
4. Format: Calculation or short answer with explanation.
5. Word Limit: 800 words as a very rough guide mostly calculations.
6. In-text citation and reference list are expected when you incorporate other people’s views into your work. Common knowledge, your own ideas or findings do not need a reference.
7. The assignment must be done in Excel (scanned hand-written answers are not acceptable).
8. Upload a soft copy of your Excel file to Blackboard under Assessment by the due date and time.
9. You can submit multiple times up until the due date, upon which the submission links will close. Only the last attempt will be graded.
10. Late assignment + No extension = 0 as per QUT policy.
11. A hard copy is not required. Keep a copy for yourself.
12. To avoid mixing up assignments, name your assignment using the following format:
· unit code, your name, your student number, and the assignment
· for example: EFB343 Doe John n1234567 Assignment
· Save your file using this name and then upload it to Blackboard.
13. Finally, this cover sheet will form the first page of the document you submit. By submitting this document, you are agreeing to the declarations that appear on the next page:
|
Student to complete |
|
||||
|
Student Name: |
|
Student Number: |
|
||
|
Assignment |
EFB343 Individual Assignment – Valuation Project |
||||
|
Due Date: |
|
||||
|
|
|||||
|
DECLARATION: By submitting this assignment I declare that: |
|||||
|
· This work is entirely my own, and no part of it has been copied from any other person’s work, words or ideas, except as specifically acknowledged through the use of inverted commas and in-text references; |
|||||
|
· No part of this assignment has been written for me by any other person except where such collaboration has been authorised by the Unit Coordinator concerned; I understand my assignment may be scanned as part of the assessment process, and that plagiarism detection software may be utilised; |
|||||
|
· This assignment has not been submitted for any other unit at QUT or any other institution, unless authorised by the relevant Unit Coordinator; |
|||||
|
· I have read and abided by all of the requirements set down for this assignment. |
|||||
|
|
|||||
|
· If the above declaration is found to be false, you may receive reduced or zero marks for this assignment, and you will be dealt with under QUT’s Student Rule No. 29 - Academic Dishonesty, and the associated procedures for Academic Dishonesty which are available at: http://www.qut.edu.au/admin/mopp/Appendix/append01cst.html#Rule29; and http://www.qut.edu.au/admin/mopp/C/C_09_07.html |
|||||
|
I acknowledge that I have read all the instructions on this declaration sheet. |
|||||
|
|
|||||
|
|
|||||
You work for a prestigious investment bank, JP Morgan, which has been recently commissioned by the US-based e-commence giant, Amazon.com, Inc. (Amazon hereafter), to investigate potential M&A opportunities. The lead partner has asked you to provide a valuation of a possible takeover target.
Amazon is seeking to grow through M&As with the desire to expand from e-commerce into the world of bricks-and-mortar retail. The management is investigating the possibility of acquiring the major instore competitor Target Corporation (Target hereafter; Ticker Symbol: TGT). The acquisition of Target would give Amazon access to more than 1,800 physical stores, allowing Amazon to compete more aggressively with its major rival Walmart. However, Amazon management believes that the current stock price of Target Corporation is overvalued and has commissioned JP Morgan to perform a proper valuation of the company. Your lead partner has provided you with the most recent historical accounting information (10-K reports). Your task is to estimate the value of Target using the Free Cash Flow Methodology.
To help with your calculations, the lead partner has advised you to make the following assumptions. Broadly speaking, you need to use Target’s past 4 years financial data to forecast the firm’s future sales, variable cost, fixed cost, depreciation, working capital, and capital expenditure. You then need to work out the Weighted Average Cost of Capital (WACC) which will be used as the discount rate in your free cash flow analysis.
Forecasting free cash flow to the firm (FCFF):
· Compute the average sales growth rate over the last 4 years and use the rate to forecast the sales revenue in the next 4 years.
· The ratios of variable costs to sales, working capital to sales, and capital expenditure to sales are assumed to be the same as year 0 (2020). Use the cost of sales excluding depreciation and amortization as a proxy for variable costs.
· Use the average depreciation and amortization over a rolling four-year window to forecast the depreciation expense from year 1 (2021) onwards.
· Use the average fixed costs over a rolling four-year window to forecast the fixed costs from year 1 (2021) onwards. Use the selling, general and administrative (SG&A) expense as a proxy for fixed costs.
· Make assumptions about the growth rate of future net cash flow after year 4 based on your own independent research.
Estimating the discount rate (WACC):
· Get the equity beta of Target from Yahoo Finance; Risk free rate is assumed to be 3% and market risk premium is assumed to be 6%.
· Use the net interest expense divided by the book value of the noncurrent liabilities in year 0 (2020) to work out Target’s cost of debt.
· Use the book value of total liability and market value of equity as of the fiscal year-end (January 30, 2021) to work out the proportion of debt and equity (Hint: you can retrieve the current and historical stock price data for Target using Yahoo Finance; Use the adjusted closing stock price on the last trading day if the fiscal year-end , January 30, 2021, falls on a non-trading day).
· The cost of debt, cost of equity, and Debt-to-Equity (D/E) ratio are assumed to remain constant over time.
· The expected effective tax rate is 25%.
You can make additional assumptions as necessary, provided that they are not inconsistent with those of the lead partner.
(a) Calculate Target's cost of equity using CAPM. (2 marks)
(b) Calculate Target's cost of debt. (2 marks)
(c) Calculate Target's WACC. (2 marks)
(d) Provide a thorough justification for your choice of the growth rate after year 4.
Clearly state any other assumptions you have made. (3 marks)
(e) Estimate the years 1-4 free cash flow and the terminal value at the end of year 4. (12 marks)
(f) Find the value of Target. (3 marks)
(g) Compare your valuation with the current share price of Target Corporation (as of today). Based on your evaluation, would you conclude that the company’s stock is
overvalued or undervalued? (3 marks)
(h) How would your conclusion in part (g) affect Amazon’s incentive to acquire Target? (2 marks)
(i) Determine how sensitive your valuation is to a ±1% change in the sales growth rate, i.e., the average sales growth rate over the last 4 years. Discuss the impact of such
changes on your conclusion. (5 marks)
(j) Discuss any limitations of your analysis. Your discussion should include a critical evaluation of your assumption(s) and those of your lead partner, especially those in relation to the use of the historical data for year 2020. (6 marks)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
