For decades, Excel has been a fundamental tool in financial planning. Businesses of all sizes, from small startups to large corporations with billions in revenue, have utilised Excel to efficiently manage their operations. However, as businesses become more complex, depending solely on Excel can restrict financial planning and analysis (FP&A) professionals, particularly in areas like collaboration, data integration, and handling large volumes of data.
In this article, we will examine the benefits and limitations of using Excel for financial planning and forecasting. Additionally, we will cover best practices and explore alternative solutions for organisations looking to optimise and expand their planning processes for more effective insights.
Excel: A powerful but limited planning tool
Excel continues to be a highly utilised tool in the finance sector. Its adaptability, broad recognition, and ease of access make it a vital component of corporate resources. Excel is especially beneficial for small to medium-sized enterprises that can handle their data and complete planning within Excel without exceeding its limits. Businesses leveraging Excel for planning benefit from its customizable interface to create budgets, forecasts, and reports.
Over the years, Excel has undeniably enhanced its features and user-friendliness, making it apt for contemporary bookkeeping and financial planning. For instance, the addition of PivotTables and PivotCharts, along with functions like HLOOKUP, VLOOKUP, and XLOOKUP, have been significant. Power Query facilitates the integration of external data, while Power BI enables users to access, analyse, and visualise data effectively.
While these features certainly enhance Excel’s financial planning capabilities, they also introduce their own learning challenges and constraints. In terms of scale, Excel now supports up to one million rows, which is adequate for many small to mid-sized companies. For businesses with around 1,000 employees, Excel’s functionality to lock cells and establish rules can adequately meet their planning needs.
It’s crucial to emphasize that careful attention is essential: Cells that should remain unchanged must be locked, and those creating the Excel models must be conscious of this requirement. Mistakes can lead to users entering data in incorrect cells. These human errors can ultimately affect planning results, undermining the core of business decision-making.
Identifying and correcting these errors later is both tedious and requires significant effort. Another issue with Excel spreadsheets arises when individuals insert rows and columns without notifying others, which can disrupt formulas and their outcomes. Users in Excel spreadsheets often lack access to real-time data. Additionally, modifications to source data are not always apparent to everyone, leading to many errors going unnoticed initially.
Excel can be effective for FP&A teams’ financial planning and forecasting, but only up to a certain level of scale and complexity. For it to function well in a collaborative and dynamic work environment, Excel spreadsheets must be meticulously and perfectly set up from the start—a task that is practically unachievable. Consequently, challenges inevitably emerge when using Excel for planning, especially regarding the requirements for successful integrated financial planning.
Best practices for Excel-based financial planning
For businesses that rely on Excel for financial planning, the following best practices can help offset some of the most common and pressing pain points:
- Establish and maintain financial modelling standards: Implement standards like color-coding formulas (e.g., black for formulas, blue for inputs, etc.) to improve transparency and readability for all. It’s easier for everyone to be on the same page when standards are adhered to (e.g., the International Business Communication Standards System [ICBS]).
- Prevent “rogue” changes: Avoid adding unnecessary rows and columns that lead to models’ increased susceptibility to errors.
- Use consistent templates: Stick to consistent formats and templates to reduce errors and ensure scalability.
- Maintain basic “housekeeping”: Only put what’s necessary into Excel spreadsheets and throw out what’s not. Poor data will result in poor insights.
Where planning in Excel falls short
As companies expand and their data sets grow, using Excel for planning can swiftly become cumbersome. The sheer volume of data can slow down calculations and extend loading times. Tracking changes in underlying assumptions becomes challenging, especially in teams that share models. This is where Excel’s limitations become apparent, particularly for organizations with numerous stakeholders spread across various geographies and corporate entities.
A major challenge is collaboration. When multiple team members need to work on the same Excel document, it often becomes problematic. Spreadsheets are susceptible to version control issues, and updates to source data aren’t always immediately apparent to all stakeholders. This lack of a definitive single source of truth can lead to significant consequences. Without well-defined role-based permissions to control access to sensitive data, data integrity can be compromised by human error, which may remain unnoticed for extended periods.
Moreover, the effectiveness of spreadsheet models often hinges on the creator’s expertise. If this person departs from the company, it can be very difficult for the organisation to sustain or comprehend the model, rendering it unsustainable and difficult to expand. This creates a single point of failure, and these transition gaps can be particularly problematic with intricate models.
Furthermore, this creates significant issues for maintaining traceability in an organisation’s financial planning, which can become problematic during audits. Relying on an individual’s expertise in Excel is also evident when using it for forecasting. Although Excel can be used for forecasting, the process is complex and often too difficult for the average employee. It demands extensive knowledge of both Excel and the organisation’s business, making it not scalable.
Another issue? Excel is not designed to function as a large database, yet many people attempt to use it as one. As a company evolves, Excel naturally expands with its use cases and business challenges. The accumulation of numbers and the growth of data sets make them increasingly difficult to manage and process. Eventually, the organisation finds itself in need of a large-scale database. However, they often rely solely on Excel, which is not intended for integrated planning.
Integrating external data into Excel using add-ins and plug-ins can lead to longer loading times and is constrained by the amount of data it can effectively manage. Handling a few hundred or even a few thousand records, importing large CSV files from other sources, or accessing existing systems can all result in Excel malfunctioning.
FP&A professionals are limited to working with just two dimensions in Excel. However, businesses often require multiple dimensions for planning, such as product name, serial number, customer, version, currency, and company. When more than two dimensions are needed, pivot tables serve as a workaround to represent these additional dimensions. While this is a possible solution, it is not very user-friendly. It is both time-consuming and labor-intensive, leading to longer loading times, particularly when integrating other files and managing numerous external data connections.
Modern solutions for integrated financial planning should ideally be multidimensional, providing a quicker method to display and manage numerous dimensions. The most effective solutions should utilise integrated data through extract, transform, and load (ETL) tools and application programming interfaces (APIs) to expedite the entire process.
Transitioning to an Excel alternative: When increased complexity, flexibility, and substantial data volumes necessitate more
Excel is undoubtedly a robust spreadsheet tool, providing versatility and a wide array of features. However, as businesses expand, they soon recognise the necessity of Excel alternatives to accommodate more intricate financial planning requirements and ensure seamless operations. Although Excel serves as a solid base for financial planning, it is not a database and was not intended for managing large-scale, multidimensional data.
When exploring alternatives to Excel, businesses should seek software solutions that enhance collaboration, integrate real-time data, and offer strong automation capabilities. Enterprise performance management (EPM) platforms such as Jedox provide an interface similar to Excel, facilitating a smooth transition for Finance teams accustomed to Excel’s user experience and interface.
Using Jedox, organisations can expand effectively without encountering Excel’s limitations on data volume and collaboration. As a multidimensional database, Jedox is capable of managing even the most extensive data sets. Additionally, its Integrator allows for rapid access to external data through an API.
Additionally, Jedox provides seamless integration with Excel automation, allowing businesses to automate repetitive tasks, access real-time data, and share planning models across teams without dealing with manual file exchanges. This reduces the risk of errors, improves traceability, and ensures that everyone is working with up-to-date information.
Visualising data in Excel: Improving financial insights
Excel excels in creating data visualisations using charts, graphs, and PivotTables. However, its visualisation tools can fall short when compared to contemporary business intelligence platforms. Organisations requiring visualisation of extensive, intricate datasets might find Excel’s inherent capabilities insufficient.
Sophisticated financial planning software, such as Jedox, offers improved drag-and-drop data visualisation features that simplify the analysis and presentation of financial and operational data. These tools facilitate more comprehensive analysis across various dimensions—like product lines, regions, and currencies—while ensuring that reports are both visually appealing and informative.
Determining the right time to move your financial planning beyond Excel
For businesses that manage their financial planning effectively with Excel, it continues to be a valuable tool. However, as an organisation evolves, with experts departing or retiring, increasing complexity, and expanding data, it might be necessary to enhance Excel planning or explore transitioning to an alternative that can accommodate the demands of a more dynamic, multidimensional environment.
Recall the best practice guidelines: Set and uphold financial modelling standards, avoid unauthorised changes, use uniform templates, and perform regular maintenance. Following these practices is beneficial, but it demands a unified and continuous effort from all users, along with constant attention to prevent accidental corruption of Excel spreadsheets. With Jedox, these best practices are inherently integrated.
Jedox provides numerous benefits over Excel for integrated financial planning and forecasting, such as enabling real-time collaboration with live data. The Jedox Integrator ETL feature allows for seamless connectivity with any external data sources, ensuring that data analyses are comprehensive and based on all pertinent information. This fosters transparency and a unified understanding of the business, maintaining a single source of truth. Users can effortlessly update data without the need to exchange templates, leading to improved data quality and fewer errors.
Jedox enhances workflow management and usability, especially with the Jedox Add-in for Excel 365. Its robust self-service feature allows users to independently create models without needing IT or external assistance. The Finance team can develop models that accurately represent the company and even construct a digital twin of the business. By providing valuable insights, FP&A professionals can transition into more strategic roles as business partners, contributing to the company’s success.
| Excel | Jedox EPM platform | |
|---|---|---|
| Live data set resulting in digital twin | difficult | easy |
| Preventing “rogue” changes – Role-based access to editing templates and database | difficult | easy |
| Collaboration workflow | difficult | easy |
| Multidimensional modelling | difficult | easy |
| Scalable to big data | difficult | easy |
| Template configuration and maintenance | difficult | easy |
| Adaptable to new business requirements | difficult | easy |
Conclusion: Excel for finance vs. Excel alternatives
Although Excel remains a staple across various industries, it does have its drawbacks. For smaller businesses or those with simple processes, Excel can handle most planning requirements. However, as companies expand and their operations become more intricate, Excel’s limitations—especially in terms of collaboration, real-time data access, and scalability—become apparent.
Excel will continue to be an essential tool for FP&A professionals, whether they are in small enterprises or large multinational corporations. Yet, like any tool, Excel was created for specific tasks, and the evolving challenges of modern FP&A planning have surpassed its capabilities. Therefore, it’s crucial for finance professionals to evaluate whether Excel still fulfils their needs.
Jedox, as a robust financial planning software, enhances companies’ financial planning capabilities by offering improved collaboration, automation, and real-time insights—ensuring their financial models can adapt to their growing business demands. With Jedox, FP&A professionals can continue using Excel while overcoming its limitations, such as spreadsheet disarray and challenges in managing large data volumes.
To experience the benefits of an integrated financial planning platform firsthand, request a demo today.
Discover Jedox, the #1 global planning software
Jedox is the world’s most adaptable planning and performance management platform. Over 2,800 organisations trust Jedox to model any scenario, integrate data from any source, and simplify cross-organisational plans. Discover how to plan for opportunities, react quickly to changes, and hit the mark.
Other content you may like
Aout Kybos
Kybos is a dedicated UK Jedox gold partner. We build planning and analysis solutions that deliver value fast using accountancy qualified consultants. Whether you want a fully customised application or to build upon an existing solution, Kybos consultants are here to help.