Historical Tracking of Contracts

sugarcrmdevelopers —  February 15, 2011

Editor’s Note: This post comes the blog of SugarCRM Gold partner Epicom. The article covers extending the bulit-in Contracts module to provide a better historical view of contracts with a customer to enable better business decision making.

Out of the box, SugarCRM has a functional Contracts module that can be used to store and track customer and vendor contract information. It also provides a centralized contract document repository where electronic copies of contracts can be easily accessed by CRM users across the organization.

But customer contracts often change over time as new terms are negotiated and services are expanded. Having a way to track these historical changes and understand how they affect the lifetime value of a customer contract is essential to maximizing customer value and profitability. In this post we’ll look at how Epicom solved this problem for one of our customers.

The Use Case

Our customer wanted the ability to track changes to a contract over time and to run reports with those historical values included. For example, when they update a field such as the annual contract value or the direct labor percentage (a key factor in contract profitability), a report was required to show all of the historical changes made to the contract over a given date range.

This client also wanted to calculate the “real” value of the contract which would change with each of these modifications. For example, the annual profit is the annual contract value minus the amount paid to direct labor. If the direct labor changes a few times during the year, then the true profit for the year is no longer just a simple equation, but rather needs to be calculated from the contract history.

An Example

Consider a two year contract for $100,000 a year with 60% direct labor expense. The contract starts March 1, 2009 and expires March 1, 2011. Six months after the contract has begun, the direct labor percentage increases to 70%. Three months later the contract is re-negotiated and the annual contract value increases to 130k.

Values We Have Values We Calculate
Effective Date Annual Contract Value Direct Labor % Contract Start Date Contract End Date First Year Revenue First Year Profit Last Year Revenue Last Year Profit Total Revenue Total Profit
3/1/2009 $100,000 60% 3/1/2009 3/1/2011 $83,561 $33,424 $16,164 $6,465 $200,000 $80,000
9/1/2009 $100,000 70% 3/1/2009 3/1/2011 $83,561 $30,109 $16,164 $4,849 $200,000 $65,041
1/1/2010 $130,000 70% 3/1/2009 3/1/2011 $83,561 $30,109 $21,013 $6,304 $234,849 $75,495

Total Profit
Let A = Annual Contract Value
D = Direct Labor Percentage : values between 0-100
n = number of days the contract had values A and D

Then Total Profit = For every triple (A,D,n), SUM( A * (n/365) * (1-D))

Our Solution

SugarCRM can be configured to maintain a historical audit table for any data stored in the system. One solution is to simply use this audit table to track contract value changes. We could have made all the custom fields auditable, but there are a number of drawbacks to this method. First, it’s difficult to get a snapshot of what the contract looked like on a given date because even though all of the data is being kept, it is not easy to display it in a convenient way. Second, doing the calculations (real annual revenue, first year revenue, etc.) would be more complicated, as we would need to store some kind of effective date for when a change would go into effect as opposed to when the value change was made. And perhaps most importantly, the audit table values cannot be used with Sugar’s built-in Reports module.

Instead we chose to build a custom module called Contract History that contains snapshots of the values the client wanted to track for each contract. Every time one of those values changes, a new record is inserted into the Contract History via a logic hook. After each insertion the necessary calculations are made and the corresponding contract fields are updated.

Finally, we created a many-to-many relationship between this new module and the Contracts module so that a user can easily see the entire history within the detail view of a contract. In addition, a report can be run within the Reports Module listing all contracts and their entire histories.

As a result of this project, our client has a powerful tool for tracking, reporting, and monitoring changes to the size and profitability of its customer contracts.

Would you like to have better visibility into customer profitability? Contact us as we’ll show you how a properly structured CRM application can add huge value to the customer data you already have.