Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / your excel spreadsheet as a supporting document
your excel spreadsheet as a supporting document. Upload your excel spreadsheet under "Excel Submissions”. All amounts are in $AUD. The “A2M” board of directors (BOD) is exploring the opportunity to vertically integrate the business by acquiring one of its current suppliers. The BoD has instructed, one of the Big 4 Consulting firms to perform a screening process amongst the best dairy farms in Australia with the goal of selecting potential candidates. The firm is asking $100,000 dollars as a fixed fee for its consulting services. The report generated by the consulting firm has identified two different dairy farms that can fit the "A2M” business model. Project A has an initial outlay of dollars $100 million and Project B has an initial outlay of $150 million. Project A will produce 85,000,000 liters of milk starting at the end of year 1 until the end of year 5 and 50,000,000 liters of milk starting at the end of year 6 until the end of year 10. It will also incur working capital expenses at the end of year 6 to 9 of $5 million (this working capital will not be recovered). Project B will produce 100,000,000 liters of milk starting at the end of year 1 until the end of year 10. It will also incur working capital expenses at the end of year 1 to 3 of $2 million (this working capital will not be recovered). Assume that the average selling price (farmgate price) of a liter of milk is $0.5 over the ten years. The operating costs of both projects will be 30% of the revenues from year 1-10. Both investments will be depreciated on a straight-line basis over ten years to 0 book value. "A2M” has estimated that the dairy farms can be sold at the end of year 10 respectively for $50 million (Project A) and 75 million (Project B).The tax rate is 30%. All cash flows are annual and are received at the end of the year. The weighted average cost of capital for both projects is 10%. a) Calculate the FCFs to each project [10 marks] b) What is the NPV for each project? [5 marks] c) What is the Discounted Payback Period for each project? [5 marks] d) What is the IRR for each project? [5 marks] e) Suppose that the “A2M” management payback rule is 6 years. Based on your analysis in b), c) and e) which project should be chosen? Justify your answer with reference to theory. What other elements could be taken into consideration when selecting the project?
| Sales in lts | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 |
| Project A :Sales in lts | 85 | 85 | 85 | 85 | 85 | 50 | 50 | 50 | 50 | 50 | |
| Project B :Sales in lts | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
| Capital Project Evaluation: Project A | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | |
| a | Initial Investment | (100.00) | ||||||||||
| b | Working Capital investment | -5 | -5 | -5 | -5 | |||||||
| Cash floe from operating Activities | ||||||||||||
| Sales Revenue @$0.5/lt | 42.5 | 42.5 | 42.5 | 42.5 | 42.5 | 25 | 25 | 25 | 25 | 25 | ||
| Operating cost @30% of revenue | 12.75 | 12.75 | 12.75 | 12.75 | 12.75 | 7.5 | 7.5 | 7.5 | 7.5 | 7.5 | ||
| Depreciation expense | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | ||
| Taxable income | 19.75 | 19.75 | 19.75 | 19.75 | 19.75 | 7.5 | 7.5 | 7.5 | 7.5 | 7.5 | ||
| Tax @30% | 5.925 | 5.925 | 5.925 | 5.925 | 5.925 | 2.25 | 2.25 | 2.25 | 2.25 | 2.25 | ||
| After Tax Income | 13.825 | 13.825 | 13.825 | 13.825 | 13.825 | 5.25 | 5.25 | 5.25 | 5.25 | 5.25 | ||
| Add back depreciation | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | ||
| c | Net Cash flow from Operations | 23.825 | 23.825 | 23.825 | 23.825 | 23.825 | 15.25 | 15.25 | 15.25 | 15.25 | 15.25 | |
| d | After Tax Salvage value =$50M*(1-30%)= | 35 | ||||||||||
| e(Ans a) | FCF from the project =a+b+c+d | (100.00) | 23.83 | 23.83 | 23.83 | 23.83 | 23.83 | 10.25 | 10.25 | 10.25 | 10.25 | 50.25 |
| f | PV factor @10% =1/1.1^n | 1.0000 | 0.9091 | 0.8264 | 0.7513 | 0.6830 | 0.6209 | 0.5645 | 0.5132 | 0.4665 | 0.4241 | 0.3855 |
| g | PV of Cash flows =e*f= | (100.00) | 21.66 | 19.69 | 17.90 | 16.27 | 14.79 | 5.79 | 5.26 | 4.78 | 4.35 | 19.37 |
| h | NPV =Sum of PV of Cash flow= | 29.86 |
| i | Discounted Payback in years= | 6.74 |
| j | IRR (using excel formula0= | 16.86% |
| k | Payback period in years | 4.20 |
due to space constraint, putting shortened ver of Project B by putting sales vol and depreciation data for project B
| Capital Project Evaluation: Project B | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 |
| a | Initial Investment | (150.00) | |||
| b | Working Capital investment | -2 | -2 | -2 |
| c | Net Cash flow from Operations | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 |
| d | After Tax Salvage value =$75M*(1-30%)= | 52.5 | ||||||||||
| e(Ans a) | FCF from the project =a+b+c+d | (150.00) | 27.00 | 27.00 | 27.00 | 29.00 | 29.00 | 29.00 | 29.00 | 29.00 | 29.00 | 81.50 |
| f | PV factor @10% =1/1.1^n | 1.0000 | 0.9091 | 0.8264 | 0.7513 | 0.6830 | 0.6209 | 0.5645 | 0.5132 | 0.4665 | 0.4241 | 0.3855 |
| g | PV of Cash flows =e*f= | (150.00) | 24.55 | 22.31 | 20.29 | 19.81 | 18.01 | 16.37 | 14.88 | 13.53 | 12.30 | 31.42 |
| h | NPV =Sum of PV of Cash flow= | 43.46 |
| i | Discounted Payback in years= | 8.02 |
| j | IRR (using excel formula0= | 15.61% |
| k | Payback period in years | 5.37 |
| Ans e. | If we consider undiscounted Payback , the both the projects habe pyaback period below 6 years. | |||
| However, Project B has higher NPV . So to maximize investor's wealth, | ||||
| project B should be chosen. |
