Be Wary of the Formula

Before we ever wrote a single line of code when we were developing Vivid Reports we set out to invent a new way to interface with Excel that would not be based on a formula, but would still be flexible, and most important would be secure and reliable.  Even though we were very familiar with Excel, and had used it for years, we researched Excel extensively to find a better way to integrate.  The unique method we created and resulting stability, flexibility and performance has far exceeded anything we could have imagined.

Excel Formula used to create reports

Excel Formula used to create reports

It is important to note that not all formulas are bad.  It just depends on where and how you use them.  Formula that define variances, percent of sales, totals, ratios and other logic are perfect for Excel – after all this is why we use Excel.  But formulas that reference the data source directly through a formula 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, if not all, of today’s Excel based financial reporting tools use some type of Excel formula logic to connect to the source and return results to Excel. The 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.

Here are the inherent problems with formula based reporting solutions:

  • Formulas contain errors.  No matter how much 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 and you are basing decisions off of bad information.
  • 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.  The real problem is that the formula is often very similar yet it has to be recreated over and over for each cell. The result is waiting for reports to refresh.
  • Formulas get broken.  This is similar to an error but this type of error results when something outside of the Excel file changes such as a file name, folder name, or server name.  All of these things could cause a problem.
  • Formulas are difficult to change.  It is often easier to build a formula when your report idea is fresh.  Later when you have to make a change 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.  Be careful!
  • 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”.
  • Version control or out of control! – When you make a change to a formula, you are just changing a cell in Excel.  How do you keep track of that properly?  How can you easily replicate those changes to all of your other cells, worksheets, and files that rely on that same changed definition?  How do you know where each of those definitions are located?
  • Compliance. How can you ensure those reports are accurate?  Do they meet SOX requirements?
Example of Base Input Cells

Example of Base Input Cells

This entry was posted in Blog. Bookmark the permalink. Both comments and trackbacks are currently closed.