Sales Pipeline Tracker – Free Google Sheet Template

Posted on

As an owner of a small business, you will need a tool to manage the data on your sales leads. This Google Sheet Template is developed to help you in tracking your sales leads and in managing them. 

This Google Sheet Template can be effectively used to track the status of your sales pipeline. You can determine where the deals are lost using this Google Sheet template.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Sales Pipeline Tracker 

Sales Pipeline Tracker – Excel Template – Reporting Metrics
Sales Pipeline Tracker – Excel Template – Reporting Metrics

Features of Sales Pipeline Google Sheet Template

  • Track all your sales deals in an organized way in one sheet
  • View active sales pipeline broken down by stages
  • Calculates Expected Deal Values (based on Win %)
  • Calculates average time (days) taken to win deals
  • Identifies stages where deals are lost
  • Easy tool to filter deals by stage and status
  • Easy to customize

How to Use the Sales Pipeline Tracker Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. The reports are populated automatically.

Step 1: Make a Copy of the sheet

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed. 

Step 2: Configure the input entries

Review Stages in Sales Process

By default, the template comes with 5 stages (Lead, Opportunity, Demo, Quote and Sale) . The first 4 can be renamed easily by typing directly on the stage names.

4 Stages in the Sales Pipeline – Rename Stage names
4 Stages in the Sales Pipeline – Rename Stage names

Entering a new Sales deal (or lead)

We can enter new deals by typing in the Deals table.

Each row is a separate deal and it would have Company name, Contact person name, Deal Value and Created Date.

Enter sales leads data – Company name Contact name Deal value and Deal created date
Enter sales leads data – Company name Contact name Deal value and Deal created date

Deal Value can be estimated if it has not been determined yet. But please enter a value so that the pipeline can be evaluated.

We will then enter the current Stage of the deal.

Enter Current Stage of each deal from the drop down
Enter Current Stage of each deal from the drop down

The 4 stages we had defined earlier will be the values to choose from, in the drop down list.

 Then, we enter a Win % for each deal. For deals that are not closed yet, we would enter our estimate of the chances of winning a deal. If we have a 50-50 chance of winning or losing a deal, we would enter 50%.

In some businesses, the Win % are defined to be constant for a specific stage. Example: 10% for all Leads, 30% for all Opportunities, 50% for all Demos, 80% for all Quotes.

This is a sample data set with Stage and Win % entered.

Sample Sales Deals data with Stage and Win %
Sample Sales Deals data with Stage and Win %

4 more Columns are provided to enter the Email address, Phone Number, Next Activity Date and Notes.

Updating a Deal

As we continue to work on deals, new information may arrive. Based on that, we need to update the deals.

Update Win % and Stage columns for deals as needed, so that your active pipeline is always reflecting the reality.

After working on the deal, we would usually schedule the next activity for a future date. We can enter that next activity date in the Next Activity Date column and enter details in the Notes column.

Enter follow up details – Next Activity Date and Notes
Enter follow up details – Next Activity Date and Notes

Closing a Deal

Deals can be closed if a decision has been taken by the customer to purchase. This is the scenario of a deal WON.

If prospect decides to not to purchase or if it has been a while since the prospect has been in touch, we can close the deal as LOST.

We can enter this information by entering the STATUS to be ‘WON’ or ‘LOST’.

Then, we should enter the CLOSE DATE. This date is used to calculate the Average time taken to Win a deal.

Sample Closed Deals data with Status Won or Lost and Close Date
Sample Closed Deals data with Status Won or Lost and Close Date

If the Close Date is left blank, you will see a red background color indicating that the entry is missing.

Data Validation – Missing Close Date for Closed Deals – Shown by Red data field
Data Validation – Missing Close Date for Closed Deals – Shown by Red data field

This red background color will also appear if Close Date is less than Create Date. This is to ensure that the data is accurately entered.

Tips:

Win % could be deleted for closed deals as we already know the result of the deal. They will be ignored for calculations.

For lost deals, Stage should reflect the last stage the deal was in. This will be used to identify where deals are being lost the most.

Step 3: Monitor Sales Performance

This step is one that should be done regularly. It is important to keep an eye on the pipeline metrics to improve performance.

The top section of the sheet shows several important metrics needed. Let’s discuss one by one.

Active Pipeline

Sales Pipeline Tracker – Active Sales Pipeline – Metrics
Sales Pipeline Tracker – Active Sales Pipeline – Metrics

First, the pipeline summary metrics.

Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value
Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value
  1. Open Deals: This represents the number of deals that are not closed yet. Any deal whose status is empty (neither WON nor LOST) will be considered as an open deal. Once a status is entered, it becomes a Closed Deal.
  2. Total Value: This is the total value of all open deals. The Deal Value we enter in the Deals table will be used as such. If we win all the open deals, this is the amount we will successfully sell.
  3. Expected Value: The reality is that we will win some deals and lose some deals. So, it is better to be realistic about our estimated sales. Expected value is the weighted sum of deal value by Win %.

Metrics by Stage

As the summary metrics describe the current scenario of the entire pipeline, it is also equally important to know the metrics on the deals by stage.

Sales Pipeline Metrics by Stage – Deals, Deal Value and Expected Value
Sales Pipeline Metrics by Stage – Deals, Deal Value and Expected Value

As shown in the image above, the same three metrics (Number of Open Deals, Total Deal Value and Expected Deal Value) are shown for each of the 4 stages.

Closed Deals Performance

The previous section showed the active pipeline. As important that is, it is also important to know how we have been performing in closing deals. It can tell a lot about the future.

The next section shows the performance of closed deals.

Performance Metrics of Closed Deals – Conversion Rate, Time To Win Deals
Performance Metrics of Closed Deals – Conversion Rate, Time To Win Deals

Deals Won and Lost

Closed Deals – Number of Deals Won and Lost – Deal Value
Closed Deals – Number of Deals Won and Lost – Deal Value

In the sample above, 3 deals were won for total of $161,000 and 7 deals were lost valued at a total of $74,000.

Conversion Rate & Average Time to win deals

Conversion Rate and Average Time to win Deals
Conversion Rate and Average Time to win Deals

3 deals were won out of 10 closed deals and hence Conversion Rate is shown as 30%.

The 3 won deals took on average 17 days to close. This is based on the Close Date and Created Date we entered.

Where are deals lost?

By knowing where the deals are being lost, we can work on those stages to improve our performance.

Deals Lost by Stage
Deals Lost by Stage

Of the 7 deals we lost, 29% were lost in the Lead stage, 14% in the Opportunity stage, 43% in Demo stage and 14% in Quote stage.

We have entered a small sample of deals here. As we enter more and more data, these % will become very telling in revealing our strengths and weaknesses. If Demo is the stage we fail most often, we may need to improve the demo event to convince our prospects about the usefulness of our products.

You may also be interested in...

Leave a Reply

Your email address will not be published. Required fields are marked *