The Ultimate Sales Forecast Dashboards
Pretty much every CRM system has a dashboard which delivers a snapshot view of revenue progress toward a period end date. But not all of them are created the same, and in fact most simply display a watered-down version of the pipeline or forecast report. This overtly simple approach makes for some interesting eye candy, but doesn't really help sales managers understand the root causes behind performance variances or revenue shortfalls.
What's needed are connected and deeper views which lead managers to quickly see what's working and what's not. From a business intelligence perspective, this is the difference between looking at vanity metrics and getting data driven insights that quickly hone in on the most influential root cause analysis factors that can facilitate actionable follow-through.
Taking sales forecasting to the next level means three things.
First, sales organizations are time and resource constrained so when nearing the end of quarter you've got to pick which opportunities are going to get the most time and focus. A good dashboard will make this exercise self-evident, and good sale opportunity visibility will prioritize and lead sales managers to the opportunities which will most benefit from scarce time.
Second, sales managers need to know if sales person forecasts are accurate, and if the deals forecasted are really going to close. This is where tracking forecast accuracy and opportunity scoring brings truth to the forecast. The Forecast Validation data brings science to corroborate predictability.
Third, when meeting the sales forecast is in jeopardy you've got to have additional information to see if any opportunities not currently forecasted can be accelerated with some type of sales action or flexibility. Sales modeling can often find additional revenue opportunities.
To meet these real world objectives, it's important to understand that a sales forecast isn't a single panel view, it's a set of related views that deliver dynamic information to varying business challenges.
Here's an approach and several illustrative examples that I've used many times with good success.
The Starting Point
Here's a macro sales forecast view which acts as a starting point.
Sales Dashboards to Prioritize Opportunities
Opportunity Prioritization
This is a view that I normally make as a drill-down from the salesperson forecast view. It shows how sales staff time is allocated across sale opportunities and is designed to make sure the best sale opportunities are getting prioritized. What I typically do is display the number of activities per period (usually the last 30 days) and the most recent activity date for all sale opportunities forecasted. I'll also compare these metrics to opportunities that are not forecasted. The goal here is to understand if scarce sales time is allocated to the deals that will produce the most near term revenue.
Aged Opportunities
Most every sales pipeline and sales forecast has them – bogus opportunities. These are your stalled opportunities that you may not recognize are stalled. Some sales staff have a tendency to hang on to them for fantasy reasons or they just don't have anything else to hang their hat on. They're effectively dead wood that inaccurately inflate the forecast report and will most certainly contribute to a revenue performance shortfall. A view that shows the opportunity age for each forecasted opportunity can help identify these phantom deals and get them out of the forecast.
Reverted Opportunities
This is a view that displays either all opportunities or just forecasted opportunities that have moved backwards in some way. Reverted opportunities may include deals whereby sales probability or sales value has moved downward or a previously forecasted opportunity is no longer forecasted.
This view is a good source for finding potentially recoverable sale opportunities as well as identifying those opportunities that need a change in sales strategy, or some other induced change to reverse the direction for opportunities in decline. This is valuable information to determine which sale opportunities require quick action or may otherwise be forever lost.
I also like to create workflow rules which send alert notifications in real-time for lapsed or reverted opportunities.
Stalled Opportunities
I typically create a Sales Velocity view to illustrate two things – sales cycle duration (by sales stage, salesperson, product, customer type and other variables) and stalled opportunities. By permitting drill-down on the stalled opportunities and displaying age dates and activity metrics I can then understand whether each stalled opportunity is temporarily stalled (and should be induced with new actions or recycled to marketing for nurturing) or dead (and needs to be removed from the pipeline or forecast report).
While Sales Velocity information may be included in the Sales Forecast Series, I normally put it in my Revenue Cycle group as it helps with the entire lead-to-revenue cycle.
Sales Dashboards to Validate Forecasted Results
Will forecasted deals really close? Here are some snapshots that can deliver confidence they will, or early indicators they won’t.
Forecast Comparison
CSO Insights publishes the Sales Optimization Research report every year, and every year we learn that forecast accuracy is less than 50% (sales forecast accuracy was 46.5% in the most recent research report). It's troubling when your odds at playing craps in Las Vegas are actually better than your odds at correctly predicting which forecasted opportunities will close.
To remedy this perennial challenge, I've created a display which compares three methods of flagging an opportunity as forecasted to close. The first method is using an Opportunity Score. Anyone who follows my writings knows I'm a big fan of implementing (system calculated) Opportunity Scoring.
In the above example, the Score Forecast column is configured to automatically forecast any opportunity with an opportunity score of 80 or higher. The second method is to automatically forecast any opportunity above a particular close probability value. The third method is the Salesperson Forecast indication which is simply the salesperson subjectively marking the opportunity as forecasted.
Sales pipeline best practices suggest measuring the forecast by different dimensions to surface the variables that most affect forecast accuracy. Comparing the Salesperson's forecast based on judgment as well as the two system generated forecasts with completed Won and Lost sales is generally quite revealing. This isn't a tool to make certain sales people look inferior to programming logic, but instead to help both the sales manager and the sales person learn the variables that are most influential in predicting success.
Categorical Analysis
Once forecast views deliver the big picture, you want to be able to quickly identify both positive and negative patterns by category. For example, are there things in common among sales people whom are not meeting their slated forecasts? Do they spend most of their time selling a product that doesn't compete well or are there a preponderance of target customers outside a product's sweet spot or target market? These are category and correlation questions that can be quickly identified as long as the categories are linked to the forecast data.
Sales Win Rates
I'm a big fan of bringing visibility to the Sales Win Rate as I think it's an outstanding sales management tool and extremely helpful in aiding career growth and advancing the company revenue cycle. However, I may or may not include it with the revenue forecast series for two reasons.
First, the sales win rate is largely inherent in the Forecast Accuracy information. Second, for personal reasons I tend to include this view in my Sales Performance dashboard series or with the Sales Leaderboard series. This is really just a grouping exercise, as in the end any information in any series may become the source of information for any particular use case.
Sales Dashboards to Aid Revenue Shortfalls
We've all been here. The period-end is nearing and it's increasingly clear you are not going to make the forecast commitment. Rather than take the two-step approach of getting frustrated with your sales force and giving in to hap hazard notions, you can instead turn to data that may offer more realistic scenarios.
Stretch Forecast Dashboard
I've worked with some veteran sales leaders that create multiple forecasts in order to give executive management visibility and modeling options — to potentially alter corporate performance. A common example is the Stretch Forecast which includes sale opportunities that are not forecast, but are flagged as Stretch opportunities (this is usually done on the opportunity page with a checkbox field) or opportunities that are not forecast but meet the some minimal probability value and have a close date in the forecast period.
This visibility gives sales managers the opportunity to remedy revenue forecast shortfalls by introducing new sales tactics, time sensitive incentives or other options for the Stretch opportunities which need more flexibility to get them across the finish line.
Find Revenue Dashboard
When I create this visualization for clients I typically start with a single view that supports the following four scenarios.
- Display forecasted opportunities with close dates in the period following the current period. The goal here is to see which if any may be accelerated to the current period.
- Display non-forecasted opportunities with a close date in or near (i.e. 30 days) the current forecast period.
- Display non-forecasted opportunities with a sales probability at or above a select percentage value. For example, display sale opportunities at or above a probability to close of 75% but which are not forecasted. This view can detect overly conservative sales people or even sandbagging.
- Display non-forecasted opportunities which are scheduled to incur a significant product price increase in the following period.
The key here is to mix a combination of these four scenarios. For example, show all sale opportunities forecast in the next period and which have a 80% or better chance of closing.
Each of these views (and the combinations among them) give sales managers the information to brainstorm select opportunities with the goal of determining whether there is a sales strategy or specific sales action that can accelerate their closure to the current forecast period.
Multi-Dimensional Analysis
In addition to displaying the right metrics, a best practice is to permit views to be filtered by period and by multiple dimensions. For example, when viewing sales person performance, build flexibility to permit different periods of time as well as viewing performance by key measures such as sales, margin, product or other criteria that help deliver the whole story.
In fact, if you define these variables as measures and dimensions you take this analysis a big step further by accumulating and exporting the data to excel or a data warehouse for far reaching discovery.
Most Sales Force Automation (SFA) or CRM systems will permit data to be exported to Excel, but fewer will permit the Excel export to a pivot table for multi-dimensional analysis. Salesforce and Microsoft Dynamics CRM do these forecast to pivot table downloads particularly well. This added functionality permits new visualizations for key performance indicators, the ability to see how different or multiple variables impact performance measures and the ability to drill down and see which opportunity records (or which types of records) are most influencing results.
Multi-dimensional analysis or predictive modeling can also be accomplished using a data mart or data warehouse with OLAP (online analytical processing). While this approach is superior in many ways, it's often a good idea to start with pivot tables in Excel in order to understand what data and analysis you really want before kicking-off a data warehouse project.
Of course to achieve multi-dimensional analysis in Excel or a data warehouse you have to have captured the right opportunity variables, integrated related parameters such as product and sales person data and defined methods of calculation.
Sales Forecast Dashboard Best Practices
- Deviations that predict revenue shortfalls are generally the first priority.
- Don't lose sight that the objective is to deliver leading indicator performance measures – often as exception conditions – in context to a business opportunity or challenge.
- Design and development is an iterative process. Start by defining your sales challenges and use cases, and then begin honing in on the key performance indicators (KPI) that are most helpful in identifying and responding to those challenges. Don't be tempted to start with a grid and seek out data that might look good. It's a better idea to begin by studying your history, and reviewing the sales reps, regions, products, customer types or other variables that have most correlated to prior period missed forecast results.
Also identify forecast anomalies among sales people, regions and products which suggest there is a fixable problem, and how to allow sales managers quick access to identify any recurrence of these issues. For example, if the sales team has a 55% win rate for a particular product, but one rep has a 30% win rate for that product, there's likely a fixable solution. While this sounds obvious, very few sales managers actually see the data which identifies these types of patterns.
Once you've got the KPIs in a view, you can then mature the view with color coding thresholds (often green, yellow and red) for the KPIs, visual icons (such as an up arrow or down arrow to indicate trend) and the use of images, charts or graphs to improve data visualization.
- When using color coded data, remember that 7% of men are color blind. That's why you should also associate colors with icons or image shapes.
- From a design perspective, the optimal number of frames on a page is 4 to 6. Beyond that you diluting the most valuable KPIs with less valuable data. Recognize that frame proportions deliver implied importance so make all frames a uniform size unless certain frames are more essential. A somewhat interesting user behavior is that the bottom left frame will receive the primary focus.
- Because sales forecasting is time sensitive, make sure you time-stamp all sale opportunities. This may require an update to your CRM system Opportunity record. Time stamping is also essential for calculating key measures such as lead to revenue conversions and sales velocity. This can also aid the more strategic goal of advancing your sales funnel to become a revenue funnel.
- Data visualization is a critical success factor in getting user adoption. Sales forecasts are most often columnar reports. However, sales pipeline and forecast views are generally bar and pie charts. It's been my experience that once the basic forecast chart is in place, I then display the data using combinations of dials, gauges, scorecards, line graphs, tree maps, heat maps and bubble charts. Once users see these later views, they seldom ever go back to the first version bar and pie charts.
Other factors which contribute to user adoption include designing information views by role and problem set, providing information highlights at-a-glance (not too much data), avoiding dense or cluttered data presentations and making sure the information can be used with a zero learning curve.
- Information display should permit drill-down, filters, searching and downloading to Excel. The most powerful capture sufficient data to permit What-If scenarios and sales forecast modeling. Unless you have a data warehouse, Excel may be the best modeling tool to manipulate the data and identify patterns, exceptions, outliers, anomalies and possible solutions to your sales challenges.
- Dashboards should also permit printing, but most do a terrible job at this.
- Getting sales people and sales managers to monitor shared KPIs brings increased attention and action to those performance measures. A tactic to get sales people to monitor pipeline and forecast data is to supplement those measures with sales quota visualization – including both quota to date and projected OTE (on target earnings) at forecast commitment.
The Point is This
The single revenue forecast dashboard delivered with most sales force automation systems is often little more than eye candy and insufficient to deliver the information and insight sales managers really need. However, the goal isn't to have as many visualizations as you can dream up, but to have the right performance metrics to help solve tough problems.
A good sales dashboard design framework delivers progressively more detailed insights based upon specific challenges. Fortunately, there's a fairly short list of common challenges that contribute to missed forecasts. The dashboards shared in this article help identify those contributing factors as early indicators in order to give management the time and information needed to make a proactive difference.