Financial Planning & Analysis

How to Fix 4 Excel Mistakes That Could Impact Financial Analysis

For nearly three decades, Excel has been an integral tool for businesses everywhere. Today, despite the advent of newer, more streamlined cloud-based solutions on the market, nearly 60 percent of U.S. businesses still rely on Excel spreadsheets for essential accounting functions, such as planning and budgeting (Source: Enterprise Times).

While Excel proves to be an effective budgeting tool for many small- and mid-sized businesses, it has its limitations. Most notably, financial planning and analysis teams cite that it’s prone to error. But to be fair, much of that “error” that I’ve come across, year in and year out, involves poor methods or common mistakes that you may not even realize you’re making. And while these Excel mistakes may seem small at face value, they can lead to significant inaccuracies that hinder your company’s ability to make major operational decisions. Here I’ll give a quick look at the four most common Excel mistakes that I’ve seen in budgets – and how you can avoid them.

Excel Mistake 1: Links to External Documents

In most cases, multiple people will be working on the same budget. The more links that are created to external documents, the greater your risk for versioning conflicts. On top of being annoying, versioning conflicts can lead to crashes, corrupt files and lost data. Links to external documents also mean you could be linking to stale documents that reside elsewhere and are therefore not being kept up to date.

  • The fix: Keep as much detail as possible directly within your Excel document. This gives you control over versioning issues that lead to lost data – plus, with everything in one place, there’s a better chance the budget creators will remember to keep information current.

Mistake 2: No Mission Control Tab for Financial Analysis

Any good budget should have the ability to model different scenarios, and an Excel-based budget is no exception. For this, it’s key to have a base or “as-is” budget that allows you to layer in and manipulate your data to quickly understand the impact of different drivers, strategic initiatives, potential acquisitions and so on in your financial analysis. While putting these types of variables on different tabs may seem like a good way to organize your data, the method ultimately creates a cumbersome experience. It requires decision makers to cross-reference data from separate areas, limiting their ability to quickly access the data they’re looking for. Take, for example, a company who is considering the addition of a new product line. Finance leaders will want to see the impact of this initiative on overall revenue and costs, as well as how that scenario interacts with other variables. The model, therefore, should provide the ability for users to turn on/off such variables as needed.

  • The fix: Create one Mission Control tab. This allows viewers to layer in the budgeting data they’re looking for. I’ve found this to be the best way to show the impact of different drivers on your “as-is” budget; it provides a starting point upon which other initiatives can be evaluated. This method also reduces inaccuracies. With everything on one tab, there’s less chance that users will forget to make updates to data that’s hidden elsewhere in a large, data-rich Excel file.

Mistake 3: Hardcoded Data Within Your Budget

It may make sense at the time – but using hardcoded figures while creating your budget can inevitably lead to inaccurate reports. As a rule of thumb, avoid hardcoding any numbers in your budget that could potentially change in the future. There are instances where hardcoded figures may be appropriate, for example, for historical data that cannot change. But in more cases than not, it’s best to import real-time actuals into your budget.

  • The fix: The rule speaks for itself on this one. For most of your inputs, it’s poor practice to put in static figures thinking that you think you’ll remember to go back and change them. These common Excel mistakes invariably leads to confusion and budget inaccuracies down the road.

Mistake 4: You Fail to Arrive at Revenue in Multiple Ways

Many companies will project revenue simply by using the revenue from the previous year as a baseline (and adding to that number a certain percentage of growth). This method, however, may provide an oversimplified view for budgeting purposes — and Excel certainly has the capabilities that allow you to break down your revenue in more specific ways.

  • The fix: Integrate into your budget a more in-depth analysis on your revenue. Evaluate the different factors that account for overall revenue, then use separate tabs in Excel to show each of those factors in more detail. Depending on your industry, for example, you may want to break out sales and revenue by product line, by sales rep and/or by geographic region. These separate Excel tabs should then link into the overall Profits & Losses for the company.

For organizations who currently rely on Excel as a budgeting tool, it’s important to know that Excel mistakes such as the ones listed here are not derailing your ability to make critical decisions for business. Excel, for better or for worse, is not going anywhere anytime soon – and thankfully, we have many years of precedent from which we can shape best practices.

For more valuable forecasting insights, don’t miss our free financial forecasting process guide. It provides best practices for helping accuracy within your organization:

financial forecasting process guide

Remember, if you have questions or are interested in partnering with an outside resource to help with budgeting and forecasting, feel free to contact us at any time.

About the Author

Mike has more than 15 years of experience in roles requiring expertise in strategic planning, forecasting, budgeting, business valuation, financial modeling and both buy-side and sell-side M&A transaction support. He has worked with clients across several industries, including entertainment, e-commerce and subscription, real estate and consumer products. Prior to 8020, he served as Finance Manager at Elements Behavioral Health, where his responsibilities included leading the budgeting and forecasting processes as well as acquisition transaction support. Additionally, he served as a Sr. Associate in the Private Capital group at Union Bank, where he focused on executing middle-market acquisition opportunities across various sectors. Mike holds an MBA from the Graziadio School at Pepperdine University, a B.A. in Economics from Brock University and is a CFA Charter holder.

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Planning & Analysis

Avoiding Common Pitfalls in Your Annual Planning Cycle

A proper financial planning and analysis process results in faster decision making, the mitigation of revenue and cost risks and performance optimization for the entire organization. Upon entering an annual planning cycle, it is critical not to take for granted the obvious questions that can provide key insights to financial assumptions in your model. And more… View Article

November 16, 2022Casandra del Carmen

Financial Systems

IBM Cognos TM1 vs. Planning Analytics: A Guide to the Transition & New Features

Planning Analytics is the new buzz word for organizations making the change to the latest version of Cognos TM1, but what is the buzz all about? The answer starts with the name: Planning Analytics, or “PA,” is the new name for TM1. In terms of the differences between IBM Cognos TM1 vs. Planning Analytics, much… View Article

November 9, 2022Kieran Chatulani

Business Stabilization

A Helpful Business Continuity Plan Checklist

For strategic business planners, COVID provided many lessons as the ultimate stress test, bringing issues seemingly from all angles. The scale and length of the disruptions likely stretched many a business continuity plan beyond their limits in a very short amount of time. It is imperative that companies redefine the breadth of their planning to… View Article

October 26, 2022Jim Hliboki

See All

Back to Insights