Myth #9 Spreadsheets Are the Best Way to Handle Complex Forecast Rollups

By Michael Lock

I must admit it, as a former VP of Sales, I loved my spreadsheets. I spent hours with my VP of Sales Ops defining and tweaking my spreadsheet. The spreadsheet allowed me control of how I looked at data and I had intimate knowledge of the numbers because I had co-created the system that rolled up the deals, pipelines and forecasts. I did not have to rely on systems controlled by others to get the data I needed. I took the new business and upsell data out the CRM, the renewal data out of the Service system, the small business data out of the e-commerce system. I dumped the data into an Excel spreadsheet and later a Google Doc and had my sales ops team defined rollups and pivots so we could get the data in a view that we could tell what was happening. We had complex formulas that adjusted for overlays, split deals and attempted to deal with currency. Also, we could change the hierarchy of reps and managers in the spreadsheet without asking HR or IT if we realigned territories, or terminated a manager or did a quick reorg. We did it all in the magic spreadsheet.

But after a while, I stopped loving my spreadsheet. Here’s why.

First, it was out of sync from my main systems. Business was moving too fast, there were too many product variables and sales organizations were too complex.  My magic spreadsheet was accurate for about 5 minutes per week. Each week, we would pull the data from the source systems and the forecast would rollup perfectly. But, invariably, the sales people would update the source systems and my magic spreadsheet would be out of sync.

Second, the spreadsheet had summary level data that I needed, but I could not trace that back to source systems and had little drill down capability. We had no way of tracking what had changed. In the end, we spent and huge amount of time arguing about whether the spreadsheet or the source system was right.

Finally, the more complex the spreadsheet became, we made manual errors. We would sometimes type the numbers in wrong. One mistake stands out in memory.   We typed 9M instead of $900K and over counted by $8.1M. Not pretty. And, we would make formula mistakes. I had a great sales ops person, but our magic spreadsheet had grown to 35 tabs. Another time, a cell became unlocked and someone changed the formula in that cell. We double counted $11M. Not pretty. Try telling that to your CEO, the forecast just dropped by $11M because of a spreadsheet error. A 35-tab spreadsheet is like a complex computer program—and it’s fragile. As the business grew, the spreadsheet grew as well–until it collapsed of its own weight.

In sales, we have used spreadsheets for too long. We have used them to do sales comp, track territories, assign quota and do forecasting. These fragile systems, no matter how elegantly they’re put together, can’t scale to meet the demands of a modern sales organization. Fortunately, there is no reason to rely on spreadsheets to run our business anymore. Today we have access to proven, cloud-based AI and machine learning systems which are integrated with the other essential systems like CRM or service or finance. And, these systems give us much more insight and control than we ever had, with even the most magical of spreadsheets.

See how Aviso can help