Lottery 2A, 2B & 2C

The state lottery commission has informed you that you have won the lottery, and they offer you various different options for the distribution of the prize money. Although you are happy to have won the lottery, pretend you are already wealthy enough that you will not need any money for the next 30 years. Instead of spending your winnings, you intend to put the money in the bank and leave the winnings to your newborn child when he turns 30 years old. Ignoring taxes, alternative potential investment options, etc., which Lottery Option will result in the most money for your child?

Determine how much each prize option is worth assuming that the annual interest rate you could get on your deposited money will remain fixed at 5% over the next 30 years. To compare the different prize options, calculate both the Present Value AND the Future Value of each prize option. The various options for the prize money distributions are listed below:

1. Annuity: Youll be paid equal annual payments of $50,000,000 over a period of 30 years.

2. Lump Sum: Youll immediately be given one lump sum in the amount of $1,000,000,000.

3. Lump sum & Annuity: Youll be given $500,000,000 up front and $25,000,000 per year for 30 years.

4. Future Lump sum: Youll be given a lump sum amount of $3,000,000,000 at the end of 30 years.

5. Annuity & Future Lump sum: Youll be given $25,000,000 per year for 30 years, and then a lump sum amount of $2,000,000,000 at the end of 30 years.

Assume that all periodic payments start immediately (at the beginning of the first period). Payments per Year (and interest compounding) are the same for each prize option. Use Annual paymentsi.e. 1 for the "Payments per Year" assumption for EVERY optioneven the ones where a payment is not made. This will ensure that the interest for all the prize options is compounded at the same frequencyannuallyfor each prize option. Therefore the RATE and NPER assumptions are going to be exactly the same for every one of these options. The only inputs that change from option to option are: the periodic payment amount, the beginning lump sum amount, and the ending lump sum amount.

Include a comment in cell A7, so that when the cursor is placed over cell A7, the following message pops up informing the user that the "Payments per Year" also serves as the interest compounding frequency.

Ensure that users of the spreadsheet can only enter valid input for the Payment Type. After selecting the cell containing the Payment Type, an Input Message should appear, describing the difference between the options which can be entered as the Payment type, and allowing users select valid Payment Type options from a drop-down list. If the user attempt to enter an invalid Payment Type, an Error Alert should appear, explaining the options for entering valid Payment Types.

When completing this spreadsheet, it should be COMPLETELY flexible. So if ANY assumptions in any of the Options/columns change, the formulas used to calculate the Prize Option Values (in rows 15:16) should still return correct results.

In addition to calculating the present value and future value for each option, use a function to rank the different options to see which one is worth the most and which is worth the least. Perform three different rows of rankings: the Advertised Prize Value, the Future Value and the Present Value options. Name this worksheet Lottery 2A. The title of this worksheet should be Lottery 2A Sorted by Option #.

Create a second copy of your Lottery 2A worksheet and name it Lottery 2B. The title of this worksheet should be Lottery 2B Sorted by Advertised Prize Value. On this worksheet, sort the prize Options by Advertised Prize Value, with the largest Advertised Prize Value first (on the left) and the smallest at the end (right). Do NOT manually rearrange the columns. (If you had 100 options instead of only 5, rearranging manually would be quite tedious!) (All data for each Option should be sorted, including the Option # heading.)

HINT: Change the sort options to sort from left to right (using Data, Sort, Options).

Create a third copy of your Lottery 2A worksheet and name it Lottery 2C. The title of this worksheet should be Lottery 2C Sorted by Future Value. On this worksheet, sort the prize Options by Future Value, with the largest Future Value first (on the left) and the smallest at the end (right). Do NOT manually rearrange the columns. (All data for each Option should be sorted, including the Option # heading.)

A screenshot of the Lottery 2 layout is shown below for your guidance. Please use the same layout (cell references, etc.) for you Lottery 2A, Lottery 2B and Lottery 2C worksheets (except the order of the Options will be different for 2B & 2C). Please remember that in the "real world" you will not be given the layout of the spreadsheets...it would be your responsibility to design the layout of your worksheets.

Cells with #s (blue font) are for inputs (no formulas/functions), cells with ?s (black font) represent formulas/functions.