EZ-R Stats, LLC

Overview/Narrative

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