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. (more…)
Graphs, Charts, Traffic Lights, Thermometers, Spinning Monkeys, you name it….
We are a visual species, and the seemingly endless onslaught of data visualization BI tools would suggest that we are becoming even more accustomed to making business decisions with the help of pretty pictures.
I don’t mean to diminish the value of these types of tools. Without question, data visualization is a great way to merge all sorts of data together from disparate sources. Until recently, it was nearly impossible to provide decision makers with access to this variety of information in a format that was easy to configure and consume.
Data Visualization tools are helpful for presentation and analysis of both Flexible and Stuctured G/L based Reporting. Unfortunately, you can’t file an Executive Dashboard with the IRS..
If Data Visualization is the Sizzle in Business Intelligence, Financial Reporting is the Steak.
Structured Reporting is often left out of the BI discussion, yet all organizations require and rely on Financial Reports/Statements.
Your management team might like dashboards, but anyone responsible for the financial performance of the organization, in addition to your Shareholders, your Auditors, your Banker, your Government and others all require accurate financial reports.
Cash Flows, Balance Sheets, and P&L Statements are not going anywhere, and the preferred format for creating them is still Microsoft Excel. Admittedly, Excel isn’t perfect… Human error, Security, Version Control, Ability to Collaborate all present challenges to growing organizations.
That is why we created Vivid Reports. We encourage you to learn how we are helping organizations manage their businesses more efficiently, close month end faster, and produce accurate Financial Reports in a timely fashion. We even built in some Data Visualization Tools (Spinning Monkeys are in Development).
In this brief article we will examine the Multidimensional Chart of Accounts. Don’t forget to download the Free Top 10 Tips for Organizing your Chart of Accounts Document at the end of this article.
Over the years we have seen a lot of Chart-Of-Accounts (COA), and some are set up much better than others. There can be various reasons for this such as change in organizational focus, the length of time the system has been used, staff turnover, and a poor design.
The intent of this article and the accompanying “Top 10 Guide” is to provide some tips based on our observations over the years related to the design that can hopefully save you time and help you get the most out of your financial reports and analysis.
COA Definition & Meaning
A multidimensional Chart-Of-Accounts (COA) is an account numbering system that has multiple segments (aka dimensions) and is typically used by a larger organization. These organizations are more complex (for example they have multiple physical locations or product lines) and need to track their Assets, Liabilities, Equity, Revenue, and Expense transactions from more than one perspective or identifier within the General Ledger. They are also, of course, running an ERP that supports this enhanced structure such as Microsoft Dynamics, Epicor, or SAGE.
A typical example is an organization, such as a restaurant chain, that has many locations. They cannot track revenue and expenses at just the account level but need to identify each transaction on a location by location basis so they can produce and analyze financial reports per location. This will enable management to know which location is the most profitable, the most expensive, has the best margins, where physical assets are located, and so on.
This basic example would require each transaction to be coded so that it identified two things: the main account (aka core, primary, object) segment and also the location number. In figure #1 below there are three segments because this organization also wants to track things on a departmental basis. This figure shows the “Full Account” (segment combinations that are used), the individual segment values and descriptions, and lastly the definition.
For additional Information Download the Free 10 Tips for creating and organizing your Chart of Accounts (COA) .
When we were designing Vivid Reports CPM we decided to go the route of a separate data warehouse layer. We are often asked why, and what we feel the advantages are. I hope this article will shed some light on why we feel a data warehouse layer provides a superior reporting experience.
There are two fundamental data access methods when it comes to reporting. You can either access the data directly at the source for “real-time” information (note only as “real time” as the data is entered). Or you can access a Data Warehouse that is updated as part of a scheduled process.
If real-time information is not a requirement (which is often the case with financial reporting) then a data warehouse offers many distinct advantages. Below are some of the major benefits of utilizing a data warehouse layer for corporate performance management and reporting solutions:
1) You can modify or enhance the data without affecting the core system. For example you could rename fields to make them more meaningful, or attach comments to information that may not be supported in the core system.
2) Improved processing speed, independence, and reducing or preventing record locking or other data access issues. Because a data warehouse is a separate database you have the ability to:
- Run the database on a dedicated server
- Index or optimize data in a way that is designed purely for reporting and analysis
- Perform calculations and store the results of complex queries and relationships
- Separate data write and read operations
3) Security and permissions can be different from the core system. This can enhance the security of the core system by having fewer logins. You can also potentially enhance security through data separation. For example the source database might have payroll information whereas the data warehouse does not.
4) Ability to combine disparate systems into a single cohesive view of information. An example would be to take the General Ledgers from two distinct ERP systems and combine them in a single database to produce consolidated financials.
5) By having a separate data layer you can often change the core system without having to upgrade the reporting system. For example if a field is renamed during an upgrade to the core system this field could potentially be remapped in the data layer of the data warehouse without having to rewrite each report.
6) You can control when data is synchronized with the core systems. Therefore you can schedule this to occur “after hours” or during periods of reduced demand on the core system. A second advantage is that you can take a “snap shot” of the data which allows you to compare updates to help identify what has changed.
7) You can usually reduce license fees when some of your users only need to view information that is captured and stored in a data warehouse versus the core system that might have much more expensive licensing requirements.
The ideal reporting solution uses a hybrid of both a data warehouse and real-time connection to the source. This can be accomplished by allowing the user to perform an “on demand” data synchronization or update of the data warehouse in addition to a scheduled update. This feature is useful when the user knows a significant or important change has occurred in the core system and wants this reflected in the data warehouse. These “on demand” updates can perform very efficiently if certain techniques are built in. One example is something we call an “incremental update”, which means the software can quickly find what has changed and therefore has very little work to do.
Vivid Reports CPM will actually generate the data warehouse for you and update and maintain the structure eliminating the burden of having two separate databases while providing all the advantages.