CPQ Quote – Monthly Recurring Revenue – Double Calculate Issues

Posted on Updated on by DennisCategories:Uncategorized
Print 🖨 PDF 📄 eBook 📱

This is a post about how to configure the calculation of the ever fun loving MRR field! Every customer calculates a field like this (MRR, ARR, NRR, etc) differently! And their tech stack that does the actual calculation is usually a conglomeration of various automations that probably don’t work well together. Below is an example of such a build. There are rollup fields, user editable fields, formula fields, flow injected fields and price rule injected fields. Normally, when I encounter a setup like the example below, I would just redesign the calculation from scratch and remove the existing setup. But we’ll go through the motions of a progressive change as a routine, to illustrate what an admin could do as an iterative rollout.

As a review, here is a basic list of how the CPQ line editor calculates things.

  1. Price Rules
  2. Formula Fields
  3. Save Button
  4. Flow Injections

Let’s see how the below setup affects how the intended field is calculated.

Current Setup

Intended Field: Quote -> Monthly Recurring Revenue

Here is how the Monthly Recurring Revenue field value gets set for a renewal (the quote type of the example quote).

  1. Rollup Summary Field: Quote -> MRR__c
    • Quote -> MRR__c = SUM(Quote Line -> Net Total WHERE Quote Line -> Recurring Revenue = TRUE and Quote Line -> Quantity != 0)
  2. User Edited Field: Quote -> SBQQ__SubscriptionTerm__c
    • Quote -> SBQQ__SubscriptionTerm__c = User Input Value
  3. Formula Field: Quote -> MRR_Monthly__c
    • Quote -> MRR_Monthly__c = IF( ISPICKVAL(SBQQ__Type__c, ‘Amendment’), 1, MRR__c / SBQQ__SubscriptionTerm__c)
  4. Flow: Quote: Record Created – V3
    • Quote -> Contract_MRR__c = {!$Record.SBQQ__Opportunity2__r.SBQQ__RenewedContract__c}.Customer_Contracted_MRR__c
  5. Price Rule: Inject SUM of Renewal MRR Change
    • Quote -> Quote_MRR_Change__c = Quote -> MRR_Monthly__c – Quote -> Contract_MRR__c
  6. Price Rule: MRR for Renewal
    • Quote -> Monthly_Recurring_Revenue__c = Quote -> Quote_MRR_Change__c

Then, here is what the user needs to do to get this to calculate properly. I’ve even ordered it in the most efficient way, with “Calculate Immediately” being set in the package settings.

  1. User: Click the “Add Products” button and add a product
  2. System: Calculate #1 (happens automatically with “Calculate Immediately”)
    • Fills #1: Quote -> MRR__c (Rollup Summary)
  3. User: Fill in Subscription Term
    • Fills #2: Quote -> SBQQ__SubscriptionTerm__c
  4. User: Calculate #2
    • Fills #3: Quote -> MRR_Monthly__c
  5. User: Click the “Save” button
    • Fills #4: Quote -> Contract_MRR__c
  6. User: Calculate #3
    • Fills #5: Quote -> Quote_MRR_Change__c
    • Fills #6: Quote -> Monthly_Recurring_Revenue__c
  7. User: Click the “Save” button
    • Saves the final value

So, in summary, the user has to: Add a product, fill in subscription term, click calculate, click save, click calculate, and click save. Maybe the two calculates would happen when clicking the save button but you’d still need to save twice before you got an accurate number in the field.

Suggested Changes to get to one calculate

Each of these things can be done by itself and rolled out separately. The more you do, the more efficiency gained.

  • Calculate a “Net Total” field with a Price Rule.
    • Create a new field on the Quote Line to hold a value for “Net Total”.
    • Below is a formula you can put into a price action to set this new field. (does not include calculations for contracted prices or discount schedules)
(
	(
		IF(
			Recurring_Revenue__c && !SBQQ__Bundled__c && 
			IF(
				OR(ISPICKVAL(SBQQ__DiscountScheduleType__c, 'Slab'),ISPICKVAL(SBQQ__PricingMethod__c, 'Block')),
				IF(
					OR(AND(!SBQQ__Existing__c, !SBQQ__CarryoverLine__c, SBQQ__Quantity__c == 0),AND(OR(SBQQ__Existing__c, SBQQ__CarryoverLine__c),OR(SBQQ__Quantity__c == SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, ''), SBQQ__Quantity__c < SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c))))),
					0,
					1
				),
				IF(
					AND(!SBQQ__Existing__c,!SBQQ__CarryoverLine__c),
					SBQQ__Quantity__c,
					IF(
						SBQQ__Quantity__c >= SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),
						IF(
							ISPICKVAL(SBQQ__SubscriptionPricing__c, 'Percent Of Total'),
							SBQQ__Quantity__c,
							SBQQ__Quantity__c - SBQQ__PriorQuantity__c + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
						),
						IF(
							AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, '')),
							0,
							SBQQ__Quantity__c - SBQQ__PriorQuantity__c + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
						)
					)
				)
			) != 0,
			IF(
				AND(SBQQ__Renewal__c, !SBQQ__Existing__c, ISBLANK(SBQQ__PriorQuantity__c)), 
				0,
				(
					(
						(
							(
								IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
							) - (
								IF(ISBLANK(SBQQ__AdditionalDiscountAmount__c), 0, SBQQ__AdditionalDiscountAmount__c / SBQQ__ProrateMultiplier__c)
							) - ((
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__Discount__c), 0, SBQQ__Discount__c)
								)
							) - (
								(
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__PartnerDiscount__c), 0, SBQQ__PartnerDiscount__c)
								)
							) - (
								(
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__DistributorDiscount__c), 0, SBQQ__DistributorDiscount__c)
								)
							)
						)
					)
				) * 
				SBQQ__ProrateMultiplier__c * 
				IF(
					OR(ISPICKVAL(SBQQ__DiscountScheduleType__c, 'Slab'),ISPICKVAL(SBQQ__PricingMethod__c, 'Block')),
					IF(
						OR(AND(!SBQQ__Existing__c, !SBQQ__CarryoverLine__c, SBQQ__Quantity__c == 0),AND(OR(SBQQ__Existing__c, SBQQ__CarryoverLine__c),OR(SBQQ__Quantity__c == SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, ''), SBQQ__Quantity__c < SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c))))),
						0,
						1
					),
					IF(
						AND(!SBQQ__Existing__c,!SBQQ__CarryoverLine__c),
						SBQQ__Quantity__c,
						IF(
							SBQQ__Quantity__c >= IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),
							IF(
								ISPICKVAL(SBQQ__SubscriptionPricing__c, 'Percent Of Total'),
								SBQQ__Quantity__c,
								SBQQ__Quantity__c - IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
							),
							IF(
								AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, '')),
								0,
								SBQQ__Quantity__c - IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
							)
						)
					)
				)
			),
			0
		) / SBQQ__ProrateMultiplier__c
	) * IF(ISPICKVAL(SBQQ__Quote__r.SBQQ__Type__c, 'Amendment'), IF(SBQQ__Quantity__c > 0, 1, -1), 1)
) - IF(ISBLANK(SBQQ__RenewedSubscription__c)||ISBLANK(SBQQ__RenewedSubscription__r.Item_MRR__c),0,SBQQ__RenewedSubscription__r.Item_MRR__c)
  • Rollup Summary Field: MRR__c: Convert to Price Rule (Use above field in Summary Variable)
  • Formula Field: MRR_Monthly__c: Convert to Price Rule
  • Flow: Quote: Record Created – V3: Convert to Price Rule
  • Smartly order and calculation event your Price Rules to fire in the correct order.

Better MRR Design

This is a design for an MRR value from the ground up. The spaghetti has been unravelled. Well… aside from the formula for Net Total, pulled from various SBQQ formula fields and CPQ documentation pages. 👍🏻

  • Price Rule: Quote Line – Before Calculate – Inject Recurring Revenue
    • Price Action: Calculate a “Net Total” field.
      • Create a new field on the Quote Line to hold a value for “Net Total”.
      • Below is a formula you can put into a price action to set this new field. (does not include calculations for contracted prices or discount schedules)
IF(
			Recurring_Revenue__c && !SBQQ__Bundled__c && 
			IF(
				OR(ISPICKVAL(SBQQ__DiscountScheduleType__c, 'Slab'),ISPICKVAL(SBQQ__PricingMethod__c, 'Block')),
				IF(
					OR(AND(!SBQQ__Existing__c, !SBQQ__CarryoverLine__c, SBQQ__Quantity__c == 0),AND(OR(SBQQ__Existing__c, SBQQ__CarryoverLine__c),OR(SBQQ__Quantity__c == SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, ''), SBQQ__Quantity__c < SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c))))),
					0,
					1
				),
				IF(
					AND(!SBQQ__Existing__c,!SBQQ__CarryoverLine__c),
					SBQQ__Quantity__c,
					IF(
						SBQQ__Quantity__c >= SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),
						IF(
							ISPICKVAL(SBQQ__SubscriptionPricing__c, 'Percent Of Total'),
							SBQQ__Quantity__c,
							SBQQ__Quantity__c - SBQQ__PriorQuantity__c + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
						),
						IF(
							AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, '')),
							0,
							SBQQ__Quantity__c - SBQQ__PriorQuantity__c + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
						)
					)
				)
			) != 0,
			IF(
				AND(SBQQ__Renewal__c, !SBQQ__Existing__c, ISBLANK(SBQQ__PriorQuantity__c)), 
				0,
				(
					(
						(
							(
								IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
							) - (
								IF(ISBLANK(SBQQ__AdditionalDiscountAmount__c), 0, SBQQ__AdditionalDiscountAmount__c / SBQQ__ProrateMultiplier__c)
							) - ((
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__Discount__c), 0, SBQQ__Discount__c)
								)
							) - (
								(
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__PartnerDiscount__c), 0, SBQQ__PartnerDiscount__c)
								)
							) - (
								(
									IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c, SBQQ__SpecialPrice__c) + IF(ISBLANK(SBQQ__Uplift__c),0,SBQQ__Uplift__c * IF(ISBLANK(TEXT(SBQQ__SpecialPriceType__c)), SBQQ__ListPrice__c,SBQQ__SpecialPrice__c))
								) * (
									IF(ISBLANK(SBQQ__DistributorDiscount__c), 0, SBQQ__DistributorDiscount__c)
								)
							)
						)
					)
				) * 
				SBQQ__ProrateMultiplier__c * 
				IF(
					OR(ISPICKVAL(SBQQ__DiscountScheduleType__c, 'Slab'),ISPICKVAL(SBQQ__PricingMethod__c, 'Block')),
					IF(
						OR(AND(!SBQQ__Existing__c, !SBQQ__CarryoverLine__c, SBQQ__Quantity__c == 0),AND(OR(SBQQ__Existing__c, SBQQ__CarryoverLine__c),OR(SBQQ__Quantity__c == SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, ''), SBQQ__Quantity__c < SBQQ__PriorQuantity__c - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c))))),
						0,
						1
					),
					IF(
						AND(!SBQQ__Existing__c,!SBQQ__CarryoverLine__c),
						SBQQ__Quantity__c,
						IF(
							SBQQ__Quantity__c >= IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) - IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c),
							IF(
								ISPICKVAL(SBQQ__SubscriptionPricing__c, 'Percent Of Total'),
								SBQQ__Quantity__c,
								SBQQ__Quantity__c - IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
							),
							IF(
								AND(!SBQQ__AllowAssetRefund__c, ISPICKVAL(SBQQ__SubscriptionPricing__c, '')),
								0,
								SBQQ__Quantity__c - IF(ISBLANK(SBQQ__PriorQuantity__c), 0, SBQQ__PriorQuantity__c) + IF(ISBLANK(SBQQ__UpgradedQuantity__c), 0, SBQQ__UpgradedQuantity__c)
							)
						)
					)
				)
			),
			0
		)
  • Price Action: Calculate a Quote Line -> MRR
    • This will be your new Net Total field and then any adjustments to it based on your requirements.
    • Sometimes people want to see MRR minus existing MRR from an existing subscription record.
    • Put your special super sauce calculation here.
    • (Custom Net Total / SBQQ__ProrateMultiplier__c ) * IF(ISPICKVAL(SBQQ__Quote__r.SBQQ__Type__c, ‘Amendment’), IF(SBQQ__Quantity__c > 0, 1, -1), 1) ) – IF(ISBLANK(SBQQ__RenewedSubscription__c)||ISBLANK(SBQQ__RenewedSubscription__r.Item_MRR__c),0,SBQQ__RenewedSubscription__r.Item_MRR__c)
  • Summary Variable: SUM(MRR__c)
  • Price Rule: Quote – On Calculate – Inject Recurring Revenue
    • Price Action: Roll up MRR from Lines
      • This will be injecting a value into the Quote-level MRR field with the value of the above Summary Variable.

Please note this is not the definitive

Dennis

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.