MACPA Case Study

XBRL Case Study MACPA

The Maryland Association of Certified Public Accountants uses multiple MissionKit tools to transform internal accounting financial data to XBRL, then repurpose it in multiple formats for reporting, analysis, and publication – all in-house.

Overview

With close to 10,000 members, the Maryland Association of Certified Public Accountants (MACPA) is often looked to for their expertise around issues relevant to the field of accounting. The US Securities and Exchange Commission’s (SEC) mandate on the submission of financial data in XBRL is one of those issues.

MACPA’s CEO and Executive Director Tom Hood early on recognized the value of XBRL for reducing costs and driving efficiencies in large public companies and wondered if the benefits of XBRL would also extend to smaller, non-profit organizations such as MACPA.

Hood and MACPA’s director of finance Skip Falatko conceived of a project that would increase their working knowledge of XBRL, thereby taking the mystery out of working with it, and leverage MACPA’s financial data in XBRL format to gain efficiencies and improve transparency. Ultimately, they hoped a successful project would showcase XBRL and its benefits to the organization’s members and other nonprofits.

MACPA was able to take advantage of widely available XBRL software tools including the Altova MissionKit and, with the help of a college intern, successfully transform their internal accounting data to XBRL. The project turned MACPA’s financial data into a force for driving efficiencies and accountability – with their data in XBRL format, the association was able to automate previously burdensome data collection and transformation tasks to gain more insight into their financial data.

The Challenge

By order of the SEC, public companies with tradable shares in excess of $5 billion as of June 15, 2009 have been required to submit financial data in interactive XBRL format. This mandate will be extended to encompass almost all public companies with periods ending after June 15, 2011. According to the SEC, the mandate was introduced “… not only to make financial information easier for investors to analyze, but also to assist in automating regulatory filings and business information processing. Interactive data has the potential to increase the speed, accuracy and usability of financial disclosure, and eventually reduce costs.”

Despite this potential, many organizations are concerned about the time and expense that will be required to convert all of their financial data into XBRL, a process that can be further complicated when financial data is housed in multiple systems. These obstacles are felt even more acutely by nonprofits like MACPA and other privately held companies, who in the absence of a mandate are left to wonder whether translating their financial data into XBRL would deliver enough benefits to make up for the cost.

Hood and Falatko set out to prove that with the right tools these obstacles are easily surmounted – and that the efficiencies and greater transparency resulting from the transformation to XBRL are more than worth the effort.

The Solution

XBRL Data Flow

Figure1: XBRL Mapping – the Foundation of a Robust and Efficient Financial Reporting System

Mapping Existing Financial Data to the XBRL Global Ledger Taxonomy

The first step of the project was mapping MACPA’s financial data from the Microsoft Dynamics accounting system to the XBRL Global Ledger (GL) taxonomy.

The XBRL GL taxonomy provides a way to translate transaction data from the general ledger (including accounts and journal entries) as well as payables, receivables, inventory balances, and other assets/non-financial data to a format conducive to reporting. The Global Ledger taxonomy was designed to increase the portability and extensibility of financial information, a goal that the project was designed to support.

MACPA CEO and Executive Director Tom Hood’s son Tom IV, a student at the Perdue School of Business at Salisbury University in Maryland, was pressed into service. Tom IV took on the mapping as a summer project prior to returning to Salisbury to begin his senior year.

Using Altova’s MapForce, a graphical data mapping application with automated code generation capabilities, Hood IV mapped the financial data in Microsoft Dynamics to the XBRL Global Ledger Taxonomy.

Data mapping to Global Ledger Taxonomy

Figure2: Using MapForce, Altova’s graphical mapping application, Hood IV mapped financial data in Microsoft Dynamics to the XBRL General Ledger Taxonomy. Hood IV is working on creating user defined functions to simplify repetitive transformations.

In MapForce, data is mapped from the source to the target by simply dragging connecting lines between fields in the data source (here, Microsoft Dynamics) to the target (the XBRL Global Ledger Taxonomy). MapForce transforms the data instantly and also generates code in Java, C#, or C++, as well as XSLT 1.0, XSLT 2.0, and XQuery) from a menu command.

The output tab in MapForce was used to show the mapping in development and testing. Once the mapping was complete, Hood IV used Java code to create the XBRL GL instance document with MACPA’s financial data.

MACPA was able to use the generated instance document to populate their financial Key Performance Indicator (KPI) system. By significantly reducing the amount of time and effort required to prepare the KPI documentation, they are now able to run the system at more frequent intervals. (Please see Leveraging MACPA’s XBRL Data for More Robust Reporting and Analysis below for more information.)

Extending the GAAP Taxonomy

With the XBRL instance document in hand, Hood IV could turn his attention toward mapping MACPA’s financial data to the US-GAAP (Generally Accepted Accounting Principles) taxonomy.

GAAP are a set of fairly exhaustive standards governing the reporting of transactions and preparation of financial statements. Other recognized standards exist (e.g., International Financial Reporting Systems), but GAAP for now is the most widely used set of accounting standards in the United States.

Although designed to cover the vast majority of financial accounting entries, the GAAP taxonomy is often extended to include entries specific to an organization or industry. Perhaps unsurprisingly, MACPA as a nonprofit association had a number of entries that needed to be added to the standard GAAP taxonomy.

Hood IV used Altova's XMLSpy, an XML editor and development environment with native support for XBRL, to extend the US-GAAP taxonomy to accommodate entries specific to MACPA. XMLSpy's XBRL validation and editing capabilities were key to this part of the project.

In XMLSpy users can select both a base taxonomy (i.e., US-GAAP, IFRS) as well as an industry entry point taxonomy (e.g., Banking and Savings, Insurance, Real Estate), view entry point taxonomies (i.e., Document and Company Information, Schedule of Investments), and non-GAAP entry point taxonomies including Accountants Report, Document and Entity information, Management Report, and SEC Certification when building or extending a taxonomy schema.

In the absence of a dedicated US-GAAP taxonomy for nonprofit organizations, Hood IV opted to extend the Commercial and Industrial industry entry point taxonomy because it offers the most parallels to nonprofit reporting standards.

Extending the taxonomy revolved around adding entries specific to a nonprofit professional association. In one instance, he had to account for revenue sources related to two of MACPA’s lines of business. The association’s general ledger includes accounts receivables and deferred revenue for two revenue sources not reflected in the standard US-GAAP taxonomy. These are membership dues and courses offered by the association. Rather than creating four individual elements, however, Hood IV created the domains “Dues” and “Courses” under the Products and Services axis. He added the label links “Income and Expenses Related to Dues/Courses” to each so that these entries would be identifiable to the human eye.

This allows each of these revenue sources to exist as an individual service line to which income, expenses etc., can be assigned. It also builds flexibility into the taxonomy – if additional expenses associated with dues were to be realized, for example, they could be applied to that line of business.

Extending GAAP Taxonomy

Figure 3: Extension of the US-GAAP taxonomy in XMLSpy

Another issue Hood IV resolved in XMLSpy is the swapping out of SEC-specific references in each element for references that correspond to accounting principles governing nonprofits.

Mapping the GL Taxonomy to the Extended GAAP Taxonomy

Once the GAAP taxonomy had been extended to accommodate MACPA-specific entries, Hood IV was able to map the Global Ledger Taxonomy to the extended GAAP taxonomy via MapForce.

As was the case when he mapped MACPA’s financial data to the XBRL Global Ledger Taxonomy, Hood IV took advantage of MapForce’s data conversion capabilities to use the same element to meet different information needs.

For example, he created two identification numbers for the taxonomy based on a single bank account number from the XBRL instance document. In this case, the bank account number with hyphens is mapped to the accountMainID element in the taxonomy while the bank account number stripped of all separators is passed to the taxonomy and used as a parameter to group data.

Hood IV also created new elements in the extended taxonomy based on calculations performed on existing source data right within MapForce. He added “ending balances” as another entryHeader tuple, which already included “beginning balances” and “period balances,” using the MapForce “duplicate input” command. He then wrote a SQL statement that grouped data together by account number and summed the beginning and period balances to result in “ending balances.”

Mapping Global Ledger Taxonomy to GAAP

Figure 4: Adding a new element to the Global Ledger mapping via MapForce – ending balances join beginning and period balances via an additional entryHeader element under accountingEntries.

Once all elements in the Global Ledger Taxonomy had been mapped to the extended GAAP taxonomy, Hood IV was able to generate code instantly – MapForce automatically generates royalty-free Java, C#, and C++ code from a menu command. He then compiled the Java code through a command line and created batch files that will generate XBRL files.

Leveraging MACPA’s XBRL Data for More Robust Reporting and Analysis

The XBRL files generated from MapForce can be the foundation of a number of reporting functions. MACPA CEO Tom Hood, for example, has plans to publish the association’s audited financial statements on their website using XBRL. This will eliminate the costly reconciliation process and reduce the risk of errors while increasing transparency and the timeliness of the data.

MACPA Director of Finance, Skip Falatko also has plans to automate heretofore onerous tax filing tasks by mapping the association’s financial data to the 990 tax return. With almost 1.5 million exempt organizations in the US filing hundreds of thousands of Form 990s each year, the efficiencies afforded by the use of XBRL data for this purpose alone make it a technology worthy of serious consideration.

Beyond efficient tax reporting, XBRL has the potential to drive greater accountability, transparency, and comparability within the non-profit industry. For example, if you want to view an organization’s Form 990, you can look it up on a Web site such as guidestar.org and view it in a PDF document. Hood and Falatko envision a future where you will grab XBRL tagged data from an organization or even from multiple organizations and compare fundraising costs, program expenditures, net assets, etc., quickly and easily.

Furthermore, as more associations adopt XBRL it will be possible to share financial ratios, statistics and analysis across organizations to drive industry analysis and best practices.

In addition to helping to make the process of complying with tax and other public reporting requirements much easier, more transparent, and more accurate, the association’s XBRL data has been used to drive informed decision making within the association itself.

XBRL instance files generated from the Microsoft Dynamics-XBRL Global Taxonomy mapping are used to populate MACPA’s KPI system, the Financial Scoreboard™. The Financial Scoreboard is a tool developed by the Performance Management Institute and other financial professionals that presents financial statements in easily digestible templates.

Designed to help organizations visualize their financial data to improve performance, the Financial Scoreboard is a series of Excel worksheets that end users populate with information from their general ledger, profit and loss, and other statements. Generating the required information, however, is a fairly labor intensive, manual process as the information comes from various sources. For this reason MACPA performs Financial Scoreboard analysis quarterly but generally long after financial statements are produced.

As he did with the GAAP extension taxonomy, Hood IV used MapForce to map the Global Ledger instance document to Excel sheets previously populated manually. He then compiled the Java code generated by MapForce through a command line and created batch files that when executed generate Excel files for use in the Financial Scoreboard.

Automating the population of the Excel sheets for the Financial Scoreboard will allow MACPA to run Scoreboard analytics at more regular intervals rather than just quarterly as the association is doing now. With the XBRL GL to Excel mapping Hood IV created in MapForce, MACPA can perform timely, monthly reviews of their financial data.

MACPA future plans include extending its work on KPIs by linking other key systems (e.g., the association management system (AMS) and the sales force automation system) to allow for non-financial KPI links and presentation.

The Results

The MACPA project is significant not only for the efficiencies, enhanced decision making capabilities, and cost savings afforded the association. In addition to contributing to the body of evidence supporting XBRL as the lingua franca of financial and business reporting and data exchange, the MACPA project offers a much needed model for nonprofits seeking to leverage the XBRL Global Ledger taxonomy – until now there have been few if any examples of the use of the GL taxonomy by nonprofits.

The project is also paying off for the international business community as well. Salisbury professors George Summers and Kathie Wright, intrigued by the promise held by XBRL, had been studying the XBRL Global Ledger. Under their direction, six Perdue business students are in the process of developing XBRL Global Taxonomy maps for the Convergence Assistant, an application that translates financial statements into various international accounting standards. The MACPA project not only served as a model but Tom Hood IV’s experience has been put to good use by the Perdue XBRL team – in fact he was able to turn the mapping project into an independent study for college credit.

Tom Hood IV doesn’t plan on stopping his XBRL activities there, however. He has started working on a financial dashboard for MACPA that will bring together all functions around compiling, analyzing, and reporting XBRL data. His vision includes generating XBRL instance documents, creating Excel files, and automatically populating the Financial Scoreboard among other activities from a single user interface.

Conclusion

The future of XBRL has been secured by the SEC mandate requiring the submission of financial data in XBRL format. However the real value of XBRL for many businesses, even those not bound by the mandate, will be its power to drive more informed decision making and the ability to disseminate financial information to a broader range of stakeholders.

Using Altova tools to design a comprehensive system that uses XBRL data for a number of different reporting functions, both internal and external, MACPA offers a model for public and private businesses of any size seeking to leverage XBRL.

After all, MACPA was able to convert all of their financial data to XBRL – and then use it for both internal and external applications that deliver value to the organization – efficiently, cost effectively, and in-house. As Tom Hood notes, “If we can implement XBRL as a state-based non-profit association working with a college intern, you can do it too – implementing XBRL in-house is easy with the right tools.”

Want to know more about XBRL? Check out our online XBRL resources.

Find out how you can use the XBRL tools in the MissionKit to leverage XBRL data in your own organization.