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 2 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