When we were developing Vivid Reports, we set out to invent a new way to interface with Microsoft Excel® that would not be based on a Excel formula while still being flexible, secure, and reliable. Although we had used Excel for years, we researched it extensively to find a better way to integrate. The unique method that we created resulted in stability, flexibility and performance that far exceeded our own expectations.
Excel Formula is not used to create reports with Vivid
It is important to note that not all formulas are bad. It just depends on where and how you use them. Formulas that define variances, percent of sales, totals, ratios and other logic are perfect for Excel, after all this is why we use Excel. However, formulas that reference the data source directly are problematic. We refer to these as base input cells because they are the starting point or foundation for a report and the connection back to your ERP. These cells should never be based off a formula as they can be calculated and controlled much better outside of Excel®.
Many of today’s Excel®-based financial reporting tools use some type of Excel® formula logic. These formulas query the data source and you copy them to all the cells of a report changing various parameters or references until the report is built. This approach has numerous drawbacks.
These are the inherent problems with formula based reporting solutions.
1. Formulas contain Errors
No matter how often you check a report, as soon as you have 10 columns and 10 rows you have 100 cells and 100 formulas. In accounting, if even one cell is wrong the report is wrong.
2. Formulas are Slow
Each formula is an individual query making an individual call to the data source. This is inefficient and puts undue stress on the server. Additionally, very similar formulas have to be recreated over and over for each cell. As a result, reports take a long time to refresh.
3. Formulas can be Broken
Errors can happen when something outside of the Excel file changes. Examples include a file name, folder name, or server name. These changes could cause a problem for a formula that is calling to a file name that no longer exists.
4. Formulas are Difficult to Change
Usually, it’s easier to build a formula when your report idea is fresh. If you have to make a change at a later date, you must interpret the formula and figure out how to change it. If there are any exceptions, you must ensure they are preserved. The result is changing a bunch of formulas and often one at a time.
5. Where is the Security?
What if someone changes a formula to get different results? All of the definitions are sitting in Excel and not on the server where they are secured. These definitions are critical and they must remain consistent across reports. This is famously referred to as “creating a single version of the truth”.
6. Version Control
When you make a change to a formula, you are just changing a cell in Excel. This makes it difficult to properly track. How can you easily replicate those changes to all of your other cells, worksheets, and files that rely on that same changed definition? Do you know where each of those definitions are located?
7. Compliance
Can you ensure your reports are accurate? Do they meet SOX requirements?
Think it’s time to upgrade your reporting to a system that is wary of formula? Contact our sales team or book a free demo today!
Share:
