Finance 380: Financial Modelling

1. I understand that the due date and time for the exam is Monday, October 28th at 9 AM. I understand that the exam must be submitted electronically. I understand that exams passed in after that time will be considered late and will incur a minimum penalty of 1 point lost per minute late.

1. The file name should contain both your name and section. For example, if you’re in the 9AM section (Section C), your file name should be something like David.Ketcham.C.xlsx.

1. Make sure you pass in the exam, not a homework assignment or your midterm. I don’t have time to hunt you down.

1. If you submit your exam early, I’ll try to open the file and make sure I have the right exam and that I can view it. I’ll let you know if I have problems. It is unlikely that I will be able to do so for any exam submitted after Midnight before the due date.

1. The exam is being made available to you on October 15th and is due October 28th. That means you have ample time to ask questions in class, during office hours, via email, or through myriad other means. If you don’t understand a question, ask BEFORE the exam is due for clarification. Misunderstood questions leading to wrong answers will count against you.

1. I reserve the right to extend the due date.

1. Good luck!

Print Your Name Signature

Finance 380

Fall, 2019 – Midterm Exam

The following examination is due no later than 9 AM Monday, October 26th. You are to email me the exam in an XLSX file named after yourself and containing your section. For example, if your name is Leslie King, the file should be called “Leslie.King.A.xlsx”. DO NOT FAIL TO FOLLOW THE INSTRUCTIONS.

The basic set up for the exam can be found in the file: f380.fall19.exam1.data.xlsx. That file contains nine worksheets that you are to complete. Each worksheet is named (and the number in parentheses is the points allotted to the problem). The following information should help you complete the problems:

EARLY [15] The 30 year loan amortization table we developed in class starts:

Suppose a borrower decided to pay the loan in the following fashion. In period 1, the borrower pays the regular payment of $1,475.82 + the period 2 interest (assuming the loan IS NOT prepaid) of $1,061.04. In period 2, the borrower pays the regular payment of $1,475.82 + the period 3 interest of $1,059.57, and so on; each period making the regular payment plus the one period ahead interest. Write a spreadsheet that works for any loan amount and annual interest (assume the original loan is a 30 year mortgage with monthly payments). The spreadsheet should return a loan amortization table, the sequence of payments the individual plans to make and the life of the loan to the nearest month and should use only those cells needed.

RETIRE [15] Write a spreadsheet that allows the user to input his (or her) age today, salary today, growth rate in salary (assumed constant throughout his or her working life), initial investment in both the bond and stock fund (which may or may not be $0 and can vary across funds), proportion of salary invested, age at retirement, planned age at death and the inheritance they plan to leave to their heirs one yea after their last retirement payment. The spreadsheet allows the user to invest a portion of his or her salary in two retirement vehicles (or funds) – a bond fund whose return is 4% and a stock fund with a return of 10%. The proportion invested in bonds equals the decade of the individual’s age. For example, in their 20s (i.e. from 20 through 29), they will invest 20% in bonds and 80% in stock. In the 30s, 30% in bonds and 70% in stock and so on. Immediately after retirement, ALL FUNDS will be transferred into an account with a guaranteed constant return of 3% per year. The funds are to be withdrawn starting one year after retirement and ending with the last payment at the age at death. (the balance should equal the amount left to their heirs one year after the last withdrawal). The spreadsheet should return the annual retirement benefit. The spreadsheet should work for all ages from 16 to 100 and use only those cells necessary.

ABALONE [10] This tab contains data on 1,500 abalone (sea snails) The variables are:

Sex (Male, Female, or Indeterminate)

Length in centimeters

Diameter in centimeters

Weight in grams

Age in years

For each numeric variable, calculate mean, median, mode, standard deviation, maximum, and minimum. WITHOUT SORTING THE DATA, count the number of males, females, and indeterminate abalone, and calculate the means of each numeric variable for each sex. Graph the histogram of ages for the entire data set.

CATCH [20 points] The worksheet Catch contains financial data for Catch-Em Company, makers of ankle bracelets for those sentenced to home confinement. You are to forecast balance sheets, income statements, and statements of cash flow for the next 7 years using the percent of sales approach, assuming the following.

· The firm is at full capacity.

· Firm pays 8% interest on long-term debt, 4% on short-term debt, and earns 1% on marketable securities.

· Excess funds are invested in marketable securities. Shortages are made up first with notes payable, and next with additional long-term debt. Notes Payable can be no greater than the firm’s existing line of credit of $2,000.

· Sales growth is expected to be 20% next year, and is expected to fall by 3% per year to a constant rate of 2%.

RAISE [10] This worksheet contains salary levels for twelve employees at Weather-Tite Windows. Each employee is slated to receive a raise that potentially has three components. First, is an across-the-board cost of living adjustment (COLA) (everybody gets an x% raise). Second, is an incentive bonus based on productivity. Each employee is expected to produce 10,000-12,000 windows per year, but can earn an additional one percent raise per 1,000 windows over 12,000 produced. (For example, if you produce 17,000 windows, you would get an additional 5% raise.) Based on past performance, the production manager has provided estimates of the maximum number of windows each worker could produce. Third, each female employee MUST receive an additional raise of $3,500 to correct past pay inequities. (Assume the gender equity raise is NOT affected by the cost of living adjustment). The raise pool for this year must not exceed $95,000. Use solver to determine the maximum cost of living adjustment (COLA) the firm can offer. (Note, the incentive raises are based on this coming year’s salaries, not last year’s.)

NPV [10] This worksheet contains cash flows after tax for two capital budgeting projects. For each project, calculate the NPV assuming a discount rate of 12%, the IRR and the MIRR assuming a reinvestment rate of 12%. For each project, calculate NPVs for whole discount rates from 0% to 15%, and graph both NPV profiles on the same graph. You will notice that the two lines cross. Determine this crossover rate – the rate at which the two projects have the same NPV.

DONE [10] Write a spreadsheet that allows the user to input MONTHLY deposits, an ANNUAL interest rate and the number of months for which the user makes deposits. Allow them also to indicate MONTHLY withdrawals and the number of monthly withdrawals they intend to make. The spreadsheet returns the balance remaining in the account immediately after the last withdrawal (assume deposits and withdrawals are annuities and that the interest rate is held constant) AND the maximum number of months that the individual could make withdrawals at that specified amount. Assume monthly compounding throughout

.

SIM [10] Suppose you deposit $1,000 per year in a bond fund and $2,000 per year in a stock fund and do so for 10 years. The bond fund pays a return each year that is a random draw from a normal distribution with a mean of 4% and a standard deviation of 1%. The stock fund pays a return each year that is a random draw from a normal distribution with a mean of 10% and a standard deviation of 6%. Calculate the terminal value of the total of the two investments in year 10. Simulate the total 100 times. Dynamically graph the histogram of these terminal values using a range of $30,000 to $60,000 using bins of $2,500.