Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Project Spreads 6231Spreadsheetnewnew.xlsx%20(1).pdf 1271 D E $60,000 $75,000 10.00% 5 A B C 1 Schedule of Lease Payments 2 (Amortization Schedule) 3 Five-Year Lease--Beginning-of-Year Payments

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
Project Spreads 6231Spreadsheetnewnew.xlsx%20(1).pdf 1271 D E $60,000 $75,000 10.00% 5 A B C 1 Schedule of Lease Payments 2 (Amortization Schedule) 3 Five-Year Lease--Beginning-of-Year Payments 4 5 Annual Lease Payment 6 Bargain Purchase Option Amount or 7 Excess Guaranteed Residual Value 8 Interest Rate for the Lease 9 Life of the Lease (# of annual payments) 10 11 Initial Lease Obligation (Payable) 12 13 14 Interest 15 Date Amount Expense 16 Initial Balance 17 Beg of Year 1 $60,000 0 18 Beg of Year 2 $60,000 $23,676.10 19 Beg of Year 3 $60,000 $20,043.71 20 Beg of Year 4 $60,000 $16,048.08 21 Beg of Year 5 $60,000 $11,652.89 22 End of Year 5 $75,000 $6,818.18 $296,761.03 Lease Principal Obligation $296,761.03 $60,000 $236,761.03 $36,324 $200,437.13 $39,956 $160,480.84 $43,952 $116,528.93 $48,347 $68,181.82 $68,182 $0.00 Your assignment is to prepare a spreadsheet similar to the example given (with the same numbers). This particular spreadsheet is set up to provide a schedule of case payments (amortization schedule) for a five period lcase with payments made once cach year at the beginning of the year with the option of including a bargain purchase option amount or excess guaranteed residual value. Your task is to make the sprcadsheet general enough that I could input any new numbers for the annual lease payment, bargain purchase option amount or excess guaranteed residual value, and interest rate (cells E5, E7, and EX in the example) and automatically have an updated amortization schedule for any five period capital lease with payments at the beginning of the period. In your spreadsheet, all of your numbers corresponding with the shaded cells in my example should be the result of formulas referring to other cells in the spreadsheet , NOT the result of simply typing numbers into your spreadsheet cells (but you do not need to shade your cells) You are not required to use Excel, but I will give some help assuming that you use Excel. If you use another spreadsheet program, you will need to make sure you understand how to accomplish the requirements in that program. The initial lease obligation (cell Ell in the example) should be a present value formula (PV in Excel many of you will use the Insert Function command under Formulas) which calculates the present value of the liability based on the numbers in the cells above that (cells E5, E7, E8, and E9 in the example). The arguments of the present value formula can/should be cell addresses (except the last "I to indicate beginning-of-year payments). Your initial lease obligation may come out negative by using the syntax given, so add a sign to your formula so that the lease obligation on your spreadsheet will be a positive number You need to submit two pages for your solution one page will have the spreadsheet with the numbers showing and the other will have the spreadsheet with the formulas showing (You may need to adjust the column size of some columns to make sure all formulas are printed out completely. Since you are turning in formulas, I will not need your file for grading). DO NOT submit the Excel file. Instead "print" cach page to PDF format and submit those PDFs. That way I can verify that you followed the instructions below. I will also ask for you to use the following specific format when you "print" your results Each printout should have the row and column headings (A, B, C, 1. 2. 3. etc.) EXCEL Print PreviewPage Setup Sheet Row and Column Headings (check this item) Lach printout should have the ridlines pimed 1 2 C spcom WHICH YOU your results. 1. Each printout should have the row and column headings (A, B, C, 1, 2, 3, etc.) EXCEL: Print Preview/Page Setup/Sheet/Row and Column Headings (check this item) 2. Each printout should have the gridlines printed. EXCEL: Print Preview/Page Setup/Sheet/Gridlines (check this item) 3. Each printout should be formatted to fit on one page. EXCEL: Print Preview/Page Setup/Page/Fit to (check this item and put I wide x 1 tall) 4. Each printout should be formatted using either the portrait or landscape format, whichever fits better for that particular printout (you may need to change your choice here when you print out your formulas) EXCEL: Print Preview/Page Setup/Page/Portrait or Landscape (check appropriate one) 5. To print the format with the formulas instead of numbers use the following procedure in Excel (or use CTRL-to toggle back and forth from numbers to formulas): EXCEL: Formulas/Formula Auditing/Show Formulas (check this item) I want each student to complete the project individually. However, you may help each other with the concepts of the problem and the spreadsheet skills as long as you are not doing the work for each other. Make sure any help you give is assisting others to learn (either spreadsheet or accounting skills) rather than just helping them to complete the assignment. The purpose of this project is to make sure each student understands how to use a spreadsheet program and how to perform a specific application related to this class. I don't want this experience to be extremely frustrating. Therefore, ask me if you need help. Start early so you have time to get help if you need it. I suspect some of you will find this assignment very easy. Others may struggle and need some help, especially those who don't have much experience with spreadsheets. Also include the following on your spreadsheet so they will be included on each page you "print"/submit your name and your statement that you completed the assignment by yourself (or with appropriate help). The assignment (both "printouts") is due in Canvas on Thursday, October 28, 2021, by the end of your class time. DATE SUBJECT: October 19, 2UZT PROJECT #1-COMPUTER SPREADSHEET ASSIGNMENT-LEASES DUE IN CANVAS ON THURSDAY, OCTOBER 28, 2021, BY THE END OF YOUR CLASS TIME Your assignment is to prepare a spreadsheet similar to the example given (with the same numbers). This particular spreadsheet is set up to provide a schedule of lease payments (amortization schedule) for a five-period lease with payments made once each year at the beginning of the year with the option of including a bargain purchase option amount or excess guaranteed residual value. Your task is to make the spreadsheet general enough that I could input any new numbers for the annual lease payment, bargain purchase option amount or excess guaranteed residual value, and interest rate (cells ES, E7, and E8 in the example) and automatically have an updated amortization schedule for any five-period capital lease with payments at the beginning of the period. In your spreadsheet, all of your numbers corresponding with the shaded cells in my example should be the result of formulas referring to other cells in the spreadsheet, NOT the result of simply typing numbers into your spreadsheet cells (but you do not need to shade your cells). Excel-many of you You are not required to use Excel, but I will give some help assuming that you use Excel. If you use another spreadsheet program, you will need to make sure you understand how to accomplish the requirements in that program. The initial lease obligation (cell Ell in the example) should be a present value formula (PV in will use the Insert Function command under Formulas) which calculates the present value of the liability based on the numbers in the cells above that (cells ES. E7 E8, and E9 in the example). The arguments of the present value formula can/should be cell addresses (except the last "1" to indicate beginning-of-year payments). Your initial lease obligation may come out negative by using the syntax given, so add a "_" sign to your formula so that the lease obligation on your spreadsheet will be a positive number. You need to submit two pages for your solution one page will have the spreadsheet with the numbers showing and the other will have the spreadsheet with the formulas showing (You may need to adjust the column size of some columns to make sure all formulas are printed out completely. Since you are turning in formulas, I will not need your file for grading). DO NOT submit the Excel file. Instead "print" each page to PDF format and submit those PDFs. That way I can verify that you followed the instructions below. I will also ask for you to use the following specific format when you "print" your results: Project Spreads 6231Spreadsheetnewnew.xlsx%20(1).pdf 1271 D E $60,000 $75,000 10.00% 5 A B C 1 Schedule of Lease Payments 2 (Amortization Schedule) 3 Five-Year Lease--Beginning-of-Year Payments 4 5 Annual Lease Payment 6 Bargain Purchase Option Amount or 7 Excess Guaranteed Residual Value 8 Interest Rate for the Lease 9 Life of the Lease (# of annual payments) 10 11 Initial Lease Obligation (Payable) 12 13 14 Interest 15 Date Amount Expense 16 Initial Balance 17 Beg of Year 1 $60,000 0 18 Beg of Year 2 $60,000 $23,676.10 19 Beg of Year 3 $60,000 $20,043.71 20 Beg of Year 4 $60,000 $16,048.08 21 Beg of Year 5 $60,000 $11,652.89 22 End of Year 5 $75,000 $6,818.18 $296,761.03 Lease Principal Obligation $296,761.03 $60,000 $236,761.03 $36,324 $200,437.13 $39,956 $160,480.84 $43,952 $116,528.93 $48,347 $68,181.82 $68,182 $0.00 Your assignment is to prepare a spreadsheet similar to the example given (with the same numbers). This particular spreadsheet is set up to provide a schedule of case payments (amortization schedule) for a five period lcase with payments made once cach year at the beginning of the year with the option of including a bargain purchase option amount or excess guaranteed residual value. Your task is to make the sprcadsheet general enough that I could input any new numbers for the annual lease payment, bargain purchase option amount or excess guaranteed residual value, and interest rate (cells E5, E7, and EX in the example) and automatically have an updated amortization schedule for any five period capital lease with payments at the beginning of the period. In your spreadsheet, all of your numbers corresponding with the shaded cells in my example should be the result of formulas referring to other cells in the spreadsheet , NOT the result of simply typing numbers into your spreadsheet cells (but you do not need to shade your cells) You are not required to use Excel, but I will give some help assuming that you use Excel. If you use another spreadsheet program, you will need to make sure you understand how to accomplish the requirements in that program. The initial lease obligation (cell Ell in the example) should be a present value formula (PV in Excel many of you will use the Insert Function command under Formulas) which calculates the present value of the liability based on the numbers in the cells above that (cells E5, E7, E8, and E9 in the example). The arguments of the present value formula can/should be cell addresses (except the last "I to indicate beginning-of-year payments). Your initial lease obligation may come out negative by using the syntax given, so add a sign to your formula so that the lease obligation on your spreadsheet will be a positive number You need to submit two pages for your solution one page will have the spreadsheet with the numbers showing and the other will have the spreadsheet with the formulas showing (You may need to adjust the column size of some columns to make sure all formulas are printed out completely. Since you are turning in formulas, I will not need your file for grading). DO NOT submit the Excel file. Instead "print" cach page to PDF format and submit those PDFs. That way I can verify that you followed the instructions below. I will also ask for you to use the following specific format when you "print" your results Each printout should have the row and column headings (A, B, C, 1. 2. 3. etc.) EXCEL Print PreviewPage Setup Sheet Row and Column Headings (check this item) Lach printout should have the ridlines pimed 1 2 C spcom WHICH YOU your results. 1. Each printout should have the row and column headings (A, B, C, 1, 2, 3, etc.) EXCEL: Print Preview/Page Setup/Sheet/Row and Column Headings (check this item) 2. Each printout should have the gridlines printed. EXCEL: Print Preview/Page Setup/Sheet/Gridlines (check this item) 3. Each printout should be formatted to fit on one page. EXCEL: Print Preview/Page Setup/Page/Fit to (check this item and put I wide x 1 tall) 4. Each printout should be formatted using either the portrait or landscape format, whichever fits better for that particular printout (you may need to change your choice here when you print out your formulas) EXCEL: Print Preview/Page Setup/Page/Portrait or Landscape (check appropriate one) 5. To print the format with the formulas instead of numbers use the following procedure in Excel (or use CTRL-to toggle back and forth from numbers to formulas): EXCEL: Formulas/Formula Auditing/Show Formulas (check this item) I want each student to complete the project individually. However, you may help each other with the concepts of the problem and the spreadsheet skills as long as you are not doing the work for each other. Make sure any help you give is assisting others to learn (either spreadsheet or accounting skills) rather than just helping them to complete the assignment. The purpose of this project is to make sure each student understands how to use a spreadsheet program and how to perform a specific application related to this class. I don't want this experience to be extremely frustrating. Therefore, ask me if you need help. Start early so you have time to get help if you need it. I suspect some of you will find this assignment very easy. Others may struggle and need some help, especially those who don't have much experience with spreadsheets. Also include the following on your spreadsheet so they will be included on each page you "print"/submit your name and your statement that you completed the assignment by yourself (or with appropriate help). The assignment (both "printouts") is due in Canvas on Thursday, October 28, 2021, by the end of your class time. DATE SUBJECT: October 19, 2UZT PROJECT #1-COMPUTER SPREADSHEET ASSIGNMENT-LEASES DUE IN CANVAS ON THURSDAY, OCTOBER 28, 2021, BY THE END OF YOUR CLASS TIME Your assignment is to prepare a spreadsheet similar to the example given (with the same numbers). This particular spreadsheet is set up to provide a schedule of lease payments (amortization schedule) for a five-period lease with payments made once each year at the beginning of the year with the option of including a bargain purchase option amount or excess guaranteed residual value. Your task is to make the spreadsheet general enough that I could input any new numbers for the annual lease payment, bargain purchase option amount or excess guaranteed residual value, and interest rate (cells ES, E7, and E8 in the example) and automatically have an updated amortization schedule for any five-period capital lease with payments at the beginning of the period. In your spreadsheet, all of your numbers corresponding with the shaded cells in my example should be the result of formulas referring to other cells in the spreadsheet, NOT the result of simply typing numbers into your spreadsheet cells (but you do not need to shade your cells). Excel-many of you You are not required to use Excel, but I will give some help assuming that you use Excel. If you use another spreadsheet program, you will need to make sure you understand how to accomplish the requirements in that program. The initial lease obligation (cell Ell in the example) should be a present value formula (PV in will use the Insert Function command under Formulas) which calculates the present value of the liability based on the numbers in the cells above that (cells ES. E7 E8, and E9 in the example). The arguments of the present value formula can/should be cell addresses (except the last "1" to indicate beginning-of-year payments). Your initial lease obligation may come out negative by using the syntax given, so add a "_" sign to your formula so that the lease obligation on your spreadsheet will be a positive number. You need to submit two pages for your solution one page will have the spreadsheet with the numbers showing and the other will have the spreadsheet with the formulas showing (You may need to adjust the column size of some columns to make sure all formulas are printed out completely. Since you are turning in formulas, I will not need your file for grading). DO NOT submit the Excel file. Instead "print" each page to PDF format and submit those PDFs. That way I can verify that you followed the instructions below. I will also ask for you to use the following specific format when you "print" your results

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Ethical Obligations And Decision Making In Accounting Text And Cases

Authors: Steven Mintz, Roselyn Morris

2nd Edition

0078025281, 9780078025280

More Books

Students also viewed these Accounting questions

Question

Are the hours flexible or set?

Answered: 1 week ago