Audit Objective

The objective is to determine the overall reasonableness of telephone call charges and that usage appears appropriate.

Audit Procedures / Audit Program

1. Obtain telephone billing transaction data in electronic format, note data elements.
2. Sort the data by type of charge (e.g. land-line vs. mobile).
3. Summarize all charges by type, separated by debit and credit.
3. Prepare a schedule of all calls which originated on either a weekend or a federal holiday.
4. Prepare a schedule of the five largest and five smallest charges, both debit and credit.
5. Review the scheduled transactions to ensure they appear reasonable and appropriate.

6. Write a report explaining your observations, and provide conclusions and recommendations.

Example Data (Excel and Flat File)

Excel WorkBook Sheet "PhoneLog"
Tab separated file phonelog.tab

Narrative of procedures

Detail telephone billing records are available from most of the Telecom providers, but tend to be in different formats. Unless you have specialized software, I have found that one approach is to load the billing data into MS-Excel in order to get the data into a format that is easier to work with. Note that this approach may need to be modified if your billing data exceeds the limits of Excel, i.e. about 65,000 rows of data.

Once the data has been loaded into Excel it can then be saved in the tab separated values (TSV) format, which is easier to work with. As part of the audit planning process, you may wish to first obtain overall population statistics, in order to tie the detail billing to the control total. In order to get a better picture of what is in the data, you may wish to prepare various charts such as histograms of calls made by hour of the day, day of week, area code, etc. You may also want to test for unusual conditions, such as calls made on weekends or Federal holidays (may not necessarily be unusual depending on your business situation).

To illustrate how the process might be performed, I have taken some actual data extracted from telephone billing records, both for a land line and a cell phone. These records covered a period of approximately five years for calls made by one employee. However, the actual telephone numbers have been removed, as well as other data contained in the billing records, such as names of cities, etc. This file is named "phonelog.tab" and consists of six columns of data, as follows:

1. Date - The date the phone call was placed or received, in the format MM/DD/YYYY
2. Hour - The hour of the day, i.e. a number from 0 to 23
3. Minutes - the number of minutes the call lasted, which is the basis for the bill
4. FromAC - this is the area code from which the call was placed or the call received (all the same for this employee)
5. ToAC - this is the area code of the person receiving the call, or placing the call in the event of a call receipt on a cell phone
6. Charge - This is the amount of the detail charge for this call.
7. Type - C (cell phone) , L (land line)

The data consists of 15,225 individual billing details.

The first step is to summarize the data by type of charge (i.e. cell phone vs. land line). This can be done using a variety of means:

1. Use the Excel sum function
2. Use EZ-R Stats for Excel statistical functions
3. Use EZ-R Stats for Windows statistical functions

We will illustrate all three below.

Excel sum function

Open the data file in Excel and insert the following formulae at the bottom the sheet (below the last lines of billing data):

=sum(F2:F15226) Obtain the total
=min(F2:F15226) Obtain the minimum amount
=max(F2:F15226) Obtain the maximum amount
=stdev(F2:F15226) Obtain the standard deviation

EZ-R Stats for Excel

Highlight and copy all the worksheet data;
Either Click Edit | Copy, or type Ctrl-C, or use the toolbar icon
Open EZ-R Excel for Windows and click the "Paste" button
Using the drop down menu item, select Statistics
Double click the column named "Charge"
Click the "Run" button

EZ-R Stats for Windows

Start EZ-R Stats for Windows
Select Tools | Summary
Specify input file as "phonelog.tab"
specify output file as "phonetotals.tab"
specify variable name as "Charge"
Click "run"

All these procedures will provide the same results, i.e.

Sum 5097.76
Average .3348
Min 0
Max 14.08
Std Deviation .634

Often at this point you may want to look at the top highest charges, as well as identify which charges may be considered "outliers".

A possible approach would be as follows:

In Excel, sort the worksheet in descending order by charge, so that the top 10 go to the top.
To identify potential outliers, you would expect that less than 1/2% would be more than 2.6 standard deviations from the mean. The actual cutoff points could be determined by entering the formula (=norminv(.005,.3348,.634) and (=norminv(.995,.3348,.634) which yields the values -1.298 to 1.967. We know we have no negative values, so now we look for values in excess of 1.97. We find 448 of these values, well in excess of what we would expect if the population were normally distributed. We would expect 1/2% time 15,226 or about 78 observations. Instead, we find 448, which is 2.9% of the population.

As you can see from above, almost anything can be done in Excel, if you have extensive knowledge of macros and visual basic for applications, and plenty of time.

The same approach using EZ-R Stats for Excel would include selecting and copying the data from Excel, then "pasting" it into EZ-R Excel. From there you would select the menu options, such as population statistics, outliers, histogram etc. The column to be analyzed would be "Charges".

In EZ-R Stats for Windows, you could run either a proc summary, proc means, proc univariate, proc outlier or proc histo on the data to get a good feel for the population. In order to test that the data seems reasonable, it might also be a good idea to run a proc Benford on the length of the call.

A final audit test is to extract those phone record details for calls originating on either weekends or Federal Holidays. (If your company’s holidays don't match up with the Federal Holidays, then you will need to customize the routine for your needs. This is done by entering the holidays observed into an Excel workbook, <fill in the detail here>

Both of these tests can be done Using ScriptBasic, an open source version of Basic. In order to run the tests, you need to know which column contains the Date value, and whether the date format has a two digit year or a four digit year. (Note that the current version of the routine requires that the data be in either the format MM/DD/YY or else MM/DD/YYYY. If your dates are in a different format you will either need to convert them to this format or else modify the ScriptBasic routine so that it can accommodate your specific format.

The ScriptBasic programs are named weekend.sb (handle weekends) and holidays.sb (handle holidays). Both use the same format for command line arguments, which is as follows:

scriba weekend.sb i=phonelog.sb o=wke.txt v=Date yl=4

The command line arguments all start with a letter and then an equal sign (=) followed by the value. Arguments can be:

i=inputfilename
o=outputfilename
v=Variable name for Date
yl=2 or 4 (depending upon whether the length of the year value is 2 or four digits).

The command for the holidays is the same, just a different program name (holidays.sb)

Like any DOS program, the output can be redirected to a file, if so desired.

The DOS command file can be viewed here. Redirected output for holidays is hl.txt and redirected output for weekends is we.txt.

When we run the extracts, we find 99 billing records for holidays (hol.txt) and 355 calls on weekends (wke.txt). (Note that it there will not be overlap between the weekends and holidays, as Federal Holidays do not fall on weekends - without being rescheduled).

Customizing the Holidays

The current set of holiday dates are the Federal holidays from 1/1/2002 to 12/25/2007. If you need to have a different set of dates processed as holidays, then perform the following procedures:

Open the Excel workbook JE.xls
Go to the Sheet named "Fed Holidays" and modify the first column as appropriate
Save the sheet only as a tab separated file
Using the ScriptBasic program blddates.sb, read this file and produce an output file named "sbdates.txt" which will be stored in the directory where the holidays.sb routine is stored (i.e. replace the current one)

Description of Analytical Procedures Performed

First sort the file provided (phonelog.tab) by Type.
This can be done by opening the file in Excel, sorting and then saving as a tab separated file, or by using EZ-R Stats for Windows Tools | Sort and specify the input file as "phonelog.tab", the output file as "psort.tab" and the sort key as Type.

Using EZ-R Stats for Windows, choose the menu option Tools | Summarize.
Specify input file "psort.tab", output file "psum.tab".

Variable to be tested will be Charge

Using Ez_R Stats for Windows, choose the menu option Tools | Pareto.
Specify input file "psort.tab", output file "Phonepareto.tab".

Variable to be tested will be Charge

Click the "Run" button to process the file and obtain the output.

Using EZ-R Excel to Audit Telco Records

Step	Procedural description	Image
1	Select range in Excel and copy
2	Open EZ-R Excel and "paste" the data into it
3.	Select the variable to be processed and click the drop-down menu
4	Click the menu item "statistics" in order to obtain population statistics
5	Click the histogram menu item in order to obtain a view of the charge amounts
6	Select "outliers" on the menu option and click to view potential outlier charges