Posted by Peder Enhorning
November 6, 2016
Little has changed in the collection of financial data, and reporting on it is just as complicated as ever. We hear this from clients all the time.
I am constantly frustrated with the reporting obtained from our own accounting and finance system. It lacks useful reports, and I am never (rarely) able to get answers to my questions. This blog will pick on Sage, but all accounting systems have similar problems with reporting. Most of it’s due to how Sage records and stores data. Fortunately, there are great options for creating much more robust reporting. We particularly like using Tableau to visualize data because it offers the user the ability to interact with the data in a user-friendly interface. It empowers users to ask their own questions. See Accounting Analysis Made Easier with Tableau.
We get lots of clients wanting to create management dashboards in Tableau from financial data, but its inherent structure and data storage can present challenges. This post will list some of the difficulties and what to look out for.
The main problem with Sage, and any accounting system, is the highly normalized data structure. What this essentially means is that the data is broken down into very small pieces and spread across numerous tables inside the database. It’s not possible to just go into one place to grab a number. One number usually requires visiting 6-8 different tables in the right sequence and with the right criteria. If you don’t relate the data correctly, or include the proper criteria you’ll get a number… but it will be the wrong number.
For example, one of our Sage clients had 700+ tables with over 17,000 fields. We had to do some deep database investigation to figure out which tables and fields produce the correct numbers and how the tables and data points related to each other. But once that’s done, the reporting can be excellent.
In addition to the fragmented data there is the issue of the general structure of financial data. Accounting data is very hierarchical and must be computed in the correct sequence to arrive at the correct answer. Dashboards must follow the standard computational sequence to ensure the right numbers are generated. Unfortunately, the hierarchical, tabular structure of financial data is one of the slowest ways to impart information to the dashboard user and organization in general. It’s difficult to quickly understand. Instead you have to look at each row and value.
For example, EBITDA is calculated after net revenue and involves adding a few expenses back in. Similarly, you compute net revenue after gross revenue, but before net income. The sequence of the computations is mandatory in order to comply with GAAP.
Dashboards that present financial data using a visual format work much better for rapid interpretation of the information. However, converting accounting data to visual information can be quite complex and may require restructuring the data before bringing it into Tableau while retaining the correct computational sequencing. But with experience, you can get this done right.
Another problem is that the same number is likely replicated in numerous places throughout Sage for different purposes. For example, the exact same transaction may affect multiple accounts, and in one account it “adds” while in another it “subtracts” (i.e., debits versus credits). If you don’t properly aggregate the number as a debit or credit as you sum across accounts, you will come up with a total that doesn’t match with the expected result.
Yet another issue is how the accounts may be used. Some accounts are used in a balance sheet or income statement, while others are used in a statement of equity. When including an account in the balance sheet, you often need to refer to an account summary table that pre-calculates the balance instead of summing up individual transaction records. However, the accounts may behave and be used differently in sub-totals in various financial reports. It is imperative to use the Sage accounts correctly as different financial reports are computed to construct the visuals.
The “ways” in which accounts can be used for financial reporting are extremely complex and require a thorough understanding of financial reporting, databases, and pulling data in and restructuring it for use in a data visualization product like Tableau.
We had a complicated situation where accounts could be used in multiple financial reports, so we created an account mapping table that allowed Tableau to include or exclude accounts in various dashboards. The user could then pick a financial report, such as “balance sheet” or “income statement”, and Tableau would dynamically pull in the accounts and place them in the appropriate section of the financial report (e.g., in a balance sheet the account is an asset, a liability, or an equity account). Doing this meant the dashboard user didn’t have to know which accounts belong in various sections across various financial reports.
Accounting is a living, breathing system and filled with “exceptions”. The chart of accounts rarely stays static for long. This means accounts change in how they are used. When a business is started, the IT equipment account may have been defined as a capital expenditure (i.e., CAPEX), but over time IT equipment was replaced by cloud-based services such as Amazon Web Services (AWS), so the account was changed to an operational expenditure (i.e., OPEX). For the first few years it would be treated very differently than after its classification was changed from CAPEX to OPEX. Only a very robust, time-based formula can correctly reallocate accounts to different financial report sections depending on the time period of transactions to the account.
It is essential that the dashboard designer identify these exceptions and include them in the dashboard calculations. If the exceptions are not identified, the dashboards will never reconcile with the accounting system reports.
Different companies handle accounting differently depending on their structure, industry, service, product, etc. The structure of standard financial reports will differ from one Sage installation to another. Some financial ratios or reporting values are used in different situations and for different reporting purposes. Say you need to include “income” in a report. Should “income” be the gross margin, EBIT (i.e., operating income), EBIDTA, or net income?
Companies have different reporting requirements depending on how the information will be used and by whom. Without properly including account balances, transactions, and aligning them with the various financial ratios, reports, and sections, it leads to incorrect conclusions or confusion amongst the organization. We help clients map requirements and data usage to the correct Sage sources, so the organization can make decisions on the most appropriate data.
Comprehensive analysis of financial performance isn’t limited to data in Sage. It’s often important to bring data together from manufacturing, CRM and HR systems and join it with financial data. But Sage is notoriously poor at facilitating that.
Without in-depth technical expertise in both financial systems and data Integration tools, end-users are left to export data from their financial and accounting systems into data silos and then create complex column and row lookup functions to join disparate data. End-users are left with some reports generated in Sage, some in Excel, and still others in different tools.
Tableau offers the ability for non-technical users to connect directly to Sage and other data sources, and to combine information in a single dashboard for better insight into business performance. Users with limited technical skills are able to join data from different sources to improve their reports.
Financial systems contain the most critical data in any organization, but it’s often locked away and difficult to analyze. And because other data sources can’t be integrated, analysis is siloed. With Tableau, you can combine your financial and other corporate data in one tool and share multiple reports and dashboards from one central system. It’s easier to create and delivers better insight.
Check out our risk-free Tableau Guided Evaluation, so you can see your data in a dashboard before you buy.