Articles

XBRL Ends Spreadsheet Hell

"The goals were timely and accurate data, stronger internal controls, reduced costs, a standardized system of seamless data exchange, business processes and data elements. XBRL met all of those goals."

by / May 1, 2009

"The goals were timely and accurate data, stronger internal controls, reduced costs, a standardized system of seamless data exchange, business processes and data elements. XBRL met all of those goals." -- Nevada Controller Kim Wallin (pictured)

States and localities have in recent years been engaged in developing financial Web sites, transparency portals, campaign finance disclosure and online checkbooks to open the process of government spending to a very interested and sometimes skeptical public. Recently, with the flood of federal stimulus money, that development has accelerated.

While a commitment to openness and transparency is commendable, tracking grants and other financial information -- across multiple agencies and departments running different software on incompatible systems -- is a tough job for jurisdictions, and the resulting information may be too late for real-time decision making, and too complex for anyone but a professional auditor to understand. Increasingly, XBRL is being heralded as a solution.

What is XBRL?

XBRL stands for eXtensible Business Reporting Language -- an XML-based open standard for financial reporting. "Instead of treating financial information as a block of text -- as in a standard Internet page or a printed document -- XBRL provides an identifying tag for each individual item of data," says an explanation on the XBRL International Web site. "This is computer readable. For example, company net profit has its own unique tag. In addition, the tags provide other information about the item, such as whether it is a monetary item, percentage or fraction, etc.

"XBRL can show how items are related to one another," continues the site. "It can thus represent how they are calculated. It can also identify whether they fall into particular groupings for organizational or presentational purposes. Most importantly, XBRL is easily extensible, so ... organizations can adapt it to meet a variety of special requirements."

"The introduction of XBRL tags enables automated processing of business information by computer software, cutting out laborious and costly processes of manual re-entry and comparison. Computers can treat XBRL data 'intelligently' ... they can recognize the information in an XBRL document, select it, analyze it, store it, exchange it with other computers and present it automatically in a variety of ways for users. XBRL greatly increases the speed of handling of financial data, reduces the chance of error and permits automatic checking of information."

So with XBRL, the world of finance has agreed on a list of data definitions or tags that can be applied to financial information. The CFO, the CIO, the CEO, the CFA (certified financial analyst) and the CPA, the auditor, have all agreed to the same standard definition of financial information. Which means that if financial items can be tagged in XBRL, they can be moved across all software formats and can be looked at and analyzed without re-keying.

The XBRL business reporting standard is now mandated or used in regulatory filing programs in many of the world's leading capital markets, including Australia, Canada, China, France, Germany, India, Japan, Korea, Netherlands, Singapore, and the United Kingdom. The U.S. Securities and Exchange Commission now requires that large publicly held companies adopt the XBRL standard.

The Nevada Experience

Nevada Controller Kim Wallin has been wrestling with the problems of financial reporting, grant tracking and making sense of financial information for many years, and has become an advocate of XBRL -- She talked to Government Technology recently about several XBRL pilot projects in Nevada state government.

The Nevada Department of Agriculture had more than 60 different grants for which information was gathered and maintained on a variety of spreadsheets and Word documents. Each month a detailed report was prepared

in Excel for grant managers and owners.

To prepare the reports, said Wallin, staff would manually take general ledger information from the financial data warehouse and enter it into a spreadsheet. They would download a  CSV file and then select and paste only the relevant information into the report. That information had to be manually input along with grant reimbursements, footnotes and other information.

The process was very time-consuming -- requiring about two weeks for each grant-reporting cycle. In addition, all the cutting and pasting could introduce significant errors. And because managers didn't have real-time information on how much was spent and where -- most of the time, they were operating without the data that would be most useful.

"With governments you tend to have these old legacy systems that can't talk to one another," explained Wallin, "and because of that I attended an XBRL conference in 2006 and began talking about where it could go in government. At the time I thought I wanted to use it to track contracts and projects. So if you tag the data at the vendor level, then you could keep track of how the project was progressing, how much you were spending, and more detailed information of what was going on."

In 2007, Wallin begin considering XBRL as a solution to grant reporting, and when the Department of Agriculture asked for help with their reporting, Deloitte was brought in and two of Agriculture's grants were selected for the XBRL pilot.

"The goals were timely and accurate data," said Wallin, "stronger internal controls, reduced costs, a standardized system of seamless data exchange, business processes and data elements. XBRL met all of those goals."

Results

"Using XBRL," said Wallin, "the grants manager now enters the initial grant information into what looks like a spreadsheet or a template. They get a grant from the federal government and it tells them that 'you are going to get this much money, and you need to spend it in salaries, supplies and the different areas.' So they enter that initial information on this template that has X-forms underneath it. X-forms can actually facilitate automating and improving the source data capture and the validation. The X-forms data is tagged at the source and then from there, it is housed in a grants repository, which has our grant instance documents, our GL (General Ledger) instance documents, our grant taxonomies, and also our GL taxonomies."

Wallin explained that a key piece of this solution is a GL adapter, that draws data from the state's data warehouse and transforms that data into XBRL GL instance documents. "So now we've got data coming from our data warehouse, so this person no longer has to manually input it into a spreadsheet," she said. "We're taking data from our general ledger into the grant repository that has our taxonomy for grants and GL and we're using XBRL GL for our transactions. Because of that we now have standardized reporting using grant taxonomies for our Department of Agriculture. They still are using the CSV file, but the CSV file goes through the GL adapter and right now it's not an automated process -- we're in the process of downloading that information on a nightly or weekly basis."

The time required to do grant reporting, said Wallin, went from two weeks down to only one day, and once they automate the CSV file, it will take around one hour.

"With the removal of manual manipulation of the data, we have strengthened our internal controls," she said. "A key to using XBRL is SBR standard business reporting. With XBRL you have to sit down and come up with a standard in your reporting. That's what we've done in

the Department of Agriculture, and we are in the process of expanding that and we are sitting down with other state agencies and looking at what are the common data elements that each agency has in the grant reporting area, and then we are identifying those common data elements and then those items that are not common."

Uncommon data elements, said Wallin, can be unique because of laws or regulations, or just because the manager or department has always done things a certain way. So Wallin is meeting to try to work out acceptable common data elements.

Wallin thinks that XBRL would be perfect for ARRA (stimulus) funding transparency as well. "The data gets tagged," she explained, "and sent into OMB's site. Each agency is asking for a certain type of reporting, and OMB wants reporting and congressional committees are going to be having some kind of reporting. Well, if everybody reported to OBM using XBRL, these agencies then can pull their reports and take the data and put it into whatever report they want it to be in, without having each state go and do a separate report for Department of Agriculture, and OBM, and the congressional committee that oversees agriculture. It would be a seamless way for people to get the reporting that they need."

IT-Related Issues

Wallin explained that because XBRL is relatively unknown in the U.S., that IT departments often have concerns about it. Security, for example.

"XBRL -- as an information standard -- has no built-in security," said Wallin, "so the IT system processing the data is always responsible for the security. The XBRL standard ensures that the IT systems can build the same security measures that they have been building into the data. XBRL data can be digitally signed, encrypted, transferred and secured similar to any other data formats. XBRL is like an additional layer over the data in the system. It makes computer programs intelligent, they can read the data relationships and rules from electronic documents -- the taxonomy. Because of the instance documents," she continued, "it enables auditors and those checking for compliance and accuracy to ensure the data has not been manipulated or corrupted.

"Once you have these systems built," she said, "you can process any XBRL documents. They work on other data formats -- state governments have all these disparate systems, you can get your data out of these different systems and have it talk to one another without having to develop a whole new ERP system. It's scalable and can accommodate changes in the rules -- your taxonomy -- with minimal modifications. SOA and XBRL combine and are complimentary. XBRL-based systems are based on the SOA and information that is transferred and processed is in XBRL format .

Business Portal

Nevada is looking at developing a business portal using SOA, and Wallin thinks XBRL would be an excellent enhancement. Currently new businesses need to go to the Secretary of State, then to the Department of Taxation, then Unemployment, DMV to register vehicles, city, county, etc., to obtain all the permits, licenses and so on to do business in the state. "And every place you go," said Wallin, "you have to fill out a form with your name, address, ID number -- basically the same information at each location. If you use XBRL in a business portal, you would fill out name, address, ID number, etc. and then the data can go out and populate the other systems. If you do it without the XBRL, systems don't talk to one another."

Debt Collection: Spreadsheet Hell

Wallin said that the Controller's Office is also responsible for collecting debts owed to the state. "Agencies send in their debts to us on a spreadsheet -- name, address, tax ID number and various other

information about what is owed. Often, they add columns to the spreadsheets, so staff have to cut and paste by spreadsheet column, which can create errors and other problems. So in debt collection, we have 71 spreadsheets with 18 tabs per spreadsheet. Spreadsheet hell. It's a very cumbersome process, there's no centralized database, and when we do debt offset, we check our vendor files to see if any of those vendors turned over to us for collection are vendors to the state, so that's a manual process. My staff cut and paste these data into another spreadsheet to send to our outside debt collectors, and then they report back to us what they've collected and we cut and paste back into spreadsheets and then cut and paste into reports to the agencies what we've collected."

But Wallin has a plan. "We're entering into a pilot with IRIS business services to use XBRL for this. Agencies will have a spreadsheet, with XBRL under it. The agencies won't notice, it's a smart spreadsheet, Web enabled. They will enter their data, because we are using XBRL, information will be validated, they can't send us data that doesn't add up. Then it goes into the debt collection repository and taxonomy, and it will have our instance documents there. Once in the database, staff will be able to go and pull whatever reports we need, no more spreadsheets. Data goes back into the debt collection repository."

Resources

Wallin says that as they develop taxonomies they will register them with XBRL international. "So if another state wanted to do something in debt collection or grant reporting," said Wallin, "they could go there and see what has already been developed."

Wayne Hanson Senior Executive Editor, Center For Digital Government