Forecasting monthly revenue in Salesforce.com

Many of our customers earn revenue as their service is used instead of at the point in time when a sale is made. Salesforce.com provides basic revenue scheduling, but it isn’t granular enough for most of our media and software as a service customers.

The out of the box product schedules display revenue equally across time and do not recognize variations in a schedule. You can manually update the product schedule amount and the total amount to show the correct amounts per month; however these calculations must be done outside of Salesforce. This manual calculation and update is laborious and becomes more so when you have multiple Products (possibly with multiple schedules even!) associated with one Opportunity.

With this in mind, we’ve developed a model that generates a monthly forecast for each line item on an opportunity. For example, if you have an opportunity named “Spring Campaign” which begins on March 15 and ends on May 15, the monthly forecasts will automatically allocate 25% to March, 50% to April, and 25% to May.

So how did we set it up in Salesforce? Here’s one approach we’ve developed, but many alternatives are equally feasible.

1. Used the standard Opportunity object and Opportunity Product object.
2. Added Start Date and End Date custom fields to Opportunity Product.
3. Created a custom Monthly Amount object and added custom fields for Month and Amount.
4. Developed an Apex trigger to Auto-calculate a monthly amount as a record in a custom object called “Monthly Forecast” that is a child of Opportunity Line Items.

The amount for each month will be derived by reading the amount for the line item, and multiplying this amount by the % of days in the given month (i.e. If a line item has an amount of 10,000 and the start is March 15 and the end is April 30, the code in this project would create 2 Month records. The first record would be for March with an amount equal to $3,333, and the second would be for April with an amount equal to 6,667). This “Monthly Amount” record not only has to be inserted with the correct value but changes to line items (i.e. a change to the start, end, or amount) will cause the “Monthly Amount” to be recalculated. 

See chart below for a visual representation of this object model.

Below is an example of how the forecasts appear on the related list for an opportunity.

….and here is what happens when the dates are changed on the line item.