Mehedi Hasan Shimul
A commercial pipeline comes in handy for determining all ongoing opportunities for a company. It’s quite easy to create a commercial pipeline for a company in Excel. With some basic formulas and built-in features of Excel, we can create a commercial pipeline. Here, we will show the stepwise procedures to create a commercial pipeline in Excel for a company.
Download Practice Workbook
You can download the practice workbook from here.
What Is Commercial Pipeline?
A commercial pipeline means the visual representation of all ongoing sales opportunities for a company. Usually, the commercial pipeline consists of the company name to deal with, their contact information, the expected date for the deal to close, the deal amount, the probability of the deal to be a success, the weighted forecast and a lot more. A commercial pipeline helps to evaluate the sales and negotiate on the business.
Step-by-Step Procedures to Create Commercial Pipeline in Excel
In this section, we will discuss step-by-step procedures to create a commercial pipeline in Excel. For demonstration we included an empty dataset which consists of Organization, Contact Name, Deal Size, Deal Stage, Probability, Weighted Forecast, Expected Close Date, Our Rep, Next Steps columns. We will calculate these elements for four quarters and at the end will determine the Grand Total for the Deal size and Weighted Forecast.
STEP 1: Fill up Basic Information for First Quarter of Pipeline
As the primary stage of creating the commercial pipeline, we have to fill up some of the data manually.
- Firstly, input the associated data in column Organization, Deal Size, Expected Close Date, Our Rep, and Next Steps (Date for next activity).
Read More: How to Create a Sales Pipeline in Excel (with Easy Steps)
STEP 2: Create Drop-Down List for Deal Stage
For the Deal Stage column, we will create a drop-down list consisting of Contacted, Decision Maker Bought In, Contract Sent, Closed–Won, and Closed–Lost which will help us to determine the Probability.
- Firstly, select Cell D8.
- Then, go to the Data tab in the ribbon and select Data Validation from there.
- Consecutively, Data Validation window will appear. Select List in the Allow section.
- Also, write the list elements Contacted, Decision Maker Bought In, Contract Sent, Closed–Won, Closed–Lost there.
- Afterward, press OK.
- Finally, we will see the drop-down list created in Cell D8.
Read More: How to Build a Sample Sales Pipeline in Excel (with Easy Steps)
STEP 3: Apply Excel Formula to Calculate Probability of Deal Confirmation
It’s time to calculate the probability of deal confirmation. For that follow the procedures given below.
- Firstly, write the following formula in Cell F8 to calculate the Probability there.
=IF(D16="Contacted",0.15,IF(D16="Decision Maker Bought In",0.35,IF(D16="Contract Sent",0.8,IF(D16="Closed-Lost",0,IF(D16="Closed-Won",1)))))- Then, hit Enter and we will see the Probability of the deal being a successful one.
Read More: How to Create Pipeline Report in Excel (With Easy Steps)
STEP 4: Calculate Weighted Forecast for Commercial Pipeline Using Simple Formula
A simple multiplication of Deal Size with Probability will give the Weighted Forecast. Follow the given procedures for that.
- Firstly, write the following formula in Cell G8 to get the Weighted Forecast there.
=E8*F8- Simultaneously, press Enter.
- Finally, you can follow the previous steps to fill up the rest of the table for the first quarter of the dataset. In a few cases, you can use the Fill Handle to fill up the data.
Read More: How to Create Project Pipeline in Excel (2 Suitable Methods)
STEP 5: Calculate Total Deal Size and Weighted Forecast for One Quarter
Now, let’s calculate the Total Deal Size.
- Firstly, write the following formula in Cell E12 to get the sum of all Deal Size.
=SUM(E8:E11)- Then, press Enter. We can see the sun of all Deal Size there.
- Also, write the following formula in Cell G12 and press Enter.
=SUM(G8:G11)- Further, press OK and we will see the sum of all Weighted Forecast for the first quarter there.
Thus, we have completed the table for the first quarter of the commercial pipeline.
STEP 6: Generate Dataset for Rest of Three Quarters of Commercial Pipeline
In a similar fashion, you can prepare the rest three quarters’ data table for the commercial pipeline. Change some data according to your need.
Table for Quarter 2:
Table for Quarter 3:
In a similar way, you can create the table for the fourth quarter.
STEP 7: Determine Grand Total of Deal Size and Weighted Forecast
After completion of all four quarters tables, we can determine the Grand Total for Deal Size and Weighted Forecast now. Follow the given procedures for that.
- Firstly, write the following formula in Cell E38.
=SUM(E12,E20,E28,E36)- Afterward, press Enter and we will see the Grand Total for Deal Size there.
- Again, write the following formula in Cell G38 to get the Grand Total of Weighted Forecast.
=SUM(G12,G20,G28,G36)- Consecutively, press Enter. Thus, we calculated the Grand Total for Weighted Forecast there.
Final Output
Thus, we have created the commercial pipeline for the year 2022 for the company SalesPush which will look like the picture below. For clear visualization, we have hidden rows of two-quarters tables.
Conclusion
A commercial pipeline is quite useful for sales estimation and negotiation. Here, we have discussed stepwise procedures to create a commercial pipeline in Excel. If you have any queries or suggestions, please let us know by commenting. Visit our ExcelDemy Website for similar articles regarding Excel.
