Revenue Forecasting & Recognition at Contract Research Organizations: The Alternative to Spreadsheets

Because of the nature of their businesses, Contract Research Organizations (CROs) need tools that allow for reliable revenue forecasting and recognition. Some CROs need to manage hundreds of forecasts at a time.

These revenue forecasts are important for overall planning and for making staffing decisions. When the future month-to-month workload can be reliably forecasted, appropriate management decisions can be made on both the sales and delivery fronts.

The go-to revenue forecasting tool of choice for many CROs is spreadsheets—either Microsoft Excel or Google Sheets.

Advantages to using spreadsheets for CRO revenue forecasting & recognition

Spreadsheets tend to be the “software of least resistance” for a lot of data management applications. Here are some of the reasons:

Spreadsheet files are free

There’s no incremental cost to the organization when someone creates a new spreadsheet. There’s no software to procure or configure for revenue forecasting and recognition.

Spreadsheets are universal

Almost everyone has access to a spreadsheet application. A spreadsheet can be emailed to anyone within the organization and to people outside the organization.

Spreadsheets can be collaborative

With the advent of cloud-based spreadsheet applications such as Excel Online and Google Sheets, multiple people can simultaneously work on the same spreadsheet.

Spreadsheets are powerful

Excel has hundreds of functions as does Google Sheets.

The desktop version of Excel is well known for its macro capabilities. Google Sheets has scripts.

It’s worth noting that macros, which can be used to lock down ranges of a spreadsheet, do not work in the collaborative online version of Excel.

Spreadsheets are flexible

A user doesn’t need an administrator to make a structural change to a spreadsheet. They simply make the change themselves.

 

Disadvantages to spreadsheets for CRO revenue forecasting & recognition

There are a number of disadvantages to using spreadsheets for revenue recognition compared to using a true shared database.

A spreadsheet is not a true multi-user system

While online spreadsheets can be shared with multiple users, there’s no way to give each of the users access to only what they’re responsible for. In other words, spreadsheets are a “free for all” among users in terms of information viewing and editing.

Spreadsheets are not necessarily secure

The fact that spreadsheets are universal can also be a security risk. If a spreadsheet is not password protected, the data can be shared with anyone, anywhere by emailing an attachment.

Spreadsheets do not scale well

Let’s say a revenue forecast has these three general dimensions:

• The number of contracts
• The contract duration
• The month by month figures for each contract (salesperson forecast, manager adjustment, actuals)

If we assume:

• 200 past and future contracts
• 24 months average contract duration
• 3 sets of month by month figures

That works out to 14,400 spreadsheet cells.

This volume of cells means a lot of scrolling, clicking worksheet tabs and/or switching among multiple spreadsheet files. Finding the data for a specific contract can be time consuming.

Spreadsheets are only partially structured

Spreadsheets are partially structured to the extent that they don’t have a predefined database model. Most new spreadsheets are a blank slate and can take any form. Any user can add columns and rows to an existing sheet.

Spreadsheets don’t always protect the integrity of data

Data can get corrupted fairly easily in a spreadsheet. Examples are when a formula has not been extended through its expected range or when the wrong cell is referenced in a formula.

Spreadsheets do not have an inherent hierarchy

CROs often get repeat business from existing customers. As such, there can be multiple contract opportunities related to a customer. These can include contracts that were won in the past, those that were lost and those that are in a status of “open” or “forecasted.”

There may be different people within a customer or prospect organization who are involved with different contracts.

Spreadsheets do not inherently track these relationships.

With multiple spreadsheet files, there’s no automatic rollup reporting

If revenue forecasting is spread across multiple spreadsheet files, it’s a time consuming, manual process to create a management report that rolls up all the data.

Spreadsheets do not lend themselves to accounting system integration

Integrating a forecasting system with an accounting system requires mapping the relationships and fields of one structured data repository to another.

Because of the aforementioned partially structured nature of spreadsheets (and possibility that data is in multiple spreadsheets), it’s not practical to set up a regularly scheduled back office system update from a spreadsheet.

User commenting in spreadsheets is very subtle

When a comment is added to a spreadsheet, the main evidence is a tiny triangle in the top right corner of a cell. With Excel Online, comments cannot be directed to specific users. Google Sheets does allow for user directed comments along with email notifications.

---

How CRM makes revenue forecasting more secure and efficient

At its core, a CRM system like Salesforce (which is what we support) is a structured database. This structure overcomes many of the spreadsheet pitfalls mentioned above.

A hierarchal structure

Salesforce has an out of the box hierarchy. A customer or prospect is at the top level. Each company can have multiple related people and multiple related contracts (Opportunities). There can be a secondary association among people and contracts.

The Contracts “object” can be customized to accommodate forecast data input. Here is one of the templates that we have set up. A salesperson is responsible for filling in the first section. A sales manager makes adjustments in the second section. The third section is for entering the actual recognized revenue.

Access Security

By default, logging into Salesforce requires two-factor authentication. Anyone logging in from an unfamiliar location and/or device must input an emailed or texted code. For even stricter access security a user can be required to use a mobile authenticator app to get into the database.

User Permissions

Salesforce allows for different levels of user permissions. For example, a salesperson may be set up to only have access to the accounts and associated contracts that he or she is responsible for. A sales manager can have access to all accounts managed by his or her team.

The system is normally set up so that only a sales manager can change the numbers in the forecast adjustment section of a contract opportunity.

Data integrity

The fields for capturing month to month figures are defined administratively. A user cannot add new fields they way they can add new columns in a spreadsheet. However, a user can dynamically expose the appropriate number of months for a contract by specifying the number of months in a field.

Rollup reporting & dashboards

Detail and summary reporting is a standard function of Salesforce. For example, if management wants to see the total monthly amount forecasted and grouped customer and/or by salesperson, that data can be displayed with a simple report.

When actual revenue is added to a won contract, the delta between what revenue was forecasted and what revenue is recognized can be reported on.

Back office system integration

Salesforce has tools for mapping to accounting systems and setting up business rules. There are also third-party products such as Scribe for integrating Salesforce data with accounting software.

Collaborative commenting

Salesforce Chatter allows for directed commenting along with images, links, and file attachments within any record. This means better internal collaboration around contract Opportunities.

Through its acquisition of Quip, Salesforce offers an even deeper level of team collaboration.

While spreadsheets are an indispensable business tool, they are not ideal in the long term for revenue forecasting and recognition. A CRM system removes the risks and inefficiencies associated with this application of spreadsheets within Contract Research Organizations.