Valuing a Company for Purchase and Sale: Given recent worldwide events vis--vis the global pandemic, you believe that shipping companies will thrive for the next

Valuing a Company for Purchase and Sale:
Given recent worldwide events vis-à-vis the global pandemic, you believe that shipping companies will thrive for the next several years. You have decided to evaluate a certain trucking company that you have found for sale. You will want to determine the net present value of Bill's Trucking given the following assumptions.
1. You will own and operate the trucking company for three years
2. At the end of the third year you will sell the company for 3 times year 3 revenue
3. Subject to following base case assumptions
Purchase price: $1,000,000
Cost of Capital: 10%
Number of Trucks: 50
Truck rental rate: $1,000 (monthly)
Utilization rate: 60% (in other words assume for base case you rent 30/month)
Rent increase: 9% (increase in rental rate applies to year 2 and 3)
Taxes: $35,000
Tax increase: 4% (years 2 and 3)
Maintenance: $4,800 (per truck per year)
Maintenance increase: 7%
The assumptions above you have derived from the current company owners. You are wondering if they are maximizing value at the current rental rate. You estimate that for every $100 increase in the rental price, the utilization rate will fall by 7%. Likewise for every $100 decrease in price the utilization will increase by 7%. This relationship is proportionally true for every dollar of increase or decrease, such that if you set the price at $1, 001 utilization would fall to 59.93%. This relationship between price and demand should be built into your model. To select the optimal rental price, add a one-way data table of price vs NPV at the various prices. Graph the table and include a text box that explains the optimal price based on your
model. Complete the model, starting with the template sheet BillsTrucks_strt.xlsx and determine NPV (15pts)
One way data table (2 pts)
Graph of one way table and text box explaining optimal rental price (3pts)

Input variables Number of trucks Discount rate Sale Multiple Cost variables Property tax (base) Property tax growth Maintenance expenses (base) Maintenance expense growth Truck Rental Base Case Base rate Utilization Increase (year 2 and 3) Truck Rental Variables Actual Rental Price Capacity Change Truck Utilization Average trucks rented per month Monthly Rental Revenue Cash flows Period Bill's Trucks Rental Company Cash inflows Truck rental Sale of company Total Cash outflows Purchase of company Maintenance Income taxes Total 50 10% 3 35000 4% 4800 7% Year 0 1000 60% 9% 7% 1 Year 1 2 Year 2 3 Year 3