The objective of this assignment is to encourage students to use Excel spread sheets to aid in solving a capital budgeting problem.

The objective of this assignment is to encourage students to use Excel spread sheets to aid in solving a capital budgeting problem.

Weight: 10% of total assessment.

You must contact your Lecturer directly and make alternative arrangements should you not be able to submit your assignment in class.

Late submissions: Late assignments cannot be accepted unless a time extension has been requested from, and approved by, the Unit Convenor. Such requests must be made in writing via email.

Format: This assignment is a problem solving exercise, using Excel spread sheets, with discussion of findings.

Details

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 out-dated, 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,000 with 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.

Required

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 no more than 1,000 words, stating any assumptions made, and explaining the bases for your recommendation as to whether or not the company should replace its existing machine with a new one.

Marking

Marks will be awarded for:

• Setting up of spread sheet

i. Ease of reading spread sheet

ii. Use of formulae in spread sheet

iii. Correct application of theoretical model

• Writing of report

i. Statement of assumptions

ii. Correct recommendation based on analyses

iii. Coherence of arguments

iv. Presentation, grammatical structure, referencing, etc.

Refer to the corresponding marking rubric on the Black Board site for this Unit.