# How to Create Commercial Pipeline in Excel (With Easy Steps)

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.

## 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). ### 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, ClosedWon, and ClosedLost 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, ClosedWon, ClosedLost there.
• Afterward, press OK. • Finally, we will see the drop-down list created in Cell D8. ### 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. In the formula, we used a nested 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.

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

## Related Articles #### Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts  5 Excel Hacks You Never Knew  