Overview

This forum post explains how to build an Opportunity Report that displays deals over £25,000, due to close within the next 180 days, which is achieved using the Criteria tab.  

In addition, a column named "Key Deal?" is present, which features a green background and white text, to instantly draw attention to "Key Deals".  This is achieved via a Calculated Column and CSS Styling, which only highlights the deals that are at an "Advanced Stage" (Stage 3 or 4), and due to close in 60 days (not the "entry level" 180 days that's controlled by the report's Criteria).

As explained in our Without a sales process, you are doomed! blog (click here to read the blog), it’s important to understand your pipeline and identify key deals so that your sales team can really focus on the deals you are most likely to win.  It would be nice to win every deal you’re working on, but the reality is more likely to involve you focusing on deals that best fit your “sweet spot” deal profile.  The example discussed in the blog explains a sales team consider the value and time frame of a deal to be key indicators of a deal’s particular importance (a “Key Deal”).

In this example report, an Opportunity is a “Key Deal” when it:

• has a value (Amount column) greater than £25,000, AND…
• the estimated decision (Close Date column) is within the next 6 months (180 days), AND…
• is at either “Stage 3 – Closing” OR “Stage 4 – Awaiting Order” in the sales process (defined by the Stage column)

The sales team should focus on these deals as they are deemed to be more important than other smaller and longer-range deals in the pipeline.

This report is especially useful towards the end of a key time frame for a sales team, such as the end of a month, quarter or year, as it helps them easily identify where effort should be focused.

In addition, to the “Key Deal?” column the days to Closure is also colour-coded.

• < 30 = Red background
• < 90 = Orange background
• < 180 = Blue background

How do I build it?

• Start > New > Report > select “Create a report from a template report”
• Select the “Template - Sales - Key Deals (Opportunities) at Stage 3 or 4” template
• Give the report a suitable name and description on the Main tab of the report editor and click Save.

Our Knowledge Base has several useful pages detailing the techniques used in this report:

• Calculated Criteria
• CSS styling
• Using Formulae (how to use DATEDIFF)

It’s that simple.  However, you may wish to amend the Low and High thresholds to reflect your requirements.  Your sales process will likely have other data points that indicate that it is a Key Deal - for example the location of the prospect (e.g. country = United Kingdom), you should therefore also amend the template report to reflect your specific requirements.

This screenshot shows the “Key Deal?” calculated column, and the formulae used to detect if the Close Date is within the next 60 days AND the Stage name contains either the number 3 or 4 (the numbers for Closing and Awaiting Order).

• You can amend the low and high thresholds to suit your requirements.
• The background and text colours are also editable