)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Salesforce’sCalculating true ACV gets messy when your deals include both implementation fees and recurring subscription revenue.native formula capabilities hit a wall with complex mixed revenue scenarios, leaving you with inaccurate ACV numbers that skew your forecasting and reporting.
SalesforceHere’s how to build sophisticated ACV calculations that automatically separate one-time fees from recurring revenue while keeping your data connected to.
Build accurate ACV calculations using Coefficient
Coefficientsolves this by importing your Salesforce opportunity and opportunity product data into spreadsheets where you can create unlimited, complex formulas. You get the calculation flexibility that Salesforce formulas lack while maintaining live connections to your data.
How to make it work
Step 1. Import your Salesforce opportunity and product data.
Connect to your Salesforce org and import from both Opportunity and OpportunityLineItem objects. Include fields like Product Family, Revenue Type, Sales Price, and any custom fields that identify implementation vs recurring revenue.
Step 2. Create formulas to categorize revenue types.
Use SUMIFS formulas to sum only recurring revenue line items based on your product categorization. For example: =SUMIFS(D:D,C:C,”Subscription”,E:E,”Recurring”) where column D contains amounts, C contains product family, and E contains revenue type.
Step 3. Calculate true ACV with contract length adjustments.
Build formulas that divide total recurring revenue by contract term stored in your opportunity fields. Handle multi-year contracts by using =SUM(recurring_revenue_range)/contract_length_cell to get accurate annual values.
Step 4. Set up automated refreshes.
Schedule your imports to refresh hourly, daily, or weekly so your ACV calculations stay current with Salesforce changes. You can also push calculated ACV values back to Salesforce using scheduled exports.
Get accurate ACV reporting that updates automatically
Start buildingComplex ACV calculations don’t have to be a manual nightmare. With live Salesforce data and unlimited formula capabilities, you can build ACV models that handle any revenue mix while staying current with your deals.your automated ACV system today.
