Tammys Truck Rentals

Tammy Grossman owns Tammys Truck Rentals, which is currently for sale with a price of $650,000. She would like you to develop a five-year spreadsheet model to assist potential buyers in their evaluation of the company.

The company currently has a fleet of 100 trucks, which are rented out for $15,000 per year. At this rental rate, the average utilization of the fleet is 62%. Tammy believes that for every $1,500 decrease in the annual rental rate, the utilization will increase by 7%. Conversely, the same size increase in the rental rate will lead to a decrease of the same amount. The spreadsheet model should be able to handle any change in rental rate and calculate the corresponding change in utilization, the user should not be limited to increments of $1,500 only (assume that fractional values of utilization are acceptable as these are annual averages of utilization). Note: this will require the creation of an Excel formula to handle this calculation; and you can use several small formulas rather than one big formula if that is easier. The annual rental rate should be set once and held steady 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 costs are expected to increase by 9% per year. The company has fixed office costs of $60,000 per year, which are expected to remain stable, and pays property taxes of $35,000, which is expected to grow at a rate of 3% per year. Profits are subject to a 30% income tax rate (and losses, if incurred, are not taxed). All values provided are to be used for Year 1, the increases begin applying in Year 2.

At the end of five years, it is assumed that the buyer will resell the business for cash. Tammy estimates the selling price at that 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 the business, which would be taxed at the same rate as annual income (and we will assume full depreciation of the trucks has already occurred, thus no tax effects). Investment profit for the buyers is defined to be the Net Present Value of the annual cash flows (all operating revenues and expenses are in cash), computed at a discount rate of 10%. The calculation of NPV includes the purchase price, incurred at the beginning of Year 1, and net income from the operations (include the sale price in Year 5) over five years.

Tasks:

1. [80 points] Build the spreadsheet model to solve this problem. Make sure you follow the guidelines for good spreadsheet design including proper cell referencing, good flow/layout, etc. Your grade on this portion of the HW reflects not only on having the correct calculations, but also for following those guidelines and creating a well-designed and easy-to-use worksheet.
   • I will not be collecting influence diagrams, written calculations, or other materials that can help with formulating the black box portion of the model, but please use these tools if necessary to facilitate your spreadsheet building process.
   • Use Excel functions to help you, as necessary.

2. [5 points] Tammy is thinking about raising the selling price. Use Goal Seek to find the point at which the NPV hits $1 million (Tammy considers the point at which it might be hard to find a buyer).
   • What is the selling price at which this NPV occurs? Write the answer to this question somewhere (easy to find) on your worksheet.
   • Make sure to reset the selling price to the base value before proceeding to Task 3!

3. [15 points] Tammy would to have some additional analysis available on the spreadsheet to provide the potential buyers with information that might help inform their decision. She would like you to analyze the effect of the annual rental rate on the NPV. You recommend using a Data Table. Build this table on the same worksheet as your model, using values of annual rental rate ranging from $10,500 to $19,500 in increments of $1,500. Beyond including the corresponding NPV output values, you want to include the utilization rates in this table to confirm for the potential buyer that these changes are being calculated correctly.
   • Based on the results of the table alone, what is the annual rental rate that is most attractive to a potential buyer?