P R E V I E W

So you think that you can do some of the field examination work faster and maybe even do more in the office?  You believe in the future, that technology will win in the long run, that Thomas Malthus was wrong.  

This white paper is on the topic of DATA mining and data analysis, not general ABL Auditing (we cover that in one of our other White Papers).  We've read a great deal of the available the literature and articles on this topic and are astonished by the casual and seemingly effortless use that they portray.  However, after 18 years of computer based filed examinations and 18 months of research, training, fidgeting, testing, sometimes failing and sometimes succeeding, we think that we've got the good and bad facts about DATA mining and analysis.

We want your feedback and input, just e-mail us.  We promise to keep all names and all companies strictly confidential.  This white paper is a community resource that will be constantly revised as input comes in from people like you.  Thanks for your time.

O V E R V I E W  O F   R E S E A R C H

Too much data, not enough time, not enough auditors, too much fraud, reams of paper, just not enough time.  So you have dreams of getting all of the client data with downloads and perhaps scrutinizing the data in the office.  Well, we have some bad news, it's not always that easy and it doesn't always go that smoothly.  Furthermore, it doesn't apply to every loan, making it a partial solution at best.  Our research and hands-on experience also found a HUGE gap in technical skills, client cooperation and the tools needed to pull this off.  

This white paper is derived from the following:

• Clear Choice Seminars, Inc. invested 18 months of time researching this issue for the development of a new course on the subject.
• The research included  working with the existing crop of tools and prior advanced skills in Lotus and Excel.
• Taking over 100 hours of courses on DATA mining, including intermediate and advanced Excel courses.
• It included detailed discussions and examples of data with a guru level programmers in Visual Basic, Power Builder, SQL Server, Oracle and other languages.
• It included reading all the literature that was available.
• The research includes my existing 18+ years of ABL field examination experience (over 500+ ABL examinations, all computer based).
• Being co-owner of FinSoft, LLC, maker of AssetWriter field examination software and AssetReader ABL aging analysis software helped too.
• Being the author and engineer of the course materials used to write Data Analysis Techniques for Auditors, the most advanced ABL audit course in the world helped too.
• Our management includes the author of the ABL-Help file.  Considered by industry insiders to be the definitive guide to ABL ineligible calculations and the foundation of ABL audit knowledge.
• Feedback from people like you, from hundreds of lenders and students of Clear Choice Seminars also plays a part in the direction and depth of the continuing research.
• This continuing research has made AssetReader the easiest and most complete tool available.

As a final declaration of independence in this matter I also note in this white paper that our own DATA analysis software is subject to some of the limitations noted in this white paper. There is some hope, so read on.

A   B R I E F   H I S T O R Y

Until the advent of the spreadsheet with VisiCalc in 1980, it was necessary to do most calculations by hand and only certain large corporations had computers for number analysis. Even then, the information system staffing had limited time and limited tools to pull-out summary numbers. Auditors were sometimes able to get reports through MIS.

With the PC came new programs that allowed programmers to design friendlier query tools and databases that ran on the PC.  Eventually, programmers created end-user programs, the graphical interface emerged and the power tools transferred to the masses... sort of.

As time has moved forward, advanced features were integrated into spreadsheets, stand-alone query tools and in some cases complex software programs that allow millions of records to be searched, summarized and tested.  In some cases, the accounting software has improved too. 

D A T A   M I N I N G   O R   D A T A   A N A L Y S I S ?

Overview

In brief, getting data electronically and performing some degree of analysis on it.  It can be as simple as footing a report with the aid of a computer or recomputing extended cost on inventory.  It can be as complex as using the computer to looking for every missing check or invoice for an entire year.  What you can do with the data depends on the tools used, user skills, the data itself and of course the testing and Examination scope.

Mining vs. Analysis

In the above preview, we noted several things that can be done with data mining.  If you ask ten people about data mining, you'll get twelve opinions.  However, there are two distinctly different approaches as these examples illustrate:

The Case for Data Analysis
For ABL purposes our needs are somewhat simple.  It is most beneficial to look at saving time in key compilation areas such as:

• Analysis of thick agings

• Analysis of thick sales and cash journals looking for credit memos, debit memos, etc.

• Summarizing debit and credit postings to inventory

• Summarizing debit and credit postings to payables

• Recomputing / re-extending prices in a perpetual report

• Calculating turnover of every inventory item (may require combining two reports)

• Looking for skipped invoice sequences

• Looking for duplicate invoices

Depending on the size and nature of the ledgers, this task can vary in scope and may be simple based on summary numbers provided by the reports.  In other cases, these tasks require an investment in compilation time that can vary from 1 hour to several days.  Larger deals and a larger number of transactions (i.e., a distributor with small invoice sizes) may require more effort.  Larger corporations tend to a staff of MIS personnel that can help with the queries.

The rest of the field examination report will need to be completed, inclusive of trends, insurance analysis, interviews, test counts, writeup, consolidations, multiple examiner combinations, etc.  There are other pitfalls to consider (see below).

The Case for Data Mining
I hate to use the "F" word, but fraud auditing may require the use of these tools.  When there is reason to expect willful misconduct or fraud, it makes more sense to use this type of approach.  Unfortunately, due to the reluctance or refusal of Borrower Debtor's to provide the information and the lack of thick reports and a lack of user skills and a lack of easy to use tools; this form of analysis is available only some of the time.  Over time, the downloaded reports can be saved and compared to current reports for anomalies.

Better, But Not a Panacea
Due to certain concerns about this white paper being posted here (onto the web), we do not discuss individual examples of willful misconduct or fraud.  However, you should be aware that there are hundreds of things that the computer analysis will miss due to the need for trained eyes to spot relationships, anomalies and minor variances that lead to big audit trail issues.  We would like to cite for your benefit, the possibility of improper accounting classification or other accounting tricks / omissions that would prevent items from appearing in a computer inquiry.

U N D E R S T A N D   T H I S   F I R S T

After years of doing demos, training on Data and more, it is somewhat surprising to hear the misconceptions about data tools and the entire process, which is outlined in the section below.  

DATA Vocabulary 101

"Parsing" means to split things into columns.  This can include, for example, getting a customer name and customer number that is above the invoices of an aging to appear next to each invoice for that customer.  Parsing tools generally slice the columns into raw data.  Most of these parsing tools have some mathematical features, but not the ease of use of say Excel for formulas.  

"Processing" of data is the application of math, business rules and code logic to test, summarize, analyze, sort and report things about the data.  For example, does the aging foot across and down for each customer and in total?  This usually requires tedious setup, programming logic application and time.  Key employees may gain skills in this area over time, but their departure can be both costly and non-productive.  In general, this "processing" phase of dealing with data is the most complex and employee specific with respect to skills. 

The Pre-Process

SLOW DOWN!  This is a bit tricky.  You will need to educate your Borrower to learn to get you data.  You'll need to educate your staff about getting data and asking the right questions.  You'll need to assess and test ways to get data from their system to your system using email, disks and other media.  

Once you look at the reports, you might find garbage and useless data in the reports.  You may find that some reports or report formats are useless.  In many cases, the skills of an auditor or programmer that understand data will be needed, if these skills are available at all.  

Once data is in hand and it appears to be usable, the process of converting the data to electronic analysis can begin.  But do you have the skills to get this far before you touch the electronic tools to assess the data?  We wrote the DATA course for this very reason.

The Process 101

How do you read-in a file?  An overview:

In the above chart, it is assumed that parsing (splitting into columns) is needed.  Note that parsing (or using a parsing tool) is only one of the steps!  In some cases, the data is already parsed into columns that can be read by the analysis program (this, typically from a programmer's data file or accounting software export routines that save to ASCII comma, tab delimited or spreadsheet formats).  Some systems integrate the parsing into the product (custom written such as AssetReader or based on other parsing engines).  Other systems start with the parsing and then require separate steps or the launch of another program.  

In all cases, there are steps to follow to go from the accounting system to a file that is in your hands to a parsed file to the analysis software.  Therefore, the "analysis" portion is starting with data that is based on results from the parsing software or in some cases, an already parsed file.  Products like Excel and Monarch have parsing routines, although Monarch is a far better parsing tool than Excel (particularly for agings or large text files).  AssetReader an expert parsing routine written entirely by FinSoft, LLC and the approach makes this analysis available to even beginners.  Again, the general parsing tools are not going to know that ABL folks do cross-aging, trap credits over 90 days, group customers, remove international accounts, etc.  That's why we wrote AssetReader to begin with.

Analysis tools can be split into three basic camps (examples are just a sampling):

Simple, Inexpensive and Widely Used:
Excel - Spreadsheet capable of 65,536 records - Uses accessible and easy techniques
Monarch - Capable of @ 4,000,000+ records - Used to parse Fixed Length records.  Screens are a bit confusing and frustration is common with this product.  After being parsed into columns the data must be programmed or exported to another analysis tool.   
Access - Capable of @ 4,000,000+ records - Used to read-in and parse Fixed Length or delimited records; requires some programming and training skills beyond basic auditing
Other - Several other tools are available for data parsing

Fraud Analysis and Large Scale Analysis:
ACL - Advanced query tool used for fraud analysis and data integrity testing.  Requires training and practice plus setup to ABL needs
IDEA - Advanced query tool used for fraud analysis and data integrity testing.  Requires training and practice plus setup to ABL needs
Other - Several other tools are available for data summarizing and exception testing.  All require training and practice plus setup to ABL needs.  Most rely on Monarch to do the parsing portion.

ABL Industry Specific:
AssetReader - AR aging analysis and ineligibles, with current modifications slated to work on sale journals, cash journals and inventory reports.  Capable of @ 4,000,000+ records.  The only tool that uses advanced expert systems to assist in the import of agings and other raw data.  The easiest to use tool of its kind.  Analysis of agings is superior to any other tool on the market and future releases will use the same expert parsing techniques for other reports.
AssetWriter - ABL Field Examination software designed for field examination data gathering, consolidations and report writing.  Not a data mining tool, but data from AssetReader is posted into AssetWriter.  This combination is the most complete and state-of-the-art solution available.
Other - A wide range of tools based on Monarch or other general purpose tools.  Users note that these tools are difficult to master and that the ineligible calculations are often limited (i.e., only a handful of ineligibles) and sometimes inflexible (can't group customers or credits over 90).  Except for AssetReader, the integration to the auditing package is missing or impractical, making company-wide adoption impractical.  

F R O N T   E N D   O R   B A C K   E N D

This part of the white paper is one of philosophy and approach.  Where can we save time?  Do we try to get the numbers faster (sometimes) or get the report completed faster and with more accuracy all of the time?  What do we get?

Front End
This white paper is largely about the front end Data Analysis approach of getting the numbers electronically to reduce compilation time and improve accuracy.  In the case of Data Analysis, the approach is to avoid willful misconduct and fraud exposure before and after the deal is booked.  Both Data Mining and Data Analysis are therefore noble and worthwhile ideas.  Unfortunately, it is not that easy to do consistently and there are other snags to consider.

Our hands-on experience and research show that this is a solution that will work @10%-70% of the time and save only 10%-30% of the workload in those cases.  There are some success stories that include 80% or more of the AR agings and some higher rates for other data.  One lender noted 70% of their borrowers providing some data, but there are three catches-- (i) they have a dedicated staff that does the setup and ongoing analysis and (ii) they are a high risk lender that can demand it and (iii) they are saving only a bit more than 10% in time overall (see below).  Remember, the time savings are not as good on the first exam (setup time), not every borrower can do this and some examiners will never figure this stuff out, even with training.

What's a fraud worth?  Well, if most people are honest and the risk factors are low and we can only get this to work some of the time, then the chances of finding a fraud are increased only a bit.  Borrowers could circumvent some of this download stuff (don't lend to that type of person to begin with).  Furthermore, there is usually something in the results of the numbers that would make you look at the variances or strange relationships more closely (this is taught in Intermediate ABL Auditing - the #1 ABL Audit course).  It can save time, it can find problems, but only some of the time.  There is additional time that you will be investing, not saving (see below). 

Back End
This approach is not the focus of this white paper, but it is illustrated here to show yet another alternative.  In the above Front-End discussion we're talking about mathematical accuracy, fraud prevention and speed from downloading electronically.  Unfortunately, downloaded and scrubbed data can easily miss reasonableness, number relationships and overall business sense.  

Why not make the report preparation process faster, more accurate, more detailed and easier to accomplish all of the time (not some of the time)?  Given the degree of complexity to setup an examination, complete a writeup, consolidate divisions, combine multiple examiners, etc., these time savings are a minimum of 10% on every exam and average @ 30% on every exam.  For example, one of our software products (AssetWriter) includes the ability to consolidate 2 or 2,000 divisions with 4 mouse clicks, import from another examiner in under 2 minutes and print consolidated workpapers with point and click ease.  Setup time is under 2 minutes per division.  The data analysis from this level of programming is at the expert level, giving even new examiners the ability to get meaningful analysis from the data.  

In the end, the report is not for the examiner, not for the audit manager and not for the software company; it is for the credit decision makers.  Mathematical accuracy and fraud detection are noble causes, and downloads can save time, but "What does it all mean?"  Given the salary levels and shortage of examiners, this solution works all of the time and provides the greatest and most consistent time savings while providing expert level analysis.

We have found that people spend days in the office on consolidations, multiple examiner combinations and detailed analysis, even after downloading some or most of the data.  Data analysis users have noted that while they may have saved 4-8 hours in the field on some of the exams, the office analysis and writeup are still chaos without tools such as AssetWriter.  

Combine both approaches.  Now AssetReader feeds the data directly into AssetWriter for the most consolidated and widely used approach in the industry for examiners, officers, and back-office personnel.

A   S O L U T I O N   I N   S E A R C H   O F   A   P R O B L E M ?

While the overall idea sounds solid, there are many factors to consider in the wide-scale adoption of data mining for ABL.  Consider the following:

The Human Factors:

Some time is lost from getting the data analysis template setup, but it may be recovered when a prior setup is reused monthly or on the next field examination.  Unfortunately things can be difficult from:
 - The reluctance or refusal of some Borrower Debtor's to provide the information
 - A lack of thick reports
 - A lack of user skills
 - A lack of easy to use tools

The Problem Factors:

1. You can't log onto purged computer data, it's gone.

2. You can't log onto manual ledgers.

3. Small agings, sales journals and cash journals are not worth the time to log onto (especially with monthly reporting).

4. Larger corporations have MIS departments or report writing staff that can generate query reports and save those report formats for future reuse.

5. Tools like Excel allow data to be sorted and grouped without the complexity or learning curve of other products.  We wrote the DATA course because this works so well.

6. Medium sized companies have some reporting and exporting abilities and this can allow simple tools like Monarch and Excel to work fine and quickly.

7. Members of the ABL staff may not possess the skills to setup the more complex programs.  In addition, the skills are quickly lost if not used or the person switches positions or leaves.  Our experience shows that many staff members lack the skills to use "IF" statements, Boolean logic and other intermediate skills needed to make this work.  There are cognitive limits for some.  Again, we wrote the DATA course to help.

8. Corporate banking clients don't like being treated like thieves and the AO may have difficulty selling these practices to the borrowers.  Banking vs. Commercial Lending.

9. Many Borrower/Debtors are reluctant to provide data electronically

10. Many Borrower/Debtors don't know how to export data to files

11. Many Borrower/Debtors can't export to data files

12. Many Lender/Creditors don't know how to get the data from the Borrower/Debtor

13. Many Lender/Creditors don't know how to move large files off of a Borrower/Debtor's system.

14. We will not discuss fraud analysis techniques here, but it is possible that you won't find all of the problems by looking for them in reports.  Example: Certain ineligibles may not be identified in the AP aging.

15. What does it all mean?  How does the data summarize and show the overall picture, problem areas and success areas?  Downloads don't provide that.

16. You may download bad or misleading data and spend time chasing shadows (see below).

The Conditions for Best Use:
The above list has been abbreviated to show some of the typical hurdles and problems.  What we are left with is a limited use "sometimes" solution, based on how hard you push and the luck of the above factors.  Therefore, Data mining or Data Analysis techniques are best when the following conditions apply:

• The amount of data is large and time consuming to analyze by conventional methods.

• Agings are thick

• Aging analysis must be frequent (i.e., daily)

• The Borrower/Debtor is able to export into a readable format.

• The Borrower/Debtor can provide some query reports.

• The examiners are trained and capable of understand how to do this type of analysis.

• Fraud is suspected or willful misconduct is likely.

While we can't mention any names (because they are our friends), we have seen some alternative strategies that are available to larger lenders.  The small independent lenders may be at a disadvantage at this time; however, this disadvantage will diminish as technology becomes more user friendly and as accounting systems comply with more common file exporting routines.

With the above qualifiers in mind, the larger lenders can consider the following:

1. Train the Examiners - We recommend minimum and basic Excel skills as a start.  We wrote the number one course on the subject (Data Analysis Techniques for Auditing)

2. Start with Monarch and Excel and see how it goes.

3. Use the programming staff to analyze this stuff in the office.

4. Create a SWAT team of select and trained experts to setup the Borrower/Debtor.

5. Move more of the analysis into the office to allow the examiners to perform tests instead of compilation work.  This has the added advantage of freeing up those busy Examination schedules and costly Examiner salaries.

6. Consider outsourcing some of the programming and analysis needs.

7. Use risk based examination techniques where needed and consider a risk based examination scope.

8. Use software that will get results more often (i.e., AssetWriter and AssetReader).

While the above list may be short, it can save tons of time and improve analysis.  Furthermore, the "geek" toys are in the hands of those that can.  Better yet, get a no-geek tool like AssetReader.  But there are things that take time away from this (see below).

So you have a stressed out examination schedule and demands are coming in for more.  More deals, more new business, more recurring exams, more problems, just more!  Imagine if the economy dips too much and we have more frequent exam cycles?  So you're searching for the holy grail of time savings and you think this could be it.  We think it can help, but it's still a carpenter's cup against the fire of growth and all that it brings.

There are some BIG questions that need to be answered here:

1. Data Mining or DATA Analysis?
   Data Mining is more fraud oriented and this will extend the scope of the examination.  Data Analysis is a sometimes thing and it may not be available.  In some cases the setup time outweighs the time savings on the first transaction.  Therefore, further time savings require more examinations.  Some time savings can be made by taking the downloads in-house and thus reducing the staff time in the field.  Remember, it is sometimes, not all the time.  Finally, the data is also only part of the examination and testing of transactions, interviews, test counts and writeup still need to be done.
   
   

2. How Much Compliance Will we Get?
   We have found it to be 10%-70%, with spots of success in other cases (generally with AR agings).  Some have reported no success and some have reported only 10% success.  It's not a guaranteed success rate thing and results vary by lender based on pushiness, examiner skills, and training.  Our surveys show the greatest success in the AR aging analysis and ineligible automation.  When we wrote the first version of this White Paper in 2000 we had found that total data downloads were only @ 30% of the exam (generally from small deals).  Therefore, at that time, the savings were only @ 10%-21% across the board (30% X 30% to 30% X 70%).  
   
   In 2003 we had success with 19 of 20 customers (95%).  Your results may vary.  Customers have evolved into new Windows based software and databases now hold several years of data.  The ability to get sales, cash and G/L journals is the same as it is for agings, but you need to ask for these things.  This bodes well for making this work for your organization with the right training and the right tools.  
   
   

3. Why are Time Savings Not Better?
   We have no clear answer for this, but offer the following feedback from our surveys and personal experience.  Setup time may counterbalance or outweigh the time savings on the first 1-2 exams.  Examiners lack the skills to get this done (even after training = cognitive limits).  Borrowers don't want to give us the data.  Borrowers can't give us the data (see list above).  One of the largest commercial lenders in the world uses data analysis and mining regularly; complete with a dedicated office staff to do this.  They noted chasing shadows at times because the data is wrong, the fields are wrong, something is missing, etc. and it takes detailed manual analysis to find the variance, which may be nothing major.  We had one lender complain about dead time because the examiners were in the office more and what took 5 days can sometimes be done in 4 days, but then again, a new deal can't be started on Friday.  It is not Utopia, it is a step toward a more modern society.  
   
   

4. Do we Dedicate a Computer Savvy Group to do This?
   This is a possible yes for larger institutions and a no for the smaller shops.  Large institutions are at a disadvantage over finance companies, because banks tend to time slice who gets MIS support, while finance companies have dedicated personnel.  While it may work sometimes for the medium to large finance companies and some banks, the programming brain trust can leave at any moment.
   
   

5. Can we leave my Exam Program Alone?  
   This is a big question if you are moving to Data Analysis and Mining because the scope of the exam may  expand to "check things out."  If risk based examinations are scheduled, the extra time needed for problem accounts can be spent on the riskier areas under an expanded scope.  On the other hand, if you add this to your existing time, you will be straining the limited resources that are available.  Exam templates in Excel and AssetWriter can have some data integrated from the electronic analysis, although in reality, it's a minor task to key-in some of the exam report items that were downloaded and summarized (the download saved you some time).
   
   

6. Who Will do This?
   Time to upgrade your back-office.  The brain power needs to be there and that can make this dangerous to implement.  If a core person leaves, your clients will still want to report electronically.  Software support is critical and it must not be overlooked.  After working with this stuff for 18+ years, training hundreds of people on computers and teaching the DATA Course, I have one key observation on this question.  Everyone can learn something new, but there are few that can learn and handle complex things.  I believe that my college professors called this cognitive limits.  Perhaps that's why I'm in ABL and not rocket science.  Our research noted a large number of ABL shops and accounting firms that have parsing software at the office sitting idle on a computer or in a box on a shelf (by large, we mean at least 100 examples of this).  Idle software will not help you if key employees leave after implementation, you must have support.  
   
   

7. How Will we Teach this?
   Not as easy as it sounds.  While some of the software vendors offer specific ABL examples, the parsing software companies do not.  The tools must be adapted to ABL needs or training must fit ABL needs.    While this appears to be an obvious plug for my DATA Course, it is still a question that needs to be answered in combination with the other issues in this report.  Still, we have devised a state of the art system to teach some of these skills to people with basic Excel level experience (i.e., SUM formulas and basic IF statements), which can serve as a springboard to some simple, yet powerful analysis.
   
   

8. What's It Cost?
   We have seen the whole spectrum, from Excel (you probably have it) to systems that cost lots of money per user.  We have gotten outstanding results with just Excel, AssetReader and some training.  Budget considerations aside, the ability (skills) to do this are more powerful tools to have and the results with just Excel and Monarch are outstanding for sales journals, cash journals, inventory reports and receivable ledger listings.  Aging analysis (ineligibles, etc.) takes more time to automate (tweak), but tools like AssetReader make the job easier to complete and staff sizes can be reduced.
   
   

9. Integrate with the ABL Monitoring (back office) Software?
   Systems integration with your back office sounds like a good idea, but is it?  Integration will likely shut-down system use during the upload process and the solution itself may be weak.  We have even seen one solution that requires every ineligible invoice to have a tag of some sort for uploading into the monitoring system.  This type of solution is both tedious and factoring-like, but the cash is not applied at the invoice level and the this negates the work of tagging the invoices (we didn't write that and we don't know what they are thinking).  In most cases, keying a few ineligibles (generally under 5 per borrower) into your monitoring system is very easy, while the ineligible calculation automation is the real time saver.  Don't be fooled into thinking that an integrated solution is best when you could have a much better solution with external software that does a great job.  Note that AssetReader has been winning-over converts from the leading integrated back-office solution due to power and ease of use.
   
   

10. Alternate Use of Time?
    Yes, we have some lenders that have decreased their back-office staffs be several heads.  Personally, on the audit side I have more free time when I travel and it is more enjoyable to be able to leave the borrower site in regular business hours and this prevents burnout.  Some lenders do additional audit steps and ask questions about transaction types and reversing entries because they spend less time compiling stats.  Some lenders pre-process the data (FinSoft actually does that for some lenders) to save field time and travel expenses.  Some lenders compute ineligibles every day!  Why not, once the template is setup?  
    
    

11. Do I need an Implementation Strategy?
    Yes.  This varies widely from lender to lender.  Higher risk lenders can demand data, while corporate banking lenders may need to tread lightly.  Some lenders have allocated up to half of the bonus pool based on the Lending (line) officer's selling this to the borrower.  Those that book new business and convert older customers to electronic reporting share in the rewards.  Some auditors are receiving compensation bonuses for each aging that they setup.  Some lenders have a swat team of experts that set these up.  Still other lenders have hired us (FinSoft) to setup clients, get data on new business exams and even process monthly data for them.  The implementation strategy can pivot on the strength of your staff's technical skills and the cost of hiring those technical skills, so assess those skills and ongoing support risks first.
    
    

12. Are you Asking the Right Questions?
    If you decide to look at the higher-end software, have the vendor provide a list of references, particularly the most recent additions.  Call the references and ask about support, bug fix time, help files and patch support.  Ask about ease of use, learning curve time and training.  Ask about the percentage of use and exactly what is being downloaded.  Ask the references about what doesn't work for them (not the software - the data analysis problems).  Consider fewer copies to try it first, but do indeed give it a chance to take root.

Yes, we do recommend training.  That is why Clear Choice Seminars, Inc. invented the Data Analysis Techniques for Auditing (DATA) course specifically for ABL auditors and operations personnel.  There are many training ideas to consider, but here is our assessment of what is needed:

1. What is data?

2. How do you get data from a Borrower/Debtor?

3. What formats does it come in and what formats are best?

4. How do you get it off of the Borrower's computer?

5. What tools are available?

6. What tools work best?

7. What are the intermediate ABL related features that can be used from Excel?

8. What are the advanced ABL related features that can be used from Excel?

9. How do I use Excel for Sales journals, Cash Journals and AR postings?

10. How do I quickly summarize Sales journals, Cash Journals and AR postings with Excel

11. What are the key limitations of Excel?

12. What is Monarch and What does it do?

13. How do I use Monarch for ABL purposes?

14. What are multiple line traps in Monarch and why learn about them?

15. Should I do the math in Monarch?

16. What about other tools like AssetReader?
     

Researchers are working around the clock for a single cure for dozens of forms of cancer.  Some researchers are working on one unifying solution and we all hope they find it.  However, the budgets used in software, particularly accounting software, are not at the same level of the NIH.  Fortunately, FinSoft has invested heavily into the analysis of Agings, Sales and Cash journals.  We see more willing customers and more willing examiners too.  However, we also see some examiners that are afraid to try new things or to ask for help when they need it most.  You'll need to assess the skills of your staff with some hard thinking and tough questions.

We have seen data analysis implementations done successfully by strategically deployed accountants on large engagements at commercial finance companies and at national CPA firms.  Unfortunately, when you have staff turnover, the brain trust just left.  Parsing tools are at least affordable for getting the data split into columns, but they do little on their own and aging analysis is far too complex for what is offered by even the best of the parsing tools.  The needs for ABL concentrations, contras, due date aging adjustments and other ABL quirks makes programming the likely solution for every aging (that is built into AssetReader software). 

The average amount of data downloaded may be @ 10% - 70% for just AR agings, however, this estimate should be diluted from getting data only @ 10%-70% of the time (not all the time) for all reports.  Therefore, saving range from several hours per examination that can use the software to several days per exam on large transactions, depending on the size of the journals, agings, posting summaries, etc.  With a savings of up to 30%-50% of the workload, only 30% of the time, that's only a 9% average savings, but it could be higher (saving 70% of the workload 70% of the time=49%).  You will have some success stories and some failures at both the audit and back-office levels.  

With the correct blend of people, training, tools and client cooperation, you can download almost anything (above problems and limitations in mind) and generally get very good results, with little cost for the tools.  We still find Excel to be an outstanding tool for analysis of sales journals, cash journals, AR Postings, inventory reports, AP transactions, etc.  However, Excel falls down when agings have customer names above the invoice rows, when negative signs are on the right of the number or when date formats include blank spaces.  

We have found that getting the aging and other data electronically is controlled by the factors in this white paper and that full compliance is rare, making time savings difficult for some accounts.  Our research data showed that none of the data analysis based exams were completed by the download software, even when everything was downloaded.

C O N C L U S I O N S

We said it once and it bears repeating, we believe that for data analysis, training is a great investment.  We do offer words of caution about skill levels and the potential frequency of use based on the above limitations.  We also offer words of caution about ease of use for some of these tools.  The parsing solutions offer very limited aging analysis and almost no ABL specific calculations.  Programming skills are needed and this may not equate to the skills within your staff.

Therefore, even though we sell one of those tools (ours clearly has the best and easiest to use interface with the most advanced ineligible setups), the back-office needs to have the right people.  For field examinations, the exam process is not going to get done by downloads alone and again, not all of the time.  

We hope that your perception has been broadened.

"FinSoft Knows DATA"