ch14_sol

Instructions for the Microsoft Excel Templates b y Rex A Schildhouse

B e a d v i s e d , t h e t em e m p l a te t e w o r k b o o k s a n d w o r k s h e e t s a r e n o t p r o t e c t ed ed . O v e r ty ty p i n g a n y d a t a m a y r e m o v e i t . Extensive detail and information is contained within the help function of Microsoft Excel and in the provided text. You should enter your name, date, instructor's name, and course into the cells at the top of the page. This information will be printed on the top of each page if the template requires more more than one page.

Each template is set to print with File Name, Page # of # Page(s), the print date, and the print time to assist in assembly of multiple pages. If more than one page is required by the template, manual page breaks have been set to provide consistent presentation. All of the cells have been correctly formatted for presentation and should not require any adjustment. For example, if the text requires one, two, or three significant digits in a presentation, the template has been set for that presentation in the appropriate cells. In general, the yellow highlighted cells are the cells which work and effort should be presented. These entries may include date(s), account title(s), values, memorandum appropriate to the entry, or t ext answers to questions. And information or data which may be required by the solution will be entered in cells with borders to help identify them. Where a yellow highlighted cell shows "Date" enter the appropriate date for that step of the challenge. This may be any date format that Microsoft Excel accepts. Some of these formats include "1/1/12", "01/01/ 12", and "01/01/2012. " All of these will return January 01, 2012, in the format set in the t emplate. Where a yellow highlighted cell shows "Acct Nbr" enter the appropriate account number, provided in the template and in the text for that step of the challenge. This is entry may be a "Look to" formula to another cell where that information has been provided or previously entered. Where a yellow highlighted cell shows "Account Title" enter the appropriate account title for that step of the challenge. This is a text entry and most of those cells are set for the proper indentation for that step. Frequently the chart of accounts appropriate to the challenge is provided and you can use the " look to" formula to reference the appropriate account title without typing it. Check with your instructor to see if abbreviated account titles are acceptable. For example "A/R" for Accounts Receivable, "A/P" for Accounts Payable. If your instructor is using a comparison process between workbooks for grading, these abbreviates may not be acceptable. Where a yellow highlighted cell shows titles such as "Values," "Amounts," or "Quantities" enter t he appropriate numerical value for that step of the challenge. The cell is formatted for proper presentation of the entered information. If a dollar sign is appropriate, it should not be entered, Microsoft Excel will place it there through formatting. Commas and significant digits (decimals) are also set through formatting for common presentation. presentation. Since the formatting of the templates is not protected by an assw asswor ord, d, ou ma chan chan e an of the the form format attin tin foun found d in the the tem tem late latess to meet meet our our desi desire res. s. Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical value appropriate for that step of the challenge. Most of the values necessary for for the appropriate formula are located on the template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money time-value-of-money formula.

These are addressed in the tutorial text provided for Microsoft Excel. Where a yellow highlighted cell shows "Text" enter the appropriate text for that step of the challenge. This may be a memorandum entry for a journal entry or a lengthy text answer discussing the results of an analysis of a company's financials. These titles can simply be typed over. Where a yellow highlighted cell shows titles such as "Journal Number" or "Journ #" you should enter the appropriate number provided in the template and in the text for that step of the challenge. In general this will appear in instances such as "Record the following events in General Journal number six." The print area is defined to fit onto 8 1/2" × 11" sheets in portrait or landscape mode as required. Margins are generally set to no less than 1/2" so most printers can print them without a problem. If you printer cannot accept margins less than 1" you may have to reformat the margins through Page Setup. The display may have "Freeze Pane" invoked so column titles remain visible during data entry. This can be removed by utilizing the View menu and selecting "Unfreeze Panes" under "Freeze Panes." When negative values are required, enter t hem by starting with a minus sign, "-". Negative values may be shown as ($400) or -$400. Negative values in formulas can be created by putting a minus sign in front of the cell reference - "=E10*-E11" will return a negative value if both cells E10 and E11 contain positive values. Microsoft Office and Microsoft Excel are products of, and copyrighted by, Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399

Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E14-4 (Entries for Bond Transactions—Straight-Line) Foreman Company issued

$800,000 of 10% 20 -year bonds on January 1, 2013, at 102 Interest is payable semiannually on July 1 and January 1. Foreman Company uses the straight-line method of amortization for bond premium or discount. Instructions:

Prepare the journal entries to record the following. (a) The issuance of the bonds. Jan 1, 13

Cash ($800,000 × 102%) Bonds Payable Premium on Bonds Payable

816,000 800,000 16,000

(b) The payment of interest and related amortization on July 1, 2013.

Jul 1, 13

Interest Expense Premium on Bonds Payable ($16,000 / 40 periods) Cash [$800,000 × 10% × (6/12)]

39,600 400 40,000

(c) The accrual of interest and the related amortization on December 31, 2013.

Dec 31, 13

Interest Expense Premium on Bonds Payable ($16,000 / 40 periods) Interest Payable [$800,000 × 10% × (6/12)]

39,600 400 40,000

153010924.xlsx.ms_office, Exercise 14-4 Solution, Page 3 of 12, 6/20/2013, 6:59 AM

Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E14-4 (Entries for Bond Transactions—Straight-Line) Foreman Company issued

$800,000 of 10% 20 -year bonds on January 1, 2013, at 102 Interest is payable semiannually on July 1 and January 1. Foreman Company uses the straight-line method of amortization for bond premium or discount. Instructions:

Prepare the journal entries to record the following. (a) The issuance of the bonds. Jan 1, 13

Account Title Account Title Account Title

Amount Formula Formula


Jul 1, 13


Formula Formula Formula


Dec 31, 13


Formula Formula

153010924.xlsx.ms_office, Exercise 14-4, Page 4 of 12, 6/20/2013, 6:59 AM

Formula

Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse Foreman Company issued $800,000 of 10% 20 year bonds on January 1, 2013 at 102 Interest is payable semiannually on July 1 and January 1. Foreman Company uses the effective interest method of amortization for bond premium or discount. Assume an effective yield of 9.7705% Instructions:

Prepare the journal entries to record the following. (Round to the nearest dollar.) (a) The issuance of the bonds. Jan 1, 13

Cash ($800,000 × 102%) Bonds Payable Premium on Bonds Payable

816,000 800,000 16,000


Jul 1, 13

Interest Expense [$816,000 × 9.7705% × (6/12)] Premium on Bonds Payable Cash [$800,000 × 10% × (6/12)]

39,864 136 40,000


Dec 31, 13

Interest Expense [$816,000 × 9.7705% × (6/12)] Premium on Bonds Payable [$815,864×9.7705×(6/12)] Interest Payable [$800,000 × 10% × (6/12)] Carrying amount of bonds at July 1, 2013: Carrying amount of bonds at January 1, 2013 Amortization of bond premium [$40,000 - $39,864] Carrying amount of bonds at July 1, 2013, $815,864

39,857 143 40,000

$816,000 (136) $815,864

153010924.xlsx.ms_office, Exercise 14-5 Solution, Page 5 of 12, 6/20/2013, 6:59 AM

Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse Foreman Company issued $800,000 of 10% 20 year bonds on January 1, 2013 at 102 Interest is payable semiannually on July 1 and January 1. Foreman Company uses the effective interest method of amortization for bond premium or discount. Assume an effective yield of 9.7705% Instructions:

Prepare the journal entries to record the following. (Round to the nearest dollar.) (a) The issuance of the bonds. Jan 1, 13


Formula Amount Amount


Jul 1, 13




Dec 31, 13

Account Title Account Title Account Title Carrying amount of bonds at July 1, 2013: Text title Text title Text title



153010924.xlsx.ms_office, Exercise 14-5, Page 6 of 12, 6/20/2013, 6:59 AM

Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P14-2 (Issuance and Retirement of Bonds) Venezuela Co. is building a new hockey arena at a

cost of $2,500,000 . It received a downpayment of $500,000 from local businesses to support the project, and now needs to borrow $2,000,000 to complete the project. It therefore decides to issue $2,000,000 of 10.50% 10 -year bonds. These bonds were issued on January 1, 2011, and pay interest annually on each January 1. The bonds yield 10.00% . Venezuela paid $50,000 in bond issue costs related to the bond sale. Note: Use of tables or financial calculators may result is slightly different values due to rounding and

significant digits. Instructions:

(a) Prepare the journal entry to record the issuance of the bonds and the related bond issue costs

incurred on January 1, 2011. Jan 1, 11

Present value of the principal = $2,000,000 × 0.38554 = (PV of $1 for 10 periods at 10%) = $578,310 Present value of principal formula =

$771,087

Present value of the interest = $210,000 × 6.14457 = (PV of a $1 anuity for 10 periods at 10%) = $967,770

Jan 1, 09

Present value of interest formula =

$1,290,359

Present selling value of the bonds =

$2,061,446

Cash Unamortized Bond Issue Costs Bonds Payable Premium Bonds Payable

2,011,446 50,000 2,000,000 61,446

(b) Prepare a bond amortization schedule up to and including January 1, 2015, using t he effective

interest method.

Date

Jan 1, 11 Jan 1, 12 Jan 1, 13 Jan 1, 14 Jan 1, 15

Interest Paid

$210,000 210,000 210,000 210,000

Interest Expense

$206,145 205,759 205,335 204,868

Premium Amortization

$3,855 4,241 4,665 5,132

on Carrying Value

$2,061,446 2,057,590 2,053,349 2,048,684 2,043,553

153010924.xlsx.ms_office, Problem 14-2 Solution, Page 7 of 12, 6/20/2013, 6:59 AM

Name: Instructor:

Solution

Date: Course:

(c) Assume that on July 1, 2014, Venzuela Co. retires half of the bonds at a cost of

$1,065,000

plus accrued interest. Prepare the journal entry to record this retirement. Hint: Resolve value of unamortized bond issue costs f or the bonds being retired.

Unamortized bond issue costs Years of bond issue Unamortized bond issue costs per year Unamortized bond issue costs per six months Six month periods to July 1, 2012 Unamortized bond issue costs to July 1, 2012 Remaining unamortorized bond issue costs as of July 1, 2012 Bonds retired as a percentage of bonds issued Value of remaining unamortized bond issue costs to retired bonds

$50,000 10 $5,000 $2,500 7 $17,500 $32,500 50% $16,250

Hint: Resolve carrying value of the bonds being retired.

Date

Interest Paid

Interest Expense


on Carrying Value

Jan 1, 11 $206,145 $3,855 Jan 1, 12 $210,000 205,759 4,241 Jan 1, 13 210,000 210,000 205,335 4,665 Jan 1, 14 Percentage of bonds to be retired in the year Carrying value on Jan 1, 2014, of the bonds to be retired Interest on bonds to be retired as of July 1, 2014 51,217 1,283 Jul 1, 14 52,500

$2,061,446 2,057,590 2,053,349 2,048,684 50% 1,024,342

Reacquisition price Carrying value as of July 1, 2014

$1,065,000 1,023,059 41,941 16,250 $58,191

Unamortized bond issue costs Loss Entry for accrued interest Interest Expense [1,024,342 × 10.00% × (6/12)] Premium on Bonds Payable Cash [$1,000,000 × 10.50% × (6/12)] Entry for reacquisition Bonds Payable ($2,000,000 × 50%) Premium on Bonds Payable Loss on Redemption of Bonds Unamortized Bond Issue Costs Cash

1,023,059

51,217 1,283 52,500

1,000,000 23,059 58,191 16,250 1,065,000

The loss is reported as an ordinary loss.


Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P14-2 (Issuance and Retirement of Bonds) Venezuela Co. is building a new hockey arena at a

cost of $2,500,000 . It received a downpayment of $500,000 from local businesses to support the project, and now needs to borrow $2,000,000 to complete the project. It therefore decides to issue $2,000,000 of 10.50% 10 -year bonds. These bonds were issued on January 1, 2011, and pay interest annually on each January 1. The bonds yield 10.00% . Venezuela paid $50,000 in bond issue costs related to the bond sale. Note: Use of tables or financial calculators may result is slightly different values due to rounding and

significant digits. Instructions:

(a) Prepare the journal entry to record the issuance of the bonds and the related bond issue costs

incurred on January 1, 2011. Jan 1, 11

Present value of the principal = $2,000,000 × 0.38554 = (PV of $1 for 10 periods at 10%) = $578,310 Present value of principal formula =

Formula

Present value of the interest = $210,000 × 6.14457 = (PV of a $1 anuity for 10 periods at 10%) = $967,770

Jan 1, 11

Present value of interest formula =

Formula

Present selling value of the bonds =

Formula

Account title Account title Account title Account title

Formula Amount Amount Formula

(b) Prepare a bond amortization schedule up to and including January 1, 2015, using t he effective

interest method.

Date

Jan 1, 11 Jan 1, 12 Jan 1, 13 Jan 1, 14 Jan 1, 15

Interest Paid

Formula Formula Formula Formula

Interest Expense




on Carrying Value

Formula Formula Formula Formula Formula

153010924.xlsx.ms_office, Problem 14-2, Page 9 of 12, 6/20/2013, 6:59 AM

Name: Instructor:

Date: Course:

(c) Assume that on July 1, 2014, Venzuela Co. retires half of the bonds at a cost of

$1,065,000

plus accrued interest. Prepare the journal entry to record this retirement. Hint: Resolve value of unamortized bond issue costs f or the bonds being retired.

Unamortized bond issue costs Years of bond issue Unamortized bond issue costs per year Unamortized bond issue costs per six months Six month periods to July 1, 2014 Unamortized bond issue costs to July 1, 2014 Remaining unamortorized bond issue costs as of July 1, 2014 Bonds retired as a percentage of bonds issued Value of remaining unamortized bond issue costs to retired bonds

Amount Number Formula Formula Number Formula Formula Percenage Formula

Hint: Resolve carrying value of the bonds being retired.

Date

Interest Paid

Interest Expense


Jan 1, 11 Formula Formula Jan 1, 12 Formula Formula Formula Jan 1, 13 Formula Formula Formula Formula Jan 1, 14 Percentage of bonds to be retired in the year Carrying value on Jan 1, 2014, of the bonds to be retired Interest on bonds to be retired as of July 1, 2014 Formula Formula Jul 1, 14 Formula Reacquisition price Carrying value as of July 1, 2014 Unamortized bond issue costs Loss Entry for accrued interest Account title Account title Account title Entry for reacquisition Account Title Account Title Account Title Account Title Account Title

on Carrying Value

Formula Formula Formula Formula Percentage Formula Formula Amount Amount Formula Formula Formula

Amount Amount Amount

Amount Amount Amount

Enter text as appropriate.


Amount Amount

Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse


Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse


ch14_sol

Recommend Documents