COVID-19 Remote Field Exams & Reporting

Data Downloads? Desktop Exams?

We need to do fieldwork without the field part. Hmmmm? Most of the accounting systems export data in several formats and most of the lenders doing Asset Based Lending are getting electronic files from their customers for AR and Inventory reporting to the Borrowing Base. Many field examiners are downloading reports and performing slow moving analysis, sales, cash, GL and related ledger analysis for Stats and other things. Most of the outsource firms are also downloading data for the field exam reports.

In this late-March of 2020 time slot, we can download and prepare over half of the field exam work and in many cased closer to 80% of the work. This is clearly needed now and it serves as a wise way to decrease travel expenses, travel time and to enhance employee well-being (less stress) for the future.

Let’s take a look at what we need to work remotely and to get a field exam done without the traditional on-premises visual inspections for things such as inventory counts, invoice-level billing tests, cost tests, check-flip tests, etc., what can we do?

If we hold off now, the backlog will pile up and impact our industry’s ability to get assignments timely completed. Offices, warehouses and plants are currently impacted (downsized, working from home, shut down).

Rich Denman Cascade Credit Services

Can Anyone Get In?

Once the Governors, Mayors, County Executives, etc. declare a lock-down, that means only essential businesses can open. That could be your Borrower; however, some businesses may be closed and will have access to only the on-line bank account information, credit card sales and possibly the books, records and payroll. This is going to mean limited data and some issues getting source documents. Still, half of not much is still something, and you should get what you can.

While the topic discussion is beyond the scope of this Blog article, the planning for resuming operations with fresh inventory, prior payable cleanup and payroll needs should be discussed early and revised often as more supply chain and cost data is understood. We might find ourselves in the company rebirth, emergency merger and restart business when the doors reopen.

Outsource Firms – Advertise It!

Putting this BLOG together, I asked our outsource friends to provide some insights and there was some feedback about this topic. A few providers showed that they are advertising with these points, including the capabilities to get data, spread numbers, calculate Stats, etc. More details are in the text below. If you are an outsource firm and you have not yet contacted your customers, now is the time to put together some marketing materials and personal assurances to show the Lenders what you can do.

Can You Get Downloads?

A quick guess here is that you probably download customer data right now as noted above in the opening comments. Lenders fall into four camps here:

Downloads for Operations and Audit – This is most of our software users. It is just more efficient to have everyone gathering data electronically. The Borrowing Base setups are particularly efficient to share by and between the Audit and Operations departments.
Downloads for Operations Only – This is generally Borrowing Base stuff, with some sales and cash journals for ABL lenders and Factors that get frequent reporting. The audit side is probably Excel only in this case.
Downloads for Audit Only – Outsource firms and some Lenders get the electronic downloaded data for some reports and some (like our customer) have invested in the tools to do this. Most of the field examiners can manipulate Excel files. Dealing with Text, Heavily report formatted Excel, HTML, DOCX, PDF, CSV and other files requires the right tools as noted below.
Don’t Download at all – Some of you need to modernize and save time, reduce fraud, become more competitive compared to your peers and otherwise automate and update. You know who you are.

Download – What do you Mean? – Reports provided by the Borrower such as AR Agings, AP Agings, inventory Reports, Sales journals, Cash journals, GL journal, Inventory usage reports, address lists, etc. can be sent to the lender via e-mail or through a web-portal. Examiners often get e-mail access while in the field and many Borrowers are comfortable emailing reports to the examiners.

The Report Variations – Based on the above split-outs you are somewhere in the process. Do you need to update and upgrade and get the skills to do this? We’ve seen in excess of 500 accounting systems and the report formats are all over the place. Sometimes the report are perfect Excel files with nice columns and repeating names and well formatted dates. Sometimes we get highly formatted Excel reports, PDF documents, incorrect negative signs that make all of the numbers positive and date formats from all over the world. We sometimes get DOC, DOCX, HTML, CSV and plain text formats. Clearly, you need to deal with the report variations.

Loan And Security Agreements / Forbearance Agreements – Since most people are getting customer files vie e-mail or web-Portals, there are no changes needed. On the other hand, if you don’t have this in writing but you need to modify your Loan and Security Agreement due to the COVID-19 situation, then by all means get electronic reporting into the forbearance agreement.

Outsource Firms for Field Exams – Time to ask what these firms can do. Time to ask about how they download data and what tools they use. We have a huge list of outsource firms that use our products at www.finsoft.net.

Getting the Right Data

Experiment Time – T H R E E Variations of O N E Report:

B I G piece of advice here… Ask them to send you: – ONE report such as the most recent detailed (invoice) AR Aging . – In THEE formats such as: – PDF – Excel – TEXT (e.g., PRN, RPT or TXT).

Your Borrowers will love you (ok, maybe just like you a bit) for being thoughtful. DO NOT ask for dozens of reports that might be impossible to use. This three report experiment will probably get you at least one that is useful. Once that is tested, then you can ask for more reports, and again, your borrowers will appreciate the time savings from this experiment.

Get The Data to Us:

Email or Web-Portal should do the trick. In some cases the Lender will need to allow certain things to come through the e-mail or bank firewall.

Cloud Transfer Methods such as Box, DropBox, GoogleDrive, iCloud, Sharefile (Citrix), OneDrive (Microsoft), and others could be used. Most banks don’t allow these (even though they have great security, SOC 2 audits, etc.). Ask IT to approve one. Use Secure e-mail in the bank that allows incoming file attachments. Sometimes the files are quite large and will need to be ZIPPED (we recommend WinZip) and even then, you might get a large file that exceeds the e-mail limits. Ask your IT department for a higher limit or another method. We MUST get the data to do our work and we are policing the collateral as part of what we do day-to-day.

Screen Sharing – Some of the items can be viewed on the computer screen of the Borrower such as G/L balances, observing the EFTPS payment history log and similar screen-share discussion points. Products like Adobe Connect, GoToMeeting, Microsoft Teams, Skype, ZOOM, WebEx, and others will let Borrowers share their screens with you.

Scheduling the time for such things and where several items can be discussed and cleared is a more professional approach. Your Borrowers might not be in the office, but can share accounting data via their VPN Link to the office.

Processing the Data:

You can only do so much with Excel because some are highly formatted and have the layout of a text report such as the Debtor Name appearing once above the invoice number, dates, transaction type, codes, etc. and for many invoices of that debtor. PDF files are common and problematic because there are hundreds of software drivers that generate PDF documents and some are not great.

You need the right tools and that includes ABL specific tools

Monarch – Over the years a lot of people have tried to use Monarch software for text reports with varying success. Most of those users dump the imported data to Excel and then write formulas to do things like add up columns, cross-aging, mark finance charges, Etc. Lots of formulas to maintain (and sometimes break) every month. This is a very hard way to group together all of the Walmart stores with Sam’s Club and to find Contras against AP. Exceptions like allowing Walmart to have 120 days and in this COVID-19 environment, allowing everyone to have 120, 150, 180 days form Invoice Date is still more work.

AssetReader – FinSoft, LLC produces AssetReader (with related AssetArchive and AssetArchive Confirmation software) that is able to read-in most report formats and is also ABL specific for both Operations and Field Exam users. Underwriting, Operations and Field Examination imports can include AR and AP agings (summary, detailed or unaged), Inventory, Inventory Usage, Sales, Cash, G/L, Customer Address and Vendor Address lists. This product produces the most detailed AR and Inventory ineligible calculations in the ABL business. Get STATS done in minutes. Get AR concentrations, group Walmart and Sam’s Club and find Contras between AR and AP with ease. Advanced trapping gets ineligibles at the transaction level such as Finance Charges and Debit Memos. Over 90 can be calculated four different ways (even at specific debtor levels) and much more. The user base includes some of the top ABL shops and many of the outsource firms too.

AssetWriter – FinSoft, LLC also makes AssetWriter field exam software that can consolidate multiple divisions with four mouse clicks and import from other examiners (single tests to entire divisions). While this might not be the best time to switch programs, it is noted here because of the speed, consistency, consolidations and import features that make a lot of sense, even now.

The Desktop Examination?

A shout-out to Cascade Credit Services and Cost Reduction Solutions for providing materials that went into this section.

This will be a modified scope field examination. Appropriate engagement documents with the outsource providers or internally modified procedures are recommended. It is not clear how the OCC will handle the scope of work done without some of the core evidential matters being investigated and audited. However, some form of monitoring will be better than none at all in the eyes of Internal Audit, the OCC and shareholders.

To the extent possible, the procedures that are waived should be documented, perhaps on a predefined form, so that this can be consistent across the portfolio and the waivers can be signed-off with proper credit authority to document the credit files.

Frequency of Work: Daily, Weekly, Per Borrowing, As Needed, Monthly, Quarterly, Semi-Annually, Annual
Timeline: All parties on board to get this done (Account Executive, Borrower, Examiner). Avoid stalling, require Borrower to cooperate to get things done on time and avoid excess idle labor.
Upload and Share Data: Basic reports – Email, Portal, etc. for Scope of Work items noted below. Test Selections that are available (scanned or PDF). Screen share what you can with the Borrower.

Scope of Trended Work

The sufficiency of procedures is up to the Lender’s credit and field examination teams and possibly centralized risk management and credit above the department. For outsource firms, the procedures should be dictated by the Lender to you in writing and since the lender dictates the procedures, you should not make statements about the sufficiency of those procedures.

This blog wording and the authors make no statements or recommendations about the sufficiency of the procedures for Trends, Substantive Work, Alternate Procedures, Targeted procedures or Other ideas noted below and what follows are possible ideas only.

Financial Statements: For write-offs and general sales and profitability and financial spreads for current vs. prior same periods and Year-end.
Borrowing Base: Ongoing reporting of AR, AP, Inventory and other Borrowing Base roll-forward numbers. Reconciliations can be done by borrower.
Accounts Receivable: Summary agings for trends (Lender might have these). Most recent aging for Concentrations, number of invoices, average balance. G/L Data for changes in Allowance account. Data for Write-offs (see financials above). Since last exam – Billing Journals, Cash Journals, Adjusting Entries for AR accounts. Month-end G/L balances for AR. Credit Memo Testing – See Substantive Testing below. Billing Test – See Substantive Testing below. Verifications – See Substantive Testing below. AR Cash Application – See Substantive Testing Below. All Bank Accounts – Get G/L Listing. Reconcile cash sources to Lock-Box/Sweep as from AR Stats or AR stats cash receipts to operating account. Reconcile cash sources into Operating as coming from Loan Advances. Reconcile other deposits into other accounts as being sourced from Operating Account.
Inventory: Inquire into general changes in reserves, full physical dates and variances, average Cost Per Unit, warranty period, last appraisal date. Data to list Concentrations by type and or Location. Data for Trends of Raw, WIP, Finished, Other or any other classification used for trends. Since last exam – Purchase Journals and Cost of Sales and related G/L activity and Adjusting Entries for Inventory accounts. Month-end G/L balances for Inventory. Count and Cost Testing – See Substantive Testing below. In Transit items – See Substantive Testing below. Gross Profit – Download All Sales and COGS if possible to pull 100% test and not a sample. If pulling a selection, see Substantive Testing below. Backlog and Open Items – Data for Trends. Might want to add this for more Borrowers to help plan for lack of orders or need to fill orders. Data for Past-Due Backlog. Standard Cost variances for a period of time that covers the inventory turnover cycle.
Fixed Assets: Get Appraisal report. See Substantive Testing below.
Accounts Payable and Liabilities: Summary agings for trends (Lender might have these). Most recent aging for Concentrations, number of invoices, average balance. Since last exam – Purchase Journal (see Inventory above) and Disbursements journals, Adjusting Entries for AP accounts. Month-end G/L balances for AP. Detail of Accrued Expense items. List of Leases and monthly payment schedules. List of Notes Payable and monthly payments (G/L for balances). Copies of Payroll Register summary page for each pay period. Proof of Payment of Taxes from bank account or use screen-share to view EFTPS summary for Federal taxes (US). Check Flip – See Substantive Testing below.
Contracts: Changes, new or if new business this could be all significant contracts.
Insurance: Copy of last bill and trace to payment (have them look-up the vendor in the accounting system to get the payment dates, check numbers, etc).

Scope of Substantive Work

Some of this might get waived and that depends on the ability to get the data from a closed business or quarantined city or other conditions. Risk based thinking about collateral, the capital base, character of the Borrower, the capacity to pay and conditions (market, legal, safety) should be considered if conditions are safe enough to provide some of this information on-site. The authors of this Blog recommend safety first. To the extend possible consider known internal controls such as segregation of duties, cycle counts (if they are still doing them) and a lack of nepotism as better controls and mitigating circumstances. To the extent possible, select the desired items and provide several or all of these at once so as to avoid confusion (“Oh, there was another email?”) and so as to avoid being annoying. For on-site work done by the Borrower, consider weekends and evenings to avoid human contact and reduce contagion risks. Where test counts, fixed asset counts or similar visual inspections are needed, this must be planned and agreed to in advance. Good lighting (perhaps the cell phone LED light and a flashlight) with well organized work instruction (a task list), will make this go faster. For each item below, select items and prepare for sending. Request what item are to be copied and sent back to you.

Receivables: Credit Memo Testing – Select, prepare document copies, return to you. Invoice Selection – Detailed recent aging for Borrowing Base (above) and invoice selection from this report. Select, prepare document copies, return to you with shipping evidence. Invoice Test Delivery Evidence – Lookup delivery evidence on line where possible (i.e., FedEx, UPS, etc.). Verifications – Get correct addresses from invoices. Lookup phone numbers where possible. Do deeper confirmations such as asking about quality and quantity received and if there is any reason to believe that payment will not be made in full. Ask about payment timing due to current economic conditions in general. Cash Testing – Get bank statements from bank. Reconcile lock-box/ sweep account, Operating, Payroll and other accounts for source of funds.
Inventory: Test Counts – Visual inspection with cell phones. Third Party Public Warehouses (negotiable warehouse receipts) – Get warehouse name, address, any confirmation numbers of held receipts, etc. Look-up the number to call. Inquire about the expertise of the company warehousing. Get confirmation sent to you directly if possible. Not sure, ask more questions and seek more information about the warehousing company, including their Surety Bonding, training, etc. Cost Test items – Select, prepare document copies, return to you. In Transit Inventory – Obtain list of items with detailed shipper information, order numbers and other data needed for confirmation. Make calls as needed. Gross Profit Test – If selecting items (not a 100% test), you are looking for Lower of Cost or Market issues and thus the Billing Test Invoices could be used to get the selling prices. Then pull only the cost data needed for specifically selected items. Select, prepare document copies, return to you.
Fixed Assets: Use appraisal report for cell phone video for visual verification that the item exists and is not dismantled and that it is working if possible.
Accounts Payable: Check Flip – Use bank statements. Select, prepare document copies, return to you.
Reconciliations other than Cash: Borrower to prepare Borrowing Base to AR and Inventory reports. Borrower to prepare AR Age to G/L to Financials. Borrower to prepare Inventory Report to G/L to Financials. Borrower to prepare AP Age to G/L to Financials.

Alternate Procedures – Field Exam

The list of Substantive Testing Items above could be waived by appropriate credit procedures. Still, some items could be done with alternate procedures:

Receivables: Billing Test – Subsequent cash receipts, possible from Lock-Box images. Billing Test – Confirm by telephone (assuming the company is open). Cash Testing – It might be your bank and thus the statements could be available. Cash Testing – Get access to the on-line account, perhaps with screen sharing.
Inventory: Cost Test with screen share
Accounts Payable: Check Flip – use screen share
Reconciliations other Than Cash: Use screen Share

Targeted Substantive Selections

Focus on invoices that are from new debtors (new customers), large increases in invoice size, large increases in debtor balances, man invoices, round dollar amounts and other conditions can be flagged electronically with time-series analysis. FinSoft, LLC produces AssetArchive to identify these issues with per borrower settings and an optional targeted confirmation module to focus on the most likely problem balances and invoices. Focus!

Alternate Procedures – Credit

We’re working with a vendor to gather credit information electronically. This includes direct links to bank accounts, credit reports, spreading financials from the Borrower systems, underwriting faster and with great automation. If you are interested, just contact us from the FinSoft, LLC website.

Other Reporting

The Account Executive / Account Officer can pave the way for this to take place. Good cooperation and communication should be in place by now (see prior Blog) and we’re all trying to keep these companies going.

Some of this information can be requested by the the Account Executive / Account Officer along the road. There is no reason to wait for a field examination to verify and confirm larger invoices, new customers, unusual things on the Borrowing Base Etc. Most of this data can be requested between exams.

Did We Say Desktop?

A desktop Exam? If the doors are open and data is available, more like an exam really. Lender management priorities and concerns will dictate much of this. Is the business even open? Some of this is going to be a bother to the Borrower at a time when they might be busy or can’t access the building. Some of this is going to take well organized lists and question sets and emails. Stay safe, plan well, and be outstanding in your field of work.

March 30, 2020
Joseph Caplan, CPA, Managing and Creative Director