You will find data on your companys leases in columns A to P. 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.

Please use additional columns (on the right side of the data) to show your calculations and whether each of the five lease criteria are met (or not met). Then another column with the classification of the lease type as Operating or Finance.

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 P is changed, this would result in automated changes to the necessary calculations, lease classification criteria and the final lease type.

Lease number Lease term (yrs) Payments ($) Payment Period Beg/End Implicit rate (p.a.)Borrowing rate (p.a.) Residual value Guaranteed Expected val Ownership at end?Bargain purchase option Bargain purch Specialized? Fair value of asset ($) Asset useful life (yrs) 301 2 $ 1,000 Monthly End 0.06 0.05 $ No $ Yes No $ No $ 25,000 3 302 5 $ 6,000 Monthly End 0.055 0.05 $ 50,000 Yes $ 50,000 No No $ No $ 500,000 10 303 15 $ 4,000 Monthly End 0.05 0.05 $ 100,000 Yes $ No No $ Yes $ 630,000 15 304 3 $ $ 1,500 Monthly End 0.06 0.05 $ No $ No No $ No $ 70,000 6 305 2 $ 700 Monthly End Unknown 0.05 $ No $ NO No $ No $ 14,000 2 2 306 2 S $ 800 Monthly End Unknown 0.05 $ No S No No S No S 23,500 4 307 5 $ $ 1,800 Monthly End 0.06 0.05 S $ No $ No No No S 98,000 5 308 4 $ 12,000 Quarterly End 0.05 0.05 $ 35,000 Yes $ 20,000 No No S No S 325,000 7 309 12 $ 20,000 Quarterly End 0.06 0.05 $ 90,000 Yes $ NO NO $ No $ 770,000 15 310 10 $ 9,000 Quarterly End Unknown 0.05 $ 50,000 No S 10,000 No No S Yes S 289,000 12 Lease number Lease term (yrs) Payments ($) Payment Period Beg/End Implicit rate (p.a.)Borrowing rate (p.a.) Residual value Guaranteed Expected val Ownership at end?Bargain purchase option Bargain purch Specialized? Fair value of asset ($) Asset useful life (yrs) 301 2 $ 1,000 Monthly End 0.06 0.05 $ No $ Yes No $ No $ 25,000 3 302 5 $ 6,000 Monthly End 0.055 0.05 $ 50,000 Yes $ 50,000 No No $ No $ 500,000 10 303 15 $ 4,000 Monthly End 0.05 0.05 $ 100,000 Yes $ No No $ Yes $ 630,000 15 304 3 $ $ 1,500 Monthly End 0.06 0.05 $ No $ No No $ No $ 70,000 6 305 2 $ 700 Monthly End Unknown 0.05 $ No $ NO No $ No $ 14,000 2 2 306 2 S $ 800 Monthly End Unknown 0.05 $ No S No No S No S 23,500 4 307 5 $ $ 1,800 Monthly End 0.06 0.05 S $ No $ No No No S 98,000 5 308 4 $ 12,000 Quarterly End 0.05 0.05 $ 35,000 Yes $ 20,000 No No S No S 325,000 7 309 12 $ 20,000 Quarterly End 0.06 0.05 $ 90,000 Yes $ NO NO $ No $ 770,000 15 310 10 $ 9,000 Quarterly End Unknown 0.05 $ 50,000 No S 10,000 No No S Yes S 289,000 12