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)
|
Humans still need to
investigate:
What do all the numbers
mean?
What are the exceptions and why?
Examiners spend tons of
time filtering through the numbers from the workpapers to make small
(intelligent and useful) summaries of the numbers and then talk
about them. |
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.
|
To save time you need
ABL Specific Software that has solved complex issues
To improve field exam time, you need both
ABL field examination software and data download software.
But, to improve the field exam content,
you need intelligent and expert automated analysis plus data
downloads.
Integration of the data analysis and
field exam analysis are even better. |
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:
-
Work with personnel to obtain skill
assessments for computers (there, I said it - You need skill assessments)
-
Train those that need more computer
skills with specific courses for their levels of assessment (Windows, Excel,
MS-Word, File Management, WinZip, Etc.)
-
Perform general training as much as
possible, perhaps one hour meetings (once a month would be 12 hours for you
mathematicians)
-
Build a library of magazine and web-based
articles for access by all
-
For specific software applications more
assessments are needed and the slower performers should be retrained at a
slower pace
-
Don't think that this will ever solve
itself. Managers are paid to deal with this, but not to ignore it.
-
Is this unspoken at your institution,
perhaps it needs another look?
|
If you are missing the
skills training...
What are you doing
about it? |
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:
-
Skill level of the computer software user
-
Complexity of the examination
-
The accounting system and how clean and
simple it is or (more likely with larger deals) is not
-
Number of Pages in the AR and AP Agings
for Ineligible Analysis
-
Number of pages in monthly sales, cash
and GL Journals used for stats
-
Whether or not there are multiple
divisions to consolidate
-
Whether or not there is more than one
examiner
-
Whether or not there is more than one
currency in use
-
Whether or not multiple examiners work on
one company or share work on any one division of a company
-
The extent of data downloads, if any
|
Do not underestimate
the above list, this is the core of
the time savings for field examination reports |
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:
- Who will
develop and support the project into the future?
- Can
an existing package do it all?
- Can
an existing package be customized to my needs for items that are not in the
package?
- Will the time
savings and quality benefits outweigh the costs over time?
- Can the
system produce reliable and usable results?
- How will
Outsourcing people get the software?
-
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 users don't leave
their jobs, except for promotions.
"Why would I want to work
for someone that does it the hard way" is what they tell us.
We say, "Glad to hear
that!" |
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.
|
WARNING.
Do not be fooled by any
company that hires a consultant to help them get your business.
You want a full-time employee and better yet an ABL industry insider
owner that will be there in the future. Ask questions about the
ABL expert and be wary of employees that are "new" or that could leave.
Be ready to run (don't walk) from the firms that hire consultants to
fool you into believing that they know ABL. Can they sustain the
support? |
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:
|
When you develop software
you get 80% of what you want for 100% of your budget.
The other 20% will take another 100% of your initial investment
WHY?
Because the finer points, the exceptions, the complex setups and the
revisions to workflow are highly detailed and costly to program. |
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!
|