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.

The more this lease classification process is automated, the better grade you will receive on this assignment. Hint: for Columns P and Q, look at the present value tables in Chapter 6 of your textbook. At the top of each table is the formula to calculate the present value factors. Try writing your Excel formulas on paper first so that you get all the parenthesis matched up correctly. After you put the formulas in, double check your answer to the numbers in the Chapter 6 tables to make sure you are calculating correctly.

Asset useful life (yrs) Payment Period 10 6 Annual Annual Annual Beg Beg/End Residual value ($ Guaranteed? Beg $ 50,000 Yes No End No 100,000 Yes End No 3 2 15 15 Beg Lease number Fair value of asset ($) 101 500,000 102 70,000 103 25,000 104 630,000 105 15,000 106 98,000 107 23,500 108 325,000 109 770,000 110 289,000 Lease term (yrs) Payments ($) 75,000 20,000 12,300 50,000 8,296 22,000 8,000 47,500 12 81,612 10 35,000 2 2 Implicit rate (p.a.) 0.055 0.06 0.06 0.05 Unknown 0.06 Unknown 0.05 0.06 0.06 Beg No Annual Annual Annual Annual Annual Annual Annual 4 End No 7 Beg Yes 15 35,000 90,000 50,000 Beg Beg Yes 12 No 12 K M N 0 Ownership at end? Bargain purchase option? Annuity PV factor si Implicit rate (p.a.) 0.055 Specialized? No No No 0.06 No No No Yes No No No No Borrowing rate (p.a.) 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 Yes No No 0.06 0.05 Unknown 0.06 Unknown 0.05 No No No No No No No Yes No No 0.06 No No No 0.06 No No Yes Annuity PV factor Single Sum PV factor PV Lease Payments Criteria 1 Criteria 2 Criteria 3 Criteria 4 Criteria 5 11 v W Y Z AA PV Lease Payments Criteria 1 Criteria 2 Criteria 3 Criteria 4 Criteria 5 Lease Type on minor OOO Asset useful life (yrs) Payment Period 10 6 Annual Annual Annual Beg Beg/End Residual value ($ Guaranteed? Beg $ 50,000 Yes No End No 100,000 Yes End No 3 2 15 15 Beg Lease number Fair value of asset ($) 101 500,000 102 70,000 103 25,000 104 630,000 105 15,000 106 98,000 107 23,500 108 325,000 109 770,000 110 289,000 Lease term (yrs) Payments ($) 75,000 20,000 12,300 50,000 8,296 22,000 8,000 47,500 12 81,612 10 35,000 2 2 Implicit rate (p.a.) 0.055 0.06 0.06 0.05 Unknown 0.06 Unknown 0.05 0.06 0.06 Beg No Annual Annual Annual Annual Annual Annual Annual 4 End No 7 Beg Yes 15 35,000 90,000 50,000 Beg Beg Yes 12 No 12 K M N 0 Ownership at end? Bargain purchase option? Annuity PV factor si Implicit rate (p.a.) 0.055 Specialized? No No No 0.06 No No No Yes No No No No Borrowing rate (p.a.) 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 0.065 Yes No No 0.06 0.05 Unknown 0.06 Unknown 0.05 No No No No No No No Yes No No 0.06 No No No 0.06 No No Yes Annuity PV factor Single Sum PV factor PV Lease Payments Criteria 1 Criteria 2 Criteria 3 Criteria 4 Criteria 5 11 v W Y Z AA PV Lease Payments Criteria 1 Criteria 2 Criteria 3 Criteria 4 Criteria 5 Lease Type on minor OOO