Using Excel 2007, I am trying to create a revenue forecast for the year based on several sets of criteria. Would like to have the spreadsheet calculate monthly payment values based on:
1. Payment options (1 payment, 2 payments, etc. through 6 payments) with established table of probability factor calculated
2. Payment beginning month

Payments are also based on a set of criteria:
For example, if Customer 1 has selected Pay Option "1" (meaning one payment) and Begin Month of "1", the $1500 would show in Jan. If 2 payments, half of payment Jan with probability of 100% and half in Feb with a probability of 85%. For customer 2, Pay Option "3", so three payments starting in Mar with probability factor. So Mar (=Pmt Amount/3*1, Apr (=Pmt Amount/3*value from designated cell (.85)), May (=Pmt Amount/3*value from designated cell (.7)).

I want the model to be able to calculate any combination of 6 payment plans, starting on selected month, and the amount of payment based on term and weighted probability for each customer. I set up chart for probability factors so that can be changed to estimate revenue numbers. Using this chart created formulas to calculate payments with If & And statements, I could get the payment to move down the month and could calculate correct payments just using non designated cells, but cannot figure out both. Please Help.