Question

In: Finance

Calculations must be done in Excel As the financial advisor to Upmarket Car Rentals you are...

Calculations must be done in Excel

As the financial advisor to Upmarket Car Rentals you are evaluating the following types of cars to add to the fleet: -

Speedster: - A sporty convertible with a cost of $120,000 and a useful life of 4 years. It will produce rental income of $80,000 per year and operating costs of $15,000 per year. A major service is required after 2 years costing $20,000. A salvage value of $30,000 is expected after 4 years. The required return is 8%.

Cruncher: - A rugged off-road vehicle costing $180,000 but with an expected useful life of only 2 years, due to the harsh conditions. It will produce rental income of $150,000 per year and operating costs of $20,000 per year. A major service is required after 1 years costing $30,000. A salvage value of $45,000 is expected after 2 years. The required rate of return is 10%.

Income tax can be ignored.

Required

  1. The NPV’s of the two cars.
  2. An analysis of the two cars assuming they are mutually exclusive and can be repeated indefinitely.

Please provide both the Excel calculation + explanation of how the answer was derived. Thank you.

Solutions

Expert Solution

NPV computation

Speedster

Cost

    120,000.00

Useful life

4 years

Rental income

      80,000.00

Operating costs

      15,000.00

Service cost after 2 years

      20,000.00

Salvage value

      30,000.00

Cashflow table

Year

0

1

2

3

4

Initial cost

   (120,000.00)

Total Initial cashflows

(120,000.00)

Rental income

     80,000.00

     80,000.00

80,000.00

80,000.00

Less : Operating costs

     15,000.00

     15,000.00

15,000.00

15,000.00

Less : Service at the end of 2 years

     20,000.00

Total operating cashflows

     65,000.00

     45,000.00

65,000.00

65,000.00

Terminal cashflows

Salvage value

30,000.00

Net cashflows (Initial + Operating + Terminal)

(120,000.00)

65,000.00

45,000.00

65,000.00

95,000.00

PV factor @ 8% ---> 1/ (1+8%)^nth year

1.00

0.93

0.86

0.79

0.74

PV of cashflows (Net cashflows x PV factor)

(120,000.00)

60,185.19

38,580.25

51,599.10

69,827.84

NPV ---> Sum of PV of cashflows

100,192.36

Cruncher

Cost

    180,000.00

Useful life

2 years

Rental income

    150,000.00

Operating costs

      20,000.00

Service cost after 1 years

      30,000.00

Salvage value

      45,000.00

Cashflow table

Year

0

1

2

Initial cost

   (180,000.00)

Total Initial cashflows

(180,000.00)

Rental income

   150,000.00

   150,000.00

Less : Operating costs

     20,000.00

     20,000.00

Less : Service at the end of 1 year

     30,000.00

Total operating cashflows

100,000.00

130,000.00

Terminal cashflows

Salvage value

     45,000.00

Net cashflows (Initial + Operating + Terminal)

(180,000.00)

100,000.00

175,000.00

PV factor @ 10% ---> 1/ (1+10%)^nth year

                1.00

               0.91

               0.83

PV of cashflows (Net cashflows x PV factor)

(180,000.00)

     90,909.09

144,628.10

NPV ---> Sum of PV of cashflows

55,537.19

Unable to provide the complete answer on account of space constraint. Please let me know if you need the googledrive lnk with the complete answer spreadsheet.

Hope this helps you answer the question. Please provide your feedback or rating on the answer.

Thanks


Related Solutions

Project 1 Calculations must be done in Excel As the financial advisor to Upmarket Car Rentals...
Project 1 Calculations must be done in Excel As the financial advisor to Upmarket Car Rentals you are evaluating the following types of cars to add to the fleet: - 1. Speedster: - A sporty convertible with a cost of $120,000 and a useful life of 4 years. It will produce rental income of $80,000 per year and operating costs of $15,000 per year. A major service is required after 2 years costing $20,000. A salvage value of $30,000 is...
Project 1 Calculations must be done in Excel Fergy Smith, the financial advisor to Uncovered Car...
Project 1 Calculations must be done in Excel Fergy Smith, the financial advisor to Uncovered Car Rentals is evaluating the following types of cars to add to the fleet:- Topless:- A sporty convertible with a cost of $100,000 and a useful life of 5 years. It will produce rental income of $60,000 per year and operating costs of $10,000 per year. A major service is required after 3 years costing $15,000. A salvage value of $25,000 is expected after 5...
Calculations must be done in Excel – You must create your own spreadsheet do not copy...
Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s. Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and...
Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do...
Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do not copy and paste someone else’s). This question should be done using Method 1 as outlined in lecture 6 (i.e. Tax Effects, then Cash Flows then NPV) As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: - The project has a useful life of 12 years. Land costs $6m and is estimated to have...
Calculations must be done in Excel - Using the capital budgeting method of; Tax Effects, then...
Calculations must be done in Excel - Using the capital budgeting method of; Tax Effects, then Cash flows, then NPV. As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: - The project has a useful life of 12 years. Land costs $6m and is estimated to have a resale value of $10m at the completion of the project. Buildings cost $5m, with allowable depreciation of 10% pa reducing balance and...
Calculations must be done in Excel Polycorp is considering an investment in new plant of $3.2...
Calculations must be done in Excel Polycorp is considering an investment in new plant of $3.2 million. The project will be partially financed with a loan of $2,000,000 which will be repaid over the next five years in equal annual end of year instalments at a rate of 6.20 percent pa. The rest of the project will be financed by equity. Assume straight-line depreciation over a five-year life, and no taxes. The project’s cash flows before loan repayments and interest...
Project 1 Calculations must be done in Excel Polycorp is considering an investment in new plant...
Project 1 Calculations must be done in Excel Polycorp is considering an investment in new plant of $3 million. The project will be partially financed by a loan of $2 million, which will be repaid over five years in equal annual end of year instalments at a rate of 6.5 percent pa. The rest of the project will be financed by equity. Assume straight-line depreciation over a five-year life, and no taxes. The project’s cash flows before loan repayments and...
NEEDS TO BE DONE IN EXCEL WITH CALCULATIONS. An ARM for 100,000 is made at the...
NEEDS TO BE DONE IN EXCEL WITH CALCULATIONS. An ARM for 100,000 is made at the time when the expected start rate is 5 percent. The Loan will be made with a teaser rate of 2 percent for the first year, after which the rate will be reset. The loan is fully amortizing, has a maturity of 25 years, and payments will be made monthly. a. What will be the payments during the first year? b. Assuming that the reset...
NEEDS TO BE DONE IN EXCEL WITH CALCULATIONS. An investor has $60,000 to invest in a...
NEEDS TO BE DONE IN EXCEL WITH CALCULATIONS. An investor has $60,000 to invest in a 280,000 property. He can obtain either a $220,000 loan at 9.5 percent for 20 years or a $180,000 loan at 9 percent for 20 years and second mortgage for $40,000 at 13 percent for 20 years. All loans require monthly payments and are fully amortizing a. Which alternative should the borrower choose, assuming he will own the property for the full loan term? b....
Use Excel to complete the following question. You must submit/attach all work done in Excel in...
Use Excel to complete the following question. You must submit/attach all work done in Excel in order to receive full credit. 5. A health advocacy group conducted a study to determine if the nicotine content of a particular brand of cigarettes was equal to the advertised amount. The cigarette brand advertised that the average nicotine content per cigarette was 1.4 milligrams. The advocacy group randomly sampled 24 cigarettes. The nicotine level for each of the sampled cigarettes is given below....
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT