\ACCT 311 Intermediate Accounting I Long-Term Liability Excel Assignment Instructions: For each of the scenarios below you will be expected to prepare an amortization schedule in Excel, and answer some related accounting questions. Here are the requirements for the amortization schedules: a) Within each amortization schedule, you are allowed to enter only the inputs related to the information below, and the rest of the amounts should be based on cell references, Excel calculations and Excel functions. I would suggest putting the inputs in the top left part of each Excel Worksheet, with the amortization schedule below these inputs. b) All Amortization schedules should be professional in appearance. Consider formatting, organization, etc. to make them look nice and easy to view. Round all dollar amounts to the nearest dollar. d) Use a separate tab/ worksheet for each Amortization schedule and related questions. Use the space below each Amortization schedule to answer the applicable questions. f No descriptions are needed for the journal entries. Scenarios 1. Bonds Payable: On January 1, 2021, ABC Company issued ten-year bonds with a face value of $5,500,000 and a stated interest rate of 7%, payable semiannually on June 30 and December 31. The bonds were sold to yield 9%. Perform the following rate of 1%, and the final Note Payable payment of $50,000 is due in 6 years. The market rate of interest for this type of purchase is 3% a) Prepare an amortization schedule in Excel for the entire term of the note payable. In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the equipment on January 1, 2021. c) Prepare the joumal entries on March 31, 2021 (first payment) and December 31, 2026 (last payment). d) What is the total cost of Borrowing 1.e. Interest Expense over the life of this note? How much in cash interest was paid over the life of this note? How much total Discount was amortized over the life of the note? 4. Mortgage Payable - Vehicle Loan: You purchase a vehicle for $40,000 on May 1, 2021 at 6% interest. You finance the entire purchase (no down payment). It will be paid off in equal monthly payments over 4 years, with the first payment due on May 31, 2021. Perform the following a) Prepare an amortization schedule in Excel for the entire term of the loan. In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the vehicle on May 1, 2021. - Prepare the joumal entries on March 31, 2021 (first payment) and April 30, 2025 (last payment). d) In regards to the December 31, 2021 Balance Sheet, what amount will show in the 1) current liability section and 2long-term liability section? What is the total amount of payments made over the 4 years? How much of this is interest vs. principal payments? c e a) Prepare an amortization schedule in Excel for the entire term of the bond. In the area below the amortization schedule document the following: b) Calculate the bond price using the Time Value of Money tables from the book (also posted under Blackboard / Course Content / Class Handouts). Prepare the journal entry for the issuance on January 1, 2021 d) Prepare the joumal entries for December 31, 2021 and January 1, 2022. What is the total cost of Borrowing i.e. Interest Expense over the life of this bond? How much in cash interest was paid over the life of this bond? How much total Discount was amortized over the life of the bond 5. Mortgage Payable Home Loan: You purchase a home for $350,000 on January 1, 2022 at 4% interest. You pay $50,000 as a down payment and enter into a Mortgage for the remaining $300,000. It will be paid off in equal monthly payments over 30 years, with the first payment dve on January 31, 2022. Perform the following: a) Prepare an amortization schedule in Excel for the entire term of the mortgage In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the home on January 1, 2022. Prepare the joumal on January payment) December 31, 2050 (last payment) d) In regards to the December 31, 2022 Balance Sheet, what amount will show in the 1) current liability section and 2) long-term lability section? What is the total amount of payments made over the 30 years? How much of this is interest vs. principal payments? How would these numbers change if the interest rate was 6% instead of 4%? You should just be able to change the 1 input cell and your entire schedule should update if it is built using formulas and cell references). e 2. a. Bonds Payable: On January 1, 2015, XYZ Corp. issued 7-year bonds with a face value of $5,500,000 and a stated interest rate of 12%, payable quarterly on March 31, June 30, September 30 and December 31. The bonds were sold to pield 10%. Perform the following a) Prepare an amortization schedule in Excel for the entire term of the bond. In the area below the amortization schedule document the following: b) Calculate the bond price using the Time Value of Money tables from the book (also posted under Blackboard / Course Content / Class Handouts). c) Prepare the journal entry for the issuance on January 1, 2015 d) Prepare the joumal entries for March 31, 2015 and December 31, 2015. e) What is the total cost of Borrowing .e. Interest Expense over the life of this bond? How much in cash interest was paid over the life of this bond? How much total Premium was amortized over the life of the bond 3. Below-Market Note Payable: You purchase equipment on January 1, 2021 and financed the purchase with a Note Payable. It requires interest to be paid quarterly March 31, June 30, Sept. 30 and Dec. 31), has an annual interest \ACCT 311 Intermediate Accounting I Long-Term Liability Excel Assignment Instructions: For each of the scenarios below you will be expected to prepare an amortization schedule in Excel, and answer some related accounting questions. Here are the requirements for the amortization schedules: a) Within each amortization schedule, you are allowed to enter only the inputs related to the information below, and the rest of the amounts should be based on cell references, Excel calculations and Excel functions. I would suggest putting the inputs in the top left part of each Excel Worksheet, with the amortization schedule below these inputs. b) All Amortization schedules should be professional in appearance. Consider formatting, organization, etc. to make them look nice and easy to view. Round all dollar amounts to the nearest dollar. d) Use a separate tab/ worksheet for each Amortization schedule and related questions. Use the space below each Amortization schedule to answer the applicable questions. f No descriptions are needed for the journal entries. Scenarios 1. Bonds Payable: On January 1, 2021, ABC Company issued ten-year bonds with a face value of $5,500,000 and a stated interest rate of 7%, payable semiannually on June 30 and December 31. The bonds were sold to yield 9%. Perform the following rate of 1%, and the final Note Payable payment of $50,000 is due in 6 years. The market rate of interest for this type of purchase is 3% a) Prepare an amortization schedule in Excel for the entire term of the note payable. In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the equipment on January 1, 2021. c) Prepare the joumal entries on March 31, 2021 (first payment) and December 31, 2026 (last payment). d) What is the total cost of Borrowing 1.e. Interest Expense over the life of this note? How much in cash interest was paid over the life of this note? How much total Discount was amortized over the life of the note? 4. Mortgage Payable - Vehicle Loan: You purchase a vehicle for $40,000 on May 1, 2021 at 6% interest. You finance the entire purchase (no down payment). It will be paid off in equal monthly payments over 4 years, with the first payment due on May 31, 2021. Perform the following a) Prepare an amortization schedule in Excel for the entire term of the loan. In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the vehicle on May 1, 2021. - Prepare the joumal entries on March 31, 2021 (first payment) and April 30, 2025 (last payment). d) In regards to the December 31, 2021 Balance Sheet, what amount will show in the 1) current liability section and 2long-term liability section? What is the total amount of payments made over the 4 years? How much of this is interest vs. principal payments? c e a) Prepare an amortization schedule in Excel for the entire term of the bond. In the area below the amortization schedule document the following: b) Calculate the bond price using the Time Value of Money tables from the book (also posted under Blackboard / Course Content / Class Handouts). Prepare the journal entry for the issuance on January 1, 2021 d) Prepare the joumal entries for December 31, 2021 and January 1, 2022. What is the total cost of Borrowing i.e. Interest Expense over the life of this bond? How much in cash interest was paid over the life of this bond? How much total Discount was amortized over the life of the bond 5. Mortgage Payable Home Loan: You purchase a home for $350,000 on January 1, 2022 at 4% interest. You pay $50,000 as a down payment and enter into a Mortgage for the remaining $300,000. It will be paid off in equal monthly payments over 30 years, with the first payment dve on January 31, 2022. Perform the following: a) Prepare an amortization schedule in Excel for the entire term of the mortgage In the area below the amortization schedule document the following: b) Prepare the journal entry for the purchase of the home on January 1, 2022. Prepare the joumal on January payment) December 31, 2050 (last payment) d) In regards to the December 31, 2022 Balance Sheet, what amount will show in the 1) current liability section and 2) long-term lability section? What is the total amount of payments made over the 30 years? How much of this is interest vs. principal payments? How would these numbers change if the interest rate was 6% instead of 4%? You should just be able to change the 1 input cell and your entire schedule should update if it is built using formulas and cell references). e 2. a. Bonds Payable: On January 1, 2015, XYZ Corp. issued 7-year bonds with a face value of $5,500,000 and a stated interest rate of 12%, payable quarterly on March 31, June 30, September 30 and December 31. The bonds were sold to pield 10%. Perform the following a) Prepare an amortization schedule in Excel for the entire term of the bond. In the area below the amortization schedule document the following: b) Calculate the bond price using the Time Value of Money tables from the book (also posted under Blackboard / Course Content / Class Handouts). c) Prepare the journal entry for the issuance on January 1, 2015 d) Prepare the joumal entries for March 31, 2015 and December 31, 2015. e) What is the total cost of Borrowing .e. Interest Expense over the life of this bond? How much in cash interest was paid over the life of this bond? How much total Premium was amortized over the life of the bond 3. Below-Market Note Payable: You purchase equipment on January 1, 2021 and financed the purchase with a Note Payable. It requires interest to be paid quarterly March 31, June 30, Sept. 30 and Dec. 31), has an annual interest