The Problem Isn’t Excel — It’s Your Data Process

Introduction

Picture this: You are an FP&A analyst and you just got word that accounting has closed the books for the month. 

The board meeting is in 2 days so you have to download and upload the new financial actuals into your model and update the formulas that will update the forecast so that you can deliver the preview of the figures to the CFO the day before.

As you upload the data and copy your formulas things begin to break and not make sense. Costs have jumped, recognized revenue seems off and the balance sheet doesn’t balance. 

Now a simple activity turns into an all night debugging and data validation event. All these formulas and headaches stem from one thing, the data we receive is never the data we need.

1. Why Excel Models Get a Bad Rap

Expectations vs. Reality:

• What Excel Models Are Meant to Do:

  • Take input data and summarize in a cohesive manner that is to be understood by executives and stake holders

    
    

• The Excel Models Actually to Do:

  • Take data and then formulaically clean and format it 

  • Make manual fixes and updates to data and assumptions

  • Summarize the formulaically and manually adjust data for executives and investors

The Blame Game:

• Sales, Accounting and Finance spend hours bickering over who is right and who is wrong when it comes to the output of the data

• Stakeholders often assume Excel models are inherently flawed, when in fact, they are only as good as the data feeding into them. 

  • Even the best BI tool is if there is bad data being fed into it.

2. Common Data Issues That Derail Excel Models

Inconsistent Data Upload Process:

• Pulling data from other systems that is already formatted or transformed in some other way that is different from other departments

• Using formulas like look ups and if statements so the data has the necessary fields to update your model

• Always having to paste in the the new data for a given month rather than systematically having it updated

Incomplete or Outdated Information:

• Missing fields such as customer IDs, Start/End Dates or prior revenue (MRR/ARR) leading to manual fixes and excel formulas

Lack of Standardization:

• Different teams or systems use varying systems to attain figures such as customer ARR, especially when it comes to CRM vs. ERP. 

• This is where sales people will argue with finance about their commissions as they think they should be getting paid on one ARR figure while finance is paying them on another.

3. The True Cost of Bad Data

Wasted Time and Resources:

• Analysts spend countless hours cleaning and reconciling data instead of generating insights.

• Rework caused by data errors delays critical reporting and decision-making.

Erosion of Trust:

• Leadership loses confidence in reports when numbers don’t add up or look different from one department to another

• Teams become skeptical of data-driven recommendations, hindering progress.

Missed Opportunities:

• Poor data quality obscures growth opportunities, risks, and operational inefficiencies.

• Decisions based on faulty data can lead to costly mistakes.

4. Fixing the Root Cause: It’s All About the Data

Data Integration:

• Invest in tools that centralize and standardize data from all systems. The only way to fix this issue at the source

• Ensure seamless communication between all systems and that there is one true source of data in the correct format for all parties involved to use

Data Cleansing:

• Regularly audit and clean your datasets to remove duplicates, fill gaps, and resolve inconsistencies before they are pulled into a tool (Excel, BI, Tableau) otherwise there is higher risk for error and variances

Data Governance:

• Establish clear ownership of data management responsibilities within your organization.

• Changes to the data should be made as close to the source as possible, departments can query and format the data to fit their specific needs but all the number should flow the same relatively speaking

Conclusion

Don’t let bad data undermine your Excel models or presentations to executives and investors. By addressing the root causes of data quality issues, you can unlock the full potential of your models and deliver insights that drive your business forward.