Audit Management Workbook

This Excel spreadsheet is a tool for managing audits.

Overview
The three tabs to the worksheet are a planning calendar that indicates when audits are scheduled including the projected completion date, who the auditor will be and the date of actual completion. The second tab is an overview of the audit results and the third tab is a compilation of violations found that are tabulated and expressed in pie chart form by type and location. Tab 2 is meant to be used in a management recap report as are the pie charts. This is done automatically in a separate Word file report on the state of the audit program. Write once - read many.

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.

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.

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.

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.