Is your sales organization maturing to the point where you need to provide sales forecasts? Have you always built your sales forecasts in Excel, but you want improve your forecast or find a way to reduce the amount of time and effort it takes?
Here are four tips for building a better sales forecast in Excel.
(For more detailed information about sales forecasting, check out our FREE eBook: The Definitive Guide to Data-Driven Sales Forecasting.)
1. Determine your method
What sales forecasting method are you using? Do you know why you are using that method? Will you calculate the expected value of each opportunity or are you being highly subjective and asking your reps to judge which opportunities will close?
I’m on record favoring more objective forecasting methods, but there are circumstances in which subjective inputs are unavoidable, like highly variable, high value negotiations. Or when you simply have a low volume of historical transactions to base your forecast upon. So if you are selling aircraft to the airlines, you are welcome to be subjective in your forecast. Otherwise, I encourage you to try to be as objective as possible and project the expected value of each of your sales opportunities.
2. Use real probabilities
If you are using an objective method for creating your forecast, one of the first things you’ll try to incorporate into your spreadsheet is probability values. You’ll say something like “If it is in stage 1, it has 20% likelihood of closing. If stage 2, 40%. If stage 3, 60%” and so forth. Almost every sales leader I’ve ever met who builds a sales forecast this way has no idea what their true probabilities are. CRM systems like Salesforce.com provide pre-set, default probabilities and most customers simply use those or take other intuitive guesses.
You should make every effort to use real win rates based on your historical performance. If your guesses for the right probability are even 10% off for a stage, it will have a serious impact on your forecast. Go try to extract your historical data, or track all your opportunities for a month to determine what your sales funnel really looks like.
3. Make it repeatable and easy to update
I’ve built my share of Excel spreadsheets in my career. And many of them I’ve had to update repeatedly (every week, every month, etc.). This is especially true of a forecast. You want to structure your spreadsheet in a way that makes it very easy to update so you’re not repeatedly having to clean up your spreadsheet and make lots of manual changes each and every time you update it.
My personal favorite for minimizing my on-going effort is to make sure that the “raw data” I copy into the spreadsheet is completely isolated from any calculations. I make it 100% dead easy to export my list of opportunities from the source system and then copy and paste the contents of the entire export into my forecasting spreadsheet. No extra moving of data. No extra calculations. No extra dragging or filling of formulas. Turning your first version of a sales forecast into a “clean” and easy spreadsheet will take some extra effort up front. But if you keep your spreadsheet clean in this manner, you’ll save yourself a ton of time down the road.
4. If it is not in your CRM, it didn’t happen
And if you are taking an export of opportunity data from your CRM system, it means your reps need to be updating your CRM with every piece of critical data. If it is not in the CRM, it did not happen. You can’t tolerate reps “not getting around to updating the status out of ‘New'” because they were “busy”. You can’t tolerate $0 values.
They need to keep their opportunities accurate and up-to-date. Otherwise, you’ll never have a decent forecast, Excel or otherwise.
What are your favorite tricks for building your sales forecast in Excel? Let us know down in the comments.