Refer to the Big Rig Rental Company case. Design a spreadsheet that will allow the firm to determine the Net Present Value of cash flows over the five-year period.
The following exercises refer to the cases in the back of this book. Read the case description carefully, sketch an appropriate spreadsheet on paper, and then build and test a prototype model. The purpose of the model is specified in the question.
Refer to the Big Rig Rental Company case,
The Big Rig Rental Company, which owns and rents out 50 trucks, is for sale for $400,000. Tom Grossman, the company's owner, wants you to develop a five-year economic analysis to assist buyers in evaluating the company.
The market rate for truck rentals is currently $12,000 per year per truck. At this base rate, an average of 62 percent of the trucks will be rented each year. Tom believes that if the rent were lowered by $1,200 per truck per year, utilization would increase by seven percentage points. He also believes that this relationship would apply to additional reductions in the base rate. For example, at a $7,200 rental rate, 90 percent of the trucks would be rented. This relationship would apply to increases in the base rate as well. Over the next five years, the base rental rate should remain stable.
At the end of five years, it is assumed that the buyer will resell the business for cash. Tom estimates that the selling price will be three times the gross revenue in the final year.
The cost of maintaining the fleet runs about $4,800 per truck per year (independent of utilization), which includes inspection fees, licenses, and normal maintenance. Big Rig has fixed office costs of $60,000 per year and pays property taxes of $35,000 per year. Property taxes are expected to grow at a rate of 3 percent per year, and maintenance costs are expected to grow 9 percent per year due to the age of the fleet. However, office costs are predicted to remain level. Profits are subject to a 30 percent income tax. The tax is zero if profit is negative.
Cash flow in the final year would include cash from the sale of the business. Because the trucks have all been fully depreciated, there are no complicating tax effects: Revenue from the sale of the business will effectively be taxed at the 30 percent rate. Investment profit for the buyer is defined to be the Net Present Value of the annual cash flows, computed at a discount rate of 10 percent. (All operating revenues and expenses are in cash.) The calculation of NPV includes the purchase price, incurred at the beginning of year 1, and net income from operations (including the sale price in year 5) over five years (incurred at the end of the year). There would be no purchases or sales of trucks during the five years.