Roll-up custom Opportunity fields to the Account Plan

We use the Opportunity Amount field to calculate the values in the KPI section of the Account Plan. The reason is that the standard Amount field on the Opportunity level is the one field we can guarantee everyone has in their Salesforce environment.

However, many businesses rely on something other than the standard Amount field to calculate the opportunity value. Fortunately, the app is sufficiently flexible to allow you to roll up custom fields from the Opportunity to the KPI section Account Plan.

There’s a four-step process for doing this, which we’ll show you in detail. In summary, these steps are:

• Create three formula fields on the Opportunity.
• Add your three newly created Opportunity formula fields to the GSP Account Plan Field Set on the Opportunity.
• Create three matching Currency (not formula) fields on the Account Plan.
• Create custom target and variance fields on the Account Plan.

Let’s step through this with the example below. We’ve also created a video to see the steps in action.

In this example, we’ll assume you have a custom field on the Opportunity called ARR (short for Annual Recurring Revenue) and that the API Name for this field is “ARR_c.”

1. Create three formula fields on the Opportunity

The first step is to create three formula fields with the type of Currency on the Opportunity. These fields will store the won, pipeline, and weighted pipeline values for your custom field.

To do this, navigate to:

• Setup.
• Object Manager.
• Opportunity.
• Fields & Relationships.

Here are the formulas you can use based on our ARR field. Substitute ARR_c with the API Name of your custom field.

• Won ARR IF (IsWon = True, ARR_c , 0)
• Pipeline ARR IF (IsClosed = False, ARR_c , 0)
• Weighted Pipeline ARR IF (IsClosed = False, (ARR_c * Probability ), 0)

2. Add formula fields to the GSP Account Plan Field Set

Next, drag your three new formula fields onto the field set. To do this, navigate to:

• Setup.
• Object Manager.
• Opportunity.
• Field Sets.
• GSP Account Plan.

Drag the three formula fields into the field set:

Optionally, remove the default fields from the field set if you don’t need to show the rolled-up value of the Opportunity Amount.

Remember to click Save on the Field Set once you have finished making your changes.

3. Create three matching Currency (not formula) fields

Now, create three new fields on the Key Account Plan object to store the values rolling up from the Opportunity.

To do this, navigate to:

• Setup.
• Object Manager.
• Key Account Plan.
• Fields & Relationships.

Create these fields with a type of Currency.

The critical thing is that these three fields have the same API Name as the fields on your Opportunity. The custom roll-up will not work correctly if the API name is even a single character different.

Pro Tip:

Make sure the Label of each field on the Account Plan matches the corresponding Opportunity field. Salesforce will then create a matching API Name automatically.

In each case, enter 0 (zero) as the Default Value when creating the fields.

Remember that the fields on the Account Plan will update once you change an Opportunity that fires the underlying trigger. For example, you need to change an Opportunity stage or the value of your custom field for the figures to update on the Account Plan.

4. Create custom Target and Variance Fields

Finally, you’ll want to create custom Target and Variance fields on the Account Plan.

To do this, create the following fields based on our example. You can substitute your custom field values where appropriate.

ARR Target (Currency)
Expected ARR (Currency Formula) (Won_ARR_c + Weighted_Pipeline_ARR_c)
ARR Target Variance (Currency Formula) (Expected_ARR_c – ARR_Target_c)

After neatly organizing the fields on the Account Plan page layout, the final result looks like this.

Remember, you’ll need to change the stage or value fields on an Opportunity to initiate the trigger that rolls up the numbers from your custom fields.