Introduction to acl1

BASIC ACL TERMS AND FUNCTIONS

Now that you have access to the ACL software, manuals, and files, you should review the terms below and try each exercise below.

ACL Terms

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.

1. 
   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).
   
2. 
   Select the directory and folder in which your project is located and the project file within that folder.
   
3. 
   Click on Sample Project.ACL.
   
4. 
   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.
   

1. 
   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
   
2. 
   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:
   
   1. 
      double click the file,
      
   2. 
      drag the file from the Project Navigation window into the view window, or
      
   3. 
      right click the file and select “open”.
      
3. 
   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.
   
4. 
   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.
   
5. 
   To Print the log:
   

1. 
   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
   
2. 
   Right click on any line and select “save selected items”
   
3. 
   Export to a WordPad
   
4. 
   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.
   

1. 
   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.
   
2. 
   Click the X in the upper right corner of the file’s view window.
   

1. 
   Select Tools from the menu bar and choose Add Comment.
   
2. 
   Type your team members’ names in the comment text box.
   
3. 
   Click [OK].
   
4. 
   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.

1. 
   To move a column in the view window:
   
   1. 
      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.
      
   2. 
      Drag and position the hand to the new location for that column.
      
   3. 
      Release the mouse button.
      
2. 
   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):
   
   1. 
      Point to the column title (ACL will highlight the entire column).
      
   2. 
      Click the Remove Columns icon on the toolbar at the top of the screen.
      
   3. 
      ACL will ask, “Remove (name of column)?” or “Remove selected columns?” Click [Remove].
      
3. 
   To add a column to the view window:
   
   1. 
      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.
      
   2. 
      To add fields to the Selected Fields list, you can [Add All], or select one or more fields and click [-->].
      
   3. 
      You can remove fields from the Selected Fields list in much the same manner as adding them, [<--].
      
   4. 
      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.
      
4. 
   You can use the Modify Column icon to change column titles, numeric formats, and column width.
   
5. 
   You can print a view of the records in the input file to the printer.
   
   1. 
      Select the Report icon in the Toolbar. ACL displays the Report dialog box.
      
   2. 
      Select the [Output] tab and select the [Print] radio button. Click [OK]. ACL displays the Print dialog box.
      
   3. 
      In the Print Range you can select [All] or [Pages] (to print one or more pages).
      
   4. 
      Click [Setup] to change paper size and print orientation and click [Print].
      
6. 
   You can print a view to the screen (and therefore to the command log).
   
   1. 
      Select the Report icon in the view window. ACL displays the Report dialog box.
      
   2. 
      Select the [Output] tab and select the [Screen] radio button. Click [OK].
      
   3. 
      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.
      

1. 
   Count – counts all the records in the file. This feature is also available via the Count icon on the menu bar.
   
2. 
   Total – displays the total of numeric fields. This feature is also available via the Total icon on the menu bar.
   
3. 
   Statistics – provides an overview of descriptive statistics for numeric fields. This feature is also available via the Statistics icon on the menu bar.
   
4. 
   Profiling – provides total value, absolute value, minimum value, maximum value on one or more numeric fields in an input file.
   

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

1. 
   Select Analyze from the menu bar and choose Count to open the Count dialog box.
   
2. 
   Click [If] to open the expression builder (labeled “Count: If”).
   
3. 
   In the Available Fields list box, double click Location. Location is copied to the Expression text box.
   
4. 
   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.
   
5. 
   Type "05" (the quotes are required) for location code 05 beside the equal sign in the Expression text box.
   
6. 
   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”.).
   
7. 
   Click [OK] to save the filter and close the expression builder. The Count dialog box reappears.
   
8. 
   Click [OK] in the Count dialog box to issue the Count command. The command log window reports that 13 met the condition.
   

1. 
   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.
   
2. 
   Click [If] to open the expression builder.
   
3. 
   Double click Location_05 in the Filters box. Location_05 is copied to the Expression text box.
   
4. 
   Click [OK]. The expression builder closes. The expression Location_05 is now in the [If] text box.
   
5. 
   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.
   
6. 
   Click [OK]. The results of the extract appear in the View window and as an entry in the command log.
   
7. 
   Click back to the Project Navigation window to display the list of tables. The new definition, Warehouse05, is in the list under Inventory Review.
   

1. 
   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
   
2. 
   Right click on any line and select “save selected items”
   
3. 
   Export to a WordPad
   
4. 
   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.
   

Name1	Title	From	To	Length	Type2
PRODNO	Product Number	1	9	9	A/N
PRODCLS**	Product Class	1	2	2	A/N
LOC	Location	10	11	2	A/N
PRODDESC	Product Description	12	35	24	A/N
PRODSTAT	Product Status	36	36	1	A/N
UNCST	Unit Cost	37	42	6	N Unformatted (2)
CSTDTE	Cost Date	43	48	6	Date YYMMDD
SALEPR	Sale Price	49	54	6	N Unformatted (2)
PRCDATE	Price Date	55	60	6	Date YYMMDD
QTYOH	Quantity On Hand	61	65	5	N
MINQTY	Re-Order Point	66	70	5	N
QTYOO	Quantity On Order	71	75	5	N
VALUE	Inventory Value at Cost	76	85	10	ZONED3
MKTVAL	Market Value	86	95	10	ZONED3

**This is part of the product number, it does not need to be separately defined as part of this assignment.

2. Select the Month2_Invntry.fil data file. Click [Open].

3. The next three screens are part of the “Data Definition Wizard”.

1. 
   On the Character Set screen “PC and all other types of computers (ASCII)” is usually OK. Click [Next].
   
2. 
   On the File Format screen “Other file format” is usually OK. Click [Next].
   
3. 
   On the File Properties screen the selections should be correct and you should click [Next] to continue.
   

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:

1. 
   Open the ap_trans file.
   
2. 
   Select Analyze in the menu bar and choose Total Fields to open the Total dialog box.
   
3. 
   Select the Invoice_Amount field from the [Total Fields] list.
   
4. 
   Click [OK].
   
5. 
   The control total for the Invoice_Amount, 278,641.33, appears in the view window.
   
6. 
   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
   
7. 
   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.
   
8. 
   Now move the Invoice Amount into the [Selected Fields] list and sort this record in descending order.
   

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.

1. 
   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.

2. 
   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.

2. 
   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.

2. 
   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.

2. 
   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.

1. 
   Open the secondary file (Payroll) to determine that it is in EmpNo sequence. It is. NOTE: Use Analyze, Sequence.
   
2. 
   Open the primary file (Empmast).
   
3. 
   Select Data from the menu bar and choose Join Tables.
   
4. 
   Select the secondary file (Payroll) from Secondary File window.
   
5. 
   In the [Primary Keys] list box select the primary file’s key field (EmpNo).
   
6. 
   In the [Secondary Keys] list box select the secondary file’s key field (EmpNo).
   
7. 
   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.
   
8. 
   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.
   
9. 
   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.
   
10. 
    Click the [Main] tab.
    
11. 
    Type an output file name (Matched) in the [To] box.
    
12. 
    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

Presorting data

41 records produced

1 records bypassed

Extraction to table C:\ACL DATA\Sample Data Files\Matched.FIL is complete

Opening file "Matched"

Field 'EmpNo' renamed 'EmpNo2' to remove name conflict

Presorting data

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"

1. 
   Open the primary file.
   
2. 
   Select Data from the menu bar and choose Join Records.
   
3. 
   Select the secondary file from Secondary File window.
   
4. 
   Select the primary file’s key field.
   
5. 
   Select the secondary file’s key field.
   
6. 
   Select those fields from the primary file that you wanted included in the joined file.
   
7. 
   Click the [More] tab and click the [Unmatched Records] radiobutton.
   
8. 
   Click the [Main] tab.
   
9. 
   Type the output file name in the [To] box.
   
10. 
    Click [OK].
    

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.

1. 
   Open the Prices file (you created earlier).
   
2. 
   Select Edit from the menu bar and choose Table Layouts.
   
3. 
   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.
   
4. 
   In [Name] text box type Sales_Value.
   
5. 
   Click the f(x) (Default value expression) button to open the expression builder.
   
6. 
   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.
   
7. 
   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.
   
8. 
   Click  (Accept Entry). The named expression will now appear in the list of computed fields in the input file definition.
   
9. 
   Close the Input File Definition window and return to the view.
   

1. 
   Click on the Add Columns icon in the Toolbar to open the Add Columns dialog box.
   
2. 
   Double click the name of your expression in the [Available Fields] list box to copy it to the [Selected Fields] list box.
   
3. 
   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).