AnalyticsCase studyCRMsalesforce.com

Salesforce.com recurring revenue Opportunity forecasts

Dealing with discrete Opportunities with a single close date and value is straightforward with Salesforce.com's native Opportunity capabilities. Where things get a little more complex is when you have a single Opportunity that will result in a number of revenue recognition events. Typically this will be for repetitive subscription type transactions. The classic (and recommended) way of handling this type of business transaction in Salesforce is to use Opportunity Products with Product Schedules. I have used this approach in the past and it is relatively complex and a little unintuitive to both set up and maintain. You must set up Pricebooks, Products, Price Book entries and Schedules.

More annoyingly, it requires more work from your sales people to maintain Opportunities with Products. More work for salespeople invariably leads to adoption issues making it difficult to roll out this capability easily and quickly.

A client of mine operates in the telecoms arena and delivers services to customers that are payable on a month subscription basis across a number of connections that do not all necessarily activate at once. I had already done some work on customising the Opportunities so that they could show if an quantity is applicable on periodic (monthly) or one off basis. We also specified the number of months that elapse from close data to deployment completion.

The problem we had is that because there is only 1 close date on an Opportunity native functionality does not allow you to build a forecast report that shows the number of activations across the deployment of the service for an particular Opportunity.

My first thought was that this could be easily tackled by using the Reporting (formerly Analytic) Snapshot capability. I embarked on this path and quickly realised that there was no easy way of creating in a report the activations that took place on a monthly basis beyond the close date. Although I had a calculated field that worked out the connections per month I could only show it for the close date. I needed to be able to ensure that if an Opportunity resulted in 6 or 12 monthly activation events that I could show them as 6 or 12 entries in a report. These could then be summarised and used for the Snapshots.

So here is what I resorted to. On an Opportunity I have the CloseDate, the deployment time in months so I can then easily derive the Deployment Completion Date. In addition I created 12 custom fields (not visible on any page layout) called Month01 to Month12, each with a corresponding default value of 0 to 11. These numbers will work as an offset when we get the summary data into the Snapshot object. As I said before I already have an Activations per Month field based upon the Quantity divided by the number of deployment months.

Because I wanted my target custom snapshot object records to have the same ownership as the Opportunities themselves (so I can report by owner and also have "my" forecast type reports from the custom snapshot) I needed to create a new Custom Report Type that linked User and Opportunity. This is because with standard Opportunity Reports you seem to be unable to pass the actual UserID to the RecordOwner field in your custom object.

Because I need a 12 month rolling forecast, I then created 12 almost identical summary reports.  All 12 Report are summarised by Opportunity OwnerID (meaning it does not look very friendly) and Opportunity Type (because we want to analyse on this basis) with the following fields: Activations Per Month, New Revenue Per Month (formula field based on Activations) and Month01. Activations Per Month is Summed and Month01 is Maxed in the summary line. The criteria applied is as follows:

Close Date <=THIS MONTH AND Deployment Completion Date> THIS MONTH AND (Won = True OR Closed = False). This is the setup for the first month's report.

The second month's report is the same except I replaced the Month01 field with Month02 and the Criteria changes to Close Date <=NEXT MONTH AND Deployment Completion Date> NEXT MONTH AND (Won = True OR Closed = False). Similarly for the third report I used Month03 instead of Month02 and Criteria is Close Date <=NEXT 2 MONTHS AND Deployment Completion Date> NEXT 2 MONTHs AND (Won = True OR Closed = False). And so on for reports 4 through 12.

I then set up my custom Snapshot object with fields ForecastType (Text), Month Offset (Number), Monthly Activations (Number), New Revenue (Currency), RunTime (Date/Time). Here is the bit I am most proud of - the Month field is a Date Formula field with this formula: DATE(YEAR( DATEVALUE(CreatedDate + 365/12 * Month_Number__c)),MONTH(DATEVALUE( CreatedDate + 365/12 * Month_Number__c )) ,1)  - basically it creates a date which has the first day of the month

X months after the creation date of the record. X is defined by our Month01 to Month12 value that is passed by the Snapshot into the Month Offset field.

I then set up 12 separate Reporting Snapshots that run one a week (on a Saturday morning, an hour apart) which create the snapshot data. I then created reports based on the Snapshot which look at data from the last 7 days (which will ensure that you are always looking at the latest data).

If you followed this let me know. If you think it useful, let me know. Even better if you think there is a better or simpler way of achieving the same result please let me know.

Get in touch

Go to our contact page and leave us a message