In the second worksheet (Data and working) you will find data on your companys leases in columns A to N. These are all leases your company has entered into as a LESSEE .

Using the data on the characteristics of the leases provided, you are required to classify the leases for your company as either operating or finance leases.

The yellow columns (P,Q,S,U,V,W,X,Y,AA) 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 (or other methods) to automate this lease classification process. This should be an automated and dynamic process. So, if some of the input data in columns B to N is changed, this would result in changes to the necessary calculations, lease classification criteria and the final lease type

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