Sample Opportunity Management Formulas

For details about using the functions included in these samples, see Formula Operators and Functions.

Expected Product Revenue

This formula calculates total revenue from multiple products, each with a different probability of closing.

1ProductA_probability__c * ProductA_revenue__c + ProductB_probability__c * ProductB_revenue__c

Maintenance Calculation

This formula calculates maintenance fees as 20% of license fees per year. Maintenance Years is a custom field on opportunities.

1Amount * Maint_Years__c * 0.2

Monthly Subscription-Based Calculated Amounts

This formula calculates an opportunity amount based on a monthly subscription rate multiplied by the subscription period.

1Monthly_Amount__c * Subscription_Months__c

Monthly Value

This formula divides total yearly value by 12 months.

1Total_value__c / 12

Opportunity Additional Costs

This formula calculates the sum of the product Amount, maintenance amount, and services fees. Maint amount and Service Fees are custom currency fields.

1Amount + Maint_Amount__c +
2                    Services_Amount__c

Opportunity Categorization

This formula uses conditional logic to populate an Opportunity category text field, based on the value of the Amount standard field. Opportunities with amounts less than $1500 are “Category 1,” those between $1500 and $10000 are “Category 2,” and the rest are “Category 3.” This example uses nested IF statements.

1IF(Amount < 1500, "Category 1", IF(Amount > 10000, "Category 3", "Category 2"))

Opportunity Data Completeness

This formula takes a group of fields and calculates what percent of them are being used by your personnel. This formula field checks five fields to see if they are blank. If so, a zero is counted for that field. A “1” is counted for any field that contains a value, and this total is divided by five (the number of fields evaluated). This formula requires you to select the Treat blank fields as blanks option under Blank Field Handling while the Advanced Formula subtab is showing.

1(IF(ISBLANK(Maint_Amount__c), 0, 1) + ​
2 IF(ISBLANK(Services_Amount__c), 0,1) + ​
3  IF(ISBLANK(Discount_Percent__c), 0, 1) + ​
4   IF(ISBLANK(Amount), 0, 1) +​
5    IF(ISBLANK(Timeline__c), 0, 1)) / 5

Opportunity Expected License Revenue

This formula calculates expected revenue for licenses based on probability of closing.

1Expected_rev_licenses__c * Probability

Opportunity Revenue Text Display

This formula returns the expected revenue amount of an opportunity in text format without a dollar sign. For example, if the Expected Revenue of a campaign is “$200,000,” this formula field displays “200000.”

1TEXT(ExpectedRevenue)

Opportunity Total Deal Size

This formula calculates the sum of maintenance and services amounts.

1Amount + Maint_Amount__c + Services_Amount__c

Opportunity Total Price Based on Units

This formula generates proposal pricing based on unit price and total volume.

1Unit_price__c * Volume__c * 20

Professional Services Calculation

This formula estimates professional service fees at an average loaded rate of $1200 per day. Consulting Days is a custom field on opportunities.

1Consulting_Days__c * 1200

Stage-Based Sales Document Selection

This formula Identifies a relevant document in the Documents tab based on opportunity Stage. Use document IDs in the form of “00l30000000j7AO.”

1CASE(StageName,
2"Prospecting", "Insert 1st Document ID",
3"Qualification", "Insert 2nd Document ID",
4"Needs Analysis", "Insert 3rd Document ID",
5"Value Proposition", ...
6)
7)

Sales Coach

This formula creates a hyperlink that opens a stage-specific document stored in the Documents tab. It uses the previously defined custom formula field that identifies a document based on opportunity Stage. See Stage-Based Sales Document Selection.

1HYPERLINK("/servlet/servlet.FileDownload?file=" & Relevant_Document__c, "View Document in New Window")

Shipping Cost by Weight

This formula calculates postal charges based on weight.

1package_weight__c * cost_lb__c

Shipping Cost Percentage

This formula calculates shipping cost as a fraction of total amount.

1Ship_cost__c / total_amount__c

Tiered Commission Rates

This formula calculates the 2% commission amount of an opportunity that has a probability of 100%. All other opportunities will have a commission value of zero.

1IF(Probability = 1, 
2ROUND(Amount * 0.02, 2), 
30)

Total Contract Value from Recurring and Non-Recurring Revenue

This formula calculates both recurring and non-recurring revenue streams over the lifetime of a contract.

1Non_Recurring_Revenue__c + Contract_Length_Months__c * Recurring_Revenue__c