Part 2: Analyze a leasing situation through the eyes of the lessor ( 50 points) Jsing the NAL tab, develop an Excel spreadsheet model for the following: Lease Corp. is considering a lease to XYZ Corp. for some new manufacturing equipment. The lease would ee a 4-year contract with a lease payment of $292,000 per year. In addition, payments for this particular lease are to be made at the end of the year and would include maintenance. If .ease Corp. agrees to lease the item to XYZ Corp, they will have to purchase the equipment outright for $790,000 and also will have to pay the local dealer $22,000 at the end of each rear for maintenance service. The equipment falls into the MACRS 3-year class and has a resale value of $45,000. The lessor's tax rate is 26% and the lessee's tax rate is 34%. Lease Corp. requires a 12.25% after-tax return on equipment it leases, which is also its WACC (i.e., in this problem you only need to use one discount rate). XYZ Corp. has a weighted iverage cost of capital of 15.76%. You will need to use the PV function to calculate the present value of each relevant cash flow. Use your template to answer the following questions: Should Lease Corp. write the lease? Why or why not? WRITE HERE: Suppose Lease Corp. is unsure about the actual resale value of the equipment, so they assume that there is a 30% chance that the residual value (i.e., resale value of the equipment) will only be $25,000, another 20% chance that the residual value will be $85,000, and a 50% probability that the residual value will be $45,000. Answer the following questions using the sensitivity analysis table in the NAL tab. Assuming a 12.25% required return on the lease, what is Lease Corp.'s best and worst case NAL? What is its expected NAL? Should Lease Corp. write the lease? Recall that we can calculate the Expected NAL similar to how we calculate weighted average returns: Expected NAL =( NAL probability). WRITE HERE: Net Advantage to Leasing Input Values Asset purchase price Lease payment Tax rate Cost of owning Required return on lease (WACC) Salvage value (Year 4) Book value (Year 4) \#N/A Installed Asset Cost After-tax Lease Payment Depreciation Tax Shield After-tax Operating Costs of Owned After-tax Salvage Value Total Annual Cash Flow PV of Total Annual Cash Flow NAL (lessor perspective) \#N/A Sensitivity Analysis Scenario 1 \begin{tabular}{|l|l|l} \hline Salvage Value & Probability & NAL \end{tabular} Scenario 2 Scenario 3 Expected NAL \#N/A *for sensitivity analysis, you can manually input the NAL values for each scenario Part 2: Analyze a leasing situation through the eyes of the lessor ( 50 points) Jsing the NAL tab, develop an Excel spreadsheet model for the following: Lease Corp. is considering a lease to XYZ Corp. for some new manufacturing equipment. The lease would ee a 4-year contract with a lease payment of $292,000 per year. In addition, payments for this particular lease are to be made at the end of the year and would include maintenance. If .ease Corp. agrees to lease the item to XYZ Corp, they will have to purchase the equipment outright for $790,000 and also will have to pay the local dealer $22,000 at the end of each rear for maintenance service. The equipment falls into the MACRS 3-year class and has a resale value of $45,000. The lessor's tax rate is 26% and the lessee's tax rate is 34%. Lease Corp. requires a 12.25% after-tax return on equipment it leases, which is also its WACC (i.e., in this problem you only need to use one discount rate). XYZ Corp. has a weighted iverage cost of capital of 15.76%. You will need to use the PV function to calculate the present value of each relevant cash flow. Use your template to answer the following questions: Should Lease Corp. write the lease? Why or why not? WRITE HERE: Suppose Lease Corp. is unsure about the actual resale value of the equipment, so they assume that there is a 30% chance that the residual value (i.e., resale value of the equipment) will only be $25,000, another 20% chance that the residual value will be $85,000, and a 50% probability that the residual value will be $45,000. Answer the following questions using the sensitivity analysis table in the NAL tab. Assuming a 12.25% required return on the lease, what is Lease Corp.'s best and worst case NAL? What is its expected NAL? Should Lease Corp. write the lease? Recall that we can calculate the Expected NAL similar to how we calculate weighted average returns: Expected NAL =( NAL probability). WRITE HERE: Net Advantage to Leasing Input Values Asset purchase price Lease payment Tax rate Cost of owning Required return on lease (WACC) Salvage value (Year 4) Book value (Year 4) \#N/A Installed Asset Cost After-tax Lease Payment Depreciation Tax Shield After-tax Operating Costs of Owned After-tax Salvage Value Total Annual Cash Flow PV of Total Annual Cash Flow NAL (lessor perspective) \#N/A Sensitivity Analysis Scenario 1 \begin{tabular}{|l|l|l} \hline Salvage Value & Probability & NAL \end{tabular} Scenario 2 Scenario 3 Expected NAL \#N/A *for sensitivity analysis, you can manually input the NAL values for each scenario