I have a class to teach in a few months, basically on compliance management using technology. I believe Microsoft is the industry standard and I want your opinion, if you would be so kind. Feel free to add comments to this thread as well as reply to the poll.

I will focus on using programs like Outlook, XL formulas, Word macros, PowerPoint, etc. This will be on things like how to plan, train, create time saving spreadsheets in XL, tying Word and XL together for reports. And I am open to things you'd want to learn.

While I want the student to learn tips and tricks, I don't want it to be useless if it is specific to programs they don't have and couldn't apply to what they use.

Software products
I would be interested in MS specifically.
I would be interested in general techniques I could apply to similar programs.

I think this is a great idea. I think MS Excel is by far the most underutilized compliance powertool consistently available to most compliance officers. For quite some time I was pushing BAI to rent some computers at their ACE Conference and allow me to conduct a pre-conference workshop on Excel programming for compliance officers. My thought was that even those folks who had taken intermediate and advanced Excel courses still didn't get any practical tips out of those courses that allowed them to relate concepts they had learned to the practical realities of their day to day job. In other words, there was nothing to prompt them to see how learning about using the VLOOKUP function in Excel can allow you to build a Reg CC Calculator. Or how learning about using IF statements and macro buttons can allow you to build an automated loan compliance checklist. I felt the class would have been able to cover the rental costs of the PCs for the day. They always deferred because of the logistics involved. I do think it would be difficult to teach such a class without them having the ability to go through the programming steps in a hands-on environment.

Andy,

I attended a session you gave on Compliance Management (I think it was at a CBA conference about 5 years ago). I found it very useful when you described the daily routine you went through in obtaining info.

It's nice to have regulatory and other updates emailed.

My 2 cents.
Karen

I use Excel macros all the time. They are great for compiling all the monitoring review data from our 136 branches into one spreadsheet that goes to the board. I also, find hyperlinks very useful.

I submitted by vote (MS) but, since I can't attend your class, I would be interested in - perhaps through BOL - some tips....such as what you or Jim use VLOOKUPS to do, etc. so that, if it is an Excel procedure I am unfamiliar with I can learn it and put it to good use!

Don't forget Access, its not only a powerful database but report generator as well. In addition hooking Access or any other database to Crystal Reports is worth while.

Opinion:
The problem is everybody has jury rigged home made spread sheets and databases, which are cool, but they don't usually talk to each other, making for multiple entry and very labor intensive efforts.

Using a single database for all data and generating reports from it, resolves many problems and issues with data collection and analysis. Plus it decreases the skill levels needed and costs to purchase and run all the various programs in use.

I think that you are correct in your belief about MS. What about using PDAs as a compliance/audit tool? You seem to be very well versed in the subject.

Where's the class? I would love to find a class in automating workpapers, specifically with MS products!

Have you ever thought about traveling around to different parts of the U.S. to teach 1-2 day seminars on Compliance using technology?

It would be great to have a class like yours offered up here in the Midwest(around Chicago).

This will be at the Graduate Compliance School.

I appreciate the feedback. Keep it coming.

What about audit management using technology? I would love to "network" with other internal auditors regarding how they utilize the various applications (be it Excel, Word, etc.) in their audit function.
Who uses any of the applications heavily and in what respect as it relates to their audits? Could some of the techniques used in the compliance management function be utilized in the internal audit function as well?
I wish there was an avenue where the info. you'll be teaching could be posted somewhere on the BOL site.....hmmmmm?

The presentation will be the ABA's, but I have written similar articles in the past which are on BOL. It related to a "feature" which will work in any line, compliance, audit, etc. It addressed the ability (as I recall) to have a table of data in XL which turns into graphs and both the tables and graphs are automatically inserted into the Word audit report.

This enables the user to update XL, modify the comments in the report and print out a sparkling new report.

I guess I'll be meeting you in March (I'm assuming that was the school you were referring to), Andy! Any tips on Excel (which I love to play with) that makes the job easier will almost cancel out the cold, wet conditions that I'm expecting because of last year's weather. I'll warn you, though, if you make the job too easy I'll have to name a child after you.

Well if it goes the way it did for me, you'll make it look easy, you'll get the work of several people and there won't be much chance of any children.

I use Excel now, but I grew up on Quattro. By and large, 95% of what I learned in Quattro, I was able to apply to Excel with little difficulty. So focusing on the 'standard' applications seems like a fine approach, as there will still be enough practical information for users of deviant programs to make it worth their while.

Andy:

I have used many of the items you make available to us, such as the Reg CC Calendar, Policies, your Audit Calendar, etc. and adjust them to fit our needs. I can not tell you how much I have appreciated your help. Our staff actually requested an update on the Reg CC Calendar from me this year. It was great to think they actually had gotten to the point where many of them were using and depending on it.

As an auditor/compliance officer, I adjusted your Audit Calendar for both Safety/Soundess issues and Compliance issues to help me keep focused through my time here as an auditor/compliance officer. I am now switching "gears" back over to being a Branch Administrator, but your tools will still be needed by me to share with the branch and operational employees.

I guess the point I am trying to make is, it does not matter what position we work in at a bank. Compliance, risk, monitoring, etc. tools should be used and sought after in every position. It makes our lives easier and better, not counting the organization stronger.

While in the position of auditor/compliance officer over the last three years I have learned to become dependent on BOL users, including yourself, for advice and guidance. Not saying I did not verify the info or make my own decisions. It has been great! I have even found times when I could assist others. I plan on keeping the same relationship alive in my new position.

Again, what I am saying is that for us that have experienced the trials and tribulations of compliance, we need to keep reaching out to others within the banking community that work in various positions. I honestly believe that every person that is in a supervisory or management position of a bank should be required to attend compliance school and also assist the compliance officer for a while. It would make the views on compliance within an organization have a different aspect. I have seen it work in many situations within my own organization and I like it!

Keep doing what you are doing Andy! You are deeply appreciated by all! thanks

When is the next graduate school after March 2003?

The Graduate School is only offered each March in Indianapolis.

Could you do this class as a Bankers Online webcast? I use Excel very little and would love to know how to automate my spreadsheets and know what VLOOKUPS are!

If the technology allows and there is an audience, I'm sure Mary Beth will be on top of things and consider offering such a webcast.