Recurring Revenue Model Help

alphafoxtrot

New Member
Joined
Jun 29, 2015
Messages
4
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.)

huf7ya.jpg


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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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
ABCDEFGHIJKLM
1New1011121010101314151421
2Recurring00101112202122333537
Sheet2
 

alphafoxtrot

New Member
Joined
Jun 29, 2015
Messages
4
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top