ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

sales pipeline report in a crm

Updated on August 6, 2015

What is ?

Sales pipeline is a representation of various stages in the sales process of selling your product(s) or service(s) to your customer. In a Sales pipeline, each & every activity that an organization undertakes to complete a sale is elicited commonly in the form of a graphical representation, and to provide a detailed insight of how a lead is converted into a sales opportunity, till its closed.

There are various forms and types of reports that helps to manage a Sales pipeline,

Pipeline Funnel or Sales Funnel

A holistic Sales pipeline is generally represented in the form of a funnel, which is the first look of a sales pipeline. It encompasses the total number of valid account(s) (or customer(s)) at a particular stage in the sales process; combined with the estimated total worth / amount of revenue ( in dollars) . Each stage, that is represented in the funnel is directly aligned with the sales process within an organization, every company and the industry a company belong to has its own way of defining the sales process, but the most common order is

  • Leads
    • Marketing
    • Lead Generation
    • Suspect
  • Prospects / Contacts
    • Identify
    • Qualify
  • Opportunities
    • Present
    • Propose
    • Negotiate
    • Quote
  • Closed ( became a Customer)

A Sales Funnel might represent various criteria that can be exposed or included to provide a Sales Associate a detailed report of the "current status" of each & every stage in the complete sales process. The common criteria that can be exposed are

  1. Total Number of Accounts / Contacts/ Customers / Companies in that particular stage
  2. Total Number of records in a particular stage
  3. Total Weighted or estimated revenue in each stage using the weighted probability.
  4. Sales Velocity

Source

Determination

Total Number of records :

Determining this value is simply straight forward. Any usable CRM system will have facilities to add accounts (company or customer details), and create Leads, convert them into Opportunities, then create Quotes for an opportunity and convert that into a sale. Since each sales stage is represented as a module in a CRM software , mostly the primary index of creating a record in a particular sales process is the account or the customer's information. It is reasonably simple to calculate the total number of records in a particular stage , and also the total number of accounts, a step in a sales process has. for example, when you create an Opportunity in your CRM system or even in excel , you have to primarily integrate that Opportunity with an account (a customer), this helps you to calculate the total Number of Opportunities at a particular point in time and the total number of accounts linked to those Opportunities.

Total Weighted Revenue :

or estimated revenue or weighted forecast : This is also called the estimated revenue , and calculating the Total estimated revenue can be challenging , and it purely depends on the Sales associate and the Manager, who defines a probability based on various key factors. The generic formula for calculating is

Weighted Revenue forecast = Value of an Opportunity (or Qualified / Unqualified Prospect) * Probability.

Total Weighted Revenue (forecast) = Sum(all the weighted opportunities).

The Probability percentage can be defined by various factors, like

  • Success Rate or Conversion rate of an opportunity
  • Success / Conversion / Closure Rate based on a Sales person or sales team.
  • Predefined % for a particular stage in a sales process based on the historical data
  • Probability based on Line Items (products) of an Opportunity

for example, John has a Lead from Acme with a deal of $100,000 , he can define a probability of wining that deal as , something like 40%. Then the weighted revenue for that deal will be $40,000 , and the summation of the weighted revenues of all opportunities managed by John will provide a total revenue forecast for John . Meanwhile John's manager might decide to apply the average of all the Leads getting converted to a Prospect by John or he might also average the success rates of his/her team as a whole and apply that as a probability. The Sales Manager or the VP of sales might decide to use the historical data and apply the average of all the conversions (commonly called as sales movement) that has happened from one stage to the another in the sales cycle.

The weighted revenue forecasts , is an estimated revenue based on how an organization is defining the best possible probability factor for them to arrive at a ballpark.

The common industrial practice, or which are used commonly by CRM applications are

  • calculating the weighted revenue based on the success rate of a particular deal, or an opportunity.

what a CRM can provide ?

As a good practice, a CRM application should have capabilities to apply either of these probability factors at various levels of the pipeline, since it would hard to apply an opportunity (deal) based success rate during the Lead generation or Suspect determining stage, it is a favorable choice to apply the history of how many leads have been converted to a prospect by the sales team or a sales person, as a probability factor. For instance, if the sales team on an average for the past 2 years converts 5 out of 15 leads to a Sale, then 20% conversion rate can be considered and applied to the Lead or Suspect determination stage, but at the same time for an opportunity which is governed by the value of that deal / opportunity , its more appropriate to base the probability on the success rate of the actual deal itself.

Month
Number of Leads
Closed
20-Jan-10
19
1
20-Feb-10
10
5
20-Mar-10
19
9
20-Apr-10
19
3
20-May-10
12
5
20-Jun-10
10
4
20-Jul-10
17
7
20-Aug-10
19
4
20-Sep-10
14
2
20-Oct-10
16
1
20-Nov-10
10
9
20-Dec-10
12
6
20-Jan-11
16
1
20-Feb-11
13
6
20-Mar-11
11
2
20-Apr-11
17
8
20-May-11
12
1
20-Jun-11
12
2
20-Jul-11
13
7
20-Aug-11
15
5
20-Sep-11
12
1
20-Oct-11
10
2
20-Nov-11
17
7
20-Dec-11
18
4

according to table above, the probability factor is 20% (which means there a 20% success rate sales team converting them into contacts / prospects or to a sale) . If for instance during of April 2012 there are 22 Leads and if the average $ per sale is $50,000 the weighted revenue forecast for the month of April 2012, can be calculated as 22 * 20(%) * $50,000= $220,000.

Further more the weight of this historical data probability can be extended based on

  • Sales person's conversion rate or success rate.
  • Average Days its taking for conversion or movement.
  • Average Sale price
  • Goal set by the business

1. Based on Sales Target

A Sales Manager can set a target on his/her sales team member based on the organization's goal. In order to compute the target for each stage , the historical data on the sales movement or closure rate is critical, for example, if 75% of the proposed opportunities becomes a Sale, then 75% becomes the key index to estimate how many proposals should be targeted to achieve the organization's revenue goal, which means that if the goal of company is to sell 150 Products for the year 2010, then it should plan to propose 200 opportunities based on the movement index / rate of 75%.

Targeted (based on Average per Sale $50,000)

Stage
Historical Sales Movement Rate %
Computed Sales Target
 
 
 
Goal / Target
Suspect
10%
1500
 
 
 
150
Prospect
25%
 
600
 
 
 
Opportunity
50%
 
 
300
 
 
Propose
75%
 
 
 
200
 
Total Weighted Revenue
 
$7,500,000
$7,500,000
$7,500,000
$7,500,000
$7,500,000

Actuals

Furthermore the target and KPI can be applied based on

  • Sales person's performance or conversions
  • Number of Days it taking for conversions per stage
  • Goal set based on individual products

Opportunity based Weighted Forecasts :

For an Opportunity (which is an identified deal for a qualified prospect) , the preferable practice is to apply the success rate of the actual deal itself, as the probability.

Opportunity Name
Account
Value of
Probability %
Weighted
 
 
the Deal $
 
Value
Supply Motherboards
Coldwaters Inc
$100,000
20%
$20,000
overhaul the hardware's
Coldwaters Inc
$250,000
60%
$150,000
Replace the network
Acme Inc
$150,000
30%
$45,000
Design the web
Moon technologies LLC
$20,000
90%
$18,000
Install office
Acme Inc
$50,000
10%
$5,000
CRM solution
Darley Inc
$500,000
90%
$450,000

The Total Weighted Revenue is the sum of all the weighted opportunities,

To improvise the accuracy of the revenue forecast for an opportunity more criteria can be applied , for instance

1. Based on Milestones

The Probability can be calculated automatically by the various milestones involved in a particular opportunity. According to the above table , if to provide CRM solution for Darley Inc consists of 4 main milestones (Show Demo, Propose Solution, Negotiate and Close), and if the first 3 out of 4 milestones is completed successfully , then there is 75% change of winning this deal which implies that the Probability (Closure Rate) can be assumed as 75% and the weighted revenue will be $500,000 * 75% = $375,000.

CRM solution
Darley Inc
$500,000
75%
$450,000

2. Based on Line Items (Products) of an opportunity

Determining the probability based on the Line Item level of an opportunity and applying that to the calculation can be very challenging , but it will surely increase the accuracy of the forecast. for instance if SalesXO Co is negotiating a deal with Darley Inc to sell 4 (independent) products then determining the closure rate based on each individual product (line item)and applying that to the overall weight might yield better forecast accuracy .

Opportunity Name
Products (Line Level)
Account
Overall Value of
Probability %
 
 
 
the Deal $
 
Products & Services
X-ray Unit
Darley Inc
$2,200,000
20%
Products & Services
Ultrasound
Darley Inc
$2,200,000
75%
Products & Services
Digital Radiography
Darley Inc
$2,200,000
15%
Products & Services
CT scanner
Darley Inc
$2,200,000
50%

According to the table data above, overall there is a 40% chance of winning the deal , this can be taken into account while calculating the weighted revenue for that opportunity.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Ravi 

      3 years ago

      FYI, I have been searching for a good information on how to construct a Pipeline Funnel and never found something

      which is more detailed like this, I'm currently developing funnel report for my company's CRM product and this information is very helpful for me.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)