How I Solved This: Empower a New Sales VP Using Reports with Formulas

In my first meeting with Tina, the new Chief Revenue Officer at a 200-person SaaS company, she made it very clear that she needed to prioritize resources toward closing business while implementing her preferred framework for the company’s sales efforts. Success for Tina would come from her ability to make sense of the company’s selling history and incorporate her learnings into their immediate and longer-term go-to-market activities.

Key business problem

Like most new heads of a sales organization, Tina needs to assess what is working and what isn’t, and make meaningful changes to her team, their tools, and the underlying sales process. She also needs to determine which open pipeline deals she has inherited are real and drive them toward closure. The cost of not showing early success by hitting the monthly numbers is closer board-level scrutiny and the greater likelihood of a short tenure. 

Tina’s revenue operations team wonders which report options in Salesforce are available to help, and whether report formulas could provide clearer insights into the data.

Background

One of the first reports someone might create for a new sales VP is a matrix report of all Open Opportunities, grouped by Owner and Stage. While it's a very useful bird’s-eye view of the sales pipeline, it only shows part of the story. In this case, Nicolas Weaver appears to have few or no opportunities at later sales stages, while Irene McCoy seems to be our best hope of sales in the short term. To have a better sense of what might close, we might go a step further to apply past win success to the open pipeline to generate a projection.

How I solved it

We can dig a bit deeper into the data by creating a joined report with two blocks grouped by the Owner, listing all Closed Opportunities and all Open Opportunities. In the Closed Opportunities block, we calculate the Win Rate using a summary formula which simply divides the count of all won deals by the count of all closed deals. In the Open Opportunities block, we then calculate the Projected Amount within a cross-block formula by multiplying the past Win Rate by the total open pipeline amount.

We can see from this that while Irene McCoy has a much larger open pipeline, based on their past Win Rates, we can project her to close much less than Nicolas Weaver.

1. Create a joined report on opportunities

Let’s start out by creating a report on opportunities, and adjust the filters to be Close Date is All Time and Opportunity Status is Closed. We only use Closed Won and Closed Lost opportunities (not open opportunities) to ensure we are calculating a true Win Rate. If we include open opportunities, we would effectively penalize someone for being good at prospecting by incorrectly increasing the denominator.

We can change the report to a joined report by clicking on the black triangle in the top left corner next to “Report” and selecting Apply.

2. Group by Owner and add summary formula for Win Rate

For clarity, we relabel the block to be “Closed Opps” by clicking on the pencil at the top of the block. We Group Across Blocks with the Opportunity Owner field to make this analysis by Sales Rep. We then toggle-off Detail Rows to collapse the report.

Next, we use the Action pulldown menu next to Columns to create a summary formula. The Formula Editor has a number of useful tools to make it easier to create valid formulas. In this case, we specify the Column Name as “Win Rate” and the Output Type as “Percent.” We write our formula by clicking Won in the Fields selection box and then Insert, then clicking the / operator button, and then clicking Closed in the Fields Selection box and Insert. The Validate button confirms that our formula syntax is correct.

Fun fact: On regular reports, we can add up to five summary formulas. On joined reports, we can add up to 10 per block, meaning we could have up to 50 summary formulas on a single report!

3. Create a block with a cross-block formula

Next, we add a second block to the report, also based on the Opportunities report type. We relabel it to be “Open Pipeline $ & Win Rate $ Projection” and update the filters to show all Open Opportunities for All Time.

We can click Amount in the Outline’s list of columns to show its sum on the report.

We then add a cross-block formula by clicking the Action menu pulldown next to Columns.

When writing the cross-block summary formula, it is best to use the Fields section to the left with the Insert button, as the Formula Editor will enter in the block notation correctly. Since the report types might have fields with the same names, the Formula Editor requires B0 and B1 references in cross-block summary formulas to the specific Report block that each field referenced is coming from. In this case, we are simply recreating the Win Rate from the Closed Opps block and multiplying it by the Amount in the Open Opps block to create a cross-block summary formula for the Projected Amount Won.

To simplify the Report output, I also toggled-off Row Counts.

Business results

Tina, the new head of sales, was under a lot of pressure to close business and hit quota while simultaneously overhauling sales. By offering her a variety of insights into the sales data, we helped her make decisions about where to deploy resources and drive her team to surpass quota. The more insights executives have when running a business, the better decisions they make.

Do try this at home

Here are some other useful data points to consider when doing this kind of sales analysis:

• We can display the Sum of the standard Expected Revenue field on Opportunity, which multiplies the Probability times the Amount.  
• We can go a step further by enabling Opportunity Scoring in our Salesforce org, and creating a custom Formula field which divides the Opportunity Score by 100, then multiplies it by the Amount, to display an Einstein Scored Amount. 
• We can also simply regroup the same report based on region, industry, opportunity type, sales channel, and so on to generate more insights into the health and vitality of sales.

By triangulating across what her sales reps tell us is open, what their past sales history indicates, and what Einstein might reveal, we can provide someone like Tina with valuable insights for driving sales success.

Let us know what you thought of this solution! Reach out to us on Twitter @SalesforceAdmns and tell us how you want to use it with #AwesomeAdmins #HowISolvedThis.

Resources

• Trailhead: Create Reports and Dashboards for Sales and Marketing Managers
• Trailhead: Evaluate Report Data with Formulas
• Admin Blog: Enable Users on Lightning Reports and Dashboards
• External Site: Dashboard Dōjō

Want to see more good stuff? Subscribe to our channel!