ACCOUNTING FOR BONDS PAYABLE - USING EXCEL COURSE LEARNING OBJECTIVE: Students will use electronic worksheets or other productivity tools to solve problems and develop models. EXCEL LEARNING OBJECTIVES: -Use the built-in formulas to determine the present value (PV) of a bond -Use the built-in formulas to determine the effective issuance rate of a bond with issue costs (RATE) - Set up an automated bond amortization schedule. - Name cells using the Name Box -Use Scenario Manager to create a scenario describing the effects of a change in the market interest rate (an independent variable) on the proceeds from issuing the bond (a dependent variable) and the corresponding total interest expense (a dependent variable) over the term of the bond. Correcting entries for bonds transactions AAA Inc. recently hired a new accountant with extensive experience in tax accounting. Because of the pressures of the new job, the accountant was unable to review the topic of accounting for bonds payable. During the first year, he made the following entries for the issuance of new bonds and the two interest payments. 01/01/19 Cash Accounts Payable DR 607,434 CR 607,434 (Issued $600,000, of 12%, 3-year bonds. Interest is payable semiannually on June 30 and January 1. The effective rate was 11.5%) 06/30/19 Interest Expense Cash 36,000 (First semiannual interest payment) 36,000 12/31/19 Interest Expense 36,000 Interest Payable 36,000 (Second semiannual interest) INSTRUCTIONS: Based on the explanation of each entry. a. Set up an Excel sheet similar to the example below. b. Determine the proceeds from the issuance of bonds using Excel's built-in formula for PRESENT VALUE= PV. Funding c. Set up an amortization schedule (entirely using formulas) in Excel under the effective interest method. d. Prepare the entries (in Excel) that should have been made on 1/1/19 and 6/30/19 assuming the company uses the effective interest method to amortize the premium and discount on its bonds. 1. Use Excel's Scenario Manager to create a scenario describing the effects of a change in the market interest rate (an independent variable) on the proceeds from issuing the bond (a dependent variable) and the corresponding total interest expense (a dependent variable) over the term of the bond. Before starting to use the Scenario Manager, use the Name Box (found above column A) to name the following variables de note accents for space 1. Select the cel where you entered the market at the number) and type in the Name T 2. Select the call where you computed the total interest expense at the end of the amortization schedule) and type 3. Select the cell where you computed the present value proceeds) of the bond and type in the Name Box Example Excel Worksheet CONT 4002 Prof Name of group members: Principal $ Nominal interest rate: Effective interest rate: % This is the Name Box Period Annuity (interest payment): Bond proceeds Present Value: $ Scenario Manager Scenarios are part of a suite of commands called what-if analysis tools provided by Microsoft Excel. What-if analysis is the process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet Excel saves a set of values in a scenario and can automatically substitute these values in your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results Scenario manager is available from the Data tab, in the Forecast group, in the what-if-Analysis command Create the following 4 scenarios using the ADD button Scenario name Changing cell TasaMercado Scenario value 16 (in the screen the appears after you click ok) Scenario name 2 Changing cell TasaMercado (Discount)/Premium $ AMORTIZATION SCHEDULE Date Interest Payment Interest Expense Amortization Book Value 1/1/2019 6/10/19 We are typing the NAME of the variable: Excel will automatically identify the cell address 12/31/19 6/10/20 12/11/20 Scenario value.14 On the screen that appears affor-you click o 6/30/21 12/11/21 Total $ Scenario name 3 Changing cell TasaMercado Scenario value 11 On the screen that appears after you click ok) Scenario name 4 Changing cell TasaMercado Scenario value 09 (in the screen that appears after you click ok) Then select Summary and make sure scenario summary is selected in the next box In the result cells: Type the names of both independent variables separated by a comma GastoTotalinteres,ValorPresente We are typing the NAME of the variables, Excel will automatically identify the cell address. Ob Journal Entries 1/1/2019 and so on... DR CR