Please just let me know if i have to upload something else
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 DR CR 01/01/19 Cash 607,434 Accounts Payable 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 36,000 Cash 36,000 (First semiannual interest payment) 12/31/19 Interest Expense 36,000 Interest Payable 36,000 (Second semiannual interest) 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 Function Arguments 7 DV itate Nper Pal Tv Type - number - number Return the present value of an investment the total amount that a wes of Iture payments is worth w Rate the rest vote per period. For cumple for quarterly payments at AR 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. 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. e. Assuming the company closes its books on December 31, prepare the journal entries for 12/31/19 and 1/1/20 f. What is the effect of these errors on Net Income, Earnings per Share, Total Liabilities and Retained Earnings if they are not discovered (detected) before the publication of the annual report? g. Assuming the error was discovered at the end of 2019 but before closing the books, prepare the necessary journal entry(ies) to correct the error. h. Assuming the error was discovered at the beginning of January of 2020, prepare the necessary journal entry(ies) to correct the error, i. 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 (do not use accents nor spaces): 1. Select the cell where you entered the market rate the number) and type in the Name Box: TasaMercado 2. Select the cell where you computed the total interest expense (at the end of the amortization schedule) and type in the Name Box: Gasto Totalinteres. 3. Select the cell where you computed the present value (cash proceeds) of the bond and type in the Name Box ValorPresente This is the Name Box A1 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 = 1 Changing cell = TasaMercado Scenario value= 16 (in the screen that appears after you click ok) We are typing the NAME of the variable: Excel will automatically identify the cell address. ? X Add Sceni Scenario BAR Changing tascade Cenon acent changing te C Created by Francsorbiton 2/1/2019 Scenario name = 2 Changing cell = TasaMercado Scenario value= .14 (in the screen that appears after you click ok) Scenario name = 3 Changing cell = TasaMercado Scenario value= .11 (in 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) Protection Dewenhange OK Cancel Then select Summary and make sure scenario summary is selected in the next box 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: Gasto Totallnteres, Valor Presente Scenario Summary ? We are typing the NAME of the variables: Excel will automatically identify the cell address. Report type Scenario summary Scenario PivotTable report Result cells: GastoTotalInteres, Valor Presente OK Cancel Finally press OK and Excel will create a table with the results for each scenario. Briefly, summarize your findings. j. Assume that on January 1, 2019, AAA had bond issue costs of $5,000. Use Excel's built-in formula for RATE to determine the effective interest rate of the net liability on the issuance date. Finally press OK and Excel will create a table with the results for each scenario. Briefly, summarize your findings. J. Assume that on January 1, 2019, AAA had bond issue costs of $5,000. Use Excel's built-in formula for RATE to determine the effective interest rate of the net liability on the issuance date. What effect has the debt issue costs on the effective interest rate when compared against the original effective interest rate? DELIVERABLES: The Excel file. Example Excel Worksheet CONT 4002 Prof. Name of group members: $ % Principal: Nominal interest rate: Effective interest rate: Periods: Annuity (interest payment): % $ Bond proceeds = Present Value: (Discount)/Premium $
