Access to the Max:

Using the power of Access to monitor, manage, manipulate, and interrogate information

Course objectives

Review the architecture of Access and the principles of good database design

Review and run queries in Access on real data using advanced query functionality in Access

Understand the principles of, and develop a complete system to continuously monitor a category of expense (or income) across hundreds of audit units using the statistical principles of correlation and regression.

Create a form and a report relevant to expense monitoring (of medium complexity)

Review miscellaneous functionalities of Access relevant to management and auditing in a government setting

Calling all

Financial managers, senior internal auditors, and external auditors that want their productivity elevated by using Access as a comprehensive monitoring and analysis tool.  The course will also be of benefit to IT support personnel that support the audit and financial management functions.

Seminar style

Attendees will receive 100+ pages of handouts summarizing the main points covered.  The handouts will include screen shots of Access steps needed to get the desired result.  Attendees will also be given four data sets and these will be used for hands-on work.  The seminar will include at least ten other Access-based files related to the topics covered.  Almost all of the seminar topics will be followed by hands-on work.  About one half of the time will be hands-on applications of the topics just reviewed.  The instructor will base the Continuous Monitoring module on original research.

Microsoft Access version

The handouts will show screen shots from Microsoft Access 2000.

Content focus

The seminar will focus on those Access tasks and functions relevant to auditing.  Throughout the seminar the instructor will refer to using the Access capabilities in an audit setting.  The audit setting will be relevant to both the government sector and private industry.

Course Outline:  Day 1

Introduction (participants and instructor)

The architecture of Access (review)

Review Database design (from the perspective of user/manager and auditor)

-          

Importing data into Access

Load seminar data and templates onto computers

Hands-on Case Study #1: NorthWind Traders

Hands-on Case Study #2: Data Import (Financial data and time series data)

Hands-on Case Study #3: Review accounting-related database templates available from Microsoft (free)

Lunch Break

Advanced Data Analysis using queries

Hands-on Case Study #4: Two straightforward queries using financial database

Hands-on Case Study #5: Append query

Hands-on Case Study #6: Prepare a Data Profile using a Union query

Hands-on Case Study #7: Identifying the Gaps in a sequence

Continuous Monitoring: Value-added approaches in 2004

Lecture

-          Brief review of Internal Control and the link to Continuous Monitoring

-          Overview of the Three-D approaches

-          Discriminant Analysis

-          Detection formulas

-          Digit and Number Patterns

Hands-on Case Study #8: Creation of a Detection Formula

For this setting the user can be either a manager reviewing budgets or an auditor reviewing budgets or actual expenses.  In step 1 we will create a benchmark of how we would expect the trend of an expense or income item to behave over the course of a year.  An example might be electricity usage for a school.  The benchmark might be average usage.  For example,

We will then develop a query in Access that will identify those units that have an actual pattern that deviates most severely from the expected pattern.  The case study will draw on the concepts of correlation (R-squared) from Statistics and linear regression.  Attendees will write the queries (no need to buy any statistical software) to identify those units with a pattern that deviates most aggressively from the expected pattern (i.e., the units with the highest negative correlations).  In this module we will develop a system so that the results of any unit can be queried and graphed with only a few clicks in Access to give a neat graph of the actual and expected expenditure patterns in Excel.

The case study will use real data.

Lunch Break (well deserved)

Creating and using Forms

Creating and Using Reports

Hands-on Case Study #9: Create a Form relevant to the Continuous Monitoring module

Hands-on Case Study #10: Create a Report relevant to the Continuous Monitoring module

Resources on the Internet

End of seminar and end of Day 2.

Seminar updated January 20, 2004.

Mark J. Nigrini Ph.D.

30 Greenwood Avenue, Essex Junction, Vermont, 05452

Tel: (802) 872-7877  E-mail: mark_nigrini at msn dot com