Yousuf Khan
Microsoft Excel is a handy software. We use Excel functions for our educational, business, and sales management. Further in sales management, sales representatives can manage their leads with the help of a sales pipeline from qualification to closing. We can personalize our process, manage your lead funnel, do our pipeline analysis visually, and keep tabs on our team using a dashboard with our Excel sales pipeline. With this in mind, we will start from scratch and show you step-by-step procedures to do analysis sales pipeline in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.
Why Do We Need Sales Pipeline Analysis?
Only 20% of the sales process involves direct client contact; the remaining 80% consists of everything in between. Everything changed in the way we viewed our sales process and our sales pipeline management once we began concentrating more on that 80%. In order to close more deals, boost win rates, and hasten the expansion of our company, sales pipeline management and processes are crucial for us. Here are 3 more reasons why we need to do sales pipeline management.
- To define our sales methodology effectively.
- For better statistics and analytics.
- Track the phases of the deals go through.
- Finally, speed up sales and get maximum deals.
This article will answer the following:
- Why do we need to do a sales pipeline analysis?
- How do we input fields into a sales pipeline?
- How to create a Pivot Table & Charts for pipeline management in Excel?
Step-by-Step Procedures to Do Analysis Sales Pipeline in Excel
We advise using an Excel funnel chart to acquire comprehensive insights into the performance of your sales pipeline. The graph is specially made to assist you in seeing the path leads follow before approaching. There, we can visualize, manage, and evaluate our sales crew. To demonstrate, we take a dataset that represents a sales company namely GoBig.
Step 1: Create Fields to Fill Sales Pipeline
In this step, we generate fields to create a pipeline. In order to fill the sales pipeline, we include the following categories in our dataset.
- First, we create a pipeline where the user may enter their preferred settings for the sales pipeline under the Fields tab.
- Additionally, we enter the static data, such as the name of our sales report, the year, the name of our organization, and the event we are tracking, which is typically sales, leads, or another internal term.
- Next, select the preferred date and currency types as well as the pipeline data formatting.
- Afterward, we add the Input Fields sub-header.
- There, we add the sub-header Stage, Status, and Salesperson and fill up the categories.
- Since the Pipeline worksheet contains all the relevant data necessary for analysis.
- Furthermore, open a new sheet.
- Furthermore, do not forget to include Lead Stage, Deal Value, Commission(%), and Commission($) sub-headers.
Read More: How to Build a Sample Sales Pipeline in Excel (with Easy Steps)
Step 2: Calculate Pipeline Fields Using SUM and IF Functions
In this section, we will input relevant data for our pipeline and calculate them using IF and SUM functions. The IF function returns a TRUE value after a logical comparison between two values. Alternatively, the SUM function returns the sum total of a range of cells. Let’s use these functions in the next step for calculation.
- To begin with, in G5, type the following formula,
=IF(F5="","",E5*F5)
- Here, the IF function checks the commission rate in F5. and if it has a value that is not Zero (0) and it multiplies the E5 and F5.
- Press the Enter button to get the commission as an output.
- Later, AutoFill the formula to the rest of the cells in column G.
- As a result, we get the commission values in our dataset.
- Also, we will use the SUM function to calculate the total values and total commissions.
- To do so, in E10, write the formula.
=SUM(E5:E9)
- Tap the Enter or Tab keys to implement the function.
- Similarly, write the formula in G10.
=SUM(G5:G9)
- Press the Enter Key again.
- Thus, we calculate the Total value and Commission of the sales deals.
Read More: How to Create Pipeline Report in Excel (With Easy Steps)
Step 3: Insert Pivot Table to Trend Sales Pipeline
In the next step, we will insert a Pivot Table based on the above-mentioned data. To illustrate, let’s follow the procedure.
- First, select D4 and go to the Insert tab then click the Pivot Table option.
- Subsequently, a drop-down box appears.
- Here, check the New Worksheet box and tap OK.
- Consequently, a PivotTable Fields menu slides into the display.
- Also, check the Lead Stage and Commission ($) boxes.
- Eventually, the Pivot Table pops up in another worksheet.
- Here, the table calculates the grand total commission.
- Now, we copy and paste the value into another table.
- This is because we can create a chart from a pivot table.
- Therefore, we use the TRIM function to make another copy of the table.
- In B13, write the following formula,
=TRIM(B5)
- Press Enter key as well.
- Next, drag the formula cell down to AutoFill.
- Now, type the following in cell C13 to make a copy of the cell.
=C5
- Hence, AutoFill the cell like before.
Read More: How to Create Commercial Pipeline in Excel (With Easy Steps)
Step 4: Insert Funnel Chart to Do Sales Performance Analysis
In our last step, we will visualize the pipeline model through a Funnel chart. To do so, follow the procedure.
- Firstly, select the range B12:C17 and go to the Insert tab > Insert Waterfall, Funnel Chart > Funnel.
- Again, click on the Pivot Table, and locate Insert > PivotTable Analyze > Insert Slicer.
- Subsequently, a dropdown box pops up.
- Here, check the Sales rep and Priority boxes.
- Finally, we get our desired result.
- Last, we analyze our pipeline by clicking on the sales representatives to get individual performances over the last month.
Conclusion
In conclusion, we have discussed a step-by-step procedure to do an analysis of the sales pipeline in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
