Workings on a separate excel sheet pls

GSBS6130 Corporate Finance
Individual Assignment
Trimester 2, 2019
Weight: 25%
Total Marks: 25
Due time and date: Week 10, start of scheduled class: Wednesday (Newcastle), Monday (Sydney)
Instructions:
1. Maximum word length: 2500 words, excluding graphs, tables, references and appendices.
2. You need to submit the assignment in MS Word format, with an assignment cover sheet included, via Turnitin by the due date.
3. An identical hard copy must be submitted in class (at the start of the session) on the due date.
4. In addition, an Excel file with supporting calculations (if any) must be submitted online to Assignment_Supporting Excel File link on blackboard by the due date. You will need to include your student ID and your name in the Excel file name (for example, cxxxxxxx_FirstName_LastName_assignment). The word file should be a stand-alone report which will be marked. The Excel spreadsheet will not be marked, it is only submitted so that your working out can be checked as required. You may make reasonable assumption (if necessary).
5. Referencing style: American Psychological Association (APA) 6th Edition.
Penalties:
1. In the case of late submission without an approved extension of time, a penalty of 10% will apply for each day the assignment item is late.
2. Penalties will also apply if academic misconduct is found (refer to the Course Outline for more information).
Question 1 (10 marks)
You are the financial executive of Coffee Manufacturing Company (CMC), the manufacturing division has been evaluating various new coffee grinding machines to replace its fully depreciated old grinder. The new coffee grinder is to be used in production in an effort to improve the production process and reduce maintenance costs. To date they have spent $126,000 evaluating five different grinding machines and have now settled on a German Grinding Machine GGM as it will improve efficiency of the production process and reduce raw material requirements. In addition, GGM produces a by-product that can be sold instead of rubbish. The new GGM machine will cost $150,000. CMC is considering using a bank loan to finance the new machine purchase at an interest rate of 8.75% p.a. with payments of $31,500 per year.
The old existing machine has been fully depreciated for tax purposes but can be sold today for $25,000. Due to the complexity of the GGM machine it will last 5 years before requiring replacement. The old machine was very compact and occupied a small amount of CMC’s factory space. CMC has been renting its spare factory space to a third party for $35,000 per year. Due to the by-products generated by the new machine and the bigger size of GGM, CMC will have to cancel the existing lease with the external tenant and take over the space. This will require a once-off compensation payment to the tenant of $21,000.
The GGM machine will have a limited salvage value at the end of the five-year life. CMC estimates the salvage value of $67,000 for this machine at the end of 5 years. The GGM machine will have to be serviced by GGM specialists. The service contract will cost $54,000 annually, which is less than the current $74,000 maintenance cost spent on the old one. The main reason to buy the GGM machine is because it is more efficient and productive to run. CMC will be able to increase sales to $130,000 per year and, due to efficiency gains while COGS are expected to decrease by $39,000 per year. The byproduct will be sold for $9,000 per year and is in addition to sale improvement. No additional costs will be incurred in generating sales of the by-product.
The more efficient output of the machine will enable CMC to reduce its holdings of raw materials by $66,000 at the beginning of the first year. CMC conducts all its capital evaluations in constant dollars and has a management deprecation policy that requires depreciating all long-term assets over a tenyear life straight-line. The management of CMC is concerned about the consulting costs of $126,000 already paid to consultants and have suggested that this cost should be spread over the 5 years of the projects life. CMC’s tax rate is 30% and they use a nominal required rate of 14%pa. They expect inflation over the five-year period to be 3%pa.
Answer the following questions.
a) What cash flows occur from selling the old machine? Hint: Consider tax. (1 marks)
b) Draft two depreciation tables for the new machine. One that is based on CMC’s depreciation policy and one that is based on an accelerated depreciation approach with a 10-year life .
Discuss the differences. Which one would you prefer? (3 marks)
c) Calculate the NPV of the project, using the components of a) and b) (note: use CMC’s depreciation policy). (3 marks)
d) Based on your analysis, should CMC proceed with the project? (1 mark)
e) Perform a sensitivity analysis where (1) changes in GOGS and (2) salvage value of the GGM machine is either 50% higher or 50% lower. Discuss your findings. (2 marks)
Question 2 (15 marks)
You are a research analyst in an investment bank. You need to write a research report on the sensitivity of the stock return to the market return, that is, you need to estimate systematic risk of a group of stocks. The following model can be used to estimate systematic risk or the sensitivity of the stock return to the market return:
????,t = a0 + ß1????,?? + ????,?? (1)
where Ri,t is stock return on the ith institutions; Rm,t is return on the S&P/ASX all Ordinaries Index
(^AORD.AX); and ????,?? is the random error term at day t. You are required to estimate the market model (equation 1) for the following companies:
• Bega Cheese Ltd ASX: BGA
• Amcor Limited ASX:AMC
• Oilsearch Limited ASX:OSH
• Telstra Corporation Ltd ASX:TLS
Data sources
Daily adjusted closing stock prices for companies and the All Ordinaries Index:
http://au.finance.yahoo.com
Estimation period
January 1, 2010–December 31, 2018
Required
Write a research report based on the market model as given in Equation 1 with the following parts:
(a) Introduction: Summarise the intuition of the model and discuss how each company may be sensitive to market return. (3 marks)
(b) Methodology: Discuss the model, sources of data and the steps you have taken in your analysis. (4 marks)
(c) Findings: Discuss the results from the model. Reflect your expectations from a). Were your expectations met? (4 marks)
(d) Conclusion: Discuss the implications & shortcomings of the model. (3 marks) (e) References. (1 mark)