Please send excel to vphatedragon33 at google email for extra pay through paypal

Holmes Truck Rental Company
Parameters
Annual Property Tax ($)	35,000.00
Maintenance ($/truck)	4,800.00
Truck Rental Growth Rate	0.09
Property Tax Growth Rate	0.04
Maintenance Growth Rate	0.07
Discount Rate	0.10
Current Firm Value ($)	1,000,000.00
End of Year Sales Price (xRevenue)	3.3
Fleet (Trucks)	50
Current Rental Price ($/truck/mo)	1,000.00
Trucks rented each month	0.60
Slope (%/$)	-0.0007
Y-intercept (% rented)	1.3
Number of years holding investment	3

Consisting of 50 large trucks rented to industrial contractors, the Holmes Truck Rental Company is for sale for $1,000,000. Eric Holmes, the seller, has supplied the operating data below to assist potential buyers in evaluating the company. As a new analyst at a private-equity partnership, your job is to construct a brief, initial analysis to determine whether further investigation is warranted. Holmes pays property taxes of $35,000 per year and it costs $4,800 per truck per year to administer and maintain the fleet. The property taxes are expected to grow at a rate of 4% per year, and the maintenance costs are estimated to grow 7% per year. Each trucks rental rate (price) currently is $1000 per month. At this rental rate, on average 60% of the trucks are rented each month. Holmes believes that if he lowered the rent by $100 per truck per month, he would increase the average rental percentage by seven percentage points and that this increment would apply to each additional reduction in rental rate of $100. For example, at a $600 truck rental rate, 88% of the trucks would be rented each month. Whatever truck rental rate is set for the first year will be increased by 9% per year for years 2 and 3. The average percent of trucks rented in subsequent years will be the same as the value determined in the first year, regardless of the increased rental rate in those years. Your firm generally holds investments in small businesses for three years, and then attempts to sell them to strategic investors at a profit. Experience in the equipment rental sector suggests that a reasonable estimate of the selling price at the end of year three would be 3.3 times the revenue in year three. Operating cash flow in each year is assumed to be the same as net income (revenue minus expenses), and for convenience, we will assume all operating cash flows occur at year-end. The effects of depreciation and other factors relating to income taxes can be ignored. The cash flow in year three includes the proceeds from the resale of the business at the end of the year. The measure of overall investment performance is defined to be the net present value of the annual cash flows (discount rate = 10%), including the original purchase price paid at the beginning of year one. To simplify the analysis, assume no trucks are bought or sold during the three years.

a, Build an Excel model of the value of Holmes Truck Rental to a prospective buyer. Recall that the net present value of a stream of future payments is a weighted sum in which each years payment is discounted by multiplying it by a discount factor = (1/1+i)^t , where i is the discount rate and t is the number of years between the present date and the payment date.

b, Use a Data Table to find the initial truck rental price that achieves the highest net present value for an investor who intends to sell the firm in three years

c, If the 9% per year growth in the truck rental rate were to continue beyond year three, together with all other assumptions about taxes, selling prices, etc., would a prospective buyer prefer to sell after three years or to continue operating the firm? Is there a fairly simple way to modify your spreadsheet to address this question directly? To be specific, try extending your model out to measure the value at the end of years four and five.

d, Under what conditions concerning the growth in the truck rental rate is sale at the end of year three an optimal strategy for a prospective buyer (holding other assumptions unchanged)?