The Watson Truck Rental Company Decision Variables: Rental Rate Exogenous Parameters: Trucks Watson Trucks Rental Company Property Taxes Administer and Maintain Taxes growth Maintanace growth Truck Rental rate growth Selling Price ($'Revenue Year 3) Other Variables: Discount Rate Demand Equation Slope Intercept Trucks Rented {%) Trucks rented Month in a year Financial Results: Revenues Property Taxes Administer and Maintain Total Cost Prot selling Price ($'Revenue Year 3) Cash Flows Selling Price Year 3 net 35 $ OYear 1 Year 2 $ 1,000.00 $ 1,090.00 50 50 (1,000,000.00) $ 35,000.00 $ 4,800.00 $ 5,136.00 4% 4% 7% 7% 9% 9% 3 3 10% 10% -0.07 130 60% 30 30 12 12 $ 360,000.00 $ 392,400.00 $ 35,000.00 $ 36,400.00 $ 144,000.00 $ 154,080.00 $ 179,000.00 $ 190,480.00 $ 181,000.00 $ 201,920.00 $ 181,000.00 $ 201,920.00 $1,464,511.19 464,511.19 Year 3 $ 1,188.10 50 $ 5,495.52 $ 500.00 $ 600.00 4% OverAll Profit $ 464,511.19 7% 9% 3 The best rate to achive overall profit is $1000 10% $ 600.00 OverAll Profit $ 464,511.19 30 12 $ 500.00 $ 600.00 427,716.00 37,856.00 164,865.60 202,721.60 $ 224,994.40 $ 500.00 $ 600.00 $ 1,283,148.00 OverAll Profit $ 464,511.19 $ 1,508,142.40 $ 500.00 $ 600.00 OverAll Profit $ 464,511.19\f$ 1,300.00$ 1,400.00 $ 1,500.00 $ 1,300.00 $ 1,400.00 $ 1,500.00 $ 1,300.00 $ 1,400.00 $ 1,500.00 $ 1,300.00 $ 1,400.00 $ 1,500.00 $ 1,300.00 $ 1,400.00 $ 1,500.00Spreadsheet Modeling The Watson Truck Rental Company Required Skills Before starting this exercise you should understand how to use: 0 Excel's N PV (Net Present Value) functionality. 0 Understanding of the concept of net cash flow (time value of money) using a discount rate. 0 Understanding how to build a sensitivity analysis table. Background Consisting of 50 large trucks rented by industrial contractors, the Watson Truck Rental Company is for sale for $1,000,000. Eric Watson, the seller, wants you to develop a three-year economic analysis to assist buyers in evaluating the company. Watson pays property taxes of $35,000 per year, and it costs $4,800 per truck per year to administer and maintain the fleet (assume that only the trucks that are rented incur maintenance costs). The property taxes are expected to grow at a rate of 4% per year, and the maintenance costs are expected to grow 7% per year. Truck rental rate is currently $1,000 per month each. At this rental rate on average 60% of the trucks are rented each month. Watson 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 rent 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 rst year will be increased by 9% per year for years 2 and 3. Average percent of trucks rented in years 2 and 3 will be the same as determined in the rst year, regardless of the increased rental rate in those years. At the end of three years, Watson assumes the buyer will resell the truck business for cash at a prot. The selling price at that time is assumed to be three times the revenue in year 3. Cash ow in each year is assumed to be the same as the net income (revenue minus expenses) for that year. Effects of depreciation and other factors relating to income taxes can be ignored. Cash ow in year 3 includes in addition the cash from the resale of the business. Overall investment prot is dened to be the Net Present Value of the annual cash ows (discount rate = 10%) including the purchase price at the beginning of year 1. Assume no trucks are bought or sold during the three years. Questions 1. Identify the decision variables, the exogenous variables, the performance measure, the in termediate variables, and any constraints in this problem and use these to build an Excel model. 2. Use a Data Table to nd the initial truck rental rate that achieves the highest overall invest ment prot after the property is sold in three years. 3. Investigate the sensitivity of overall investment prot to the following variables: purchase price, maintenance cost per truck, annual property taxes, and sales multiplication factor. (Use the rental rate found from question 2 above for this analysis). To which factor is prot most sensitive? 4. Prepare a managerial report for Eric Watson summarizing your ndings and recommenda tions. You must present a Microsoft Excel worksheet as a deliverable and submit it in Blackboard. No other documents must be submitted. Use one Sheet for each response (you may copy your sheet several times to answer each question). No two questions should be answered in the same sheet. You must produce any graphs that help explain your answers. Your Excel should be formatted as explained in class: . Decision Variables . Exogenous Parameters . Other variables . Financial Results Useful Tips Demand Equation Remember the equation for a straight line given two points (2:1, yl), (1'2, yg) is given by 3J2 yi _ = _ l 3'; :91 2:2 _ 331(13 31) l ) After doing your algebra, you will obtain an equation of the form 3,: = ms: + b (2) where m is the slope of the straight line, and b is the yintercept value. Formatting a Cash ow analyses always start at year 0. Year [I is the starting point where all initial cash outlays (purchase price, etc.) are listed. . Financial analyses are made from the point of view of the investor. If the investor is buying something, that is a negative cash flow for him /her. If the investor is selling something, that is a positive cash ow. o It is always a good practice to insert a column before year 0 where you write all the parameters that do not change year after year. a In this case you have only % decision variable: the Truck Rental Rate for the rst year. Therefore, you must format it so that it stands out. a In this case you have only one objective value: the Net Present Value of the resulting annual cash ows. Therefore you must format it so that it stands out