During ACC 311 we have discussed the importance of present value calculations in recording the economic impact of financial transactions. In practice, excel is the tool of choice for most accountants in completing everyday tasks. Project #1 requires the development of a what-if model. A what-if model has an input area and an output area. The output area includes formulas that reference the cells in the input area allowing you to use many of the what-if analysis capabilities of EXCEL and to allow you to make quick changes to data and have the results immediately without editing any formulas.

Open the Project #1 excel spreadsheet. Note how the Bond Amortization sheet is set-up. Your task is to develop something similar for the PBO of a Defined Benefit Plan. There should be an input area and an output area. Your output area should be a schedule that will indicate the Service Cost, the Interest, the Benefits Paid and the Ending Balance in the PBO each year.

Your company has a defined benefit plan that specifies annual retirement benefits equal to:

Set % X Service Years X Final Years Salary

You have been asked to develop a model that will calculate the Service Cost, Interest and Projected Benefit Obligation at the end of each year for one employee. Your PBO increases each year for service cost and interest. After retirement, benefits are paid and your PBO is reduced. (Interest is still incurred during retirement since a long-term liability exists). Your model should be flexible, but you can have it set for a maximum number of years. To test your model, you should make up assumptions. For example, you expect to retire in 50 years after 50 years of service. You expect to live 20 years after retirement. You expect your salary to be $240,000 your final year of work and the set % is 2%. The settlement rate (discount rate) is estimated to be 6%. (See Chapter 20 Notes and Chapter 20 for help).

Bond Amortization Schedule Input Bond Data: Maturity Value $ 100,000.00 Bond Issue Date 1/12019 5.00% 4.50% Stated Interest Rate Market Interest Rate Years Outstanding Times Interest Paid During the Year 10 1 Output Bond Report: $103,956.36 Cash Proceeds From Bond Issue: Bond Amortization Schedule Interest Payment Cash Payment Interest Expense Bond Carrying Value Amortization DR (CR) CR DR Initial $103,956.36 $103,634.40 $103,297.94 $102,946.35 $102,578.94 $102,194.99 $101,793.76 $101,374.48 $100,936.33 $100,478.47 $100,000.00 $0.00 $0.00 $0.00 $0.00 5,000.00 $ 5,000.00 $ $ $ 4,678.04 $ 4,663.55 $ 4,648.41$ 4,632.59 $ 321.96 1 2 336.45 5,000.00 $ $ 351.59 3 5,000.00 $ $ 4 367.41 5,000.00 $ $ 4,616.05 $ 4,598.77 $ 4,580.72 $ 4,561.85 $ 4,542.14 $ 4,521.53 $ 383.95 5,000.00 $ $ 6 401.23 5,000.00 $ 5,000.00 $ 5,000.00 $ 419.28 7 438.15 457.86 10 5,000.00 $ 478.47 $ $ $ 11 $ $ $ $ 12 13 14 Bond Amortization PBO Model N L LO Bond Amortization Schedule Input Bond Data: Maturity Value $ 100,000.00 Bond Issue Date 1/12019 5.00% 4.50% Stated Interest Rate Market Interest Rate Years Outstanding Times Interest Paid During the Year 10 1 Output Bond Report: $103,956.36 Cash Proceeds From Bond Issue: Bond Amortization Schedule Interest Payment Cash Payment Interest Expense Bond Carrying Value Amortization DR (CR) CR DR Initial $103,956.36 $103,634.40 $103,297.94 $102,946.35 $102,578.94 $102,194.99 $101,793.76 $101,374.48 $100,936.33 $100,478.47 $100,000.00 $0.00 $0.00 $0.00 $0.00 5,000.00 $ 5,000.00 $ $ $ 4,678.04 $ 4,663.55 $ 4,648.41$ 4,632.59 $ 321.96 1 2 336.45 5,000.00 $ $ 351.59 3 5,000.00 $ $ 4 367.41 5,000.00 $ $ 4,616.05 $ 4,598.77 $ 4,580.72 $ 4,561.85 $ 4,542.14 $ 4,521.53 $ 383.95 5,000.00 $ $ 6 401.23 5,000.00 $ 5,000.00 $ 5,000.00 $ 419.28 7 438.15 457.86 10 5,000.00 $ 478.47 $ $ $ 11 $ $ $ $ 12 13 14 Bond Amortization PBO Model N L LO