Complete a Bond Amortization Schedule for

Stationery Specialists

In this exercise, you will complete a bond amortization schedule for Stationery Specialists. You will calculate the bond characteristics and generate a schedule with key components. You will protect worksheet elements and create a macro.

Start a new file. Reduce the width of column A to 0.75 and the height of row 1 to 7.20. Enter the bond details in the range B2:C8. Include a centered header and appropriate titles. Enter the details for a bond with a $140,000 face value, a six-year life, quarterly interest payments, a contract interest rate of 8%, and an effective interest rate of 7%. The bond is issued on 1/1/21.

(Hint: Values must be entered here so they can be used in subsequent formulas.)

Enter bond calculations for the present and future values and the payments in the range

B10:C13. The future value and payment calculations will generate results that match figures given above; you complete these calculations to check your work. Create a bond amortization schedule beginning in cell B15. Use headers that are consistent with those used in this chapter.

Calculate totals for the appropriate columns. Assign an appropriate name to the worksheet tab.

Record a macro that generates every formula in the worksheet after the bond details (in the range B2:C8) and dates have been entered. Run the macro to ensure it operates properly. Insert a text box in an appropriate location, add an appropriate name in the text box, and assign the macro to this text box.

Protect the workbook such that worksheets cannot be reordered, copied, or deleted. Modify cell protection for all cells containing formulas in column G such that the formulas are hidden.

Protect the worksheet by allowing users to format only cells, columns, and rows; apply