COURSE LEARNING OBJECTIVE: Students will use electronic worksheets or other productivity tools to solve problems and devolop models. EXCEL LEARNING OBJECTIVES: - Use the built-in formulas to determine the present value (PV) of a bond - Use the bull-in formulas to determine the effective issuance rate of a bond with issue costs (RATE) - Set up an aulomated bond amortization schodule. - 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 varlabie) over the term of the bond. Correcting entries for bonds transactions AAA inc. recently hired a new acocuntant with extensive experience in tax accounting. Because of the pressures of the new job, the accountant was unable to revew the lopic of accounting for bonds payable. During the first year, he made the following entries for the issuance of new bonds and the fwo interest. pavanents. INSTRUCTIONS: Based on the explanation of each ertry. a. Set up an Excel sheet similar to the example below. b. Determine the proceeds from the issuance of bonds using Excer's buit-in formula for PRESENT VALUE = PV. 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 compary closes its books on December 31", prepare the journal entries for 1231/19 and. 1/1/20. f. What is the effect of these errors on Net income, Earrings per Share. Total Liabilities and Retained Eamings if they are not discovered (detected) before the publication of the annual report? 9. Assuming the ecror was discowered at the end of 2019 but before closing the books, prepare the necessary journal entrylies) to oorrect the error. h. Assuming the error was discovered at the beginning of danuary of 2020. prepare the necessary journal entry(ies) to correct the error. L. 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 nof use accents nor apuces): 2. Select the cel where you computed the but interest expense (at the end od the amentization schetule) and bes. in the Name Box: Gasto Totalinteres. 3. Seloct the cel where you computed the presont value (cash proceeda) of the bond and type in the Name Box: Scenario Manager Scenarios are part of a suite of commands called what it analysis tools provided by Microsolt 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 substtute 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 dillerent results. Scenario manager is available from the Data tab, in the Forecast group, in the what-ff-Analysis command. Create the following 4 scenarios using the ADD button: Scenario name =1 Changing cell = Tasalercado 4 Scenario value= 16 (in the screen that appears after you click ok) Scenario name =2 Changing cell = TasaMercado Scenario value= .14 (in the screen that appears afterypu 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) 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. Finally press OK and Excel will create a table with the results for each scenario. Briefly, summarize your findings. 1. 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 Prot. Name of group members: Principal: Nominal interest rate: Effective interest rate: Periods: Annuity (interest paryment): Bond proceeds = Present Value: (Discount)/Premium 55 AMORTIZATION SCHACDUE \begin{tabular}{|c|c|c|c|c|} \hline Date & InterestPayment & InterestExpense & Amortimetion & Hock Value \\ \hline 1/1/2019 & & & & \\ \hline 6/30/19 & & & & \\ \hline 12/31/19 & & & \\ \hline 6/30/20 & & & \\ \hline 12/31/20 & & & \\ \hline 6/30/21 & & & \\ \hline 12/31/21 & & & \\ \hline \end{tabular} Tonal. 555 \begin{tabular}{|l|l|c|c|} \cline { 2 - 5 } \multicolumn{1}{|l|}{ Journal Entries } & DR & CR \\ \hline 1/1/2019 & & & \\ \hline & & & \\ \hline \end{tabular} and 50 on