|
|
Total Number of Subscribers: 962 |
||||||||||||||||||
|
|
|||||||||||||||||||
|
|
|||||||||||||||||||
|
Date: 9th February 2010 |
Compiled by: M Sathya Kumar <= /span> |
||||||||||||||||||
|
Audit of Retail Malls Introduction : Anand the Head — Internal Audit and Ri=
sk
Management (I &A, RM) of a private retail mall chain —
‘Bargains and More’, who had recently implemented an Oracle-b=
ased
Point-of-Sale Billing Application, was p=
resenting
to the Audit Committee on=
the ‘role’ of Internal Audit a=
nd
Management Assurance Services in the changed environment. The question mo=
st
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 directiv=
es
of the statutes and other regulatory authorities. While various reports as
required by executive, operations, field and the regulatory authorities w=
ere
customised and available as a menu option in the Point-of-Sale Billing
Application, there was a regular requirement for vital information on are=
as
like demand effect on introduction of new pilot products, reconciliation =
of
daily takings with various funds collection channels, identifying zones w=
here
novel sale schemes could be introduced and various other audit objectives.
New audit objectives were being defined in line with the new ‘Custo=
mer
is the King’ environment, considering the criticality of process ow=
ner
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 a=
lso
proposed the implementation of a Generalised Audit Software (GAS) which c=
ould
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. Methodology and observations :=
The Head (I & A, RM) set up a mid-size t=
eam
within the department to take the initiative of implementing the GAS in t=
he
retail mall. The team comprised of 2 senior audit officials (who among th=
em
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 implement=
ed)
and an IT auditor (CISA). The entire audit manual was reviewed and aud=
it
objectives were mapped to possible audit tests that could be conducted us=
ing
a GAS and otherwise. The method of using the GAS was debated and discusse=
d 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 fur=
ther
analysis. The team decided to connect to a Billing dump
(Print Report Dump from the Point-of-Sale Software) provided by the DGM-I=
T.
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. Application of GAS in auditing
Point-of-Sale Systems from the retail industry — Malls : 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. Import into GAS : 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 e=
asy
to apply, since Excel file Record Definitions are readily recognised by t=
he
GAS.
Test 1 — Reconciliation =
of
net sales with cash takings, and card receipts : 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 ar=
ithmetically
balanced by Cash Collections and Card receipts. Test results : ·
In the case under review, we noticed=
a
high percentage on Unpaid Bills almost 25% of the period’s Net Sale=
s. ·
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 give=
n by
the respective Cashiers. Inconsistent scheme discount r=
ates
offered by Cashiers to different customers against the same Scheme ID : 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 T=
ool
Bar and selecting Duplicate Key Exclusion ·
In Duplicate Key Exclusion, we ident=
ify
different discount percent values for the same Scheme ID. Test results : ·
We got a list of cases where varying discount percentages were appl=
ied
for the same Scheme ID. ·
Some cases were extremely glaring, w=
ith
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. Identifying the most efficient
Cashier across all the malls — Operational throughput : 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)R=
17;.
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 fiel=
d 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 t=
he
time taken to scan each unit. ·
In the field summarisation, also inc=
lude
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 aver=
age time
to scan. ·
In the summarisation result, sort the
time to scan (average) on a descending basis. Test result : The Cashier with the best scan rate was
isolated. The Management of the mall chain then selected the Top 10 Cashi=
ers from
the above exercise and placed them at the front desks on crucial days like
weekends or public holidays to improve customer service. Detecting transactions out of
office hours : 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. H=
ere
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 transacti=
ons.
The function first sorts the entire database and then runs the equation
through the sorted database. Hence, the results arrive faster than runnin=
g an
ordinary command on an unsorted database. Test results : 50 transactions out of 1 lac transactions wh=
ere
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 hou=
rs
were extended by two hours with local administration’s permission.<=
b> Demand study of new products
introduced into pilot mall outlets across Malls introduce new products into pilot outl=
ets
to study the customer behavioral patterns, spending patterns, loyalty to
existing products, rather than new substitutes and more. The field refere=
nce
considered were : ·
Transaction Number ·
Product Number ·
Quantity ·
Net Sales The process of interrogation in GAS followed=
was
: The Transaction Number contains a combinatio=
n 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 remov=
es
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 respec=
t to
quantity and net sales. In the summarisation result, a direct extrac=
tion
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 fa=
red
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 outlet=
s. Now
the Management was in a position to slowly and surely roll out the most l=
iked
products to the remaining mall outlets over the next month. Conclusion : While specific audit reports gave regular
feedback to the process owners, the audit objectives were greatly met usi=
ng
the GAS which went beyond the Despite the possible high returns, sharehol=
der
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 th=
en,
he said, he will fight on and wonder who will be around longer : he or Ch=
ief
Executive of ABN Amro. (Source : N.Y. Times, 20-5-2007) set standa=
rd
norms. Further, it allowed the audit team to move beyond the
‘priority’ set by the IT and were able to complete their audi=
ts
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 initiat=
ed a
review of the same. Further, the Head (I & A, RM) also made it mandat=
ory
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 Deepak Singal, an expert in the field of Intern=
al
Audit |
|||||||||||||||||||
|
|
|||||||||||||||||||
|
|
|||||||||||||||||||
|
R=
ewards
waiting for feedback at |
|||||||||||||||||||
|
|
|||||||||||||||||||
|
Disclaimer: We believe that the information contained in this e-zine is true. If you do = not wish to receive Smart Trainee please click here. |
|||||||||||||||||||
|
|
|||||||||||||||||||
|
|
|||||||||||||||||||
|
|
![3D"http://www.primeacademy.com/smartlogo4.gif"](3D"http://www.primeacademy.com/smartlogo4.gif")