Extra Credit Question (7 points. Hard copy due on the date of Test 2) Use Excel to answer questions A-E below. Print your answers for A-E. Then, print the relevant formulas on a separate page. Bristol Company leased a machine from Harvard Leasing Company on January 1, 2017. The non-cancellable lease term is 4 years. The following data relate to this lease: 1. Harvard purchased the machine for $363,950 at a cost equal to its fair market value. 2. The economic life of the machine is 6 years with no salvage value. 3. Payments are on January 1 of each year starting in 2017 (an annuity due) 4. The annual rental payment is $100,000 which includes $8,000 per year to cover executory costs paid by Harvard Leasing. (i.e., the minimum lease payment is $92,000). The executory costs are for taxes and can vary after the first year 5. The machine will be returned to Harvard Leasing with an estimated residual value of $40,000 at the end of the lease term. 6. Bristol's incremental borrowing rate per year is 10%. 7. Future costs associated with this lease are predictable and no uncertainties exist about collectability of payments. A. How much is the implicit annual interest rate for Harvard Leasing? Use Column A for descriptive titles. Use Column B to enter data and formulas. In Rows 2-5, enter as data: the fair market value of the leased machine, the minimum lease payment, the residual value, and the number of years in the lease term. In Row 6, use the RATE function and use the data in Rows 2-5. The estimated residual value is "Fv The present value "Pv" is the fair market value of the machine multiplied by -1 to make it an outflow of cash. Enter "1" for the "Type". Format the result as a percentage with two decimal places. See Hint below. B. Develop a lease amortization schedule for Bristol similar to the Table shown below assuming that the residual value is guaranteed and that Bristol knows Harvard's implicit interest rate. Bristol estimates that it is probable that the residual value will be $40,000 or more at lease end. Date Rental ExecutoryMinimum Reduction of Lease Liabil ease ment Cost in Rent Lease P Liability C. Develop a lease amortization schedule for Harvard Leasing Co. similar to Question B above. Record the jourmal entries for Harvard for 2017 (for both months of January and December) D. Record the journal entries for Bristol for 2017 assuming the lease is a finance lease for Bristol. Independently record the joumal entries for Bristol for 2017 assuming the lease is an operating lease for Bristol. E. Now assume that the residual value is not guaranteed by Bristol Company and also that Bristol does not know Harvard's implicit interest rate. Perform the 90% test for Harvard and Bristol. Use the PV function to calculate the present value of the minimum lease payments for both parties. Divide the present value by the fair market value of the machine and report your results as percentages. Hint: check figures for A: Cost & Fair Value of Machine 363,950 92,000 40,000 Lease Payment Estimated Residual Value Term of Lease Implicit Rate (Annuity due) 7.00% Extra Credit Question (7 points. Hard copy due on the date of Test 2) Use Excel to answer questions A-E below. Print your answers for A-E. Then, print the relevant formulas on a separate page. Bristol Company leased a machine from Harvard Leasing Company on January 1, 2017. The non-cancellable lease term is 4 years. The following data relate to this lease: 1. Harvard purchased the machine for $363,950 at a cost equal to its fair market value. 2. The economic life of the machine is 6 years with no salvage value. 3. Payments are on January 1 of each year starting in 2017 (an annuity due) 4. The annual rental payment is $100,000 which includes $8,000 per year to cover executory costs paid by Harvard Leasing. (i.e., the minimum lease payment is $92,000). The executory costs are for taxes and can vary after the first year 5. The machine will be returned to Harvard Leasing with an estimated residual value of $40,000 at the end of the lease term. 6. Bristol's incremental borrowing rate per year is 10%. 7. Future costs associated with this lease are predictable and no uncertainties exist about collectability of payments. A. How much is the implicit annual interest rate for Harvard Leasing? Use Column A for descriptive titles. Use Column B to enter data and formulas. In Rows 2-5, enter as data: the fair market value of the leased machine, the minimum lease payment, the residual value, and the number of years in the lease term. In Row 6, use the RATE function and use the data in Rows 2-5. The estimated residual value is "Fv The present value "Pv" is the fair market value of the machine multiplied by -1 to make it an outflow of cash. Enter "1" for the "Type". Format the result as a percentage with two decimal places. See Hint below. B. Develop a lease amortization schedule for Bristol similar to the Table shown below assuming that the residual value is guaranteed and that Bristol knows Harvard's implicit interest rate. Bristol estimates that it is probable that the residual value will be $40,000 or more at lease end. Date Rental ExecutoryMinimum Reduction of Lease Liabil ease ment Cost in Rent Lease P Liability C. Develop a lease amortization schedule for Harvard Leasing Co. similar to Question B above. Record the jourmal entries for Harvard for 2017 (for both months of January and December) D. Record the journal entries for Bristol for 2017 assuming the lease is a finance lease for Bristol. Independently record the joumal entries for Bristol for 2017 assuming the lease is an operating lease for Bristol. E. Now assume that the residual value is not guaranteed by Bristol Company and also that Bristol does not know Harvard's implicit interest rate. Perform the 90% test for Harvard and Bristol. Use the PV function to calculate the present value of the minimum lease payments for both parties. Divide the present value by the fair market value of the machine and report your results as percentages. Hint: check figures for A: Cost & Fair Value of Machine 363,950 92,000 40,000 Lease Payment Estimated Residual Value Term of Lease Implicit Rate (Annuity due) 7.00%
