use formula across certain # of months based on variable

[sashazaliz]

I have this complex financial model I'm working on that has a commissions paid section. Each rep will get a bonus payment (15% of revenue) for a certain # of months which will then revert to a smaller 2% of revenue for the remainder of the months. I would like to be able to change the variable (# of months 15% is paid out) and have that automatically flow thru to the rest of the model accordingly.

So for instance if somebody types in "3" then the model will automatcially calculate the 15% payment for the first 3 months and then calculate the 2% payout for the remainder of the months. If somebody changes it to "4" then it will calculate the 15% payment for the first 4 months and the 2% for the rest of the months etc....

Is there a formula I can utilize that would allow me to do that? I have hundreds of rows across 48 months so to get this automated and view different scenarios is imperative. Thank you!

 

[sashazaliz]

this worked like a charm

You're going to kill me but If I wanted to make this a bit more dynamic by making these formulas dependent on the month the revenue streams begin what approach could I take? For example...the cells without any corresponding revenue towards the beginning will be blank and the ones with corresponding revenue will calculate the commissions based on the month that specific rep started generating revenue and carry those payments out based on that same variable.

Excel Workbook
	A	E	F	G	H	I	J	K	L	M	N	O	P	Q
53	3
54	CATEGORY REVENUE	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12
55	rep 1	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750
56	rep 2			6,763	8,388	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750
57	rep 3				9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750	9,750
162
163	Calculated Commissions	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12
164	rep 1	1,462.5	1,462.5	1,462.5	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0
165	rep 2		-	1,014.5	1,258.2	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0
166	rep 3		-	-	1,462.5	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0	195.0
Direct Rev - Customers

 

[sheetspread]

Rep 1 is easy, he had revenue in every month. For 2 and 3, you can either calculate the first 3 months from the time they had their first revenue at 15% (see below) or some other start date. Looking at what you have, I can't figure out what that date is (rep 1 is june to august, rep 2&3 is july to september even though rep 3 started a month later).

Excel Workbook
	A	E	F	G	H	I	J	K	L	M	N	O	P	Q
53	3	*	*	*	*	*	*	*	*	*	*	*	*	*
54	CATEGORY REVENUE	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12
55	rep 1	9750	9750	9750	9750	9750	9750	9750	9750	9750	9750	9750	9750	9750
56	rep 2	*	*	6763	8388	9750	9750	9750	9750	9750	9750	9750	9750	9750
57	rep 3	*	*	*	9750	9750	9750	9750	9750	9750	9750	9750	9750	9750
162	*	*	*	*	*	*	*	*	*	*	*	*	*	*
163	Calculated Commissions	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12
164	rep 1	1462.5	1462.5	1462.5	195	195	195	195	195	195	195	195	195	195
165	rep 2	0	0	1014.45	1258.2	1462.5	195	195	195	195	195	195	195	195
166	rep 3	0	0	0	1462.5	1462.5	1462.5	195	195	195	195	195	195	195
Direct Rev - Customers

 

[sashazaliz]

I tried something similiar in counting the blank cells in a range and it kept giving me a circular reference the way I set up the formula. My goal is to keep it dynamic so that it automatically adjusts the output based on when the rep begins generating revenue. By the looks of it that's exactly what your formula addresses. Again, let me apply it and see if it works. Thank you so much for your help!