Your team says they have $4m of opportunities in the pipeline. But how much is going to close?
How much business will close in the next 30 days?
These decisions impact your hiring, your management of the team, and most importantly your cash flow. If you want to run your business quantitatively you need to be able to anticipate how much money is coming in. Here’s how.
Step 1: Clean The Data
Garbage in, garbage out. We will be looking for patterns in past placements to help us predict the future. But there’s no point in a forecast built on faulty data such as $0 deal values or placements that never got updated.
Our average customer has between 3,000 and 10,000 errors in their source ATS/CRM data. Many of these errors are not critical or date from years ago, but many are recent and “material” i.e. they would significantly affect a forecast. Even after we prioritize them most customers have between 50 and 200 errors ranked as high priority by our system.
So start by diving into your source data with a spreadsheet, combing through to find and prioritize the errors that matter, and making sure everything looks as clean as possible at the source level.
Step 2: Run A Regression
You’re going to do a regression. It’s nothing too fancy, just looking for patterns in the deals which you already closed. Those patterns will inform us about which future deals will close.
Some example patterns could be:
- Value of the placement, e.g. values close to your “sweet spot” could have a higher likelihood of closing
- Activity on the deal, e.g. how hard your team had to work to fill it
- Age of the deal, e.g. fresher deals could have higher likelihoods of closing
If you want to get really fancy, you could look for more specific patterns like:
- History of the client, e.g. had they been easy to close in the past
- History of the assigned employee, e.g. were they a rookie or veteran
Once you have the data, grab your favorite data-handling tool like Excel or SASS. If it’s Excel, the Regression module part of the supplementary add-on called the “Analysis Toolpak”. Set the independent variables to be all of the attributes like price that you identified above. Set the dependent variable to be whether the deal closed or not. Click go.
You’re looking for attributes which have a high R-squared. This is a measure of how much of the close/not-close variability could be explained by the attribute. If closing were perfectly attributable to one factor it will have an R-squared of 1.
Of course, it’s not all that easy:
- The regression may not be linear
- You may need to test fit with metrics other than R-squared
- If there is no obvious fit, you may need to refine or look for other attributes
…but assume for now you found one or two metrics which are highly correlated with deals closing. Continue to Step 3.
Step 3: Apply To Your Pipeline
Once you have attributes that correlate with closing you can apply these to your currently open job orders in the pipeline to predict what will close. Do this by breaking down each opportunity’s value into what percent likelihood it has of closing within a set of time ranges.
For example, you may have an opportunity with a nominal value of $100,000. Based on the attributes in Step 2 you compute that it has a:
- 40% chance of closing in the next 30 days
- 25% chance of closing in 30-60 days
- 15% chance of closing in 60-90 days
- 5% chance of closing in 90+ days
- 15% chance of not closing at all
Multiply each of those percents by the $100,000 nominal value to get the expected value in each time window. Then, sum those expected values up by employee (or by client) to get a pipeline sliced by employee (or client). What you end up looks like this:
- Nominal pipeline for each employee and each client
- For each, the dollar value that will close in 30 / 60 / 90 days
- For each, the dollar value that will probably not close at all
The net result is you can look at your total nominal pipeline of $4m, see that $344k is likely to close in the next 30 days, and how that $344k breaks out by employee/client.
Now…rinse and repeat. You need to reapply the prediction each day to your open job orders so the forecast stays up-to-date. You should also re-run the regression every few months in order to keep the predictive power sharp.