A company is considering the replacement of one of its machines, which it has purchased three years ago for $800,000. Although this machine still has a remaining useful life of seven years, management believes that it is outdated, and inefficient when compared to new available technologies. The existing machine has been depreciated using the straight-line method at the rate of 10% per annum. If the existing machine is disposed of now, it is expected to sell for $400,000 before taxes. However, if the company decides to keep the existing machine for its remaining useful life of seven years, and dispose of it at the end of its useful life, it is expected to be worth $0
If the company decides to replace the existing machine, the new one is expected to cost $1,200,000with an additional $200,000 to install it. The new machine is expected to have a useful life of 7 years, and it will be depreciated over its useful life using the straight-line method. It is expected that the new machine can be sold for $200,000 at the end of its useful life.
If the company decides to replace the existing machine,it is expected that operating costs would drop from $450,000 per annum down to $170,000 per annum. Moreover, there would be an initial investment of $40,000 in stock and debtors, offset by an increase of $20,000 in creditors.
The company uses a 10%discount rate for such projects, and pays 30% tax per annum.
The Chief Executive Officer (CEO) is hesitant about the decision because of uncertainty in relation to some of the expected cash flows. Accordingly, the CEO suggests conducting sensitivity analysis as follows: allow for the predicted operating costs of $170,000 for the new machine to be higher by 15% in each of the fifth, sixth and seventh years; also, allow for the probability that operating costs of the new machine would be lower by 15% in each of the fifth, sixth and seventh years.
You are to prepare a spread sheet, to present to the CEO, showing the various cash flows based on the different scenarios; assuming that the company decides to replace the existing machine with a new one.
Use Excel to prepare a full analysis, evaluating whether or not the existing machine should be replaced with a new one, taking into consideration the various scenarios.
Show all formulae, adjacent to the corresponding calculated amounts in the spread sheet.
Write a report of stating any assumptions made and explaining the basis for your recommendation as to whether or not the company should replace its existing machine with a new one.