Part 2: Analyze a Leasing Situation 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 fo_ur years. The lessor's 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 by using a Word document: (Deliverables) a. Should We Lease It write the lease? Why or why not? Be certain to discuss the NAL of the lease. 0 Create a template like 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 It's best and worst case NAL? What is its expected NAL? 0 Use your Excel spreadsheet to nd the NAL using each possible residual value. 0 You do not have to print out all three spreadsheets used for part b to complete this analysis; just show their results. 0 Expected NAL = 2(NAL * probability) c. What if We Lease it requires a 15% return on the lease? 0 What is the NAL? How does this affect the lease analysis? Do you write the lease? (Hint: you should get a negative NAL.) o 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: Yr 1: 33%, Yr 2: 45%, Yr 3: 15%, Yr 4: 7% Example Image of Excel Template for Part Two: (If you need to make modifications please do so, but the result should be similar to this example.) Installed PV of After- PV of Lease Depreciation Operating Cash tax NAL Year Asset Depreciation Costs if Cash Salvage Payment Tax Shield Flows Salvage (NPV) Cost Owned Flow Value Value AW NH Weighted Avg Residual NPV Probability Expected NPV0 Part 2: Four Spreadsheets Part a: Lease analysis Part b: Scenario template Part c: Lease analysis for NAL Part c: Results of the solver or goal seek function. (You may combine parts a 8a: b on the same print out) 0000