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
Benefits of Sales Pipeline tracker Google Sheets
- 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
Free Google Sheets
How to use Sales Pipeline Tracker Google Sheets Template
- Make a Copy and save this template in your Google Drive.
- Configure the input entries.
- 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.
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.
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.
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.
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.
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.
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.
If the Close Date is left blank, you will see a red background color indicating that the entry is missing.
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
First, the pipeline summary metrics.
- 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.
- 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.
- 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.
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.
Deals Won and Lost
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
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.
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.
