Calculating values based on variables

[RenaissanceMarketing]

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 fx, I could get the payment to move down the month or could calculate correct payments just using non designated cells, but cannot figure out both. Please Help.

Customer	Pmt Amt	Pay Option	Begin Month	Jan	Feb	Mar	Apr
1	$1500	1	1	1500
2	$2400	3	3			800	680
3	$3600	6	5
4
5

<tbody>
[TD="class: xl62"][TABLE="class: grid, width: 500"]
<tbody>[TR]

</tbody>

[/TD]
[TD="class: xl70, width: 71"][/TD]
[TD="class: xl70, width: 64"][/TD]
[TD="class: xl70, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl79"][/TD]
[TD="class: xl75"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl78"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl73"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"] [/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[/TR]
[TR]
[TD="class: xl69"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl81"][/TD]
[TD="class: xl81"][/TD]
[TD="class: xl81"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl82"][/TD]
[TD="class: xl82"][/TD]
[TD="class: xl83"][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

i would list out the options in a look up table that gave the payments, for each plan, per month.

your pay option 3 would list required payments of 0,0,0,.3333,.3333*.85,.3333*.7, (you still owe money is the idea to keep paying off less and less)

then you would not need the begin month column in your table

 

[RenaissanceMarketing]

Hmm, not sure tht works. This is a referral network plan model, so agreed payment is standard for base payment, if they are not satisfied they can opt out. Your saying to create every possible payment in table? It funny I can get the payment to move based on month starting and I can calculate all payments correctly just cannot figure out how to combine.