1 - California State University, Northridge



OVERVIEW – NORWOOD OFFICE SUPPLIES CASE

You have been assigned as the in-charge auditor for the upcoming year-end audit of Norwood Office Supplies, Inc. Your manager has asked you to use the ACL software to analyze Norwood’s inventory and accounts receivable records. Your manager has explained that Norwood is experiencing intense competition from office supply chain stores such as Staples and Office Max. Your manager has informed you that because of that competition you need to be aware of such financial reporting issues as revenue recognition and inventory valuation (e.g., lower of cost or market) and such business-related concerns as the availability of inventory to satisfy customer demands (i.e., sufficient inventory is on hand to satisfy existing sales orders). As the in-charge auditor assigned for the upcoming year-end audit of Norwood Office Supplies, Inc., you first must access and begin to analyze the inventory and accounts receivable files.

ANALYZE THE NORWOOD OFFICE SUPPLIES INVENTORY AND A/R FILES

You now are ready to analyze the Norwood Office Supplies inventory and accounts receivable files. If you have not already done so, close the project Workbook. Note: The following exercises use a separate set of data files. These data files will also be used for assignments #3 and #4. The previous data files (used for ACL #1 and the first part of ACL #2) were only used to introduce you to the ACL software.

Now open the Norwood Office Supplies project. From the VLAB you should already have copied a directory titled “Norwood Files V8.” This directory contains “Norwood Case V8.ACL”. The Norwood Case project contains three input file definitions: “Customer_Master_File,” “Inventory_Master_File,” and “Sales_Order_Master_File.” Later in this assignment you will be required to create a fourth input file definition for Norwood’s Accounts_Receivable_Master_File. Please note that when you open this project ACL will tell you that it is a case for an older version of ACL and continuing will update it with a file extension .OLD. Respond [OK] to this message.

Exercise 5: Now perform the following actions:

1. Add your name and student number to the command log.

2. Open the inventory file. The command log should indicate 12 fields were activated.

3. Use the Count command to count the records in the file. There are 94 records.

4. Extract to a file (make sure that you create a new file) called “Allocated” the records for those items with insufficient quantity on hand to fulfill open sales orders (on hand is less than allocated). Seven records should be extracted.

The table below contains a description of the Norwood inventory master file:

|Name |Title |

|Item_Number |Item number |

|Group |Product group |

|Description |Item description |

|Unit_Cost |Unit cost ($) |

|Unit_Price |Unit price ($) |

|Quantity_On_Hand |Quantity on hand in warehouse |

|Quantity_On_Order |Quantity on order from vendor |

|Allocated |Quantity allocated to open sales orders |

|Order_Point |Order point |

|Order_Quantity |Order quantity |

|Date_of_Last_Transaction |Date of last transaction |

Exercise 6: Norwood’s accounts receivable master file, “Accounts_Receivable.FIL,” has not yet been linked to ACL as an input file definition. Using the file format below as a guide, create this file definition. The file Accounts_Receivable.FIL is located in the directory “Norwood Files V8” that you obtained from the web site. There are 65 records to be imported: After you create the input file definition, “print” the file to the command log.

|Name1 |Title |From |To |Length |Type2 |

|Customer_Code |Customer Code |1 |8 |8 |A/N |

|Invoice_Number |Invoice Number |9 |12 |4 |N |

|Invoice_Date |Invoice Date |13 |20 |8 |Date MM/DD/YY |

|SO_Number |Sales Order Number |21 |24 |4 |N |

|Terms |Invoice Terms Code |25 |25 |1 |N |

|PO_Number |Customer Purchase Order Number |26 |29 |4 |A/N |

|Balance |Invoice Balance ($) |30 |34 |5 |N Unformatted (2) |

1 These names should be assigned as you import the file.

2 A/N = alphanumeric (use data type ASCII text). N = Numeric (number of decimal places).

Exercise 7: Total the invoice balance field. The total should be 5,584.07.

Print and hand in your command log for exercises 5-7. This is the SECOND deliverable to be submitted for this assignment.

The following describes other ACL commands that you might find useful.

Use the classify command (see Help” “classify command”) to count the number of records relating to a unique key character field value (i.e., a strata) and to accumulate totals of numeric fields for each stratum. The output from the classify command can be placed in the command log or in a new file. The Summarize command is very similar to the classify command.

With the stratify command (see Help: “stratify command”) you can total the number of records that fall into specified intervals (or strata) of numeric fields or expression values. You can specify classes that are either equal in size (called intervals) or vary in size (called free). To stratify data in a file you must first Open that file and then Profile the data to provide maximum and minimum values for the numeric fields you will stratify.

The age command (see Help: “age command”) produces aged summaries on unsorted data. You may select you own cutoff dates and aging periods or use the default system dates. ACL displays a count of the number of records in each age period.

Use the Relations command (see Help: “Relations Command”) to establish relationships between fields in two or more files. The fields do not necessarily have the same headings, but the data in the fields must match. Most functions that can be done with the Join command can be done faster and more easily with Relations. For example, you may want to establish a relationship between the sales order master file and the customer file (based on the customer number) as well as a relationship between the sales order master file and the inventory master file (based on the inventory item number). In this example the sales order master file is the parent (primary) file and the customer and inventory files are the related (child) files. Before you can define relations you must first Index the related files.

The Size, Sample, and Evaluate commands (see Help: “Size Command”, “Sample Command”, “Evaluate Command”) are used to determine sample sizes, draw samples, and evaluate sample errors.

You can use the Find () function (see Help: “Find Command”) to test for a string of characters in a field or record. The find function is not case sensitive.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download