Can you send the excel file if I provide an email address?

This is a question on managerial decision making withexcel spreadsheet. A step wise how to solve this will beappreciated for homework and not any exam. A model in excel willreally help.

John’s Truck

John owns John’s Truck Rental, which is currently for sale for$650,000. He would like you to develop a five-year spreadsheetmodel to assist buyers in their evaluation of the company.

The company currently has a fleet of 100 trucks, which arerented out for $15,000 per year. At this rental rate, the averageutilization of the fleet is 62%. He believes that for every $1,500decrease in the annual rental rate, the utilization willincrease by 7%. Conversely, the same sizeincrease in the rental rate will lead to adecrease of the same amount. The spreadsheet model shouldbe able to handle a change in rental rate and calculate thecorresponding change in utilization- the user should not be limitedto increments of $1,500 only (assume that fractional values ofutilization are acceptable as these are annual averages ofutilization). The annual rental rate should be set once and heldsteady for all five years of the model.

The cost of maintaining the fleet runs $4,800 per truck per year(regardless of utilization), which includes inspection fees,licenses, normal maintenance, and storage. These maintenance costsare expected to increase by 9% per year. The company has fixedoffice costs of $60,000 per year, which are expected to remainstable, and pays property taxes of $35,000, which is expected togrow at a rate of 3% per year. Profits are subject to a 30% incometax rate (and losses, if incurred, are not taxed).

At the end of five years, it is assumed that the buyer willresell the business for cash. John estimates the selling price atthat time to be three times the gross revenue in that final year.Cash flow in the final year would include cash from the sale of thebusiness, which would be taxed at the same rate as annual income(and we will assume full depreciation of the trucks has alreadyoccurred, thus no tax effects). Investment profit for the buyers isdefined to be the Net Present Value of the annual cash flows (alloperating revenues and expenses are in cash), computed at adiscount rate of 10%. The calculation of NPV includes the purchaseprice, incurred at the beginning of Year 1, and net income from theoperations (include the sale price in Year 5) over five years.

To do

1. Build the spreadsheet model to solve this problem. Make sureyou follow the guidelines for good spreadsheet design includingproper cell referencing, good flow/layout, etc. Your grade on thisportion of the HW reflects having correct calculations, followingguidelines and creating a well-designed and easy-to-use worksheet.
   • Use Excel functions to help you, as necessary.

1. John is thinking about raising the selling price. Use Goal Seekto find the point at which the NPV hits $1 million (John considersthe point at which it might be hard to find a buyer).
   • What is the selling price at which this NPV occurs? Write theanswer to this question somewhere (easy to find) on yourworksheet.
   • Make sure to reset the selling price to the base valuebefore proceeding to Task 3!

1. John would to have some additional analysis available on thespreadsheet to provide the potential buyers with information thatmight help inform their decision. He would like you to analyze theeffect of the annual rental rate on the NPV. You recommend using aData Table. Build this table on the same worksheet as your model,using values of annual rental rate ranging from $10,500 to $19,500in increments of $1,500. Beyond including the corresponding NPVoutput values, you want to include the utilization rates in thistable to confirm for the potential buyer that these changes arebeing calculated correctly.
   • Based on the results of the table alone, what is the annualrental rate that is most attractive to a potential buyer?

Homework Submission Instructions:

• Upload your completed Excel workbook
• Make sure that your model has the correct base-case inputvalues and results!
• Use at least 2 decimal places for any fractional value.