60.1K

Verified Solution

Link Copied!

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.

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

Answer & Explanation Solved by verified expert

See Answer