Question 1 (10 marks) Your company is doing well and has a profit of about $50,0

Question

Question 1 (10 marks)

Your company is doing well and has a profit of about $50,000. You want to make your profit work harder so you have looked at some investment opportunities available. They are

To insulate the current company offices at a cost of $18,000 which will provide a fuel savings of $1,600 per year over the next 10 years.

To pay the lump sum of $30,000 to the mortgage of $40,000 that has a loan term of 10 years at 6.5% interest per annum.

To invest $20,000 into a new business, which has been estimated to return the double amount in 6 years’ time.

To keep the profit $50,000 in an iSaver account earning a fixed interest of 4.5% per annum for 8 years. After 8 years, there will be no interest for this iSaver account.

Assumptions:

If only a part of the profit $50,000 is invested in a project, the remaining amount is still kept in the pocket (i.e. no investment).

The investment/cost occurs at the beginning of a year, and the investment benefits are obtained at the end of a year.

iSaver account calculates the compound interest.

Given the profit you have and assuming a discount rate of 4.8%, perform and document appropriate NPV calculations for 10 years for all possible investment options you identified. Graph the results to show the payback analysis of each investment option.

You must use Microsoft Excel or an equivalent spreadsheet program to perform and present your calculations.

Do not use the Excel NPV function and demonstrate you understand the formulas by calculating the NPV. You may confirm the calculated NPV using the Excel NPV function.

Ensure you format the spreadsheets so they use references rather than hard coding data. This allows experimenting with the data, e.g., if I change the discount rate the data should react and change accordingly.

Use bolding, colors etc. to ensure the data is easy to interpret.

[7 marks]

(b)   From your calculations in (a), which investment would you take up and why?

Use a Weighted Scoring Model to discuss the results and explain in detail why you have chosen one option over the others. It is not always just the figures that determine the final decision.

Explanation / Answer

a.

Option 1: Insulating current company offices:

Initial cost = $18000

Fuel savings per year = $1600 for 10 years

NPV = 1600 x Cumulative PVF @ 4.8% for 10 years - 18000

= 1600 x 7.797 - 18000

= -$5525

Option II: Payment of mortgage loan:

Interest savings over 10 years = 30000 x (1.065)^10 - 30000 = $26314

NPV = 26314 x PVF for 10th year = 26314 x 0.626 = $16473

Option III: Profit at the end of sixth year = 20000 x 2 - 20000 = $20000

This amount will be invested at the end of 6th year till the end of 10th year @ 4.8% p.a.

Profit amount at the end of 10th year = 20000 x (1.048)^4 = $24125

NPV = 24125 x PVF for 10th year

= 24125 x 0.626

= $15102

Option IV: Interest income at the end of 8 years = 50000 x 1.045^8 - 50000 = $21105

This amount will be invested at the end of 8th year till the end of 10th year @ 4.8% p.a

Profit amount at the end of 10th year = 21105 x (1.048)^2 = $23180

NPV = 23180 x PVF for 10th year

= 23180 x 0.626

= $14511

Option III gives the highest NPV.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.