Revenue Template

[SAInocencio]

Hi. I am trying to work on a five-year revenue projection where there is new volume and renewal volume that churns annually by a certain percentage. Moreover, revenue is recognized on a monthly basis, both for new and renewal. Example, there are five clients coming in in Year 1, say 2 in January and 3 in February. The revenue for each is$6,000 but spread over 12 months or $500 per month. Come Year 2, only 1 will renew in January and 2 in February. In the meantime, there are new clients coming in, 2 in January and 4 in February. I would like to capture the revenue using available formulas in excel where the year and month is considered for new clients as well as for renewal clients.

Thank you for your help.

 

[Hui]

SAInocencio

Firstly, Welcome to the Chandoo.org Forums

Do you mean something like?


see attached file:

 

[SAInocencio]

SAInocencio

Firstly, Welcome to the Chandoo.org Forums

Do you mean something like?
View attachment 31297

see attached file:

Thank you, Hui.

Something like that, but not quite. See, since we are still projecting, we don't have the specific number of clients yet. So in the projections, we want the template to be as flexible as possible so that we can change the number of new clients per month, say 4 in January then 5 in May. Or we may decide later to assume 4 in February instead of January. And then the clients will renew every year except that there is a churn of 10% so that only 90% renew every year. So, if there are 10 new clients in Year 1, only 9 will renew in Year 2, then 7 in Year 3, 6 in Year 4, etc.

 

[Hui]

You can use that template to achieve that
Simply add more rows
Copy the formulas down
add clients and then use the Start/End dates to control the addition of clients