Part $2$: Analyze a Leasing Situation $(3$ Points$)$ 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 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. $$ 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? $($Answer each question$)$ Use your Excel spreadsheet to find the NAL using each possible residual value. $$ You do not have to 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? $($Answer each question$)$ 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.$)$ use this as the format in excel$($And make sure you show all formulas and reference all cells: $3120$ Excel Project $\backslash$# $2$ Part $2$

Lease payment:

Tax rate:

Cost of owning:

Req. After Tax Return$\backslash \%$:

Salvage Value:

MACRS: Yr $1$: $33\backslash \%,$ Yr $2$: $45\backslash \%,$ Yr $3$: $15\backslash \%,$ Yr $4$: $7\backslash \%$

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.$)$

Rieger Excel Project $\backslash$#$2$ Page $4$ of $6$