Salesforce Forecasts & Quotas: Add Calculated Columns to Forecast Page
This is the third video in our Salesforce Forecasting Series! You will learn how to add calculated columns in the Forecast tab. Calculated columns allow you to add columns that calculate additional metrics for your revenue teams.
If you have not setup Forecasts & Quotas in Salesforce yet, check out our videos and articles containing step-by-step instructions on how to quickly setup this awesome tool for your sales teams.
- Blu Tiger Academy Videos:
- Blu Ninjas Articles:
Salesforce Resources:
Our Business Requirements
We set up a new Forecast Type for our sales team and now we need to add calculated columns to show the Gap to Quota and the Pipeline Coverage. Gap to Quota is defined as the difference between the Quota and the closed won opportunity amounts. Pipeline Coverage is defined as the ratio between our open pipeline and the Gap to Quota.
Our sales team consists of the following people:
- 1 Head of Sales
- 2 Sales Managers with 2 sales team members on each team.
- Both Sales Managers report to the Head of Sales.
Calculated Column Considerations
- Each forecast type can have up to 5 custom columns
- In the forecasts page, custom calculated columns don't show changes in the last 7 days and aren't included in rollups.
- These fields cannot be shown in a report.
- You can reference Calculated Column results in other calculated columns -> well this is supposed to be possible but we are getting an error when attempting to do this. We have opened a ticket with Salesforce support on 07.20.2024, we will let you know what we find out.
Calculated Column Formulas
To better understand the formulas below, reference this article from Salesforce and watch our video.
- Gap to Quota ->
- Data Type = Currency
- Column Type = Calculated
- Formula = IF((ForecastingQuotaAmount - ForecastAmount0) < 0, NULL, (ForecastingQuotaAmount - ForecastAmount0))
- Pipeline Coverage ->
- Data Type = Number
- Column Type = Calculated
- Formula = IF((ForecastAmount3/(ForecastingQuotaAmount - ForecastAmount0)) < 0, NULL, (ForecastAmount3/(ForecastingQuotaAmount - ForecastAmount0)))
- Weighted Pipeline -> It is not possible to reference the Stages of the Opportunities to show the exact weighted forecast as seen in the Expected Revenue field. A workaround is to assign a percentage to each Forecast Category. In our example, Commit = 75%, Best Case = 50% and Pipeline = 25%
- Data Type = Currency
- Column Type = Calculated
- Formula = ForecastAmount0 + ((ForecastAmount1- ForecastAmount0)*0.75) + ((ForecastAmount2-ForecastAmount1)*0.5) + ((ForecastAmount3-(ForecastAmount2-ForecastAmount1)-(ForecastAmount1-ForecastAmount0))*0.25)
For any questions, comments, concerns – let us know in the comment section below!
We would love to hear from you!