In the worksheet there is data on company's leases in columns A to N. These are all leases the company has entered into as a LESSEE. All of these leases are noncancelable.

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

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

The 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. It is not permissible to look up the values for columns P and Q in a present value table and type the values into those columns The more this lease classification process is automated, the better.

  

1 2 3 4 5 6 7 8 9 10 11 12 A Lease number 1432 1433 1444 1445 1445 1447 1448 1449 1450 1451 B Fair value of asset ($) $ $ $ $ $ $ $ $ $ $ 500,000 70,000 25,000 630,000 15,000 98,000 23,500 325,000 770,000 289,000 C Asset useful life (yrs) 10 6 3 15 2 5 4 7 15 12 D E Lease term (yrs) Payments ($) $ 5 3 $ 2 $ 15 $ 2 $ 75,000 20,000 12,300 50,000 8,296 22,000 8,000 47,500 81,612 35,000 5 $ 2 $ 4 $ 12 $ 10 $ F G H 1 50,000 Yes Payment Period Beg/End Residual value ($) Guaranteed? Beg $ Beg $ End $ Beg $ End $ No No 100,000 Yes No Annual Annual Annual Annual Annual Annual Annual Annual Annual Annual $ $ Beg End Beg Beg Beg $ No No Yes Yes No 35,000 90,000 50,000 $ $ J Implicit rate (p.a.) 0.055 0.06 0.06 0.05 Unknown 0.06 Unknown 0.05 0.06 0.06 K Borrowing rate (p.a.) 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 L Ownership at end? No No Yes No No No No Yes No No M Bargain purchase op No No No No No No No No No No