Select or Develop Electronic Field Examination Reports
for Asset Based Lending (ABL)

OVERVIEW

While Asset Based Lending (ABL) survived without computers for almost 45 years, the new year offers the choices and challenges of finding the right combination of hardware, software and report format to satisfy the quality requirements of management.  This "White Paper" is written as an overview of the general hardware and operating systems available and then focuses on the software choices available to field examiner's.

NOTE:  Things have Changed and this white paper was started in 1996.  Software is more than the ABL field exam report because that now includes data analysis from client electronic reports.  Please note that a complete solution includes both field examination software and data analysis software.  Integration of the two is even better.  This White Paper has been split into different white papers for Hardware and Software due to the expanded data software section.

Since the CPAs have a hard time calling field examinations "audits," we will refer to these things as reports or field examinations in this "white paper."

LETS DEFINE THE FIELD EXAM PRODUCT FIRST
(Beyond Paper and Manual Data Gathering)

Some things about field exams are the same regardless of the name of your employer.  Things like billing tests, stats, concentrations, delinquencies, credit memo tests, test counts, cost testing, disbursements analysis and others are basically the same from place to place.  What can be different is how you approach this seemingly "same" process and look for efficiencies.  What surprises people when they see our products is the polished integration and careful thought to the problem areas.  Lets look at the areas to improve time efficiency and the report itself:

Field Examination Report - Time Killers

• Consolidations of multiple divisions

• Multiple Examiners doing different tests

• Multiple Examiners doing different divisions

• Converting divisions from one currency to another (prone to manual errors too).

• Printing workpapers (size, margins, compression, orientation, reprints)

• Consolidating the workpapers from multiple divisions or multiple examiners

• Summarizing and analyzing the test results

• Summarizing and analyzing across multiple divisions of a company

• Proofing the workpaper links and summaries, particularly at the Manager and review level

• Writeup word processing summaries from the data (perhaps combined)

• Showing specific exceptions and not combined data

• Adjusting for a re-exam when the workpapers are full

• Setting up a new exam

• Setting up the borrowing base for all divisions on a consolidating or consolidated basis

• Updating workpapers to account for new changes desired by management

• Updating the writeup when the data changes in the workpapers

• Poor skills with concepts

• Poor skills with field examination software

• Poor skills with computers in general

• Dealing with locked-down spreadsheets when people are in the field

• Supporting Microsoft macros when the author of the macro code leaves the department

• Supporting Microsoft macros in general as the MS Office versions change (e.g. Office 2000 and Office 2007 broke the old macros)

We've automated all of that in our product, but if you're not using AssetWriter, then you need to deal with each of these problems with procedures, coding and time.  But the computer program coding requires money, maintenance, and other internal factors that won't necessarily be around next month or next year such as the person that did the coding.  You can save time and get support with an automated and well developed product like AssetWriter.  Our question to you is, "Have you seen AssetWriter?"  If the answer is "no" then it is hard for you to see what we have done to automate the above with almost 10,000 hours of programming time (that excludes our owner's time by the way).

Field Examination Report - Analysis
What separates the "Star" examiners from the average examiners is time savings from experience, time savings from skills and analysis of the situation.  Most of the field examination shops don't calculate dilution by credit memo type across multiple divisions or do what if analysis for when a problem division is pulling down the average turnover or dilution results,  Talented examiners do what they can to do great analysis in a reasonable time frame, but there are time limits and the complexity of some analysis on a combined basis (compared to a one division exam) makes much of what we "could do" impossible with time limits.  You could program some solutions, but the mathematics and maintenance in a spreadsheet would make the results unreliable. 

What if the analysis was all pre-programmed and you could pick which pieces of analysis to include in the writeup?  FinSoft has over 140 workpapers and 75 writeup report data grids with expert analysis  The kind of analysis that could take hours per grid to achieve.  FinSoft has the most complete and detailed analysis in the ABL industry and we have never seen anyone with more detail, more flexibility or more options.  But this is all with thousands of hours of report writing (yes thousands of hours) and we are proud to say that this requires no programming by our users.  The results are instant and mathematically accurate as compared to the common math flaws found in spreadsheets.  Sorry, but spreadsheets can't do some of the analysis in AssetWriter and this lets even beginners prepare the same expert reports as the seasoned veterans.  Yes, real value added analysis with no time investment in that analysis.

Data Downloads
This may be the biggest time saver and the greatest fraud reduction idea since the confirmation phone call.  We're downloading client data on 24 out of 25 borrowers.  Yes there are exceptions, but the data is available.  Some of you are using off-the-shelf generic products like Monarch to try to do this without too much investment, but there are dozens of problems with that strategy.  Lets look at some of the gotcha' items with data downloads:

• Key people know the software, others don't

• Documentation of what was done can be "in their head"

• Difficult date formats may not convert (every package except for AssetReader fails in this area)

• Almost every accounting system produces different report formats

• Some report formats are extremely hard to convert to usable numbers (PDF, HTML, RTF, DOC)

• More people are saving to PDF and columnar data can end up all over the place during conversion

• Examiners receive combined reports such as sales journals with 18 months of data and they need to get the monthly totals by transaction code

• More people are exporting to Excel and the data may be all in one column

• Computer skills need to be at a certain level to operate some of the programs

• You may need almost a dozen different software tools to get all of the formats converted

• You will still need to tie-out the report totals

• Some ineligibles may be based on transaction codes like DM, FC, COD and other codes.  But you need to be careful not to double count the ineligible account

• Some agings have more than one number in different buckets for a single invoice (finance charges and debit memos)

• Some agings have unaged columns such as chargebacks, retainages and unapplied credits

• Inventory reports that are combined with usage reports require careful pre-grouping to prevent errors

• Contra analysis based on just the first few letters of the name will not work (i.e., The ABC Company and ABC Company won't match).

• Etc.

There is also the issue of client cooperation, staff training, ongoing training and ease of use.  Too many tools and too many tweaks make this more difficult, so we integrated the ABL analysis into one tool.  We even have our own parsing routines to help setup ABL specific analysis.  We've got one-stop shopping for the training, ongoing support, integrated product, all of the above file formats and our ease of use puts all of the other products to shame.  In fact what we do in 5 minutes cannot be done with any other product in two hours and AssetReader consistently finds more ineligibles than any other tool, but always in a fraction of the time of any other setup.  Why?  Because we're ABL specific software and we know what you want, have reports that you want and of course it is far easier to use than any other tool available.

We've automated all of that in our product, but if you're not using AssetReader, then you need to deal with each of these problems with procedures, coding and time.  But the computer program coding requires money, maintenance, and other internal factors that won't necessarily be around next month or next year. You can save time and get support with an automated and well developed product like AssetReader.  Our question to you is, "Have you seen AssetReader?"  If the answer is "no" then it is hard for you to see what we have done to automate the above with almost 4,500+ hours of programming time (that excludes our owner's time by the way).

Database Linking
Linking to the data through a database cnnection.  You are "in their database of accounting data."  We know how "cool" this sounds and we know that it fun to work with because we've done it, but there is one GIANT problem.  There are thousands of accounting systems out there.    While Quick Books and Peachtree Accounting are obviously common with smaller businesses, once you get to a larger level of sales and you look at vertical integration markets, niche players, specialized software and industries, the reporting systems get diversified.  The author of this White Paper has seen over 300 system.  You need to deal with the bad reports and bad systems too.  An Excel layout sheet looks simple (it is), but getting data to fit that format can be a nightmare and require very special skills or software to manipulate. 

Combined Data Downloads and Field Exam
FinSoft has the only integrated package in the ABL industry.  Ineligibles, concentrations of AR and AP, and more are exported from AssetReader into AssetWriter (note that they work as stand-alone programs or together).  Sample selection becomes extremely easy with sortable columns, exports to Excel and other simple techniques to use the two products together.  This has never been done in the ABL industry before, but that integration has now been in place since 1998.  In case your wondering about price, our combined product pricing gives you all of this with the second product at 1/2 price for a huge combined savings in cash and an even better savings in time.  Can you do this yourself?  Sure, just invest over 15,000 hours of time.

 

THE UNSPOKEN TRUTH AND WHAT TO DO ABOUT IT

Nobody has the guts (until this) to say it and nobody has taken the time to assess the problems that lead to poor human performance on a field exam.  This is the unspoken, the hushed and the ignored truth about job skills.  Some managers offer a bit of training here and a bit of training there.  Some managers use on the job training (OJT) and may send staff to more advanced (perhaps even basic) training for this reason:  "Some training is better than none."  Unfortunately, general training is going to be boring to the seasoned examiner and a bit too fast for the ones that lag behind. 

The examiners that lag behind are fighting a mental battle to get it done at all and they may even live in fear about getting caught behind.  But the field exam department is a "team" and all players should come up to some standard of performance.  Unfortunately measuring that by the days on an exam is not capturing the "actual hours on the exam."

To make matters more complex, the OJT that managers expect is actually padded from stolen OJT that the managers don't know about as the staff begs, borrows and steals the brain power of others to get the OJT to bring them out of their "stuck" state.  The lead performers are slowed down a bit from the stolen OJT experience.  We all consider it an honor to teach others, but there is a price in the form of lower productivity from the star performers.

We've invested hundreds of hours into ABL-Help (the FinSoft Help file with over 180+ ineligibles defined and 750+ glossary definitions).  We've invested almost 1,000 hours into the AssetWriter and AssetReader help files so that users can get help by pressing F1 in the programs.  We've invested in excess of 200 hours into the HTML5 based tutorials and simulations that are available to our software users.  How many hours does your organization invest or how many instant Help File solutions does your field examination or data software product have for ABL specific needs.  Probably very little.

There are no concrete answers to this, but we would like to pose some possible solutions to this issue since it directly affects time spent on field examinations:

1. Work with personnel to obtain skill assessments for computers (there, I said it - You need skill assessments)

2. Train those that need more computer skills with specific courses for their levels of assessment (Windows, Excel, MS-Word, File Management, WinZip, Etc.)

3. Perform general training as much as possible, perhaps one hour meetings (once a month would be 12 hours for you mathematicians)

4. Build a library of magazine and web-based articles for access by all

5. For specific software applications more assessments are needed and the slower performers should be retrained at a slower pace

6. Don't think that this will ever solve itself.  Managers are paid to deal with this, but not to ignore it.

7. Is this unspoken at your institution, perhaps it needs another look?

WILL YOU SAVE TIME?

The truth is that it depends.  The more programming that you do, the more time will be saved.  Unfortunately for Microsoft Office users, more programming means more problems if you alter the spreadsheets by adding rows or columns, formatting, multiple divisions and multiple examiners per Borrower.  However, time savings will also depend on some of the following factors:

1. Skill level of the computer software user

2. Complexity of the examination

3. The accounting system and how clean and simple it is or (more likely with larger deals) is not

4. Number of Pages in the AR and AP Agings for Ineligible Analysis

5. Number of pages in monthly sales, cash and GL Journals used for stats

6. Whether or not there are multiple divisions to consolidate

7. Whether or not there is more than one examiner

8. Whether or not there is more than one currency in use

9. Whether or not multiple examiners work on one company or share work on any one division of a company

10. The extent of data downloads, if any

We offer several products from our software company.  One is AssetWriter (Field Examination Software) and the other is AssetReader (DATA analysis software, currently focused on ABL specific AR aging, AP Aging, ineligibles, stats, contras, inventory movement analysis, customer name lists, vendor name lists, Sales, Cash and G/L downloads with integration into AssetWriter).

Here's our estimate of time savings on each product type:

Description	Overall Use
	AssetWriter	AssetReader *
Use on Field Examinations	100% on all Exams	15%-90% on All Exams
Back-Office Support	some keying of data	25%-90%
Time Savings on Initial Examination	10-30%	25%-50%
Time Savings on each Re-Examination	15-50%	25%-50%

* It is possible to have zero time savings when no data is available due to a DOS or Unix system or perhaps old data was printed and systems were then upgraded

While AssetWriter is typically used on all exams, AssetReader is not a 100% solution due to some limitations in getting data from some customers and the field examination not being just about data.  Humans still need to ask questions, count inventory, Etc.  In addition, the above numbers can be better understood by noting the setup time after an initial examination is reduced with both products and subsequent examinations tend to have less data to enter or analyze.  But saving 1/2 to 1 full day from using AssetWriter and saving 25% or more in time from AssetReader's highly automated and easy to achieve downloads are real savings in time and big improvements for mathematical accuracy.

Additional time savings are gained because AssetWriter and AssetReader are integrated, allowing the ineligible items from AssetReader to be posted into AssetWriter.  DATA analysis is only a part of the exam, but that could be a 50% savings if multiple divisions are factored in.  While AssetReader is a capable, easy to use tool, it cannot be used 100% of the time and not for 100% of the examination (see above and note that the author has 33+ years of ABL field exam experience).  But with modern accounting software and with the ability to convert most formats, AssetReader will get the data converted from most systems and that creates a huge time savings over the course of a year for both field examiner and the Managers.

We developed AssetReader to handle sales journals, cash journals, G/L journals, inventory and of course AR and AP aging reports for ABL use.  As the year 2000 came to a close, the number of software packages that could export data was better than ever and the ability to get this done has improved through 2009.  Some lenders and most of the AssetReader users are downloading some data (typically agings) from about 90% of the Borrowers.  More advanced examiners are getting the AR, AP and Inventory stats done in minutes with AssetReader and in the case of large agings it is possible to save days of time on ineligible calculations.

There are many other issues with data analysis from simple things like bad data formats to complex things like knowing what to look at.  To this end, Clear Choice Seminars developed the first advanced ABL DATA Course to use simple and affordable tools to get the job done.  Built-in help files for AssetWriter and AssetReader have instant access to software feature descriptions.  FinSoft's user website is packed with HTML5 based tutorials for licensed users.  Most importantly, FinSoft's knowledge of ABL and dedicated customer support are indeed the best support in the ABL industry.  Tough data conversions or complex borrowing base setups are all spoken here and we strive to make even beginners into experts.

Powerful software tools like AssetWriter and AssetReader make the field exams fly.

Will you save time?  YES you will if you pick the right tools with the right support.  Yes you will with proper training.

 

FIELD EXAM REPORT SOFTWARE NEEDS (No DATA Analysis)

Our White Paper on hardware notes that spreadsheets and word processors and fast machines alone do not guarantee speed or quality results.  If you have simple needs, say you don't link to word processing documents, then the computer speed is probably fine.  If you want to expand into having several programs open, linking or writing your own program, the needs will be different, and maintenance (fixing problems) will become a key issue.  A fully integrated system would save time, but human resources and maintenance support may be limited if you do it yourself.

The Fidget Factor (a river in Egypt)
Sure you can get by with simple spreadsheets, perhaps a few print macros thrown in to be clever, but can you handle multiple companies, multiple examiners and fast turn-around times with the tools of the 80's?  Furthermore, with the new features offered by word processors and spreadsheets such as tabs for sections, OLE and workbooks, can you harness the power to put all of these technologies together?  Public accounting firms doing ABL field exams are particularly sensitive to report preparation time and turnaround time.  We have observed (and cursed) the never ending cascade of menu choices (now ribbon tabs) in Word, Excel, Lotus Word Pro, Word Perfect, and others (some say they spend more time reading menus than working).  Some have performed endless formatting chores or watched others format a handful of pages for hours, just to get one report section printed.  This "fidget factor" wastes hours on every exam and can consume days on larger engagements.  It goes beyond the learning curve and becomes a standard part of administrative time.

Fixing prior exam data, formatting printouts, debugging math errors, proofing for possible errors and other maintenance tasks can take up an entire day.

The Don't Touch It Factor (We can't change the template because ...)
Spreadsheets are flexible, but they don't allow new versions of models to be created without rekeying (moving) data and consolidations require exact model matches to be made.  Some examples follow:

Suppose you start with a simple AR stats page and use the format for a year, but then decide to add some columns for intercompany eliminations.  The old data must be rekeyed into the new format (or all old spreadsheets must be modified exactly the same), since the format has changed. "No big deal" you say because your deals don't stay around for more than two years. Think again, many deals do stay around and those updates can be needed every 3 to 6 months.

Suppose that the examiner at location "A" lists 15 concentrations for receivables and the examiner at location "Z" lists 12 concentrations for receivables.  The data must be rekeyed or processed with cut and paste to do the consolidation.  Now do that for 6 divisions and things are quite a bit more complex.  What if some of the concentration names are the same?  And you thought that listing concentrations was easy.

Suppose that you have three divisions with different receivable ineligibles and you have two examiners working on the project.  Consolidating the different ineligibles can be a chore and presenting the summary in the report could require spreadsheet debugging, a calculator, cutting and pasting, etc.

In short, the spreadsheet format requires a rigid format for consolidations and won't allow updates to the old data if the spreadsheet format is altered.  Lets not forget about reliable results and the possibility of blown formulas and links.  The solution is to warehouse the data in a database, but that requires programming (such as AssetWriter)

Also note that Excel has gone through major revisions (along with Word).  These revisions can make your existing programming obsolete if you upgraded from Office-95 and the Windows 2007 or 2010 version may be problematic for conversions.  In late 1998 we surveyed over 100 people and performed the same survey on 125 CPAs at a technology conference in Baltimore, MD about the use of macros using Visual Basic.  Results -- Under 2% of the total can write Visual Basic macros of any kind so the macro idea is quite a bit more complex than you might think.  Office 2007 and 2010 moved to an XML data structure for all Microsoft programs and macro script (VBA) conversion from prior Office versions has proven to be problematic.

Custom Programming Assessment
Our advise is to seek out new technologies (OK, we're not Trek) that address needs for: support, report formatting and cost controls.  We believe there are seven key factors to consider:

1. Who will develop and support the project into the future?
2. Can an existing package do it all?
3. Can an existing package be customized to my needs for items that are not in the package?
4. Will the time savings and quality benefits outweigh the costs over time?
5. Can the system produce reliable and usable results?
6. How will Outsourcing people get the software?
7. Is the support at the ABL Expert level and will it be there in the future?

Who will Support it?  This may be THE question to answer honestly (note, it should be us).  Support should be considered a primary requirement for development and continued future needs.  The skill levels of the MIS department or third party developer are not likely to be in the ABL Field Exam profession.. With a shortage of programmers, even the IS staffs at banks are outsourcing, be sure to ask first.  Even skilled programmers will need a top-notch examiner (Subject Matter Expert to understand the business rules of ABL field examinations, and that means more investment in the project.  Don't forget about the support queue.  That is your request for help being parked until someone can get to it.  Ever have a hardware problem that took more than a day to resolve at your institution?  Programmers are not the hardware people and their queue is usually measured in weeks and months, not days.  Finally, if there was an outside firm that could provide a package, what credentials do they really have to write and maintain ABL field exam software?  What happens when the outside company or even your own MIS staff loses those programmers?   We met with a Lender that had one full-time computer support person assigned to the ABL spreadsheet mess, but that person quit!

Existing Package and Reports?  Report formats vary from lender to lender.  Some of the primary strengths of existing software packages are the wide variety of reports and formats.  Smart options and intelligent flexibility are needed.  AssetWriter has approximately 140 workpapers and 75 data charts available for the writeup (far more than most people would select).  We customize to get you what you want, but the existing report list and flexibility is extensive.

Existing Package Customizing?  We customize the software all of the time for people (for a fee of course).  But that makes the product broader and smarter than what can be done in-house.  FinSoft has invested thousands of hours into the reports to get expert analysis from the numbers and across multiple divisions instantly.  There is a huge infrastructure in the reports and that makes customizing easier because we've already done almost everything to present the reports.  AssetWriter and AssetReader are written on a core foundation of screen layouts, report generation, writeup automation, graph automation, examiner imports, data imports, checklists,  Etc. and each module allows FinSoft to add additional features with ease.  In other words, FinSoft invested heavily in a clever foundation that can be added onto.  This foundation was expensive to develop, but FinSoft's ongoing enhancements have allowed the program to grow and receive updates with ease.

Savings over time?  Lets face the facts, the ABL field examination staff gets the fewest budget dollars.  Software should be affordable, but custom applications and the time needed to devote staffing to a large project are costly.  In order to save the most time in report preparation, extensive programming is required.  Costs can include: outside consultants or in-house programming time, staff assistance in development, training time, bug fixes, version upgrades, custom report writing and retraining new employees or programmers.  Any potential savings can be wiped-out with the loss of key employees (computer guru's), operating system changes, platform changes, or changes from inflexible formats.  One Lender that we met needed 6 months to get the project started with MIS, but then MIS got too busy and the project was eventually outsourced (all Office-95, which won't convert to the Lender's new standard of Office-97, oops make that Office-2000) and that project is now abandoned.  We have heard that one large ABL provider spent almost $1,000,000 on development and they have full time salaries to support it.  Say it.. "WOW is that expensive!" Say it again. "WOW is that expensive!"  Followup-2012 -- That same large lender is now looking to replace that investment because it won't run in Office 2007/2010.  Say it again. "WOW is that expensive!"

Automation that saves only 10% of the time can be worth several employees of expenses, particularly with larger staffs (i.e., 10% of 25 people) and savings above 10% are simply powerful.  Higher percentages are more powerful.  Imagine outsourcing less and keeping the profit dollars in-house.  Solutions that work on some exams and not others or solutions that work on a portion of the examination and not the entire examination will not produce these powerful savings and may not justify the costs.  Cost alone should not be the only concern, because burned-out examiners are expensive to replace when they leave and so is shoddy analysis that misses the problems or can't communicate the key issues to management.  Is there a fast, fun to use, efficient and analysis intense solution?  Yes there is.

FinSoft has done some ROI analysis and that analysis is available to prospective purchasers.  We also offer a list of references that can provide real-world feedback about time savings.  We must warn you though, our users are "fanatics" about our products because what you are reading here is true and accurate (check out our reputation and you will hear it again and again... awesome support, knowledgeable about ABL, high integrity, constant enhancements, reliable, fun, Nintendo for ABL).

Reliable and Usable?  The system must be reliable and useful to the entire staff.  That means support consistency, full runtime (up-time) and accurate information. It means protected formulas, consistency in the reports and other quality factors that require programming.  Quality can also include the quality and reliability of the output, freeing audit manager/supervisor time to read reports and analyze data rather than proof numbers.  Large consolidations and multi-examiner spreadsheets add time and proofreading burdens.  Factor in the time delay to get help when things go wrong and the costs keep adding up when the examiner is stuck in the field or the report can't be issued.

What About Outsourcing?  FinSoft has special pricing for outsource firms.  In 2012, FinSoft has almost every major and minor ABL outsource firm in North America using FinSoft's products, so there is a good chance that they already have it.  Imagine your IS department spending lots of time and then giving the program to the outsourcing firm?  Imaging the outsourcing firm having a big problem and calling your IS department?  These problems have pushed several lenders to issue a blank template to the outsourcing firm with no math, no formulas and no links.  While this drives up the cost to the Borrower and slows turnaround time from the vendor, this solution keeps the IS department out of the outsourcing equation and prevents the proprietary software from leaving the Lender.  Some Lenders have licensed the software to the outsourcing firm, but support is virtually non-existent because outsiders are seldom welcome by the IS department.  When the IS department has turnover, the brain trust is gone and the fun begins again.  Could a supported third party software solution solve these problems?  Yes!

What About ABL Experts and Ongoing Support?  In the above discussion the topic of Subject Matter Experts (SME's) was touched upon.  The cost for the SME is real and developing full-blown software takes years.  Be sure to factor in that salary cost.  Unfortunately, the outsourcing of software will leave you with no SME at the software company.  It is just too expensive to have full-time ABL experts at a software company that is not specialized in the ABL arena.  The non-ABL specific companies will have general support options, but not a full-time SME and programming support will generally not have ABL knowledge at all.  The cost to train people to understand ABL needs and procedures to get the big picture will cost more than you might think.  See the 80/20 rule below.

In the sections that follow we highlight some of the strengths and weaknesses of spreadsheets, word processors, database programs and developing software in-house.  Finally, we offer a new option.

SOFTWARE

From abacus to slide rule to calculator to computer to spreadsheet to full blown program... Technologies change.  Developing an electronic workpaper format and data analysis initiative requires the general choices above, but most specifically it requires a development strategy and commitment to maintain support.

Old Habits, New Tools
Do you remember manual workpapers?  Have you ever done a 24 month cash flow on 36 column pads?  The workpaper format that shows: prior year-end, last exam, and this exam in a three column format came from manual workpapers (perhaps before you were born).  Given the need to do math by hand (before calculators), this format was reasonable.  As calculators became available, some of the analysis expanded as well (calculator and ruler required).  Finally with computers, the electronic form became available.  Today, the format for many remains heavily based on the original three column presentation format used by the larger ABL shops.  The "form" is still standing on its own for many, requiring re-keyed numbers, since database links aren't easily maintained.  Download when you can, but how?

Computer software allows multiple months to be trended, compiled and compared.  Adding-in some trend lines, period-to-period reviews, Etc. and modern tools can be used.  Multiple period trend data from workpapers continues to be shown in a three column format by scores of lenders, although some reports are now starting to show 4-6 summary periods for comparison.  As electronic workpapers evolve in-house, the maintenance needs to get more periods summarized grows as well.  The ideal solution is to automate the task of summarizing more periods because the data is there and computerized analysis is free.

Spreadsheets Alone
Most of the ABL companies and accountants doing field examinations are using spreadsheets.  Lets address the strengths and weaknesses of spreadsheets first:

Strengths	Weaknesses
Simple to program macros and add-row buttons	Macros in Office 2003/XP don't run in Office 2007/2010
Simple tool to add and multiply numbers	Formulas and results can become corrupted
Easy to train and implement to staff	Complex consolidations require more knowledge
Testing is easier to do, results are instant	Complex tests are hard to summarize, spreadsheet skills are needed
Appearance can be customized easily	Requires macro/VB programming to do more and formatting creates "fidget" time.
Can be customized into a full report	Requires programming knowledge
Easy to make new enhancements	Older spreadsheets won't recognize changes
Staff members are eager to help	Staff with knowledge can leave
Can create some elaborate analysis	Complex spreadsheets require maintenance and knowledge.
Can consolidate with File Combine commands	Consolidations require exact matches in formats, can overwrite division data with combined
Can print only what is needed	Fidget with numbers to print best size
Simple word processing for short answers	Primitive word processing and reformatting needed for changes
Can share with outside per-diem auditors, AssetWriter is used by most firms	Why give away your hard work?
Date math is simple to add to formulas	Still need to know a few date based tricks

Some lenders have created elaborate spreadsheets with multiple tabs for each test or section.  The entire exam lives in one file and takes all the RAM that you have.  These exams are difficult to maintain, include a high "fidget factor" for printing and consolidating and can require even more time to debug if formulas are accidentally deleted.  Consolidations can be done, but most of these large single spreadsheet formats cannot be reliably combined with another spreadsheet from another user.  In some cases (we're not naming names) the entire exam can be lost if the file is corrupted or overwritten.

The more sophisticated spreadsheets beg the question, who will write it, support it and fix it when it needs to be changed?  Remember, even the best computer loving staff member could leave tomorrow.  Is there a better way?  YES!

Spreadsheets and Word Processors
With a faster machine and more RAM, the spreadsheet can be linked to tables or spreadsheets can be imported into word-processing tables.  These conversions or links work best with one location or one examiner.  Multiple locations can be difficult to consolidate if spreadsheet ranges, rows or columns change or if they differ from one another.  Lets look at the strengths and weaknesses of this approach:

Strengths	Weaknesses
Data is posted into a word processing document	Someone must program the transfer and subsection updates
Office prep time is reduced	Multi-examiner deals or multi divisional deals can be difficult to put together and may even take more time than before.
Data is linked into the word processor	Links would be lost if sub-directories change
Data posting saves time on simple exams	Data posting won't work well for consolidations
Data posting or linking insures accuracy	Data posting can be overwritten
Data posting can save time	Spreadsheet corrections must be posted again
Simple reports will work	Spreadsheets + Word Processors = Slow (too much overhead)
Complex reports can be programmed by MIS	The macro languages and VBA change every year.  Multi division or Multi Examiner loans are a hassle.
Staff members can maintain the system	Staff members and programmers can leave
Once trained, we'll save time	Complex systems require more support and training of "what not do or touch."
Analysis is summarized, less rework	Complex analysis requires programming and multi examiner or location loans may not be programmed.
Data is easy to enter and format	Users can enter anything, even kill formulas
Simple error checks can be devised	Invalid values can be entered, results are wrong

Who will maintain these links, programming requirements and support?  Who will do the training and convert this project to the next version of Office or Windows?  The truth is that even Microsoft has changed the macro languages to Visual Basic (more than once).  Tying applications together with Visual Basic for Applications (VBA) is possible, but who will maintain this, at what cost and at what level of speed and urgency?  The Visual Basic written today may not be supported tomorrow as we move into .NET programming and XML databases.  Even with Visual Basic today, it would require extensive programming to create consolidation routines to allow more than one examiner to combine data for a multi-location company.  The programming needs at this level are often in excess of the skill levels available from a typical field examination staff or MIS department's time allocation.

Staff members can and will leave and so will programmers.  When the brain trust leaves, where does that leave you?  Most lenders don't write back-office systems since the outside vendors (i.e., Stucky, Able, Distinctive Solutions) have got the systems wired.  Furthermore, few MIS departments can dedicate 3,000-4,000 hours to building a full-blown (first release) system for a single department of the bank.  Lets add in the time (say 500 hours) for the Field Exam department to have someone test the system and do layouts with the MIS people.  Want on-line help, add 200-500 hours.  Who does the support and training, the programmer (staff member, outside consultant) that just left for another job?

In-House Programming-Databases and Restrictions
Most of the bank programmers that we have met are deeply rooted in the client/server market defined by Oracle, MS SQL, Sybase, DB-2, Informix and others.  Database programs are fine for storing numbers, but they don't handle word processing and custom report functions as well as programming languages such as Delphi,  Power Builder or Visual Basic.  The database biased programmers at many MIS groups can kill a project from a lack of proper tools.  Want to get the right tools?  It may take extensive negotiating with the head of the MIS department to arrive at "no." 

Example:  We know of a bank IS department that developed a simple application with a simple word processor that needed a spell checker.  It took 2 months of research, trials, documentation, and meetings to arrive at the solution for the spell checker.  However it took another 60 days to get the requisition approved -- for a $260.00 purchase!

As above, when the brain trust leaves for that new job (maybe even the Audit Manager), where does that leave you and your organization?  The time needed to develop a database system, combined with the field examination staff time can be costly.  Customization?  Your bank format versus mine can cause a need for extensive modifications at an hourly rate that makes CPA's jealous.  

Perhaps the worst part of dealing with in-house programming is the delay in getting support.  Call the MIS help desk for a bug in Visual Basic and it could take days for your ticket number to come up.  These folks are busy and each project or request gets 100% of their attention -- eventually.

Custom Designed by a Software Company
We know of a lender that had an outside firm put together a full-blown system based on Excel and Word for Windows 3.11.  It cost over $100,000 in 1993.  The system was technically obsolete (in under two years) since the Windows-95/98 versions of those programs were not identical.  The system was slow, probably from the overhead of running Word, Excel , Access, and VBrun library at the same time.  We talked with the new audit manager (yes, the old one left) and he scraped it and wants to start over.  He MUST start over since the macros from Office95 don't translate into Visual Basic-6.  Note, this company just purchased 45 copies of our AssetWriter product.  As noted above, we know of one lender with an investment of $1,000,000 or more and that won't run on Office 2007/2010. 

Fortunately, the outside software company is free from the software platform and time constraints imposed by the in-house MIS director.  However, every change adds to the cost.  Want built-in help files...Add another 200 hours for the basics and up to 500 hours for something full-blown (just for help files).  Naturally, when Microsoft makes changes, you are left with outdated software, more costs and more work.  Incidentally, the turnover of employees at an outside Software Company can be high (average is @ 2-2.5 years, in some areas of the country just one year), making the future maintenance prospects even more problematic.  You end up with state of the art for a short period of time, but you end up with only about 80% of the features that you wanted.  The budget is spent (usually overspent) and you might need to "get back in line" for more budget dollars or MIS project scheduling.  Also, you might need to upgrade (start over) when Windows-200X arrives and the file format is changed or the feature set is enhanced and it is a 64 bit operating system, etc. 

Do you believe that the world is getting less computerized and that computer standards are carved in stone?  Do you believe, like we believe, that the computer world is moving toward data, databases and the ability to share information?  But, is there a way to keep up with the technology and not scrap the investment every 3-5 years?  YES, there is an affordable way.

DATA
Downloading data is a great time saver.  We have never seen an in-house system as detailed and refined as AssetReader.  From back-office or field examination ineligible calculations to AR ledger analysis by month, there is nothing easier or faster than AssetReader.  For importing a wide range of formats (Text, delimited text, PDF, Excel, RTF, DOC, HTML, and conversions to Text), AssetReader has more import abilities than anyone.  For ABL specific reports and ABL specific support, nobody beats FinSoft.  The best choice for ABL needs is an easy choice and we have the customer list that did the homework and testing to prove that point.

Future Expansion - The Audit of the Future
With up to date spreadsheets, macros, database designs and other enhancements, the level of complexity adds to the maintenance to keep the program running.   Old data may need to be rekeyed to new formats (figure 1-2 hours per division).   Add-in the cost of support to keep those fancy macros going as spreadsheets are modified and formulas are corrupted and the technical support staff person's salary and benefits should be added too.  Now look at new technologies such as download software (we produce AssetReader to download agings and other data).   Do these technologies work with your format?  Can you use these new technologies on most exams?  Can your existing system use these new technologies in an integrated way? 

What about the future?  We had a prediction in 2008 that by 2010, about half of the fieldwork analysis work would be done in the office.  Here we are at the end of 2012 and the truth is that the industry is not even close to that level  Spreadsheets are great at sorting data but poor at organizing database records from multiple sources (even if you an expert with pivot tables).   As data driven stores of information emerge, the ABL departments may someday harness some of the data for or from field examinations.  Recent inroads into XML and XBRL will clearly change the future and make data transportable.  Data that can be quickly moved from system to system, from department to department and from source to end user will ultimately cut time and reinvent the workflow process.  Will you need to adapt, convert and change; how often?  Can your software provider help, are they there or playing catch up too?  FinSoft understands the importance of technology updates and the products are always updated to be cutting edge.  But remember reality?  The truth is that reality is not like a prediction, it is much slower.

Planning for the future takes time, but technology changes at the speed of light, not the speed of Congress.  The questions raised focus on "Are you getting ready to get there and are you spending more than you should by not getting ready for the future?"  The competitive advantages of using data include knowing that reports are accurate, integrating software procedures, getting additional savings from future enhancements and more integration across the corporation.  In short, it means power and advantages for those that look for tools to enhance productivity.  Remember, the author of this White Paper has seen over 300 accounting systems and thousands of data reports, it is a mess out there.  Someday, you will do more than half of the work in-house and before gong to the audit.

Cost Analysis Worksheet for Field Exam Package
The worksheet below compares your costs against the investment in AssetWriter at 12/2012:

Description - Build It	Your Amount	AW Invested
Programming Time (Senior Programmer hours)		13,500
Programming Cost/Hour
Calculated Programming Cost
Audit Staff Administrative Coaching / Testing Time (hours)		3,500
Administrative Help File Time (hours)		850
Audit Staff Cost/Hour
Calculated Audit Staff Cost
Initial Cost Total

 

Description - Maintain It	Your Amount	AW Invested
Annual Maintenance Time (20 hrs/month) for 20 Users		240
Annual Maintenance Time (20 hrs/month) for More Users		240
Maintenance Cost/Hour		Included
Annual Maintenance Costs for 20 users		Included
Additional Annual Maintenance Costs for More Users		Included
Recruiting Cost of Programmer every 2.5 - 3.5 years		Included
Rewrite System every 3 Years for Changing OS or Needs		Included
Re-keying Data into new Template for Every Upgrade		Auto-Updated

The above maintenance costs for AssetWriter are doubled to reflect likely costs from non-senior programmers, help desk training, time needed to understand business rules by new programmers, etc. (we're ABL specific employee owned and have none of those situations).   Spreadsheet programming should consider 2 Times the expected maintenance time for recurring staff questions, errors, problems and formula fixes.  Training costs and learning curve would be approximately the same in either case.  Since we use only senior programmers with 25+ years of experience each, the above times would likely take longer with junior programmers.

Total Cost of Ownership (TCO)
This White Paper has more on the topic below, but a few words and diagrams here.  We prepared and have available a model that illustrates the time savings from automation.  If you have in house examiners and also outsource, the savings is even greater.  The in-house work generates profits for the audit department and less outsourcing means greater profits.  There may be competitive advantages for pricing deals with better automation.  We have been able to demonstrate payback cycles of 6 months to one year (unreal!) with no need to increase your staffing.  We have seen paybacks in one month due to time savings.  In one case we projected a 20% savings and the back-office department was decreased by over 50% for an annual savings of over $150,000, plus reduced recruiting expenses from reduced average staff size.  That is a negative TCO number and a payback (ROI) of one month for the company involved in this.  There is no model that is perfect, but calculating a range of possibilities makes sense and helps with the budget justification.

Time = Money = TCO
With the above information stated another way, what if the software was smart enough to not make mistakes and waste time?  What if the software was organized enough to make consolidations and multiple examiner engagements easier.  Given the high cost of examiners and other fidget factors, the TCO to produce an examination is 10%-30%  above what it could be.  Do factor-in any support costs that you now have, inclusive of maintenance, rewrites, debugging formulas, examiner's changing formulas for the next exam, re-exam setup time, etc.  Imagine saving 10% - 30% on every exam, for every examiner?  Add multiple divisions and the time savings are HUGE!

Joe's 80/20 Rule of Custom Software
This will sound funny, but the IT people that hear this and that have developed applications know that it is true, accurate and not funny:

We are not talking about a blend of Excel and Word, but a custom application that grows with your company and evolves over time.  That kind of application will last for decades and data can be carried forward for many years.

 

TRAINING

Costs and Time
One study on Total Cost of Ownership (TCO) by the Gartner Group noted that a trained person is 2-5 times more efficient on the computer.  The magazine Personal Computing has also uses similar statistics.  There is some fallacy in this, because skill sets are not used all of the time.  For example: Few people do mail-merges all of the time and few people sort spreadsheets all of the time.   However, a trained person is more effective.  Training costs per new person average 1-2 weeks of pay per year, plus travel costs, plus instructor/course costs; regardless of the system used.   All in all -- "not cheap," but unlike the fallacy noted above, we tend to use these skills over and over.

While spreadsheets offer ease of training, they do have advanced features that are often not used.  Give me Lotus 123 release 1.0 for DOS and I can get an examination done.  I may get it done faster than some Excel Macro packages, because I might not be chasing blown links and broken macros.  On the other hand, you may finish first if you know the new Excel system well.  A canned database that is not robust will have limitations and require too many workarounds.  We've seen some of these and they can be frustrating.  Can the software handle the exceptions for the user?  Training will address many exceptions and counter the "stuck" phenomenon.

In general with only one company (no divisions), training time on a custom system is the same as a macro-driven Excel system.  Additional users are trained in the field by peers and coworkers.   However, if a trained senior examiner is fidgeting with formatting, macros, print ranges, formatting lines, multiple examiner imports (cut and paste), consolidations that don't add up (oops, shouldn't have inserted that row), etc.  Time is wasted "fidgeting" on many exams.

DATA training by other vendors is quite poor for ABL needs.  We've addressed that with ABL specific examples and cases.  But more importantly, DATA training requires a broad spectrum of examples because there are so many different reports and report issues.  Negative signs in numbers on the right, DOC and RTF reports, Multiple entries for a single invoice, unaged credits, trapping the customer name rows in agings, capturing all affiliates, grouping all concentrations with similar names, Etc.  Because FinSoft is owned by the same people as Clear Choice Seminars, the training is quite focused on ABL needs and examples.  Again, the Web-based HTML5-based tutorials provide additional reinforcement for those in need after the training is done.

Since we offer a very robust and turn-key solution with our products, the training times have been similar to Excel packages, but the time savings have been greater for each exam since the fidget factors are gone and the examiners don't need to learn about macros, formatting tricks and other time wasting, "guru needed" problems.  Nobody can touch our product for accuracy, consolidation speed or importing from another examiner.  The TCO for spreadsheets is huge compared to a turn-key solution. 

For general DATA analysis we recommend training (see DATA Course), and the use of affordable tools that are well supported.

 

OUR  SYSTEM = INTEGRATION

Can't someone make field examination software that doesn't rely on spreadsheets or word processors?  Can't someone support what they sell with the same people, make constant enhancements over time and provide those updates?  Can't someone write a stand-alone program for ABL field examiners that doesn't rely on the bloated and changing combinations of Word, Excel, Access, VBA and OLE?  Can't someone develop a program that would allow user customized report formats.  Can't someone provide a reliable program with support, expert help systems, training and at a price that the ABL department can afford?   How about a system that is evolving and staying up with all the current standards?

Since 1996 the answer has been YES!  FinSoft, LLC has developed AssetWriter™, a full-blown, stand-alone database program for the Asset Based Lending field examination community.  Combined with AssetReader™, data analysis software, the field examination function is becoming creative, fun and efficient.  These applications are written is Visual Basic, by senior software engineers that have been rated at the "guru" level.  Our detailed help files are legendary (yes we wrote the acclaimed ABL-Help file). We constantly update the product with more features, the latest technology and great ideas from our users.  Those users have never had to rekey data to upgrade to the latest version, NEVER!   Our AssetReader™, users can export the downloaded data directly into AssetWriter™ for even greater time savings and direct synergies with back-office support that uses AssetReader™ for ineligible calculations.  Some shops even prep the audit package (database) with trends, concentrations, even sample tests from the office to save still more time.

FinSoft users are crazy about our systems and support.  They say they'll never go back to the old way.  We say, "thank you."  They say, "FinSoft rocks!"

Improve your future, budget dollars, reports and turn-around time with a software system that makes a difference.  Make your field exams fly!