Feel free to review and use these files. They are copyrighted and are "e-mailware". E-mail me and let me know what you think. That is what provides the motivation to produce new items and to improve the ones I have now.
If you have any problems downloading any of my tools, please let me know. You should be familiar with downloading and Zip compression/decompression tools. If you are not, check out this article Learn the basics for downloading applications from the Internet in these two Tech TV articles, Step One: How to Download and Unzip a File.
Andy Zavoina is a member of
Welcome compliance professionals. Remember being told to "work smarter, not harder". The purpose of this web page is help you do that. This is about using tools you already have, as productively and economically as you can, to automate your compliance management process.
The file you can view and download here is meant to help you schedule your audits, track and report them to senior management and finally, analyze exactly where, which regulations are problems for your staff. This will act as an indicator of training needs and, over time, provide a history denoting your progress towards eliminating violations.
You can follow two tracks here. 1) Look at the graphics, download the application and play with it. Your basic spreadsheet skills will allow you to customize this to meet your needs.
This spreadsheet is a three-tab workbook (described below). It is written to manage audit reporting for two banks, one of which has four regions. With basic editing skills you can make this workbook conform to your bank's structure by adding and subtracting branch references and regulations most important to you. This application is done in Excel. If you use Lotus 1-2-3 or another spreadsheet, you'll likely have to do some modifications.
2) You can read the information below on how I use this workbook as a compliance management tool and plan your customization. Since your bank is different from mine, you can use this workbook as a foundation to meet your management needs.
Download the Audit workbook here. (Download it, double click it and it will decompress. The resulting file is Excel.)
Audit Workbook - Tab 1
This is the Audit Calendar. The column titles are self explanatory. You may customize this and assign in any calendar month (column A), an audit subject (column C) for a specific period (column B). Consider your seasonal requirements such as an annual review of your HMDA LAR in January or February. You can also assign who will complete the audit (column E). Column F contains a formula. It is the start month from column A, plus your defined number of days to complete the audit. This allows the calendar month to be adjusted from year to year and the due date to change proportionately. I find this reduces the update maintenance from year to year. For scheduling you need to have target dates for completion and this is, after all, about automation, right.
Some audits may be completed at the bank level, and some at the branch level (columns G to J). I have filled in the cells that do not apply. As an example, the Bank Secrecy Act audit is done at the bank level in my banks. The branch is filled in because it does not apply here. However, Reg. B and Fair Credit Reporting Act audits are done for any branch with lending functions. This audit calendar is not designed to identify which branch may be audited, only that an audit is scheduled and at which level. This may be one of your desired modifications. The audit report date may be filled into the open cell, signifying completion. The calendar can be printed and used in a notebook for a hardcopy record.
Click here to see a larger scale image of the Audit Calendar.
Audit Workbook - Tab 2
In the Audit Log you list basic information which will serve as your senior management overview of audits completed. The "Print_Titles" field is defined so for each periodic report you only need to define a new "Print_Area". When printed, it may be attached to your periodic report to the board or audit committee. This should tie directly to the audit calendar and the lists of audits completed.
Click here to see a larger scale image of the Audit Log.
Audit Workbook - Tab 3
This is where the analysis takes place. Column A lists the common violations for two main audits I do, Regs. B and Z (and further down, other regulations). Feel free to list any common cites in this column that you want to watch. D7 to D26 are the "masters". The same cites for the other branches and banks below are an "=" formula, so if you change the master, the others will change as well. This is important because the columns across represent the month in which the audit was completed. In the Totals section, the citation and dates will come together to provide a count of the violations seen. With this information, you will know when the violations were seen and in what bank or branch. If an audit is repeated during the year you will see what progress was made. You will also see this from year to year. This table, and the other workbook tabs, need to be updated as you complete your audits.
After the main citations are shown for problem areas you'll get to the Totals section at line 173. There you will also find areas for listing the violation counts for other regulations you audited. These are all totaled at the bottom.
One of the "finish" features is that your numbers are automatically put into pie charts. There is one for violations by bank/branch and another for violations by cite.
Click to see expanded images.
Now the best part. This tip saves you time and provides the meat of your compliance report to senior management. It is very easy in Word and should work in one form or another with other programs. Link the Totals section (A173:I215) and the two charts to your word processor. Done correctly, you'll be able to update the text in your report on the first page(s) and have your violations table and charts automatically update on the final pages. The bottom line is that you may change a little text and print and entire report with quality information.