Lab 12: Due Tuesday, May 9, 2000

You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.

Goal:

The exercise (what to do during the lab):

Part 1: Probability, Expected Value, and Finance. The Charities For The Poor organization is investigating using a lottery for a fund raising project. They want to give away $3,000,000 (over a 30 year period with annual payments of $100,000) as the grand prize and 10 First prizes both worth $20,000 in cash. They expect to be able to sell 250000 tickets at $10 each.

1. The organization will buy an annuity for the $3,000,000 grand prize. If the current interest rate is 8.2%, how much will they have to pay for the annuity? (Remember the first payment will be on the date when the prize is awarded.)
2. Calculate the total cost to the organization assuming that in addition to the prize money it costs $200,000 for advertising, printing tickets, and other expenses.
3. They are required to publish the percentage of the ticket price that is actually used for charity and the percentage that is actually awarded in prizes (using the cost of the annuity). Determine these numbers.
4. What is the expected value of a ticket assuming that you base the calculation on the cost of the annuity as calculated in part a?
5. Create a chart to show the proportions of the income used for prizes, overhead, and charity.

Print copies of the spreadsheet and its formulas.

Part 2: Finance. Create a very general spreadsheet to determine solutions to the following problems. Values in italics may be replaced by other values. While "beauty" is not important, "readability" is.

1. Sally is only 21. If she were to retire today, she believes she would need $2000 a month from her savings. However she will not retire for 42 years. If the annual inflation rate averages 3% per year compounded annually for that period, how much will she need per month from savings at age 63? Hint: one way to calculate this is to use the formula

   Final amount = initial amount * (1 + interest rate)^years

   Alternatively you can consider this problem to be a future value problem with a present value but having a periodic payment of 0. To test your formula, try calculating it for initial amount $100, interest rate 10% for 2 years. The answer should be $121.00. It should be 133.10 for 3 years. When you are sure your formulas is correct, replace the simplistic values by the required values.

2. To achieve the monthly income calculated in part a during 27 years of retirement, how much will she need in her account at age 63 if she earns 9% APR (compounded monthly) on her investment? Assume the account is completely depleted after the 27 years.
3. She decides to deposit a fixed amount in an account every month for 42 years until she retires. What monthly amount is needed in order to achieve the amount calculated in part b? Print a copy of the spreadsheet.
4. What if...? The inflation rate drops to only 2.9% and Sally changes her mind and decides that she would need $2200 per month in today's dollars. She decides to retire at age 65 so she needs her retirement income for 25 years. She decides that she can invest her funds at 10%. Determine the amount she needs each month during retirement, the amount needed in the account at age 65, and how much she needs to save each month to achieve that amount. You should be able to solve this problem by changing input values without changing any formulas.

Part 3: Normal distribution. Solve these problems 2 ways. Calculate it by hand based on the normal curve handout table and also solve it using EXCEL.

1. The scores on some standardized tests such as the SAT have a mean of 500 and a standard deviation of 100. What percent of high school students would score 750 or better? 700 or better? 600 or better? 500 or better?
2. Jones Clothing wants to start a new line of One Size Fits All clothing but they are aware this is really impossible. Instead they will be satisfied if they accomplish "One Size fits 95.4%". Suppose that that the heights of their customers are normally distributed with a mean of 68 inches and standard deviation of 4. Find a range of heights that incorporate 95.4% of the population. (Suggestion: Take 47.7% on either side of the mean. For hand work, find how many standard deviations from the mean will be needed? On the computer, you can use NormInv))

What to hand in:

Turn in a spreadsheet and a formula sheet for each of the three parts. Make sure the solution are clearly marked so the paper grader can find them without searching.

Lab12.doc,5/2/00