ACCT 4112 Fall 2014 Excel Assignment – Financial and Statistical Functions

The purpose of this assignment is to help you learn how to use some of the useful financial andstatistical functions available in Excel. In this assignment you will practice using financialfunctions to determine values associated with bonds and notes. You will also practice usingstatistical functions to determine how much you should accrue for a contingent liability.This assignment will require you to use the excel worksheet titled ACCT4112 ExcelAssignment available on Desire2Learn. Answer the questions below using the excelspreadsheet provided. All answers will be reported in the spreadsheet. Only numbers that areshaded in the worksheet should be manually entered- all other numbers should be calculatedusing excel.You will turn in 7 pieces of paper (or 4 if you print double sided) which will be stapled togetherand turned in on or before the beginning of the class on Thursday, December 4. The first pagewill be a title page with the name of the assignment and your name (1 page). The other 6documents will include a printout of each of the 3 problems answers (3 pages) and a print out ofthe formulas from each of the three problems (Control ~) (3 pages). No late assignments will beaccepted.This assignment is to be completed completely on your own and any collaboration with otherstudents or anyone outside the class will be considered academic misconduct. You may askquestions of the instructor only. You may however consult the help function in Excel and simplesearches on the internet are also acceptable.Question 1. On January 1, 2014 Peaches Corp. issued $100,000,000 of 9.3% bonds. Interest ispaid semiannually on July 1st and January 1st. The bonds mature in 20 years. The market yieldfor 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 ofinterest do the following:Redo part A but with a stated rate of interest of 2.3% instead of 9.3%- you willnotice that the issuance price has gone down significantlyLets assume that we want to raise the same amount at issuance as in part A. Usethe FV function in Excel to determine the Maturity Value that would raise thesame 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 aninstallment 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 yearsusing 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 paymentamount, then what interest rate would Frock need to qualify for? Use the RATE functionin Excel to determine the annual interest rate required.Question 3. Shank Corporation has a class-action lawsuit pending against the firm. Ignore thetime value of money in this problem. The attorneys representing Shank predict that it will needto 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,000Probability2%20%15%20%40%3%In accounting we are often asked to estimate things. There are many different estimationtechniques available to us. Lets investigate some of the most common:a) Ignoring the probabilities, estimate the amount we are going to pay out using theAVERAGE and MEDIAN functions in Excel. In many cases this is an acceptableestimation technique, but since we have probabilities we probably shouldnt ignore them.b) Calculate the probability weighted mean. Excel does not have a function to calculateprobability weighted averages, but we can use a simple function to help us determine thisvalue. Use the SUMPRODUCT function in Excel to determine the probability weightedmean. Now calculate the probability weighted average by multiplying each line and thenadding the products.c) Some estimations have more uncertainty than others. We have a number of techniquesthat help us get an idea of how sure we are that our estimation is a good one (with lesspotential variation in outcome). Ignoring our probabilities, calculate the standarddeviation, 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 therange and the standard deviation the less certainty you have that the outcome will beclose to your estimation.