Anand the Head — Internal Audit and Risk Management (I &A, RM) of a private retail mall chain — ‘Bargains and More’, who had recently implemented an Oracle-based Point-of-Sale Billing Application, was presenting to the Audit Committee on the ‘role’ of Internal Audit and Management Assurance Services in the changed environment. The question most commonly encountered by an internal auditor after any operational software implementation is “Given that we now have a ‘World-class Point-of-Sale Billing Application’, an internal auditor’s job is greatly reduced.” Anand presented the role of the auditor in the new environment in terms of IT control reviews and risk assurance services, physical document-based audits, apart from compliance to various directives of the statutes and other regulatory authorities. While various reports as required by executive, operations, field and the regulatory authorities were customised and available as a menu option in the Point-of-Sale Billing Application, there was a regular requirement for vital information on areas like demand effect on introduction of new pilot products, reconciliation of daily takings with various funds collection channels, identifying zones where novel sale schemes could be introduced and various other audit objectives. New audit objectives were being defined in line with the new ‘Customer is the King’ environment, considering the criticality of process owner inputs in the new system.

Further, as a means of increasing the extent of transaction testing by his audit staff and reducing cost of audit operations, Anand also proposed the implementation of a Generalised Audit Software (GAS) which could help the inspection team query the system for better results.

The Audit Committee was supportive of the presentation made by Anand and asked him to implement the GAS and present the changes effected as a result of the implementation in the next quarter meeting.

The Head (I & A, RM) set up a mid-size team within the department to take the initiative of implementing the GAS in the retail mall. The team comprised of 2 senior audit officials (who among them had a wide range of experience in various process activities of the retail chain like CRM and Scientific Just-in-Time (JIT) Procurement), an IT professional (who understood the Point-of-Sale Billing software implemented) and an IT auditor (CISA).

The entire audit manual was reviewed and audit objectives were mapped to possible audit tests that could be conducted using a GAS and otherwise. The method of using the GAS was debated and discussed by the group in a way that data integrity, confidentiality and availability of the Oracle production server was not compromised and the objectives were also met.

While it was not possible to log on to the production server due to access restrictions maintained by the Database Administrator, the team was faced with a challenge to import data for further analysis.

The team decided to connect to a Billing dump (Print Report Dump from the Point-of-Sale Software) provided by the DGM-IT. The Billing dump was provided by running a File Transfer Protocol (FTP) on the DR Site Server, which is also used for Reporting Tools like Crystal Reports.

Point-of-Sale Applications deployed in malls, generate comprehensive sales reports. These reports were saved in MS-Excel data formats which is an alternative to File Print options. The fields are given in Exhibit 1.

These MS-Excel data files once saved on the workstation containing the GAS or on the Local Area Network in a shared audit data folder were accessed through the GAS’s Import Assistant — Excel component. The process of import was simple and easy to apply, since Excel file Record Definitions are readily recognised by the GAS.

Exhibit 1 — Fields in Sales Reports
Date of Sale	Quantity	Scheme ID
Time of Sale	Rate	Scheme Details
Transaction Number	Gross Value	Collections in Cash
Cashier Name	Taxes	Collections by Card
Cashier ID	Scheme Discounts	Dues
Product Sold	Net Value

The fields of reference relevant to the objective being tested were :

• Net sales

• Cash collected

• Card receipts

The steps followed for interrogation in GAS were :

• Import the Sales Report for a given period through GAS’s Import Assistant — MS Excel.

• Navigate to the Field Statistics in the Database Toolbar.

• View the numeric control totals for the Net Sales, Cash Collected and Card Receipts fields, respectively.

• Normally, the Net Sales should be arithmetically balanced by Cash Collections and Card receipts.

• In the case under review, we noticed a high percentage on Unpaid Bills almost 25% of the period’s Net Sales.

• An overview of the Unpaid Bill cases through a Field Summarisation showed that the overdue amounts were significantly concentrated on Cashier A and Cashier D.

• Unauthorised credits were being given by the respective Cashiers.

The fields of reference relevant to the objective being tested were :

• Cashier ID

• Scheme ID

• Scheme Discounts

• Gross Value

The process of interrogation in GAS followed was :

• Navigating to data in the Menu Tool Bar and selecting Field Manipulation

• In Field Manipulation, appending a computed Virtual Numeric Field Discount % with the Criteria (Scheme Discounts*100/Gross Value), rounded off to the nearest integer.

• Navigating to Analysis in the Menu Tool Bar and selecting Duplicate Key Exclusion

• In Duplicate Key Exclusion, we identify different discount percent values for the same Scheme ID.

• We got a list of cases where varying discount percentages were applied for the same Scheme ID.

• Some cases were extremely glaring, with the discount percentage being as high as 45% where the Scheme ID warrants a discount percentage of 15% only.

• The revenue leakage was plugged after review.

The fields of reference relevant to the objective being tested were :

• Cashier ID

• Cashier Name

• Start Time

• End Time

• Quantity

The process of interrogation in GAS followed was :

• Create a new computed numeric field in the imported Sales File with the difference between the Start Time and the End Time using the criteria ‘@agetime (End Time, Start Time)’. This new field will give us the time taken by each cashier in seconds to scan, bag and bill all the items against that transaction.

• Create another computed numeric field with the criteria ‘Difference in Time/ Quantity’ to arrive at the time taken to scan each item.

• Now perform a field summarisation on the Cashier ID and Cashier Name with regard to the numeric field containing the time taken to scan each unit.

• In the field summarisation, also include additional statistics like Count and Average along with Min and Max. These statistics will give us the number of scans by a single Cashier in a given period, the minimum time for scan, the maximum time for scan and the average time to scan.

• In the summarisation result, sort the time to scan (average) on a descending basis.

The Cashier with the best scan rate was isolated. The Management of the mall chain then selected the Top 10 Cashiers from the above exercise and placed them at the front desks on crucial days like weekends or public holidays to improve customer service.

The fields of reference relevant to the objective being tested were :

• Start Time

• End Time

• Cashier ID

• Cashier Name

• Net Sales

The process of interrogation in GAS followed was :

• Perform a Data Extraction on the imported Sales File.

• Build a criteria using the function .NOT. @betweenagetime(Start Time, “10:00:00”, “22:00:00”) .OR.. .NOT. @betweenagetime(End Time, “10:00:00”, “22:00:00”)

• This criteria will isolate all transactions out of the normal mall working hours of 10 a.m. to 10 p.m. Here we trap both Start Time and End Time.

• The Direct Extraction function within GAS is very popular on large databases, say, upwards of 1-crore transactions. The function first sorts the entire database and then runs the equation through the sorted database. Hence, the results arrive faster than running an ordinary command on an unsorted database.

50 transactions out of 1 lac transactions where the Start Time and End Time are after office hours at 10:30 p.m. were identified. The mall in-charge showed that these transactions were all pertaining to public holidays, when schemes were launched and working hours were extended by two hours with local administration’s permission.

Demand study of new products introduced into pilot mall outlets across India :

Malls introduce new products into pilot outlets to study the customer behavioral patterns, spending patterns, loyalty to existing products, rather than new substitutes and more. The field reference considered were :

• Transaction Number

• Product Number

• Quantity

• Net Sales

The process of interrogation in GAS followed was :

• The Transaction Number contains a combination of the mall outlet ID, the financial year and the transaction ticket number. The first 3 digits of the Transaction number represent the mall outlet ID.
• We append a new computed character field with the aim of getting the mall outlet ID into this field. This is performed through a criteria/equation @left(Transaction ID, 3). This function removes the first 3 digits from the Transaction Number and places the same in a separate computed field.
• A field summarisation is performed on the imported Sales file on mall outlet ID and product number/code with respect to quantity and net sales.
• In the summarisation result, a direct extraction is performed on the pilot product malls through the Equation @ list(mall outlet ID, “003”, “005”…)
• A top records extraction was performed with the key field being the mall outlet ID, and the top 5 products in each of the 10 pilot malls were identified.

A detailed review of the final result broadly confirmed the Management’s expectation. All the new products had fared well in the 10 pilot malls, save one product which had not been preferred over its long-existing competitor. This exercise armed the Management with factual historical data from a truly representative sample of mall outlets. Now the Management was in a position to slowly and surely roll out the most liked products to the remaining mall outlets over the next month.

While specific audit reports gave regular feedback to the process owners, the audit objectives were greatly met using the GAS which went beyond the Despite the possible high returns, shareholder activism remains hard work. Just how hard is illustrated by the retirement plans of de Vries, who after 18 years of fighting for shareholders’ rights, decided to leave the Dutch shareholder group in October. Until then, he said, he will fight on and wonder who will be around longer : he or Chief Executive of ABN Amro. (Source : N.Y. Times, 20-5-2007) set standard norms. Further, it allowed the audit team to move beyond the ‘priority’ set by the IT and were able to complete their audits within time, with drill-down capabilities and results through a thirdeye watch. The IT was also excited about the possibilities which such a tool could have for their security reviews also on a regular basis and initiated a review of the same. Further, the Head (I & A, RM) also made it mandatory for the retail mall’s outsourced internal auditors to use a GAS for their branch audits using similar methodologies as them.

As use of the GAS matured, the Head (I & A, RM) laid down the blue-print for Continuous Control Monitoring of specific audit objectives. Thus, the Internal Audit Department added value to the entity’s operations.

Article by Deepjee Singhal Manish Pipalia Chartered Accountants, authors are experts in Internal audits.