Recurring Revenue Model Help

[alphafoxtrot]

Hello,

I am trying to create a revenue forecast that can account for monthly sales that recur once a quarter.

For example, an Account Executive can make a sale in January and that very same customer will use our service again in three months (but not before). All the while our Account Executive is making sales every month leading up to the second service event for their first sale.

I can do this with manual data entry but I'm looking to automate this with very few inputs (Recurrence period, headcount, sales quota, etc.)

P.S. I tried to attach my workbook but I can't figure out how

 

[konew1]

This solution puts all of the recurring value into one line. Let us know if it is essential that you shoe recurrences on separate lines like in your pic.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	New	10	11	12	10	10	10	13	14	15	14	21
2	Recurring		0	0	10	11	12	20	21	22	33	35	37
Sheet2

 

[alphafoxtrot]

Thank you konew1, that formula worked great!

I have not used the MOD nor COLUMN functions before. I understand them both now, but I am still struggling to understand how the entire formula returns the correct answer.

In a simple sentence can you explain the logic? This would help me tremendously.

Thank you.

 

[konew1]

Hi.
The MOD(Column(),3) will return 0 or 1 or 2 for every column. here can be no other result.
When the formula is copied to G2 it becomes
=SUMPRODUCT($A$1:F1,--(MOD(COLUMN($A$1:F1),3)=MOD(COLUMN(),3)))

The condition --(MOD(COLUMN($A$1:F1),3)=MOD(COLUMN(),3))) will evaluate to {1;2;0;1;2;0}=1
that becomes --{true;false;false;true;false;false} and the -- turns true/false into 1 and 0, so it becomes
{1;0;0;1;0;0}

The formula =SUMPRODUCT($A$1:F1,--(MOD(COLUMN($A$1:F1),3)=MOD(COLUMN(),3)))
becomes =SUMPRODUCT($A$1:F1,{1;0;0;1;0;0})
and using my data in A1:F1 it is =SUMPRODUCT({"New";10;11;12;10;10},{1;0;0;1;0;0})
becomes SUM({0;0;0;12;0;0}
total 12

 

[alphafoxtrot]

konew1,

I appreciate you taking the time to help me. This was a huge help!