Many states raise money through lotteries that advertise prizes of several million dollars. In reality, however, the actual value of the prize is considerably less than the advertised value, although the winners almost certainly do not care. Consider, for example, a lottery prize advertised as being worth $1.5 billion. But if the winner opted for a single lump sum payment, he/she would not receive $1.5 billion. This advertised value may simply be the summed up value of annual payments over 30 years. In some lotteries, annuity payments may vary/increase from year to year, but for simplicitys sake, assume that the entire $1.5 billion is split into equal annual payments over 30 years. What is the actual value of the prize if paid as a single lump sum (assuming an annual interest rate of five percent)?

You should include 2 sheets for Lottery 1. The first sheet, named "Lottery 1A" should show the prize worth if payments started immediately. The second sheet, named "Lottery 1B" should look exactly the same, except that it should show what the prize is worth if the annual payments are started at the end of the first year.

When calculating your results, please use a "neutral" perspective so that all cash flows show as positive values. (Do not construct the spreadsheet from a single perspective...of either the lottery winner, or the lottery commission dispersing the payments...your spreadsheet should not attempt to show cash inflows as positive and outflows as negative.)

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.

Make sure you use a cell reference for "Payments per Year", even though the payments are made annually. (You should easily be able to edit your spreadsheet to reflect semi-annual or quarterly payments rather than annual payments.)

Please note that you do NOT need to use the PMT function in any parts of the Lottery problem since the payment amounts (the lottery fund disbursements) are already predetermined. (You would use the Payment function if you were trying to find the Payment required to pay off a particular loan amount.) HINT: Use simple multiplication & division to calculate the Periodic Payment Amount (prize money disbursement) based on the Advertised Lottery Value, Payments per Year, and Number of Years (the term). This Periodic Payment Amount would automatically be recalculated if the prize disbursement schedule was changed to semi-annual payments or paid over 15 years rather than 30 years.

Refer to the Chapter 6 Notes file for detailed explanation on how to "fill in the blanks" for the various arguments of the financial functions.

Use the appropriate formulas: FV, IPMT, IRR, ISNUMBER, NPER, NPV, PMT, PPMT, PV, RATE, simple/compound interest, cash flows in Excel (positive/negative vs a neutral perspective), amortization schedules & mortgage calculators

List the formulas you use. thanks