On January 1, 20X4, CharmsCorporation sold 10% bonds having a maturity value of $700,000. The market determined that 12% was the appropriate rate of interest, given the risks that CharmsCorporationposes to bondholders. The bondsare dated January 1, 20X4, mature January 1, 20X9, and pay interest on June 30 and December 31 of each year.

CharmsCorporation does not elect to report its liabilities at fair market value. Ignore taxes for this assignment.

Required:

1-Determine the amount that bondholders (investors) will pay Charms Corporation for these bonds when the bonds are issued on January 1, 20X4.

2-Prepare an amortization schedule for the bond issue using the effective interest method.

3-Prepare the following 20X4financial statements for CharmsCorporation:

Statement of Cash Flows

Income Statement*

Balance Sheet

4-Prepare the following 20X6financial statements for CharmsCorporation:

Statement of Cash Flows

Income Statement

*Balance Sheet

when preparing the balance sheets for 20X4and 20X6list the bond payable at the face value of the bond, and list the any remaining discount or premium on the bond separately. Similar to the format used in the pdf files of the handouts posted on Canvas, under the Module named Financing Decisions -Debt

5-Prepare CharmsCorporation's journal entries related to the bond issue for 20X4, 20X6, and 20X9(upon maturity of the bond).

6-Each member must fill in one of the text boxes (found in the worksheet named Group Charter) explaining his or her respective role and contribution to the Excel Group Assignment before submitting the assignment.

Helpful Hints:

Make sure you use formulas for calculations and totals when applicable. Make sure your numbers are properly formatted. For example, the number 50 million in excel should look something like this 50,000,000. Round to whole numbers. If an assignment requires calculating earnings per share, then round to two decimal places. In addition, negative numbers/amounts should be formatted to appear as in parenthesis in Excel. For example, the negative number -3,000 should appear in Excel as (3,000).

When preparing the financial statements, it is better to just enter the numbers on the financial statements instead of referencing a cell from the amortization schedule. Often times, due to rounding, when referencing a cell from the amortization schedule you will end up with a rounding difference in your totals on the financial statements. At least this is a problem students have encountered in the past, and the numbers on the financial statements did not correspond to the numbers shown the amortization schedule.

H. Excel Bond Issue Worksheets Version 1.xlsx - Excel Tell me what you want to do File Home Insert Page Layout Formulas Data Review View Help HEM ab Wrap Text ALL General X Cut Copy Format Painter Clipboard Calibri - 12 A BIU U...A Paste Merge & Center 10 .000 Insert Delete Format Conditional Format as Cell Fomatting Table Styles Styles AutoSurm Fill S Clear F Editiru Font Alignment Number Colls 011 35000 1 J K L V N P A B D E 1 Requirement 1 2 3 Friendly reminder: For this assignment, you must use the Present Value Tables found at the end of your textbook to 2 calculate the price or the bund. In the space below, show how you determined the price of the hond. You must show the rates used and the name of the lables where you uured the rules, kur cxarriple, Present value of $1. 5 6 7 8 9 10 n= 6% 10 11 i= 12 13 PV of Interest Payments: 14 35000 15 15 1/ PV of Face (maturity) Value of Bond: 18 19 21 Price of Bond 22 23 24 25 Requirement 2 25 27 Amortization of 28 Cash Interest Effective Discount or 29 Date Payments Interest Premium *Amortized Cost 31/1/Y Requirements 1 & 2 Requiremerts 3 & 4 Requirement i Group Charter + Ready Excel Bond Issue Worksheets Version 1.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do % Cut 02- ab Wrap Text General Fe Copy Calibri - 12 BLUE Paste . Merge & Center - $ % 0.00 000 Format Painter Clipboard is Conditional Format as Formatting Table Styles Font Alignment Number B14 fx 35000 F G H LILAS............. B LIIL LUVIL WIILILyuu TUUIIULI IL TULLS. 1 J 7 8 9 10 ns 6% 11 i= 10 12 13 PV of Interest Payments: 14 35000 15 16 17 PV of Face (maturity) Value of Bond: 18 19 20 21 Price of Bond 22 23 24 25 Requirement 2 26 27 28 29 Date 30 1/1/X4 31 6/30/X4 32 12/31/X4 33 Cash Interest Payments Effective Interest Amortization of Discount or Premium *Amortized Cost 34 35 26 Requirements 1 & 2 Requirements 3 & 4 Requirements Group Charter + Excel Bond Issue Worksheets Version 1.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Help EMS Tell me what you want to do Cut Calibri - 11 - ab Wrap Text General s Copy Paste BIU Merge & Center - $ - % !! 000 011 7.13 Insert Delete Format Format Painter Clipboard Conditional Format as Cell Formatting Table Styles Styles Font Alignment Number 5 Cells F121 A B D E G H 1 j K L M N 1 Requirement 3 2 3 4 5 S 20x4 ? 8 9 Operating Activities 10 11 12 Financing Activities 13 14 15 Change in cash 16 10 17 18 19 20 21 22 23 20X4 24 25 Revenues 26 Expenses 27 Other income (expense) 28 29 30 Net innmarel Requirements 1&2 Requirements 3 & 4 Recuirements Group Charter Reach AS Excel Band Issue Worksheets Versioni.xlsx - Excel Hongbin Du File Home Inset! Page Layout Formulas Review View Help FENA Tell me what you want to do X Cut * Calibri 11 Wrap Text General AYO - Copy Paste BIU. TA Merge & Center $ %, . 000 Insert Delete Format AutoSum- I Fill Sort & Find & Clear- Filter - Select Editing Conditional Format as Cell Formatting Table Styles 3 ERNE * Format Painter Clipboard Forel 1 Aligrurierat Nurnber Cells 1121 G L D E F M N P R S 4 25 Nevenues 26 Expenses " Other inconuexpense 19 30 Net income ilossi 3 22 2.3 24 25 Cantinue on Next Page 36 37 18 19 40 41 12 13 1.1 Hequirement 3 (Continued) 15 46 47 46 49 I 20x4 5 52 Assets 55 Current one! 1:4 Total Anats 56 57 Requirements & 2 Requirements 3 &4 Recuirements COLCailer + H 5 Excel Bond Issue Worksheets Version 1.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do IT = Calibri 11 A A Wrap Text General X Cut Copy Format Painter Clipboard AutoSu Fill Clear Paste BTU A Merge & Center - - $ - %, 6.1.09 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Font IN Alignment N Number Cells F121 fy 3 E H J K 2 M N P K . 12 11 Requirement 3 (Continued) -13 -16 -17 18 -19 30 = 1 - Assets - Current assets -4 == Total Assets 20x4 :! - Liabilities and Shareholders' equity -9 Long-term liabilities: **?!?"8 1 ? 3 Total long-term liabilities 14 + Shareholders' equity ! Total shareholders' equity 9 Total liabilities and shareholders' equity 10 71 !! 73 14 Continue on Next Page 7: cumarts 1&2 Requirements 3 & 4 keguicnions Group Charler Readly + Excel Bond Issue Worksheets Version 1.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do * Cut Copy Calibri - 11 Wrap Text General Paste BIU.. >> - Merge & Center $ - % 00 .000 Format Painter Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number F121 x A B D E F G H 1 J K L M 85 86 87 Requirement 4 88 89 90 91 92 93 20X6 94 95 Operating Activities 96 97 98 Financing Activities 99 100 101 Change in cash 102 103 104 105 108 107 108 109 20X6 110 111 Revenues 112 Expenses 113 Other income (expense) 114 115 116 Net income (loss) 117 Requirements 1 & 2 Requirements 3 & 4 Requirements Group Charter Ready # 5 Excel Bond Issue Worksheets Version 1.xlsx - File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do * Cut Calibri 11 2 Wrap Text General Copy Paste BIU A. Merge & Center. $ - %, 6.0 00 .00 0 Format Painter Conditiona Formatting Clipboard s Font Alignment Number F121 fx E F G H J K A B C D 115 116 Net income (loss) 117 118 119 120 121 Continue on Next Page 122 123 124 125 126 127 128 129 130 Requirement 4 (Continued) 131 132 133 134 135 136 20X6 137 138 Assets 139 Current assets 140 141 Total Assets 142 143 144 Liabilities and Shareholders' equity 145 Long-term liabilities: 146 147 Requirements 1 & 2 Requirements 3 & 4 Requirement 5 Group Charter Ready + 5 Excel Bond Issue Worksheets Version 1.xlsx File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri 11 ab Wrap Text General * Cut Copy Format Painter Paste BIU A- Merge & Center $ - % 6.0 .00 00 0 Condition Formattin Clipboard Font is Alignment Number F121 fx B D E F G H K 20X6 A 127 128 129 130 Requirement 4 (Continued) 131 132 133 134 135 136 137 138 Assets 139 Current assets 140 141 Total Assets 142 143 144 Liabilities and Shareholders' equity 145 Long-term liabilities: 146 147 148 149 Total long-term liabilities 150 151 Shareholders' equity 152 153 Total shareholders' equity 154 155 Total liabilities and shareholders' equity 156 157 158 159 Requirements 1 & 2 Requirements 3 & 4 Requirement 5 Group Charter + Excel Bond Issue Worksheets Version 1.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do * Cut Calibri 11 ab Wrap Text General E Copy Paste BIU 9. A. $ %) Merge & Center e. .00 .00 0 Format Painter Conditional Forma Formatting Table Styles Clipboard Font Alignment Number A5 > fx B C D E F G H 1 J General Ledger (GL) Account Debit Credit A 1 Requirement 5 2 3 Date 4 1/1/X4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Requirements 1&2 Requirements 3 & 4 Requirement 5 Group Charter + Raadur