Q. I’m struggling to forecast revenue for my company. I think I can forecast sales for my ten existing clients relatively accurately. However, I have identified about two dozen prospects. I am much less certain how to forecast revenue for them. Do you have any tips for how to think about this issue?
A. First, congratulations, it sounds as though you have a robust sales pipeline. We have a very helpful technique we have used many times with clients to help them forecast revenue. The technique will work over any time frame, but we’ll assume you are focused on forecasting sales for the balance of the year.
You can use this technique on paper or on a whiteboard, but we find it is helpful to use excel because you can set it up to do the calculations for you. In the left most column, list the names of all of your prospects. At the bottom of this column, add one more item called “Unknown.” We’ll use this to add in revenue from clients you will pick up throughout the year that you have not yet identified.
In the next column to the right, enter the amount of revenue you expect to receive during the year if you close the client. Remember, if you think it will take six months to close the client, you will only bill for the balance of the year. Make sure to factor this timing into your estimate of revenue.
In the third column, enter the probability that you will close the deal (obviously, this assessment will be between 0% and 100%). If one client has more than one possible piece of work with different probabilities of closing each deal, create multiple line items. For example, consider two possible projects at the Acme Machine Company. Project A would bring in $150,000 and has a 75% probability of closing. Project B would bring in $500,000, but has only a 20% chance of closing. Create one line for Acme – Project A and a second line for Acme – Project B.
In the fourth column, for each row, multiply the contents of the second column (the expected revenue if the deal closes) by the contents of the third column (the probability that the deal closes). So for example, the fourth column for Acme – Project A would be $112,500 ($150,000 X 75%) and for Acme – Project B it would read $100,000 ($500,000 X 20%).
In the fourth column in the row titled “Unknown” put your estimate of revenue that will come from clients you have yet to identify. Admittedly, this can be somewhat of a crap shoot, but if you have been in business for a few years, history should be a good guide for making this estimate.
The sum of the entries in the fourth column will be the forecast of revenue from the list of prospects. What you have done is to discount the potential revenue from each prospect using your assessment of the probability that the revenue will come to fruition.
A simple example, where you will intuitively know the answer, will help illustrate the concept. Suppose you had ten prospects, each of which you expected to generate $200,000 of revenue if you could close the deal. Further, you assess the probability that each closes to be 20%. This would mean that two of the ten would close. Two new clients at $200,000 each would be $400,000 of incremental revenue.
You’ll reach the same conclusion following the process we outlined. That, is multiply each of the ten estimates of $200,000 of revenue by the 20% chance each will close and add up the ten results (i.e., $200,000 X 20% = $40,000 and $40,000 ten times equals $400,000).
These are still estimates and reality usually varies from estimates—sometimes significantly. This notwithstanding, the process outlined above is the best we have found for estimating revenue from a number of potential sources where there is uncertainty around whether the deal will close.