INCLUDED EXCEL FORMULA

252 Excel for Accounting Chapter 8: Bond Amortization Apply Your Skills EA8-A1 Create a Bond Amortization Schedule for Kim's Kitchen In this exercise, you will complete a bond amortization schedule for Kim's Kitchen. You will calculate the bond characteristics and generate a schedule with key components. You will also protect worksheet elements and create a macro. The bond is issued on 1/1/10 and has a $120,000 face value, a seven-year life, semiannual interest payments, a contract interest rate of 12%, and an effective interest rate of 10%. 1. Open a Blank Workbook and save the file in your Chapter 08 folder as: EA8-A1-Bond-[YourName] 2. Change the width of column A to 0.75 and the height of row 1 to 7.2. 3. Merge and center the range B2:C2, type Bond Details in the merged cell, and bold the entry. 4. In the range B3:38, enter the descriptions Date, Face Value, Contract Rate, Effective Rate, Life (years), and Payments per Year. 5. Enter the bond details in the range C3:C8; center these entries. 6. Apply borders to the appropriate locations in the range B2:C8. 7. Adjust the column width of columns B:G to 15. 8. Merge and center the range B10:010, type Bond Calculations in the merged cell, and bold the entry. 9. In the range B11:B13, enter the descriptions Present Value, Future Value, and Payment. 10. In the range C11:013, enter appropriate formulas that contain solely cell references and apply the Accounting number format. 11. Apply borders to the appropriate locations in the range B10:C13. Complete the Amortization Schedule 12. In the range B15:G15, set text wrapping and center alignment, apply an outside border, and add bold formatting. 13. In the range B15:G15, enter the column descriptions for the bond amortization schedule. 14. In the range B16:B18, enter dates for the bond issuance and the first two interest payments. 15. Apply additional dates as appropriate in column B. 16. Type appropriate formulas in the necessary cells in rows 16-17 of the bond amortization schedule. Apply Your Skills 253 17. Copy the formulas in row 17 to the remaining rows in the bond amortization schedule. 18. In column B below the final populated row of the bond amortization schedule, enter Totals and right-align the text. 19. Use AutoSum in the Totals row to calculate the appropriate columns and apply the Accounting number format. 20. Add appropriate borders to the entire bond amortization schedule. Protect a Workbook and a Worksheet 21. Use the Protect Workbook command and apply a password. 22. Turn off workbook protection. 23. Use the Protect Worksheet command, ensuring that all cells can be selected and all columns can be formatted while worksheet protection is on; apply a password. 24. Set the width of column C to 12 and autofit the width of column B. 25. Turn off worksheet protection. Protect Individual Cells 26. Toggle off the Lock Cell command for all cells in the Bond Details and bond amortization schedule sections. 27. Hide the formulas in the Bond Calculations section. 28. Use the Protect Sheet command, ensuring that all cells can be selected while worksheet protection is on; apply a password. 29. Rename the worksheet to: Bond Schedule Record and Run a Macro 30. Ensure that Disable All Macros with Notification is the active macro setting. 31. Delete all data in the bond amortization schedule except the dates and headers. 32. Record a macro that populates the bond amortization schedule, applying an appropriate name for the macro. 33. Delete all data in the bond amortization schedule except the dates and headers and then run your macro. 34. Unprotect the worksheet. 35. Insert a text box from which to run the macro in an appropriate location and add an appropriate name in bold; assign your macro to the text box. 36. Use the Protect Sheet command, ensuring that all cells can be selected while worksheet protection is on; apply a password. 37. Delete all data in the bond amortization schedule except the dates and headers and then use the text box to run your macro. 38. Save your file as a Macro-Enabled Workbook and then close it