Professional Services Firm Forecasting Models

Hi Mark,

At my firm, I work with the CEO and our sales team to maintain a central revenue forecast. It's an admittedly imperfect and brute forced method, but it works for the size of our firm for now. It has served us well for the past couple years and may help you as well. Let me know if you have questions.

Our forecast is updated at least weekly by our sales folks. They enter each opportunity they're working on and update it as they know more, or realistically, as we nag them to update it. At the least, they update it before the weekly sales meeting, and during specific times we ask, such as around planning periods.

Specifically, here's how it works. We have our intranet built around SharePoint. Within SharePoint we made a custom list, and shared it amongst sales team members and the executive team. The list includes the following columns, in this order, from left to right:
-Forecast owner: for opps, this is the sales person. For closed business, this is the PM. For us it's a dropdown list of SharePoint users
-Client: free text field
-Opportunity/Solution/Project: free text field. Each opportunity is detailed on its own line, so the list builds downward as you add to it.
-In plan?: this is a Y/N check box. It is only updated by a select few individuals. It is meant to reflect whether the business is firm enough to include in our planning. This is always yes for closed business.
-Status: drop down of our standard sales statuses, e.g. 1-Closed, 2-Selected, etc. This is where we can sort and filter by the pipeline of deals/projects. Depending on how familiar you are with SharePoint, this field is also useful to build out different views. For example, our default view excludes business that was lost or has been completed. However, it's not lost: just filtered. We can always recall that data through another view filtering on the status field.
-Probability: today this is hardcoded by our sales folks in a free form field where they type in a percentage. We provide general guidance about what percentages generally go with each sales stage. Ideally, you could have this linked to a status selection, but it can be useful to have them independent. All closed deals are set to 100%.
-Hourly rate: This is just an information point about the lead that helps us see what rates we're quoting generally (blended rates) and where our committed projects landed.
-Market: drop down list to choose from our markets and indicate which market is leading the pursuit or won the deal

Then the following columns follow this format:
-Month: You would replace 'month' with a month and year, such as Jan 2011, for each month in a quarter. The month fields are free form, currency formatted. This is where we ask sales reps to estimate their best guess on revenue that might fall into the months. Granted, this can be a tough task and requires training and guidance from you. I find most sales people think about the deal size only in aggregate, not necessarily when revenue will be recognized or earned. This is where teamwork between you, delivery folks, and the sales people is important. Once a deal closes, then we switch over the forecast owner field from the sales rep to the PM running the engagement. They are then responsible for periodically updating the revenue estimate based on their project plan for the period. Finally, once a period is closed, the accounting team goes in an updates a month with the actual numbers. So, when January is closed, you overwrite the estimates with the actuals so the rest of your quarterly forecast reflects that accuracy. If you want to track % error on your estimates, you can download a copy of the SharePoint list into Excel for record keeping and later analysis.
-Month: same as above
-Month: same as above
-Qx Total: this is a formula field, summing all the months in the quarter e.g. [Jan rev est + Feb + Mar] per opportunity. This gives you a quick look at how much revenue you might expect from a project in a quarter. Again with the filtering functionality, you could then also filter by the client field, market, sales rep, etc. to see revenue estimates; helpful stuff and quick to pull up!
-Qx Weighted: this is a formula field, taking the quarter total and multiplying it by the probability field. Ex. An opportunity has 70% probability listed, with $10,000 revenue estimated in Jan; none in Feb or March. The total for the quarter would be $10,000 while the weighted total would be $7,000. As a quarter progresses, you should notice the difference between your weighted total and overall total closing together. As you do this more frequently, you'll get better as well.

You can repeat this format (mo,mo,mo, total, q-w) as long as it's meaningful. We usually keep 1 full rolling year posted, but in reality, only the next 2 months have meaningful data in it (at least the way we work). If you have hard contracts and can forecast better, perhaps you could populate a year or more. The effect is that the list builds out months and quarters into the future out to the right.

That might be more than you bargained for, but that's a detailed look at how we handle sales forecasts for now. I'm in the middle of leading our migration to NetSuite now from QB. We'll be pulling CRM, SFA, accounting, T&E, and reporting into NetSuite, so our sales forecasting will move to NetSuite as well. We're hoping this will streamline and improve the efficiency of the process I outlined above, though much will remain the same, like status, probability, etc.

Cheers!

John Krebsbach
Controller
TiER1 Performance Solutions