Corporate Finance
______________________________________________________________
The objective of this assignment is to encourage students to use Excel spread sheets to aid in solving a capital budgeting problem.
Format: This assignment is a problem solving exercise, using Excel spread sheets, with discussion of findings.
Details
Riverlea Limited is a company that specialises in the production of quality farm gates. the production process relies on the use machinery as well in order to expedite some of the tasks.
The company is considering the replacement of one of its machines, which it has purchased three years ago for $850,000. 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 nothing.
If the company decides to replace the existing machine, the new one is expected to cost$1,800,000 with an additional $20,000 to install it. The new machine is expected to have a useful life of seven 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 but the chief financial officer has decided to ignore the residual value when setting the depreciation amount per annum.
If the company decides to replace the existing machine, it is expected that operating costs would drop from $620,000 per annum down to $200,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 requires you to calculate an appropriate discount rate from the market information below. The Company tax rate is 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 a 30% probability that operating costs for the new machine would be lower than expected by 20% in each of the fifth ,sixth and seventh years; also, allow for a 10% probability that operating costs for the new machine would be higher than expected by 20% 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 for the Surf Waves
i. Ease of reading spread sheet
ii. Use of formulae in spread sheet
iii. Correct application of theoretical model
• Setting up the spread sheet for deriving the discount rate
• Writing of report
iv. Statement of assumptions
v. Correct recommendation based on analyses
vi. Coherence of arguments
vii. Presentation, grammatical structure, referencing, etc.
viii. Discussion of the appropriateness of the discount rate
Refer to the corresponding marking rubric on the Black Board site for this Unit.
Construct a spread sheet to derive the discount rate from the market information below
Market Information: Column 4 contains the annual returns Surf the Waves
Year (1)
Market Return (Ordi. Shares) (%) (2)
Risk-Free Rate
BONDS (%) (3)
Excess Market Return (m)
(1)-(2) (4)
Stock return (5)
Market Deviation from Ave Return (6)
Squared Deviation (7)
Stock Deviation from Ave Return (8)
Squared Deviation (9)
Cross Product
(5) x (7)
1 26.60 7.80 18.80 28.4 13.24 175.3
2 1.60 6.10 -4.5 -3.0 -11.76 138.3
3 15.30 5.50 9.8 14.1 1.94 3.76
4 15.50 6.40 9.10 14.3 2.14 4.58
5 9.10 5.80 3.30 8.5 -4.26 18.15
6 -4.70 6.00 -10.7 -6.67 -18.06 326.16
7 -1.70 5.40 -7.10 -8.00 -15.06 226.8
8 21.60 5.50 16.10 18.00 8.24 67.90
9 26.40 5.50 20.90 23.10 13.04 170.04
10 23.90 5.40 18.50 20.60 10.54 111.09
Total 133.6 59.4 74.2 0 1242.08
Average Return 13.36 5.94 7.42
You need to calculate the required rate of return to be used in evaluating the project.
Riverlea is totally Equity Financed.
For this exercise you can assume that the current 10 year Bond Rate is 5.94%.
You are required to complete columns 7, 8, 9 and solve Ke=Rf + ß (rm-rf)
