Develop an Excel spreadsheet model for the following: We Lease It is considering a lease to PP&Q of some new manufacturing equipment; the lease would be a 4-year guideline contract with a lease payment of $100,000 per year, payments to be made at the end of the year (regular annuity) and would include maintenance. We Lease It would purchase the equipment outright for $250,000, and would have to pay the local dealer $7,000 at the end of each year for maintenance service. The equipment falls into the MACRS 3-year class and has a residual value of $40,000 (do not deduct residual value when determining MACRS deprecation), which is the expected market value after four years. The lessors tax rate is 40%. We Lease It requires an 11% after-tax return on equipment it leases. (For our purposes, the NAL = NPV of the lease.) Answer the following questions (using a Word document.):

a. Should We Lease It write the lease? Why or why not? (Be certain to discuss the NAL of the lease.) Create a template similar to the one shown on the next page.

b. Suppose that there is a 30% chance that the residual value will only be $10,000, another 20% chance that the residual value will be $60,000, and a 50% probability that the residual value will be $40,000. What is We Lease Its best and worst case NAL? What is its expected NAL? Use your Excel spreadsheet to find the NAL using each possible residual value. You do not have print out all three spreadsheets used for part b to complete this analysis; just show their results. Expected NAL = (NAL * probability)

c. What if We Lease it requires a 15% return on the lease? What is the NAL? How does this affect the lease analysis? Do you write the lease? (Hint: you should get a negative NAL.) What lease payment must We Lease It charge in order to be indifferent between writing the lease and loaning at 15%? (Hint: Use the solver or goal seek function in Excel. Have one cell be the lease payment and have all the other lease payment cells refer back to this one.)

Lease payment Tax rate Cost of owning WACC Salvage value MACRS 33.00% 45.00% 15.00% 7.00% Year Installed Asset Cost Lease Payment Depreciation Depreciation tax shield Operating Costs if Owned CF 0 1 2 3 4 After-tax salvage value PV of salvage value NAL (NPV) Residual NPV Probability Weighted Average 30% 50% 20% Expected NPV