ACCT 4112 Fall 2014 Excel Assignment - Financial and Statistical Functions 27104

The purpose of this assignment is to help you learn how to use some of the useful financial and
statistical functions available in Excel. In this assignment you will practice using financial
functions to determine values associated with bonds and notes. You will also practice using
statistical functions to determine how much you should accrue for a contingent liability.
This assignment will require you to use the excel worksheet titled ACCT4112 Excel
Assignment available on Desire2Learn. Answer the questions below using the excel
spreadsheet provided. All answers will be reported in the spreadsheet. Only numbers that are
shaded in the worksheet should be manually entered- all other numbers should be calculated
using excel.
You will turn in 7 pieces of paper (or 4 if you print double sided) which will be stapled together
and turned in on or before the beginning of the class on Thursday, December 4. The first page
will be a title page with the name of the assignment and your name (1 page). The other 6
documents will include a printout of each of the 3 problems answers (3 pages) and a print out of
the formulas from each of the three problems (Control ~) (3 pages). No late assignments will be
accepted.
This assignment is to be completed completely on your own and any collaboration with other
students or anyone outside the class will be considered academic misconduct. You may ask
questions of the instructor only. You may however consult the help function in Excel and simple
searches on the internet are also acceptable.

Question 1. On January 1, 2014 Peaches Corp. issued $100,000,000 of 9.3% bonds. Interest is
paid semiannually on July 1st and January 1st. The bonds mature in 20 years. The market yield
for bonds of similar risk and maturity is 8.4%.
a) Determine the issuance price using the PV function in Excel.
b) Create an amortization table for this bond.
c) To show that a firm can raise the same amount of funds regardless of the stated rate of
interest do the following:
Redo part A but with a stated rate of interest of 2.3% instead of 9.3%- you will
notice that the issuance price has gone down significantly
Lets assume that we want to raise the same amount at issuance as in part A. Use
the FV function in Excel to determine the Maturity Value that would raise the
same funds with the new stated interest of 2.3%. (Hint- the payment is going to be
$3,018,878)

Question 2. On January 1, 2014 Frock borrows $230,000 from the bank at 9.8% using an
installment note. Frock will pay installment payments monthly for 3 years to repay the loan.
The first payment takes place on February 1, 2014.
a) Determine the amount of the monthly payment required to pay off the loan in 3 years
using the PMT function in Excel.
b) Create an amortization table for the installment note.
c) If Frock wanted to pay off the loan in 33 payments (instead of 36) with the same payment
amount, then what interest rate would Frock need to qualify for? Use the RATE function
in Excel to determine the annual interest rate required.
Question 3. Shank Corporation has a class-action lawsuit pending against the firm. Ignore the
time value of money in this problem. The attorneys representing Shank predict that it will need
to pay out one of the following amounts at the corresponding probabilities:
Amount
$10,000
$100,000
$500,000
$2,000,000
$5,000,000
$10,000,000

Probability
2%
20%
15%
20%
40%
3%

In accounting we are often asked to estimate things. There are many different estimation
techniques available to us. Lets investigate some of the most common:
a) Ignoring the probabilities, estimate the amount we are going to pay out using the
AVERAGE and MEDIAN functions in Excel. In many cases this is an acceptable
estimation technique, but since we have probabilities we probably shouldnt ignore them.
b) Calculate the probability weighted mean. Excel does not have a function to calculate
probability weighted averages, but we can use a simple function to help us determine this
value. Use the SUMPRODUCT function in Excel to determine the probability weighted
mean. Now calculate the probability weighted average by multiplying each line and then
adding the products.
c) Some estimations have more uncertainty than others. We have a number of techniques
that help us get an idea of how sure we are that our estimation is a good one (with less
potential variation in outcome). Ignoring our probabilities, calculate the standard
deviation, maximum, and minimum using the STDEVA, MAX, and MIN functions.
Calculate the range by taking the difference between the maximum and the minimum.
Each of these values gives you an idea of how good your estimate may be. The larger the
range and the standard deviation the less certainty you have that the outcome will be
close to your estimation.