In the second worksheet (Leases) you will find data on your companys leases in columns A thru O. These are all leases your company has entered into as a LESSEE. All of these leases are noncancelable.

Using the data on the leases provided, you are required to classify the leases for your company as either operating or finance leases and to also calculate the amount of the lease liability.

The light blue columns (P,Q,R.S,T,W,X,Y,Z, AA, AC, AE ) are suggested columns for you to use to insert formulas to enable you to classify the leases using the five lease criteria.

Your goal is to use formulas to automate this lease classification process. This should be an automated and dynamic process. So, if some of the input data in columns B thru O is changed, this would result in changes to the necessary calculations, lease classification criteria and the final lease type. It is not permissible to look up any of the values in a present value table and type the values into those columns or formulas.

The more this lease classification process is automated, the better grade you will receive on this assignment. The present value formulas are in your textbook if you need them. They are in Chapter 6 of your textbook. At the top of each table is the formula to calculate the present value factors. After you put your formulas in, double check your answer to the numbers in the Chapter 6 tables to make sure you are calculating correctly. I should note that a few students in the past have been able to create formulas for the present value factors without using the formulas on the tables. I have seen at least three different ways to obtain those factors.

Lease number	Fair value of asset ($)	Asset useful life (yrs)	Lease term (yrs)	Payments ($)	Payment Period	Beg/End	Amount of Residual Value In Lease ($)	Guaranteed?	Expected Residual Value ($)	Implicit rate (p.a.)	Borrowing rate (p.a.)	Ownership at end?	Bargain purchase option?	Specialized?
1432	$ 500,000	10	5	$ 75,000	Annual	Beg	$ 50,000	Yes	$ 55,000	0.055	0.065	No	No	No
1433	$ 70,000	6	3	$ 20,000	Annual	Beg	$ -	No	$ -	0.06	0.065	No	No	No
1444	$ 25,000	3	2	$ 12,300	Annual	End	$ -	No	$ -	0.072	0.065	Yes	No	No
1445	$ 630,000	15	15	$ 50,000	Annual	Beg	$ 100,000	Yes	$ 89,000	0.05	0.065	No	No	Yes
1446	$ 15,000	2	2	$ 8,296	Annual	End	$ -	No	$ -	Unknown	0.065	No	No	No
1447	$ 98,000	5	5	$ 22,000	Annual	Beg	$ 5,000	No	$ 3,000	0.06	0.065	No	No	No
1448	$ 23,500	4	2	$ 8,000	Annual	End	$ -	No	$ 4,000	Unknown	0.065	No	No	No
1449	$ 325,000	7	4	$ 47,500	Annual	Beg	$ 35,000	Yes	$ 30,000	0.064	0.065	Yes	No	No
1450	$ 770,000	15	12	$ 81,612	Annual	Beg	$ 90,000	Yes	$ 93,000	0.06	0.065	No	No	No
1451	$ 289,000	12	10	$ 35,000	Annual	Beg	$ 50,000	No	$ -	0.06	0.065	No	No	Yes

Please complete the following table:

Amount of Guaranteed Residual Shortfall

Interest Rate Used

PV of Lease Payments

PV of Guaranteed Residual

PV of Guaranteed Residual Shortfall

Ownership Transfer?

Bargain Purchase Option?

Lease Term >= 75% Economic Life?

Pass 90% Test?

Specialized Equipment?

Lease Type

Amount of Lease Liability