Escaping the Spreadsheet Vortex for Faster & More Accurate Forecasting
Consolidated revenue forecasting is the practice of combining pipeline, booked, and fulfilled revenue data from every media type, system, and line of business into a single trusted view of expected revenue.
For media & ad sales organizations, it is among the hardest processes in the business to get right. This V2 Perspective examines the root causes, the symptoms, and the pillars of a healthier forecast.
Purpose & Approach
This V2 Perspective aims to share V2’s experience with the media & ad sales community. We draw on 20+ years of consulting experience with media & ad sales organizations covering an array of industry segments to develop our insights and best practices. We are a humble group and, as such, are open to feedback and additional viewpoints that will help enhance this analysis. Feel free to share your insights with us at letschat@v2sa.com. We’d love to hear from you. Happy reading!Business Challenge
Revenue forecasting is at the heart of every organization, and yet, it’s arguably one of the most convoluted and challenging processes to streamline for media & ad sales. Most organizations leverage an array of spreadsheets that are manually consolidated to aggregate key data. Ad sales forecasts are often comprised of opportunity pipeline, order management, and campaign fulfillment data from first and third-party sources, often spanning multiple media types or channels, including print, digital, linear, streaming, programmatic, and others. This POV paper aims to explore the root causes, symptoms, and remedies for achieving a healthier consolidated revenue forecast.Real World Context
V2 was working with a well-known publishing organization on a prospect-to-cash enterprise architecture optimization project. Our principal consultants were leading a multi-day summit that included a series of workshops focused on challenges spanning data, people, processes, and platforms (our D(3P) methodology).
Data
Definitions, hierarchies, identifiers, and where forecast inputs are stored.
People
Seller and manager discipline, and executive sponsorship of the change.
Processes
Forecast cadence, pipeline review, and cross-department reconciliation.
Platforms
CRM, order management, billing, integration, and analytics.
The workshop on consolidated forecasting was particularly complex as we soon realized the client’s teams had vastly different definitions of “revenue” and what a consolidated revenue forecast means. In fact, at one point, the workshop got quite tense, with the Finance team and the Sales/RevOps teams going head-to-head over these concepts. Opportunity pipeline, booked budgets, delivery campaign values, adjusted delivery numbers, reconciled billing, invoiced amounts… it was a lively and heated discussion, with each department not holding back on where the issues originated. On the finance side, the point of view was “salespeople are constantly booking deals erroneously and causing significant downstream pain.” Sales’ point of view was that “finance doesn’t understand how hard it is to sell and ask for too much and too detailed data. ‘Do you want us out selling or sitting at our desks all day filling in forms?!’”
After things had settled down, we made significant progress that day; however, the end of the meeting really stood out. When asked where the ROI was on streamlining the forecasting process flows and tech, one of the executives stated, “It takes us two weeks and eight full-time people working in spreadsheets to pull together a forecast. You seem like a smart bunch, I’m guessing you can answer that question…” Silence in the room. Time to break and tap into the coffee and cookies outside. Message received.
“It takes us two weeks and eight full-time people working in spreadsheets to pull together a forecast.”
An executive at a global publishing organization, during a V2 consolidated forecasting workshop
2
weeks to produce one forecast
8
full-time people working in spreadsheets
There are all sorts of similar, albeit less dramatic, stories about the challenges associated with consolidated revenue forecasting in media & ad sales organizations. Most have significant issues getting a handle on their forecasts in a timely manner. Accuracy is questionable. Frustration is abundant. You are not alone. Let’s dig into this hot topic a bit deeper.
Understanding the Root Cause
Why has efficient consolidated revenue forecasting been so elusive over the years across all segments of media & ad sales? Here are a few root causes we’ve seen:- Lack of Specificity in the Data Sets & Details Needed to Generate an Effective Forecast
- Poor Opportunity Pipeline Hygiene by Sellers & Managers
- Insufficient Account Hierarchies & Varying Definitions of Accounts Across Sales and Finance
- Inconsistent Middle Management Enforcement of Forecasting-Related Processes
- Inefficient CRM Designs that Lead to Pervasive Offline “Magic Spreadsheets”
- Key Data Exists in Multiple Systems with Ineffective or Non-Existent Integrations to Aggregate Key Data
- Challenging in Data Sharing Between Departments Responsible for Generating a Forecast
Symptoms Checklist
There are myriad signs that your consolidated forecasting has systemic issues:- Challenges in Keeping a Basic Opportunity Pipeline Up to Date
- Siloed Downstream Fulfillment Data
- Convoluted Order Management Booked Data That’s Difficult to Match Up with Other Data Sets
- Multi-Level Spreadsheet Aggregation as a Key Part of Your Process
- Lack of Visibility into Early-Stage Deals Not Being Entered into Any System
- Programmatic Data that’s Hard to Associate with Defined Brands (Deal ID Strategy)
- No Data Warehouse / Data Store to Hold All Data Sets Needed for Forecasting
- Consistent Delays in Invoicing and Significant Reinvoicing
- Extended Days Sales Outstanding (“DSO”) Metrics
- Email-Heavy Workflows Between Departments Instead of a Centralized System of Record
How We’ve Solved It - Making It Happen
Forecasting is challenging and nuanced for each client. While we wish there were a commonly accepted formula for media & ad sales forecasting, our experience tells us that divergence is the norm. That said, we believe there are some common pillars upon which you can begin to streamline your consolidated forecasting:- Define Your Baseline Revenue Sets (e.g., Pipeline, Booked, Fulfilled)
- Obsess Over Fixing Your Opportunity Pipeline Management
- Better Align OMS Data with CRM and Other Downstream Systems
- Define a Data Warehousing Strategy to Aggregate Key Data Sets
- Leverage Enterprise-Level Analytics Capable of Handling Complex Forecasting Calculations (i.e., standard CRM reports and dashboards likely cannot handle the complexity)
- Develop a Plan that Acknowledges Change Management is Critical at Executive, Management & Individual Contributor Levels
- Create a Gold Account ID that Propogates Throughout Your Enterprise Architecture to Link Revenue Figures Across Systems
Pipeline
Expected revenue not yet committed. Owned by sales, and only ever as reliable as pipeline hygiene allows.Booked
Revenue committed through an order or insertion order. Usually owned in the OMS, and the most common point of misalignment with CRM.Fulfilled
Revenue delivered, adjusted, reconciled, and invoiced. Owned by operations and finance, and furthest from the seller’s view.
V2 worked with a well-known global media company that recognized that opportunity pipeline management was just one part of accurate forecasting. Our team built a custom application on the Salesforce platform that enabled their RevOps team to model forecasts informed by Opportunities as well as sellers’ and managers' knowledge of the account as a whole. The data aggregated in this solution was fed into a Salesforce analytics solution, providing executives with dynamic visualizations to pivot ad sales data. By moving off spreadsheets, forecasts were rolled up in real time through a much faster, more accurate process.
Closing Thoughts
Revenue is the lifeblood of any media & ad sales organization. Forecasting helps leadership across various departments understand the organization’s selling motions and delivery effectiveness, and plays a critical role in predicting financial performance. While achieving and maintaining a highly accurate forecasting strategy spanning data, people, processes, and platforms is challenging, it’s not unattainable. Each organization has its own nuances on what’s most important and which “blind spots” are the most important to rectify. In many respects, organizational discipline is a necessary foundational element - without it, one or multiple data sets that contribute to your forecast are likely to be outdated. You need to combine process rigor, intelligent systems design, and consistent data stewardship to achieve your desired state for a holistic consolidated revenue forecast. Departmental collaboration is critical because the various forecast-related datasets typically span sales, operations, and finance. There’s a lot to consider and to optimize. The reality is that you may not be able to optimize all the pieces of the forecasting puzzle at once. You’ll need to break the forecasting challenge down into smaller chunks spanning pipeline, booked, and fulfilled data sets and across different systems and lines of business. And, yes, AI is likely to play an increasingly important role; however, that path will only evolve when organizations build the foundational elements necessary for AI to thrive. Be methodical, embrace the need for large-scale change, and set aggressive, but achievable goals for your organization, and there will be a sunny forecast ahead for you and your executives!Key takeaways
- Consolidated forecasting breaks down most often because pipeline, booked, and fulfilled revenue are defined differently by the teams that own them.
- Spreadsheet aggregation is a symptom rather than the disease. It is what teams build when CRM design, integration, and data strategy leave a gap.
- Opportunity pipeline hygiene warrants outsized investment, because every downstream number inherits its quality.
- Standard CRM reports and dashboards will not carry the complexity of an ad sales forecast. Plan for enterprise analytics and a data store early.
- A gold account ID propagating across the enterprise architecture is what makes revenue figures linkable from system to system.
- AI will matter here, but only once the foundational data, process, and platform work is in place.