Question

Can you answer this in excel showing formulas? thank you

TommyHawk Bus Company is evaluating a proposed project to provide charter trips to several state and national cultural events that will be taking place throughout the country during the next few years. To make the buses more comfortable and the trips more popular, each of eight new buses will be equipped with a kitchen and a large video screen for movies and sports telecasts.

Each bus costs $200,000 and requires customized equipment that costs an additional $100,000 per bus. All the buses and the equipment have a recovery period (MACRS life) of 5 years. An advertising campaign, which will start immediately, costs $750,000 and will be paid for immediately. Other start-up costs total $75,000.

The sports trips are scheduled for 3-10 days and will generate revenues of $100 per day per seat. For the eight video buses, total revenues are expected to be $3,168,000 for the first year and $3,484800 for the second year (a 10% increase), and increase at a rate of 10% each year for the life of the project. Operating costs are 35% of total revenues. Personnel expenses, including driver salaries, are $650,000 in year 1 and will increase by 10% in year 2 and each year for the life of the project.

The company expects to discontinue operation of its video bus tours after the third year. Each bus will be sold to a university for $65,000 ($55,000 for the bus itself and $10,000 for the equipment). The finance department has determined that the beta of this project is 1.75, while the market return is expected to be 10%. Treasury bills pay a nice safe 2 percent. Hawk Transport can finance the entire project with a bank loan from their local First Guaranty Bank at 9%.

Should the company proceed with this project? Demonstrate your decision by answering the following questions (build a cash flow estimate, and in it show):

What are the cash flows for the initial investment period (CF0)?

What is the MACRS depreciation schedule for the of the project for all of the buses and the equipment? Include this in your Income Statement (*and Cash Flow) They fall into the MACRS 5-year class. See below

What are the after-tax cash flows for the 2 years that the bus tours will be in operation if their tax rate is 24%?

What are the termination cash flows in year 2?

If NPV analysis is used, should the buses be purchased?

Answer 1