Expert Answers

QQQQQQ
Warning only the numbers on sheet 1 should be typed in. All remaining sheets should be formulas, do not type in any numbers or percents.
Sheet 1 – Name is Given
Have the following information on it.
Luigi Corporation
Bonds Payable
For Years 2010 thru 2014
Scenario 1
Years
5
Market (Effective) Rate
12%
Bond Stated Rate
12%
Scenario 2
Years
5
Market (Effective) Rate
15%
Bond Stated Rate
12%
Years
5
Market (Effective) Rate
9%
Bond Stated Rate
12%
Sheet 2 – Name isXXXXX
PRESENT VALUE OF $1
1%
2%
3%
4%
5%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
1
0.9901
0.9804
0.9709
0.9615
0.9524
0.9434
0.9346
0.9259
0.9174
0.9091
0.9009
0.8929
0.885
0.8772
0.8696
0.8621
2
0.9803
0.9612
0.9426
0.9246
0.907
0.89
0.8734
0.8573
0.8417
0.8264
0.8116
0.7972
0.7831
0.7695
0.7561
0.7432
3
0.9706
0.9423
0.9151
0.889
0.8638
0.8396
0.8163
0.7938
0.7722
0.7513
0.7312
0.7118
0.6931
0.675
0.6575
0.6407
4
0.961
0.9238
0.8885
0.8548
0.8227
0.7921
0.7629
0.735
0.7084
0.683
0.6587
0.6355
0.6133
0.5921
0.5718
0.5523
5
0.9515
0.9057
0.8626
0.8219
0.7835
0.7473
0.713
0.6806
0.6499
0.6209
0.5935
0.5674
0.5428
0.5194
0.4972
0.4761

PRESENT VALUE OF AN ORDINARY ANNUITY
1%
2%
3%
4%
5%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
1
0.9901
0.9804
0.9709
0.9615
0.9524
0.9434
0.9346
0.9259
0.9174
0.9091
0.9009
0.8929
0.885
0.8772
0.8696
0.8621
2
1.9704
1.9416
1.9135
1.8861
1.8594
1.8334
1.808
1.7833
1.7591
1.7355
1.7125
1.6901
1.6681
1.6467
1.6257
1.6052
3
2.941
2.8839
2.8286
2.7751
2.7232
2.673
2.6243
2.5771
2.5313
2.4869
2.4437
2.4018
2.3612
2.3216
2.2832
2.2459
4
3.902
3.8077
3.7171
3.6299
3.546
3.4651
3.3872
3.3121
3.2397
3.1699
3.1024
3.0373
2.9745
2.9137
2.855
2.7982
5
4.8534
4.7135
4.5797
4.4518
4.3295
4.2124
4.1002
3.9927
3.8897
3.7908
3.6959
3.6048
3.5172
3.4331
3.3522
3.2743

Sheet 3 – Name isXXXXX
This spreadsheet is to take the rates and periods from the Given sheetand the factors from the Present Value Tables to calculate the selling price of the bonds. Chapter 14 of the Intermediate Accounting by Spiceland covers the calculations. You need to calculate the selling price for all three scenarios. Here is the catch, you have to use vlookup formulas to have the formula pull in the factor from the Present Value Table. Here is a major hint on the formula: =vlookup(period,present value table,interest rate*100+1) The words have to be replaced with cell address or range, the 100+1 can be typed in. Also review the vlookup handout posted. If you do not use vlookups, you will lose 15% of the points of this assignment. 

Sheet 4 – Name isXXXXX
Calculated the amortization for all five periods. Do not forget all of the premium or discount is fully amortized. Do not forget not to hardcode your formulas. Do not type in final amounts to eliminate them. Here is the basic setup of the schedule for just the 2nd and 3rd scenarios:
Luigi Corporation
Input Area:
Bond Face Amount
$1,000
Stated Interest
12%
Market Interest
15%
Years
5
Bond Discount
$101
Bond Amortization using the Effective-Interest Method
Impact Area:
Interest
Interest to
Expense to
Discount
Unamortized
Bond Carrying
Interest periods
Be Paid
Be Recorded
Amortization
Discount
Value

Sheet 5 – Name - Journal Entries
Give the journal entries for scenarios 2 and 3 from the issue of the bond, to all five interest payments, to the redemption of the bond.