In: Finance
Can you send the excel file if I provide an email address?
This is a question on managerial decision making with excel spreadsheet. A step wise how to solve this will be appreciated for homework and not any exam. A model in excel will really 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 spreadsheet model to assist 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%. He 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 a 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). 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).
At the end of five years, it is assumed that the buyer will resell the business for cash. John 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.
To do
Homework Submission Instructions:
Rental | 19500 | ||||||||||||
Utilization | 41.0% | ||||||||||||
Fleet | 100 | ||||||||||||
Running Cost | 4800 | per Truck | Investment | Rev | Cost | Office cost | Proporty Tax | Income from Sale | Total Income | Income tax | Cash Flow | Discounted Flow | |
increment in Cost | 9% | per year | 1 | 650000.00 | 799500.00 | 480000.00 | 60000.00 | 35000.00 | 0.00 | -425500.00 | 0.00 | -425500.00 | -386818.18 |
Office Cost | 60000 | 2 | 0.00 | 799500.00 | 523200.00 | 60000.00 | 36050.00 | 0.00 | 180250.00 | 54075.00 | 126175.00 | 104276.86 | |
Proprty Tax | 35000 | 3 | 0.00 | 799500.00 | 570288.00 | 60000.00 | 37131.50 | 0.00 | 132080.50 | 39624.15 | 92456.35 | 69463.82 | |
increment in Prop Tax | 3% | 4 | 0.00 | 799500.00 | 621613.92 | 60000.00 | 38245.45 | 0.00 | 79640.63 | 23892.19 | 55748.44 | 38076.94 | |
5 | 0.00 | 799500.00 | 677559.17 | 60000.00 | 39392.81 | 2398500.00 | 2421048.02 | 726314.41 | 1694733.61 | 1052296.24 | |||
income tax | 30% | NPV | 877295.68 | ||||||||||
discount rate | 10% | A | NPV = 1000000, John can increase the selling value upto 1122272 $ | ||||||||||
B. | Table for different NPV at diff Rental Values | ||||||||||||
Rental Rate | NPV | Utilization | |||||||||||
10500 | 1181871.00 | 83% | |||||||||||
12000 | 1353194.62 | 76% | |||||||||||
13500 | 1435683.77 | 69% | |||||||||||
15000 | 1429338.45 | 62% | |||||||||||
16500 | 1334158.66 | 55% | |||||||||||
18000 | 1150144.40 | 48% | |||||||||||
19500 | 877295.68 | 41% |
Buyer shall chose 13500 % as rental Rate, for this gives him the best NPV.