The objective of this introduction is for students to be able to gain access to and perform some basic functions using the ACL software. This assignment is written as a tutorial to help guide your initial experience using the software.
Now that you have access to the ACL software, manuals, and files, you should review the terms below and try each exercise below.
Project — File in which ACL stores reference information about data files. ACL projects have a file extension .acl.
Data file —Computerized information that you work with; a client’s inventory file, for example.
Input file definition — Used to describe the structure and content of a data file. A definition includes field names, data types, where each field starts, the length of each field, and so on.
View— Displays the data in the input file according to the way that you define its fields. Each row of the view is a record in the input file and each column is a field.
Project Navigation window — Provides an easy way to manage all the components of your project. It lists all of the batches, input file definitions, views, workspaces, and indexes created for a specific project.
Command log window — Contains a record of all commands that you issue and the results. The information in the log is cumulative. ACL command logs (or simply logs) have the same name as the project file but have a file extension .log.
Manual- This refers to the ACL Getting Started Manual that is available on the VLAB. It is one of three manuals available and it is the primary manual for this assignment.
Basic ACL functions
Perform each exercise in this section to get familiar with the ACL software and imagine how you would use each ACL function to perform some audit procedures.
Part 1: To open an existing project:
Select File on the menu bar and choose Open Project (or click the Open an Existing Project option from the center of the ACL Welcome Screen).
Select the directory and folder in which your project is located and the project file within that folder.
Click on Sample Project.ACL.
Scan the window to determine the names of the icons on the toolbar. To see an icon’s function, place the cursor over that icon until a one or two word description appears.
After you open the project “Sample Project.ACL,” a new window should open entitled “Project Navigator” to the left side of the screen (“+” signs to the left of the objects, indicate the object can be expanded into more options or tables). The View window to the right should still have the ACL Welcome Screen displayed
Open the inventory file by clicking the “+” next to tables, and then the “+” next to “inventory review”. Open the Inventory table listed directly underneath the Inventory Review folder. You can do this by doing one of the following:
double click the file,
drag the file from the Project Navigation window into the view window, or
right click the file and select “open”.
Look at the bottom of the Project Navigator window and click on the Log tab. The Log keeps a record of all your actions in a brief list. You should see an entry indicating you have opened inventory file accompanied by a green check mark.
Double click on the checkmark and the Command Log will fill the view window with an entry indicating that you have opened an inventory file containing 14 fields. Close the log entry in the view window by clicking the “x” in the top right corner of the view window returning you to the inventory file display.
To Print the log:
Go to the Log tab and make sure all lines of the log are selected (you will see an “x” next to all selected lines
Right click on any line and select “save selected items”
Export to a WordPad
For ease of formatting it is best to subsequently copy the text from WordPad to an MSWord document. Format appropriately so that the work is easy to follow.
Part 2: To close a file, do either of the following:
From the Project Navigation window right click the file you have open in the view window that you wish to close and select “close table”. The input file closes. The Project Navigation window remains open, allowing you to access other files.
Click the X in the upper right corner of the file’s view window.
Exercise 2: Close the inventory file. Return to the Log and notice that the close command is added to the command log window. Also notice that the log, similar to the Project Navigation window, can be collapsed and expanded. This allows the log to remain brief and broad, but also allows for the user to decide which details they would like to view by clicking on the “+” sign to display the detailed commands under each heading. Collapse the item under “Open Inventory” by clicking on the “-“ sign next to it (notice it becomes a + sign), then click the “+” next to “Open Inventory” to break out the command(s) related to “Open Inventory”. This should provide you an understanding of how the Log works.
Part 3: Comments can be added to the log at the auditor’s discretion. This allows the auditor to place his or her own reactions, observations, and any other pertinent information that he or she deems necessary for others to understand the work performed. To add comments to the command log:
Select Tools from the menu bar and choose Add Comment.
Type your team members’ names in the comment text box.
Go back to the log and locate the item “Comment” and double click on the green check mark to view your comment in the view window.
NOTE: When you open an existing project, the current activity for that project is added to the previous activity in the log. Commands in the log are organized chronologically, with the most recent being at the bottom of the log. You can review the commands that have been issued, look at command results, add comments, print the command log, or clear the log. The audit trail in the command log keeps a record of the activities (e.g., audit procedures), results, and the auditor’s reactions, analysis, and conclusions. Thus, they comprise an important element of the auditor’s workpapers.
Part 4: You can create different views for each input file. Each view can be formatted without changing other views of that input file. For example, you can have one view of an inventory file that has the item number displayed in the left column and another that has the warehouse location displayed in the left column. There are many things that you can do to customize the view, including:
To move a column in the view window:
Point to the column title (ACL will highlight the entire column). Click and hold down the left mouse button. The pointer becomes a hand icon.
Drag and position the hand to the new location for that column.
Release the mouse button.
To delete a column from the view window (this does not delete the column from the input file definition and so it can be restored later):
Point to the column title (ACL will highlight the entire column).
Click the Remove Columns icon on the toolbar at the top of the screen.
ACL will ask, “Remove (name of column)?” or “Remove selected columns?” Click [Remove].
To add a column to the view window:
Click the Add Columns icon in the Toolbar. ACL displays the Add Columns dialog box. The Available Fields box lists all the fields defined in the input file definition.
To add fields to the Selected Fields list, you can [Add All], or select one or more fields and click [-->].
You can remove fields from the Selected Fields list in much the same manner as adding them, [<--].
Click [OK] to apply your changes. New columns will be added to the right side of the view or to the left of a column that you have selected in the view.
You can use the Modify Column icon to change column titles, numeric formats, and column width.
You can print a view of the records in the input file to the printer.
Select the Report icon in the Toolbar. ACL displays the Report dialog box.
Select the [Output] tab and select the [Print] radio button. Click [OK]. ACL displays the Print dialog box.
In the Print Range you can select [All] or [Pages] (to print one or more pages).
Click [Setup] to change paper size and print orientation and click [Print].
You can print a view to the screen (and therefore to the command log).
Select the Report icon in the view window. ACL displays the Report dialog box.
Select the [Output] tab and select the [Screen] radio button. Click [OK].
ACL “prints” the input file in the view window. Examine the Log and notice a command is added for the generation of the report. During your session, you can refer back to the log, double click this command line, and the report will once again be displayed.
Exercise 4: Open the table “Sales_Reps” by drilling down the “Payroll Analysis” item (by pressing the “+” next to it). The Log should indicate 8 fields activated. Reorder the columns so that Last Name is the first column followed by First Name and then Sales Rep. Number. Delete the Address, City, State, and Zip Code columns from the view. Change the name of column “YTD Sales” to “Year-to-Date Sales.” Print the file to the screen.
Part 5:To understand data within a file you must first open the file to be analyzed and select Analyze from the menu bar at the top of the screen. The analyze drop-down menu displays a number of options, including:
Count – counts all the records in the file. This feature is also available via the Count icon on the menu bar.
Total – displays the total of numeric fields. This feature is also available via the Total icon on the menu bar.
Statistics – provides an overview of descriptive statistics for numeric fields. This feature is also available via the Statistics icon on the menu bar.
Profiling – provides total value, absolute value, minimum value, maximum value on one or more numeric fields in an input file.
Exercise 5: Open the table ‘ap_trans’ under the accounts payable option on the Project Navigation window. Total the invoice amount field. If you complete this correctly your command log should have the following 2 entries:
Command: OPEN Ap_Trans
15:09:03 - 07/07/2005
7 fields activated
Opening file name Ap_Trans.fil as supplied in the table layout.
And… As of: 07/07/2005 15:10:19
Produced with ACL by: Bentley College - EDUC, Waltham, MA
Command: TOTAL FIELDS Invoice_Amount
Part 6: You can create a filter to analyze the portions of an input file that meet certain criteria by making use of the [If] option. Complete exercises 7a, 7b, and 7c to understand the [If] option and the expression builder.
Exercise 6a: Open the inventory file and count the number of inventory items in location “05”. To perform this function you must create a local filter to make it easier to work with only records for location 05. To do this:
Select Analyze from the menu bar and choose Count to open the Count dialog box.
Click [If] to open the expression builder (labeled “Count: If”).
In the Available Fields list box, double click Location. Location is copied to the Expression text box.
Click the “=” sign operand in the set of operands in the center of the expression builder. The equal sign is copied to the Expression text box beside Location.
Type "05" (the quotes are required) for location code 05 beside the equal sign in the Expression text box.
Click in the [Save As] text box and type Location 05 to name the filter (ACL will fill in the space with an underline giving us “Location_05”.).
Click [OK] to save the filter and close the expression builder. The Count dialog box reappears.
Click [OK] in the Count dialog box to issue the Count command. The command log window reports that 13 met the condition.
Exercise 6b: The Count with the [If] option gives us the number of records in Location 05 but does not identify those records. To create a view containing only those records, click on the Edit View Filter icon toward the top right hand corner of the view window (the icon has an f(x) symbol in it). ACL again opens the expression builder (this time it is labeled “Edit view filter”). Double click on the filter Location_05 in the Filters list box (this brings the Location_05 filter to the Expression box) and click [OK]. The view now contains the 13 records in location 05. “Print” this reduced view to the screen (and therefore creating an entry in the command log).
Exercise 6c: You can create views using multiple criteria. Release the Location_05 filter (click on the Remove Filter (Red X icon) at the top of the view window). The view should again contain 152 records. Click on the Edit View Filter icon and again you will see the expression builder. Again double click on the Location_05 filter to bring it up to the Expression box. Single click on the operand AND in the middle of the expression builder, double click on QtyOH in the Available Fields list box, single click on the operand <, and double click on MinQty in the Available fields list. Click [OK]. The view now contains the 4 records. Execute the Count command. The following entry should appear in your command log:
Location_5 AND QtyOH < MinQty (4 records matched)
4 records counted
Part 7: You can extract records from a file and store in a new file. You may want to select (extract) specific records from a file in order to isolate unusual items for separate analysis and to work on these selected records in a smaller file. NOTE: This differs from exercise 6a—with extract we are creating a new file (we could accomplish the same by saving, with a new file name, the filtered view from exercise 6a). To extract data from a file you must first open the file and then select Data from the menu bar and then select Extract (or you may click on the Extract icon on the menu bar). You may extract entire records or selected fields. Use the [If] option to define the criteria for selection of records to be extracted to a new file. The extract command creates a new file and leaves the existing file unchanged.
Exercise 7a: Using the inventory file opened for exercise 6 (be sure all filters are removed), create a new file with a copy of the records for the inventory items located in location 05. To do this:
Select Data from the menu bar and choose Extract Data. The Extract dialog box appears. Leave the Record radio button selected (as it is by default) to extract all the records for the one warehouse.
Click [If] to open the expression builder.
Double click Location_05 in the Filters box. Location_05 is copied to the Expression text box.
Click [OK]. The expression builder closes. The expression Location_05 is now in the [If] text box.
In the [To] text box, type Warehouse05 to name the output file. ACL will create an input file definition with this name. Note that the Use Output Table checkbox is checked. When ACL executes the Extract command, it will also open the new input file definition in the view.
Click [OK]. The results of the extract appear in the View window and as an entry in the command log.
Click back to the Project Navigation window to display the list of tables. The new definition, Warehouse05, is in the list under Inventory Review.
Exercise 7b: Calculate the total market value for the items in location05 (warehouse 05). The total should be 52,752.62.
Exercise 7c: Verify that the total market value for the items in location 05 is correct by obtaining the same total directly from the inventory file. HINT: Open the inventory file and select Total. Put the filter “Location_05” in the [If] field (either enter directly or go to the expression builder screen). Make sure that you total the market value column. If the totals are the same you can be sure that the extracted file is complete.
PRINT OUT THE COMMAND LOG FOR EXERCISES 1-7. To Print:
Go to the Log tab and make sure all lines of the log are selected (you will see an “x” next to all selected lines
Right click on any line and select “save selected items”
Export to a WordPad
For ease of formatting it is best to copy the text in the WordPad to an MSWord document. Format appropriately so that the work is easy to follow.
Part 8: For the previous exercises, you already an ACL file to work with. In an audit, someone on the audit team has to convert the client’s data into an ACL file. This is frequently a two-step process. First, the client’s accounting data is exported into a “flat file,” which is a simple two-dimension table. [These flat files have the extension .fil.] Usually, the client personnel do this because auditors are not allowed to touch “live” systems. The second step is for the auditor to take these flat files and convert them to ACL files. The exercise below is an example of converting a flat file to an ACL file.
Exercise 8: Create an ACL input file definition for the file Month2_Invntry.fil. This file is located in the Sample Files Folder. You will save this file under the name “Prices.” Your file definition should have only five fields: “PRODNO”, “PRODDESC”, “LOC,” “SALEPR,” and QTYOH.” The formats for all the fields in the file “Month2.Invntry.fil” are as follows (notice that you will only create definitions for five of these fields):
N Unformatted (2)
N Unformatted (2)
Quantity On Hand
Quantity On Order
Inventory Value at Cost
1 These are names that you should assign as you import the file.
2 A/N = alphanumeric (use data type ASCII text). N = Numeric (number of decimals)
3 ZONED data type is numeric with the sign (+ or -) stored as a special final character
**This is part of the product number, it does not need to be separately defined as part of this assignment.
1. Select Data from the menu bar and choose External Data followed by Disk.
2. Select the Month2_Invntry.fil data file. Click [Open].
3. The next three screens are part of the “Data Definition Wizard”.
On the Character Set screen “PC and all other types of computers (ASCII)” is usually OK. Click [Next].
On the File Format screen “Other file format” is usually OK. Click [Next].
On the File Properties screen the selections should be correct and you should click [Next] to continue.
4. In the Define Fields/Records section of the wizard you will see the following screens:
On the File Type screen “Data File (Single Record Type)” is typically OK. Click [Next].
5. On the Identify fields screen you should create, delete, or move field separators as needed and then click [Next].
6. In the Edit Field Properties section of the wizard you name the fields in your input file.
7. Type the name of the field in the Name text box. You may also type a field name in the Column Title box. The column title is the one that you will see when you view or print the file.
8. If necessary, change the Valid Field Type designation by double clicking on an alternative field type (in the TYPE drop-down box). For certain field types it may then be necessary to define the number of decimal places, the format for the date field, etc. Be careful here. ACL may default to the incorrect field type. For example, ACL assigns Numeric (formatted) to the Product Number field in the Month2_invntry.fil file. This should be changed to ASCII text.
9. Click (Ignore this field) if a field is not to be part of this input file definition.
10. Move from field to field repeating 7) and 8) or 9) for each field in the record.
11. Click [Next] when you have named (or skipped) every field.
12. ACL will then open the “Final” screen. Check your work. If necessary, select [Back] and correct your errors. After you have reviewed your work, click [Finish] and:
13. Type a name that describes the input file definition. Click [OK].
After you have created and saved this new input file definition, “print” the file to the command log. Make sure that there are complete titles for each column. Part 9: You may use ACL to sort a file into an ascending or descending order based on specified fields. The sort command creates a new file that has been reordered. The existing file remains unchanged.
Exercise 9a: Sort the ap_trans file into vendor number sequence leaving the sorted file in the file SORTVEND. To make sure that the sort does not corrupt the file, total the file on the Invoice_Amount field before and after sorting. To do this:
Open the ap_trans file.
Select Analyze in the menu bar and choose Total Fields to open the Total dialog box.
Select the Invoice_Amount field from the [Total Fields] list.
The control total for the Invoice_Amount, 278,641.33, appears in the view window.
To sort the file on a single field, choose Data from the menu bar and then select Sort. ACL opens the Sort dialog box. Select the Sort On Button
Select Vendor_No from the [Available Fields] list and click the to move it into the [Selected Fields] list. You will see an arrow next to list in the pointing either up or down, reflecting whether the list is to be sorted into ascending or descending order, respectively. Sort this record in descending order.
Now move the Invoice Amount into the [Selected Fields] list and sort this record in descending order.
9. Click [OK] to return to the previous window and click in the [To] text box and type SORTVEND, the name of the output file. Click [OK].
10. To verify the total for the new file, issue the Total command again as described in steps 2 – 5 above. Compare this total to the one obtained before the sort. The totals should be the same.
Part 10: Use the Join command to combine two files such as a payroll file and an employee master file. This would be necessary, for example, to calculate payroll and produce paychecks or to test to see if there are any payroll transactions for anyone not on the employee master file. Before you begin the join operation you must first decide which of your two files is the primary file and which is the secondary. As a rule of thumb, the larger file or the transaction file (or transaction-based master file such as payroll, accounts receivable and accounts payable) would be the primary file. Master files such as customers, vendors, and employee master files are usually secondary files. These general rules may change depending on the purpose of the test. For example, in the exercises below, the employee master file is a primary file for one test and a secondary file for another test.
Choose one of the five output options to determine which records the join command will include in the output file:
M atched records: will include only those records for which a matching key characteristic (e.g., employee number) is found in both the primary and secondary files. This is equivalent to the relational operation intersect.
NOTE: The fields contained within each output file record will depend on the fields selected during execution of the join command.
Matched records – all primary: will include matching records (i.e., those records for which there is a matching key characteristic) from the primary and secondary files and unmatched records from the primary (records in the primary and not in the secondary).
NOTE: The output file will contain a record for each record in the primary file. The fields in those output records will contain those fields selected during execution of the join command. When there is a matching record in the secondary file, the output record will contain selected fields from both the primary and secondary records.
Matched records – all secondary: will include matching records from the primary and secondary files and unmatched records from the secondary (records in the secondary and not in the primary).
NOTE: The output file will contain a record for each record in the secondary file. The fields in those output records will contain those fields selected during execution of the join command. When there is a matching record in the primary file, the output record will contain selected fields from both the secondary and the primary records.
Matched records – all primary and secondary: will include all records from both the primary and secondary. This is equivalent to the relational operation union.
NOTE: The output file will contain a record for each record in the primary file and a record for each record in secondary file except when there are matching records. In that case there will be one record containing selected data from both the primary and the secondary records. When there is no matching record in the secondary file, the output file will include a record containing only selected data from the primary file record. When there is no matching record in the primary file, the output file will include a record containing only selected data from the secondary file record.
Unmatched records: includes only unmatched records in the primary file (i.e., records for which the key characteristic exists only in the primary file). This is equivalent to the relational operation subtract.
NOTE: Output file records will not contain any fields from secondary file records.
Exercise 10a: Produce a file containing all matched records from both the employee master file and the payroll file. This is the Join command with the output option matched records. If there is a record related to an employee that is not in both files, it will not be included in the output file. You must sort the secondary file in ascending sequence on the key character fields (e.g., employee number) before joining. To join all records from the primary file which match to records in the secondary file (e.g., matched records) perform the following steps:
Open the secondary file (Payroll) to determine that it is in EmpNo sequence. It is. NOTE: Use Analyze, Sequence.
Open the primary file (Empmast).
Select Data from the menu bar and choose Join Tables.
Select the secondary file (Payroll) from Secondary File window.
In the [Primary Keys] list box select the primary file’s key field (EmpNo).
In the [Secondary Keys] list box select the secondary file’s key field (EmpNo).
Click the [Primary Fields] button to get to the Selected Fields screen. Here you select those fields from the primary file that you want included in the joined file. Select EmpNo, WorkDept, Pay_Per_Period.
Click the [Secondary Fields] button to get to the Selected Fields screen. Here you select those fields from the secondary file that you want included in the joined file. Select EmpNo and Gross_Pay.
Click on the [More]tab to see that the [Matched Primary Records] radiobutton is selected and that neither [Include All Primary Records] or [Include All Secondary Records] is checked.
Click the [Main] tab.
Type an output file name (Matched) in the [To] box.
Click [OK]. As a result you should see the following in your command log where it indicates that records were joined (the line “Extraction to file…” will depend on the path to your Project file):
16:36:59 - 07/13/2005
Field 'EmpNo' renamed 'EmpNo2' to remove name conflict
41 records produced
1 records bypassed
Extraction to table C:\ACL DATA\Sample Data Files\Matched.FIL is complete
Opening file "Matched"
Exercise 10b: Repeat the prior exercise, but with Payroll as the primary and Empmast as the secondary. Name the output file Matched2. You should get 42 records with 2 bypassed.
Exercise 10c: Repeat the first join exercise again, but with both [Include All Primary Records] and [Include All Secondary Records] checked on the [More] tab. Name the output file Matched_Both. As a result you should see the following in your command log:
16:42:50 - 07/13/2005
Field 'EmpNo' renamed 'EmpNo2' to remove name conflict
45 records produced
1 PRIMARY records unmatched and written with nulls
3 SECONDARY records unmatched and written with nulls
Extraction to table C:\ACL DATA\Sample Data Files\Matched_Both.FIL is complete
Opening file "Matched_Both"
Please add to the command log your assessment of the reasons for the four unmatched records in this exercise. Use a comment to indicate this in the command log. To create a file of primary records that does not have matching secondary file records (e.g., unmatched records):
Open the primary file.
Select Data from the menu bar and choose Join Records.
Select the secondary file from Secondary File window.
Select the primary file’s key field.
Select the secondary file’s key field.
Select those fields from the primary file that you wanted included in the joined file.
Click the [More] tab and click the [Unmatched Records] radiobutton.
Click the [Main] tab.
Type the output file name in the [To] box.
The Merge command is used to combine two files that have identical record structures into a third file. Both files must be sorted in ascending sequence on the key fields before merging.
Part 11: You can create expressions with specific parameters. Expressions are text strings that you can create to apply to data fields or records to perform a variety of tasks, and to test for true or false conditions. Expressions range from a simple multiplication of a field value by another field value, to a complex string of calculations. An expression can be a combination of data fields, computed fields, operators, constants, functions, and variables. Expressions can be logical, numeric, or conditional.
You can save expressions so that you can quickly use the filter or calculation again. When you save an expression, it becomes part of the input file definition and, consequently, part of the ACL project. An expression is saved as a computed type of field, can be added as a column to a view, and appears in field lists.
Exercise 11a: For example, you could create an expression to multiply sales price by quantity on hand and store the result in a column in the view. To create this expression you must:
Open the Prices file (you created earlier).
Select Edit from the menu bar and choose Table Layouts.
Click the Add a New Expression icon on the left of the menu (place the cursor over each icon to see the function each one performs). The field list area changes to let you define expression options.
In [Name] text box type Sales_Value.
Click the f(x) (Default value expression) button to open the expression builder.
Build your expression by clicking on the () button (the parentheses) and then clicking on field names and expressions such as +, - and =. For this exercise you want to multiply Sales price and Quantity on hand.
When your expression is complete, click [OK] to return to the Input File Definition dialog box. The [Default Value] box now shows your new expression.
Click (Accept Entry). The named expression will now appear in the list of computed fields in the input file definition.
Close the Input File Definition window and return to the view.
Exercise 11b: Now you may add the computed column to the view. To do that:
Click on the Add Columns icon in the Toolbar to open the Add Columns dialog box.
Double click the name of your expression in the [Available Fields] list box to copy it to the [Selected Fields] list box.
Click [OK] to return to the view. The new column will appear as the far right column in the view (or just to the left of the column that was selected before you clicked the Add Columns icon).
Exercise 11c: Finally, let’s compare the value of items in this file that are in location 05 with the calculations that you obtained in exercise 8. To do that, use the Count function on the Prices file (make sure that the Sales_Value column has been added). Select the [If] button to go to the expression builder where you must add a filter for Location = “05”. (You are creating this filter again. This time the field move is “LOC”.)
NOTE: If you are in the expression builder and you receive a message “‘Expression Type’ invalid, should be LOGICAL, not NUMERIC” you probably have tried to create an expression using numeric operators without selecting the option Edit, Input File Definition. You can get to the expression builder to create filters. When you do you must only use logical operators.
Print out and hand in the command log for exercises 8-11.
1 Based on materials created by Jay Thibodeau at Bentley College.