MBA8025 Financial Statement Analyses Homework #4: EXCEL ASSIGNMENT

MBA8025
Financial Statement Analyses

Homework #4:
EXCEL ASSIGNMENT
Due at Monday,
December 8

The Company issued Corporate Bonds
on December 31, 2014, with face value $200,000,
due in 12 years, a stated interest rate of 10% and an effective yield of 8.25%.

Problem
1:
What should be the market value of the Bond at the date of issuance?

NOTE:
Enter the data above in four
cells, and use the appropriate Excel financial function in your software to
compute present value, rather than formulas from present value tables. The PV
cell(s) should reference only data cells, not show actual amounts. Then,the
spreadsheetcan
be easily modified for different face values, maturities, stated (coupon)
interest rates, and/or effective interest rates.

Problem
2:Prepare
an amortization schedule for the premium/discount on the above bond
using the following columns;

Date

Beginning

Cash paid

Interest

Premium

Ending

Balance

Expense

Amortized

Balance

12/31/2014

Year 2015

2016

….

2026

Final table will
look similar to our classroom example; Page 19 of Slide11.pptx.

Problem
3:Managers of the company want to know how
sensitive the market price ofthe bond would be if the stated and market
interest rates are different from the above. For the above bond, identify how
the present value changes if the stated and effective market rate changes by
completing the following table.

PV of Bonds

Stated Rate of
Interest
.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>.gif”>

6%

7%

8%

9%

10%

11%

12%

Effective 6%
.gif”>
Interest 7%
.gif”>
Rate 8%
.gif”>
9%
.gif”>
10%
.gif”>
11%
.gif”>
12%
.gif”>

NOTE: Once the Excel formulae in Problem
1 are efficiently setup, this table can be filled quite easily by clicking Data
?
What-If Analysis?
Data Table tabs of the Excel spreadsheet. When the present value becomes
same as the face value of the bond?

Output
to be submitted: Upload your Excel file in the
Dropbox of the Desire2Learn bymidnight of Monday, December 8.

Important:

You
may work in groups to help one another if difficulties are encountered.
However, the final output must be solely the result of your individual efforts,
not a group endeavor.

Copying
another student’s work and/or virtually identical cell contents will receive no
credit.
.jpg”>
MEMO: Useful information before
you start, if you do not have a good experience.

For your information, Microsoft Office is available free to all
students with GSU Panther e-mail address. To find, go to the following site;

http://technology.gsu.edu/technology-services/it-services/software-computer-purchase/software-download-and-purchase/

Microsoft Excel is
the most widely used spreadsheet software and it is very easy to get started. A
small of number of very simple Excel financial functions will be used in this
homework. Excel is extremely powerful in financial functions as well as in
other functions. Since Excel includes so many useful functions, it might be
useful to know how to get help before a beginner starts any project.

(1) From Excel: The Help button
in Excel is too small and can easily be ignored. Actually the Help button

stays in the top
right corner of the window. The button looks like a question mark.jpg”> surrounded by a circle. Or you can use the
shortcut key of F1 to enable the Help window.

(2)
Internet searching: Many times,
searching internet is an easier way to get help. You would be able tofind
more beginner-friendly explanations with numerous examples, but without using
highly technical terminologies.

(3)
Lynda.com: All GSU students have a free access to
Lynda.com which is an online learning company that helps anyone learn software,
design, and business skills to achieve their personal and professional goals.

To get some instructions on Excel, go to
the school’s homepage at http://www.gsu.edu
and click on

“Student” ? “Technology.”
Then, click on “Lynda.com Online Training” in the middle-left
side of the

page. After logging
in using your GSU student ID and password, click on “Software”? “Excel”
and “2013”

on the left. You
would find several video lectures and each one is quite lengthy. However, you
can partially watch topics that you are interested in.

(4) Reference book:
It is strongly recommended to purchase a reference book. Most
books cover same materials, but it is better to review the way of presentation
in the book by yourself, and choose your favorite one.