にCOURSE LEARNING OBJECTIVE: Students will use electronic worksheets or other productivity tools tosolve 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 (anindependent variable) on the proceeds from issuing the bond (a dependent variable) and the correspondingtotal interest expense (a dependent variable) over the term of the bond.Correcting entries for bonds transactionsAAA Inc. recently hired a new accountant with extensive experience in tax accounting. Because of thepressures 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 interestpayments.CRDR607,43401/01/19 Cash607,434Accounts Payable(Issued $600,000, of 12%, 3-year bonds. Interest is payable semiannually onJune 30 and January 1. The effective rate was 11.5%.)06/30/19 Interest ExpenseCash000'9(First semiannual interest payment)12/31/19 Interest ExpenseInterest Payable(Second semiannual interest)000'9INSTRUCTIONS: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 =Function ArgumentsRate|Pmtnumber%3DReturns the present value of an investment: the total amount that a series of future payments is worth now.Rate is the interest rate per period. For example, use 6%/4 for quarterlypayments at 676 APR.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 companyuses the effective interest method to amortize the premium and discount on its bonds.e. Assuming the company closes its books on December 31st, prepare the journal entries for 12/31/19 and1/1/20.f. What is the effect of these errors on Net Income, Earnings per Share, Total Liabilities and RetainedEarnings if they are not discovered (detected) before the publication of the annual report?a. Assuming the error was discovered at the end of 2019 but before closing the books, prepare the necessaryjournal entry(ies) to correct the error.h. Assuming the error was discovered at the beginning of January of 2020, prepare the necessary journalentry(ies) to correct the error. i. Use Excel's Scenario Manager to create a scenario describing the effects of a change in the market interestrate (an independent variable) on the proceeds from issuing the bond (a dependent variable) and thecorresponding total interest expense (a dependent variable) over the term of the bond.Before starting to use the Scenario Manager, use the ame Box (found above column A) toname 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: TasaMercado2. Select the cell where you computed the total interest expense (at the end of the amortization schedule) and typein the Name Box: Gasto Totallnteres.3. Select the cell where you computed the present value (cash proceeds) of the bond and type in the Name BoxValorPresenteThis is theA1Name BoxScenario ManagerScenarios are part of a suite of commands called what-if analysis tools provided by Microsoft Excel. What-ifanalysis is the process of changing the values in cells to see how those changes affect the outcome offormulas on the worksheet. Excel saves a set of values in a scenario and can automatically substitute thesevalues in your worksheet. You can create and save different groups of values as scenarios and then switchbetween 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 = 1Changing cell = TasaMercado-Scenario value= .16 (in the screen that appears after you click ok)We are typing the NAME of thevariable; Excel will automaticallyidentify the cell address.Add ScenarioScenario name = 2Scenario nameChanging cell = TasaMercadoScenario value= .14 (in the screen that appears after you click ok)4.Changing cells:TasaMercadoCtrl+dick cells to select non-adjacent changing cells.Scenario name = 3CommentCreated by FrancoDebitalon 2/7/2019Changing cell = TasaMercadoScenario value= .11 (in the screen that appears after you click ok)%3Dno.ProtectionBrevent changesScenario name = 4Changing cell = TasaMercadoScenario value= .09 (in the screen that appears after you click ok)CancelO.Then select Summary and make sure scenario summary is selected in the next boxIn the result cells: Type the names of both independent variables separated by a comma:GastoTotallnteres,ValorPresenteScenario SummaryReport typeWe are typing the NAME of thevariables; Excel will automaticallyidentify the cell address.O Scenario summaryO Scenario PivotTable reportResult cells:GastoTotallnteres,ValorPresenteOKCancelFinally press OK and Excel will create a table with the results for each scenario.Briefly, summarize your findings.i. Assume that on January 1, 2019, AAA had bond issue costs of $5,000. Use Excel's built-in formula forRATE 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 originaleffective interest rate?DELIVERABLES: The Excel file.

Question

Accepted Answer

Hi There, thanks for posting the question. But as per Q&A guidelines, we must answer the first…