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.

**Table of Contents**hide

## 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**.

**Note:**Please download the practice workbook if you want to have a clear view of the commercial pipeline. You will find the practice workbook above.

### 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.

**IF function**where

**D16=”Contacted”**is one criterion and

**.35**is the respective true value. Also, we included the rest of the criteria from the drop-down list and included their respective true value. Obviously, you can change the true value to any other fraction according to your necessity.

**Note:**Don’t forget to format

**Cell F8**for

**Percentage**value.

**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.

**Note:**As the argument of

**the SUM function**we used cell references

**E12**,

**E20**,

**E28**and

**E36**as they contain the

**Total Deal Size**for four quarters separately.

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

**Note:**As the argument of the

**SUM**function we used cell references

**G12**,

**G20**,

**G28**and

**G36**as they contain the

**Total Weighted Forecast**for four quarters separately.

### 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**.