Reference no: EM133560621

Scenario:

Company A operates major manufacturing facilities in five regions.
All five regions issued bonds to finance purchases of large pieces of machinery.
Bond discounts are amortized annually on December 31 using the straight-line method.

The Data worksheet contains a list of bonds.

Each row provides information about one bond for one region, including face amount, discount amount when issued, term in years, number of full years the bond has been in effect, and the call price of the bond if it has been redeemed early. If the Call price paid column includes an amount, it indicates that the bond has been repaid.

Required:

Open spreadsheet

Complete three columns on the Data worksheet: Annual amortization, Carrying amount to date, and Gain (Loss) amount.

R1. Cell H2: Enter a formula that results in the annual amortization amount using the data in row 2.
fill in the blank 1 of 1$

R2. Cell I2: Enter a formula that results in the carrying amount to date using the data in row 2.
fill in the blank 1 of 1$

R3. Cell K2: Enter a formula that results in the amount of gain or loss, if applicable, using the data in row 2. Enter a loss, if applicable, as a negative amount. If the bond has not been redeemed, leave the Gain (Loss) amount column blank.
fill in the blank 1 of 1$

R4. Copy down the entries in cells H2, I2, and K2 to all other rows with data.

Steps to create pivot table:

S1. Select a cell in the data on the Data worksheet.
S2. Select Insert / PivotTable. The data range will automatically be inserted. A "Create PivotTable" window should appear.
S3. In the window, under "Choose where you want the PivotTable report to be placed", select "New Worksheet". A new worksheet should appear with the PivotTable.
S4. A PivotTable Field List should pop up. Drag fields into rows, columns, filters, and values areas as needed. (If this gray work box does not appear, right click in the pivot table and select "Show Field List".)

Questions:

Use the information on the Data worksheet and pivot tables, formulas, and/or functions to answer the following questions.

Q1. What is the total carrying amount for bonds held more than 10 years in the York region?
fill in the blank 1 of 1$

Q2. What is the amount of the total net gain or loss for bonds that were redeemed in the Carolina and Jersey regions? (Enter a loss as a negative number.)
fill in the blank 1 of 1$

Q3. What is the total amount of annual amortization for the Mexico region on all bonds that have not yet been redeemed?
fill in the blank 1 of 1$

Q4. What is the amount of the highest call price of any 25 or 30-year term bond?
fill in the blank 1 of 1$

Q5. A bond is redeemed at a loss if

the bond's face amount minus the discount amount at the time of issue is less than the cash paid at redemption.
the bond's face amount plus the discount amount at the time of issue is less than the cash paid at redemption.
the bond's face amount minus the discount amount at the time of redemption is less than the cash paid at redemption.
the bond's face amount plus the discount amount at the time of redemption is less than the cash paid at redemption.