Data Mining Considerations for Asset Based Lending (ABL)

Data Mining: -  All Data Analysis items noted in the box below and, -  Fraud investigations -  Searching for missing invoice numbers -  Identify employees with more than 60 hours of time per week -  Identify which employees are listed at more than one location -  Search through all G/L entries for affiliated names -  Search through all expenses for affiliated names or expenses over a threshold dollar amount Data Analysis: - Adding all Credit Memos from a sales journal - Adding All Debit memos from a sales journal - Adding all discounts from a cash journal - Summarizing AR postings by Transaction Type - Calculating Ineligible AR electronically - Performing inventory slow movement analysis - Test items for mathematical accuracy - Sorting items to focus on the largest or smallest

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 complex agings with retainages, finance charges, and other adjustments

• Electronically calculating availability for a borrowing base

• Electronic contra analysis

• Electronically grouping all of the major department stores and concentrations together.

• 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.

UNDERSTAND THIS FIRST

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. 

Parsing Part II

I wish this was push-button accounting, but it is not.  Files come in many formats ranging from TXT or PRN, RTF, DOC, XML, XLS, XLSX, PDF, HTML and more.  So finding one tool to do is all is quite difficult.  Some tools deal with TXT / PRN files and some deal with PDF and some deal with HTML, Etc.  Just getting the data split into columns is not that hard, but the variety of file formats makes it harder. 

When it comes to AR and AP agings, the customer / vendor names are often on the row above the data and that needs to be addressed because those names need to be copied next to each invoice so that the data can be sorted and identified at the transaction number or code level.  Therefore, advanced parsing is a bit more complex than merely splitting things into columns, it includes some data manipulation.

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.  You'll need to do some setup work at least once to "train" the software how to look at the file and process settings such as cross aging or grouping all Wal-Mart and Sam's Club stores.

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, processor 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.  We have the most expert Import Wizard in the world to deal with these issues.

The Process 101

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

File / Report / Exported Data from Accounting System

ò

Parse (split) columns to get data into distinct columns

ò

Sort / Collate or Export into Excel or Custom Program

ò

Perform Mathematical Analysis

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.

 

 

THE TOOLS

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.  Nobody has a tool that is as easy to use as AssetReader (see below).

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
AssetArchive - Time series analysis from AssetReader data to spot issues and focus on potential threats.  Includes confirmation selections based on these exceptions
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, AP Aging Analysis, Sale Journals, Cash Journals, GL Journals, Customer Name Lists, Vendor Name Lists 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.  Clearly the industry leader in the area of ABL specific ineligibles, journal analysis and inventory analysis.  Able to parse almost every file type available.
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 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.  

 

 

 

FRONT END OR BACK END

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.

Note that your Loan & Security Agreement might not require data downloads and some Borrowers will refuse to help for "legal reasons."  Getting cooperation to hook into the accounting system will vary by agreement, loan size, risk level, Etc.  For example, a Factoring Company can demand a lot as a condition of making the asset purchases, but Corporate Banking might need to be handled quite delicately.  The list of reasons to not provide data or not provide access to databases could fill over twenty pages here (we've heard quite a few excuses and you'll see some of them in the next section).  But electronic reports that don't need to be mailed are just common now and Borrowers are now accustomed to getting a request for a PDF, Excel output, TXT or PRN report.

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 SOLUTION IN SEARCH OF A PROBLEM?

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
 - Old accounting systems

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.

• Your lending style (Non-prime lending, D-Paper, Factoring) lets you dictate all terms

• The accounting system has reports that export or print to PDF

We recommend training first  and more advanced tools second

OTHER SOLUTIONS TO CONSIDER

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.  Note that they have spent zero dollars on ABL specific code.

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).

Your "GURU" that processes reports is going to create havoc if he or she leaves.  The skill set and general lack of documentation will come to haunt you if that person leaves.  A well supported tool gives you point and click software options and ongoing support for now and later.

 

BIG DECISIONS

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.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.

   
   

2. Why are Time Savings Not Better (ghosts and shadows)?
   We have no clear answer for this, but offer the following anecdotal feedback from our surveys and personal experience: 
   a.  Setup time may counterbalance or outweigh the time savings on the first 1-2 exams. 
   b.  Examiners lack the skills to get this done (even after training = cognitive limits). 
   c.  Borrowers don't want to give us the data.  Borrowers can't give us the data (see list above). 
   d.  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.
   e.  You can't replicate their agings.  Even if you logon to their data directly, the routines that age the invoices, apply the credits and cash, age by unusual terms, calculate finance charges and other factors make it a tough job to match the agings (think about it, someone programmed that aging for that software package and the data is just data).  The top accounting fraud audit tools are not even close on this topic.  Note that we offer lots of solutions to this issue. 
   f.  You start getting smart and analytical.  I know that is good and it sounds good too, but so often we chase down things that look like trouble, but they are not.  For example, all sales with no commission code would "seem" to be a good exception to chase down.  But reality is not theory and so often, this is nothing at all.  Are you lending to that level of risk and do you have time to chase ghosts and shadows?  Anyone that knows the audit reputation of this White Paper's author would know that he's done it all and is paranoid.  But how paranoid can you afford to be before you are on long mystery chases in the dark?
   g.  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.  

3. 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.

4. 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).

5. 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 22+ 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.  

6. 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.

7. 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.

8. 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.

9. 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?  

10. 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.

11. 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.

TRAINING

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?

 

IT TAKES A BLEND OF SKILLS AND TOOLS

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.

Our Advice:  Train, Staff and have Support

CONCLUSIONS

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"

January 5, 2021 marked 25 years for FinSoft, LLC - Thousands and thousands of reports analyzed, $$$$ Billions and Billions analyzed each month.