Project 1Calculations must be done in Excel

The financial advisor to PQR Company is evaluating whether to keep company cars for 3, 4, 5 or 6 years. The following information has been gathered:-

A car costs $80,000 and depreciation is 25% pa straight-line.

Cash expenses are $20,000 per year for the first 2 years, $30,000 in year 3, $40,000 in year 4, $50,000 in year 5 and $60,000 in year 6.

The salvage value is expected to be $30,000 after 3 years, $25,000 after 4 years, $20,000 after 5 years and $10,000 after 6 years.

The after tax required rate of return is 10% pa and the tax rate is 30%.

Required

Prepare an analysis to decide whether company cars should be kept for 3, 4, 5 or 6 years before being replaced with new ones.