5 challenges of Excel or Google Sheets forecasting and what to do about them

Despite the limitations of Excel or Google Sheets forecasting, most FP&A teams still prefer spreadsheets to analyze and report data and create forecasts.

At first glance, spreadsheets have clear advantages. They’re familiar and easy to use, and they’re relatively inexpensive to implement. It’s also simpler to train people to use spreadsheets than more advanced FP&A solutions.

But is it still possible to use Excel and Google Sheets for something as complex as financial forecasting? Can spreadsheet programs still be relied upon for organizing and presenting data or are they more likely to create bottlenecks?

Our guide to the challenges of forecasting in Excel and Google Sheets takes a deeper dive into the limitations of these two solutions. We’ll also break down how a modern FP&A platform can complement them without sacrificing their ease of use.

Table of contents:

• Top challenges when using Excel and Google Sheets for forecasting
  • 1. Risk of errors with manual data entry
  • 2. Difficulty collaborating with other departments
  • 3. Limited features for protecting sensitive information
  • 4. Multiple spreadsheets to manage as the business scales
  • 5. Inefficient and error-prone scenario building
• Conclusion: Learn how FP&A tools can help you with financial forecasting

Top challenges when using Excel and Google Sheets for forecasting

Financial forecasting in Google Sheets or Excel is possible. Both programs are equipped with tools that can help you build complex models while keeping track of changes in projections.

Despite the Excel and Google Sheets forecast functions, these tools weren’t built specifically for financial forecasting and analytics, which is why it’s no wonder they have important drawbacks.

Below are five roadblocks you can expect to run into when using either Excel or Google Sheets for forecasting.

1. Risk of errors with manual data entry

Integrating data from multiple sources into your Excel or Google spreadsheets is a painstaking and tedious process. Users usually have to do this manually, spending significant time and risking making costly errors. The more sources you have, the longer it takes to export data and re-enter it into your spreadsheet. Unfortunately, there’s no automated way—you need to repeat this process every time you want to run an analysis to make sure you’re using up-to-date data.

Additionally, the flexibility that makes Excel and Google Sheets so popular is also what leads to people making unintended changes to carefully designed models. Even a small mistake can raise questions about the trustworthiness of a forecast.

According to both PwC and KPMG, more than 90 percent of corporate spreadsheets contain errors. This comes as no surprise, as it’s so easy to enter the forecast formula in Google Sheets wrong, overwrite important data in the spreadsheet, or delete a row or column you think isn’t necessary but that breaks a calculation.

If you rely on spreadsheets, your FP&A team can end up wasting time pulling in information from various sources and checking for errors. This inefficient use of resources inhibits your team’s capacity to contribute to more valuable strategic discussions.

FP&A software prevents manual data entry errors

Most FP&A tools integrate with other key data sources. For example, OnPlan works with your favorite platforms, including Netsuite, QBO Quickbooks, Salesforce, BigQuery, SnowFlake, and others. Integration saves your FP&A team significant time, as they’re no longer required to copy data from multiple sources manually.

With FP&A tool integrations, your organization will benefit from automated updates of historicals, no longer having to stitch together fragmented data. You’ll have more time to focus on value-adding activities instead of being stuck doing repetitive tasks.

2. Difficulty collaborating with other departments

Excel has been around forever, and since Google Sheets relies on a similar structure and formulas, most finance professionals are comfortable with both solutions. However, that may not be the case with other departments in your organization.

For a truly collaborative decision-making process, people performing different roles need quick access to data relevant to their area of expertise. Ideally, this should happen at the click of a button, without putting the integrity of your data at risk.

If someone who isn’t familiar with Excel or Google Sheets accesses a spreadsheet, there’s a good chance they may change something inadvertently, affecting your entire forecast. 

Additionally, a CEO may not need the same level of granularity compared to a CFO, but neither Excel nor Google Sheets can provide them with a data view or dashboard tailored to their needs. This makes filtering out relevant information time-consuming and slows down cross-company collaboration.

FP&A tools simplify collaboration

Modern FP&A solutions have fantastic reporting capabilities, and they greatly increase the speed at which you access data. Unlike Excel or Google Sheets, FP&A tools feel less intimidating and more accessible for people not comfortable with spreadsheets.

Most importantly, FP&A tools can ensure every person in the company receives information relevant to their role only. These tools let you set up role-specific dashboards and act as one source of truth for your entire company. You can set up as many dashboards as needed in parallel without having to manually copy your data like in Excel or Google Sheets. Each of these dashboards has multiple real-time data points available at any moment.

For example, the dashboard of a Head of Sales will show information like monthly recurring revenue (MRR), churn rate, average customer value (ACV), and customer acquisition cost (CAC). The CFO’s dashboard may contain information about cash balances and overhead costs, whereas a CEO will likely need an overview of every department’s metrics for a better decision-making process.

Additionally, a modern FP&A solution also makes it easy to create graphs, which let you see trends over time and compare both historical periods and forecast periods. 

3. Limited features for protecting sensitive information, audit trails, and version control

Both Excel and Google Sheets are highly versatile tools that serve more purposes than just FP&A. However, they don’t offer the most seamless process for protecting sensitive data while sharing information.

For example, an organization could have a sheet in its forecast with employee salary details. To share it safely, someone needs to manually remove the salary information and paste it into a separate spreadsheet. Using two separate spreadsheets that are linked to each other isn’t very efficient. It also increases the risk of corrupted data from broken links.

Excel and Google Sheets also offer limited audit and version control features and can’t be used for process management. Even though these tools are making progress with their audit trail capabilities, they still make checking the validity of your data more difficult than it needs to be.

FP&A solutions solve data management needs

FP&A software makes it easy to restrict access to information based on roles. It also shows you who logged in to the system and when. If there are multiple people with access to a model, knowing who made which change is crucial for validating your data.

It’s also useful to know how often your team is using the software. For example, if you see your Head of Sales hasn’t logged in to the system for a while, maybe it’s time to speak to them and figure out why that’s the case.

In areas such as protecting sensitive information, advanced audit trails, and version control, a dedicated FP&A tool offers significant advantages over Excel or Google Sheets.

4. Multiple spreadsheets to manage as the business scales

If your company is in the growth stage, and you’re using Google Sheets or Excel, you should be wary of the challenges these solutions pose to scaling up.

For example, if you’re introducing a new product line or service, you’ll need to extend your existing model. As your business expands and you keep extending your models, they’ll become increasingly difficult to maintain. If one person updates the spreadsheets, they may be the only one capable of updating the model and finding the information you need.

As your business grows and spreadsheets increase in complexity, depending on one person for making updates is going to create a huge bottleneck. 

FP&A tools allow you to scale efficiently

A dedicated FP&A solution is designed with the user in mind. It allows you to make additions to your models easily and provides a streamlined way for everyone to find the information they need.

An FP&A tool also facilitates a more structured way of scaling up and ensures you’re not dependent on a single person for making changes to your forecasts. 

Additionally, great FP&A tools allow you to quickly run or update scenarios, which is essential if you’re growing quickly and throwing more data points into the mix.

5. Inefficient and error-prone scenario building

Scenario planning in Excel or Google Sheets requires you to set up a copy of your original model, which people may not remember to do. If they do remember, they may accidentally save over the base case, only to have to recreate it once they’ve discovered the mistake. However, it may take weeks before anyone notices a problem with the base case model and discovers it’s been overwritten.

Even if you remember to save your scenarios with a different name, when you’re working with multiple scenarios, it means you need to keep track of multiple spreadsheets. Making structural updates to your model means needing to update each of them. This makes efficient data management one of the biggest issues with building scenarios in Google Sheets and Excel.

FP&A tools simplify scenarios

With the right FP&A solution, structural changes are reflected in your base case and all scenarios instantaneously. There’s never a danger of overwriting one versus the other. Creating and updating new scenarios can be done easily, without the need to copy anything or keep track of changes in multiple sheets.

Another big advantage of using a dedicated FP&A tool is the ability to see the results of all your scenarios in one dashboard.

For example, you can see the impact on your cash balance for the next three years if you decide to make an investment versus not making it. You can create a side-by-side comparison or graph for both those scenarios and more if needed. In Excel or Google Sheets, scenario comparisons aren’t possible without significant additional effort.

Learn how FP&A tools can help you with financial forecasting

Using a modern FP&A tool doesn’t mean you’re replacing your Excel or Google Sheets spreadsheets. There are FP&A tools available that keep the same formulas and structure you’re used to while adding functionality specific to the needs of FP&A teams. They even allow you to interact with models directly in Google Sheets.

Some of the FP&A tool functionality that’s missing from spreadsheets includes process and version control, dashboards tailored to specific roles, customizable access permissions, scenario building, and other capabilities that speed up and simplify your forecasts.

Some of the top benefits of FP&A software are:

• Minimizes the impacts of human error
• Reduces the number of spreadsheets to keep track of
• Provides everyone with real-time information relevant to their role
• Improves collaboration for better strategic decision making
• Lessens the time needed to gather and organize data into reports

To discover these FP&A tool benefits for yourself, try out OnPlan today. Book a 30-minute demo and start exploring a better way to do forecasting with FP&A solutions.

Want to dive deeper into essential FP&A topics? Check out OnPlan’s most popular posts of all time here:

• SaaS profit margins: how to calculate them and what are the benchmarks?
• An Introductory guide to rolling forecasts 
• Sales forecasting in Excel: How to do It + free template download
• How to use a headcount planning model to determine your next office location
• Best practices for building a remote-first workforce planning model
• Bookings vs billings vs revenue: What’s the difference?
• What does the SaaS magic number mean for business growth?
• How CFOs can prepare for the Great Resignation

Sources:

1. https://www.pwc.com.cy/en/Consulting/assets/spreadsheet-mistakes-issue-1-march-2016.pdf
2. https://www.investopedia.com/terms/s/scenario_analysis.asp