How Integrating Your Financial Model with Your Spreadsheets Can Change Your Life

By Chrisjan Pauw

What separates a good financial model from a great financial model comes down to a number of core characteristics including scalability, flexibility, accuracy, and a flow that’s easy to follow by its intended audience. It should be simple enough that anyone involved can understand it, but detailed enough to handle complex situations and stress test scenarios. It should also be easily shareable among different users without compromising on security, and a clear audit trail of changes made by different users should be readily available.

Incorporating all these features will lead to a robust financial model that produces more accurate forecasts and budgets.

For this, most organizations will turn to Excel spreadsheets. The reason Excel lives on is that it is familiar to most financial personnel, and decision makers understand its user interface and layout. It is widely available and cheap, so it’s often the easiest tool to start with.

Admittedly, the spreadsheet is a great end-user product, but its core characteristics are not compatible with that of a great financial modeling engine. It is impossible to accurately model financial situations in two-dimensional rows and columns.

Spreadsheets are not scalable. When reaching a certain size, spreadsheets take forever to open, or they simply crash.

They are difficult to administer. When a spreadsheet is sent out to various departments, it is impossible to control who is using it and what changes they are making. This leads to version control issues and inaccurate assumptions. A University of Hawaii study found that 88% of spreadsheets have errors in them.

Which brings us to the next point.

Excel spreadsheets are not made for collaboration. Multiple people cannot access the same data at the same time and changes are therefore not up-to-date or available in real-time.

Often, there will be one person (normally the creator of an intricate spreadsheet) who understands how the model works and how to manipulate it. But this creates a single point of failure, where, if this person were unavailable for some reason (holidays, change of job, etc.), it would leave a critical part of the organization’s planning process obsolete and unusable.

What would true integration between your financial model and spreadsheets look like?

First off, it would mean that you are still able to present budgets and forecasts to stakeholders in a familiar user interface, i.e. Excel, without housing the data or running the model on a spreadsheet.

The spreadsheet becomes merely the window through which the data is presented in real time and in an easy-to-understand format, while still keeping all of Excel’s native capabilities active.

It would mean secure multi-user collaboration, allowing different people to share and collaborate on the same data in real time. Everyone would work with the same information at the same time so there would be no version control issues and changes could easily be tracked.

It would mean limitless scalability, as the data and financial model would be built upon a separate engine that doesn’t overload Excel with heavy processing requirements.

And because budgets, forecasts, and analytical reports could be laid out in an easy-to-follow format, and with less number cluttering, more people would be able to understand and use the reports, mitigating the risk of a single point of failure.

So how will it actually change your life?

Reducing the costs of running a recurring process by reducing the time it takes to execute the process—that is the holy grail for any finance professional.

Producing consolidated reports can literally be reduced from days to hours. A budget, for example, involves several people, each of whom is responsible for his or her specific area of the business. This means that all the different areas will have their own spreadsheet that needs to be consolidated into a master sheet. With true integration, each area manager can update figures in real-time, with changes reflected immediately on the consolidated budget. There’s no need to send the budget around to each area separately. It reduces version control issues and increases the control and oversight a budget manager has over the process.

It also enhances teamwork and communication. Everyone works on the same set of data and, with changes updated in real-time, budget managers can quickly identify and communicate errors that need to be corrected. Again, this would reduce the time it takes to finalize annual budgets, from months to days or even hours.

And instead of having hundreds of different tabs collecting data from different sources, the financial model can collect these sources in a more streamlined fashion, do the calculations, and simply produce the end result in Excel. That means finance professionals will be less occupied with sifting through rows of data to make sure all the information is being pulled from the correct sources, and who can spend more time adding real value by actually analyzing the results and suggesting steps for growth, improvement, and optimization.

A streamlined and centralized financial model, ready to be leveraged to produce various reports in Excel, will also reduce the burden on IT staff. Users can do more with the data without necessarily needing advanced technical skills. Easier user functionality means less time training, less time waiting in a queue for reports from the IT department and less money spent on hiring additional IT personnel.

How is this possible?

The answer is a powerful business intelligence solution built on top of a calculation engine that connects relational data sources with spreadsheet end-users, making business data modeling faster and smarter. Users have more time to analyze data to find patterns for profitability, empowering them as employees and boosting morale.

Business Intelligence Technologies makes these possibilities real, transforming spreadsheets from single-user tools to multi-user platforms. BI Tech creates a dynamic connection between data sources and spreadsheet reports with a customized “metrics engine” that gives users new ways to examine and manipulate data. For smart spreadsheet users—who will have much more time, not to mention technological resources—it is the same environment in Excel, but faster and more streamlined for increased productivity and ROI.