Year 2 – 1989 – the beginning of my analytics

In 1989 I was assigned a second audit in the telecom area. The main objective was to assess our use of Leased Long Distance Lines (LLDLs). LLDLs were dedicated long distance lines that were primarily used for transfer of data. Rather than pay long distance charges, we had dedicated lines for this purpose. The LLDLs were cheaper than using regular lines, and had higher quality and faster speeds. I asked for the reports for the LLDLs and was directed to another pile of boxes of unopened reports.

We had thousands of LLDLs in hundreds of offices and buildings across the country. Since the telecom was managed centrally, all reports were sent to headquarters. I was disheartened at the prospect of slaving over more paper-based reports again. The manager told me that even though the monthly usage reports were divided by region / building / LLDL number, all the charges were processed by the same telecom company (sometimes monopolies are a good thing). So I called the telecom company and inquired about getting the information electronically.   After much discussion, they agreed to provide the data in an electronic (ASCII Text) format for $240.00. I went to my manager and asked for permission to spend the money to get the electronic version of the 12 monthly usage reports. He told me to talk to the Audit Director – who owned the budget.

In later years I would realize that my Director supported analytics, but it didn’t look like it on that day. He said that there was no extra money in the budget to waste on trying something new. I told him that we could use some of the money I had saved the company on the previous telecom audit, but I was told this was “non re-spendable revenue” which meant the refund went back to a corporate account and not Internal Audit. In the end my persistence paid off and he reluctantly agreed. While I didn’t realize it at the time, my life-long obsession with data analysis had just begun in earnest. But I still had a number of obstacles to overcome.

Our computer capabilities in the internal audit organization were very limited. There were three Wang terminals in common locations which were only used to enter time reporting information. As well there was a 3270-type mainframe terminal and an application on the mainframe called Audit Analyzer (later called Decision Analyzer). This was only being used to produce a series of standard reports for auditors in the region. They could request a list of items in inventory, a payroll report or a summary of financial transactions for their region. They could also get random sample inventory, pay or financial transactions. The reports were paper-based and mailed to the regional auditors. However, I thought we could do more with this software.

I had to take my telecom data files to another group to get them uploaded to the mainframe computer so I could use Audit Analyzer to manipulate the data. However, first, I had to use a text editor to remove headers, footers and subtotals – a pain, but eventually I was able to upload the data. I defined the data to Audit Analyzer and was able to read and combine the 12 monthly reports. I them summarized the data by LLDL to get a total of the minutes of usage for each line. Next I sorted the summary by usage (ascending). The first thing I notice was that some of the LLDLs had very little usage in the past year. These are dedicated lines that we pay for whether we use them or not. One line had only been used for 126 minutes in April and another had only been used during the period April – July (our fiscal year was April to March). If we had lines with very little usage, did we have lines with no usage?

It occurred to me that I should get a list of all the LLDLs from the billing information so I could compare the usage and billing information. Again this was a monthly report showing the cost per LLDL per month. My manager refused to pay for the electronic version, so I had a summer student type the reports directly into a data file on the mainframe. This took 8.5 days, but then I was ready to go.

I joined the Billing data by LLDL with the Usage by LLDL. The objective was to calculate the cost per minute of usage to see if a non-dedicated line would be cheaper in places where we had limited usage.   However, I was surprised to see that we were paying monthly charges for over 100 lines with zero usage. Since there was no usage, they were not on my usage reports. I had the auditors in the region check on these lines and none of the LLDLs were even connected to a telecom device. Some terminated in closets or inside a wall after a building redesign; others were simply no longer attached to any telecomm or computer terminal device.

The audit results included recommendations to stop paying for 206 LLDLs which were either not being used or had insufficient usage to justify the cost of a dedicated line. The saving amount to over $500K and according to our standard practice of using a 3-year base, we reported as $1.5M in savings.

Analysis – ACL Commands: EXTRACT Append; SUMMARIZE, Quick Sort, JOIN or RELATE

Lessons-learned: Sometimes you have to fight with management to change the way things are done. Be prepared with facts and figures; have a plan for moving forward; and be willing to take a risk. You might have to resort to manual entry of data, but having two files that you can join electronically is much better than trying to join the data manually. I was also careful to track the costs of performing this analysis so I could show a cost/benefit analysis at the end.

Another lesson learned: Even simply analysis can be beneficial. Summing the usage by LLDL and listing the results in ascending order was not difficult, but the summarized results quickly identified anomalies. However, you still have to interpret the results. I was not expecting unmatched billing records, but I noticed that the Join result had fewer records that when I started. This meant that there were unmatched Billing records. If I hadn’t taken the time to examine the join results, I would have missed the unmatched Billing records. I re-performed the Join with “unmatched primary” option to identify the Billing records with no usage.

Leave a comment