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!

 

[sheetspread]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	# Months @ 15%	3
2	Rep	Commission	Jun-11	May-11	Apr-11	Mar-11	Feb-11	Jan-11	Dec-10	Nov-10	Oct-10	Sep-10	Aug-10	Jul-10	Jun-10	May-10	Apr-10	Mar-10	Feb-10	Jan-10	Dec-09	Nov-09	Oct-09	Sep-09	Aug-09	Jul-09	Jun-09	May-09	Apr-09	Mar-09	Feb-09	Jan-09	Dec-08	Nov-08	Oct-08	Sep-08	Aug-08	Jul-08	Jun-08	May-08	Apr-08	Mar-08	Feb-08	Jan-08
3	Mike	55.93	10	75	4	10	69	45	93	27	43	20	49	92	91	60	94	23	57	9	59	41	12	92	34	28	98	92	62	10	9	11	24	4	27	22	16	98	37	52	48	32	48	81
4	Carol	72.68	66	92	24	67	34	95	90	11	34	47	53	71	62	67	42	79	6	69	13	67	53	50	30	65	18	90	70	32	75	98	17	84	42	27	35	37	42	60	42	51	18	53
5	Greg	71.22	16	81	97	0	47	8	91	80	12	87	57	79	18	29	42	9	73	57	22	72	16	78	68	54	49	2	99	46	75	42	98	43	87	3	7	8	59	72	22	93	41	30
6	Peter	66.54	21	39	78	31	33	6	73	66	25	75	99	29	94	25	73	79	75	71	38	73	90	5	66	11	75	67	86	65	62	55	28	39	0	15	3	38	96	65	55	52	0	38
7	Bobby	81.88	65	80	69	6	91	13	89	45	97	24	59	80	7	56	52	25	74	7	51	63	93	33	99	47	87	58	18	45	39	73	54	45	26	79	88	99	95	30	36	45	92	69
8	Marcia	74.11	17	82	86	15	70	84	92	42	82	64	19	11	28	48	36	14	42	89	52	38	22	79	40	81	45	7	79	66	26	54	30	62	83	58	2	64	99	75	84	79	8	5
9	Jan	45.52	10	26	24	69	55	40	9	27	85	60	90	33	6	7	10	2	22	80	12	30	52	5	99	89	74	82	10	42	52	14	0	85	5	94	68	18	45	73	71	28	35	20
10	Cindy	67.6	92	14	40	89	52	27	43	88	83	49	87	35	52	7	39	22	61	73	44	59	49	43	58	32	8	54	99	42	25	5	30	70	55	88	44	16	94	42	72	51	85	60
11	Alice	63.84	57	66	7	15	89	21	75	45	59	10	1	93	1	78	38	68	82	38	66	99	18	11	55	25	12	2	79	55	55	51	18	27	60	91	37	90	7	74	17	70	94	11
Sheet2 (2)

 

[sheetspread]

It cut off the last few columns upon posting (to Jul-07)

Excel Workbook
	AS	AT	AU	AV	AW	AX
2	Dec-07	Nov-07	Oct-07	Sep-07	Aug-07	Jul-07
3	47	63	37	25	81	57
4	66	93	40	17	53	4
5	35	85	62	19	15	15
6	27	46	66	89	40	48
7	79	96	0	28	24	73
8	22	16	52	91	64	99
9	30	11	19	12	8	48
10	94	28	37	35	6	53
11	48	46	96	69	59	62
Sheet2 (2)

 

[sashazaliz]

thanks so much for getting back to me. I see the example you provided me but just so we're on the same page let me provide you a portion of my model so that you can see it first hand. As you can see each line of commission is calculated off a corresponding revenue row and matching month from above. So in this example the end user chooses 3 months of commission paid at 15% revenue with the rest of the months paid out at 2%. In light of what I just showed you would your approach still work? How would I need to change those initial formulas? thank you so much for your help.

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	3,513	5,138	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	5,853	7,478	9,103	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		770.7	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,121.7	1,365.5	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]

What criteria tells you to exclude Jun-11 from rep 2 and rep 3's calculation (they do have revenue in that month)?

 

[sashazaliz]

each rep commissions start at different times of the year so there is no criteria I have to build into the formulas to reflect that. Those cells are blank with no formulas. So to me having the right formulas within the active ranges is key. Unlike where they start though, the end commission month is all the same across all the reps (they all end at the same column).

 

[sheetspread]

The model should include a column with the start date of each rep so the formula knows which months to skip. Otherwise you'll be changing formulas and cells like crazy, isn't the whole purpose to avoid that?

 

[sashazaliz]

no it won't be an issue since the starts dates follow a set consistent schedule. The point of this is for the results to flow thru based on the # of months the end user sets. I'm just looking for a formula that will allow me to keep the # of months 15% is paid out as a variable with the remainder of the months paid out at 2%. If we just focus on the first row and nothing more what formula would you suggest I use? thank you!

 

[sheetspread]

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	3513	5138	6763	8388	9750	9750	9750	9750	9750	9750	9750	9750	9750
57	rep 3	5853	7478	9103	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		770.7	1014.45	1258.2	195	195	195	195	195	195	195	195	195
166	rep 3		1121.7	1365.45	1462.5	195	195	195	195	195	195	195	195	195
Sheet1

So you'll subtract 5 instead of 4 in the next two rows

 

[sashazaliz]

brilliant. Let me apply it to my model and see if it works. thank you again for your help!