Excel is such a powerful tool because there are so many ways to use it in Telecoms. Bangkok Beach Telecom offers a Least Cost Routing (LCR) Application for Voice. The application is written in Perl, uses a MySQL database, and is light enough to run on just about any hardware and Operating System. The application has achieved blended cost/MOU less $0.004. That’s quite a bit lower than half a cent per Minute of Use.
The application performs several functions:
- It accepts costed routes from multiple Long Distance Service Providers,
- compares the cost route-by-route,
- identifies the cheapest IXC for each route,
- produces a file of the new switch translations,
- and, using an Operator-provided historical call distribution, estimates the expected savings using the new routes.
I wanted an easy way for about anyone to check what would be the impact of running the LCR Application in their situation. Microsoft Excel is capable of doing this, although it won’t easily generate a file of switch translations. Only the route-by-route cost comparison and blended cost estimate are produced.
Least Cost Routing Background
The basis for voice routing translations is the North America Numbering Plan Area, NANPA, using the Local Exchange Routing Guide, aka The LERG. The LERG identifies more than 450,000 distinct routes. Each route is a unique combination of the 3-digit Numbering Plan Area, NPA, or Area Code (internationally called the City Code), the 3-digit prefix or NXX (do you know of an international equivalent term for this term?) The prefix is assigned to the local central office switch. Each prefix can have a maximum of 10 Thousand Blocks, called DEFG. Each Thousand Block has a maximum of 1000 phone numbers, 000-999. The combined format is NPA-NXX-DEFG. Thus all telephone numbers in NANPA are exactly 10-digits, and each route in the LERG can be up to 7 digits, NPA-NXX-D.
Long Distance Service Providers, called IXCs (Inter-Exchange Carriers) in North America, typically use the LERG as the basis for their costed routes tables. IXCs generally distribute their routes tables as CSV flat files. Many IXCs provide only NPA-NXX routes. Some IXCs provide more granular NPA-NXX-D routes.
The Operator Call Distribution is a sampling of all calls originated from this switch during some interval. The call distribution might be sourced from the Billing System or from CDRs. Bangkok Beach Telecom does this using another Perl script which processes ASCII (not binary) CDRs. It produces a Call Distribution which is an ASCII CSV including NPA, NXX, MOU, Cost/MOU. Excel also can be used to associate the existing cost/route. This should be done in a separate Excel workbook to avoid blowing up the size. Knowing the existing cost/MOU is helpful in determining the value of this LCR exercise.
Using Excel
In Excel, it is pretty straightforward to determine the cheapest IXC for each route. The shorthand way to describe the approach is to use INDEX/MATCH. For those who need that expanded, Excel’s MATCH() function returns the row number in the table for the given route, and INDEX returns the cost associated with that row number for each IXC. Table rows are uniquely identified by NPA and NXX, possibly also the D-digit. Then use CHOOSE() to compare different IXC combinations.
To set this up, the routes table of each IXC is imported into Excel. The format is NPA, NXX, Cost/MOU. After importing, a fourth column is created which concatenates the NPANXX. This column simplifies use of the MATCH() function. On a separate worksheet, the Call Distribution is imported. The format is NPA, NXX, MOU, Cost/route
On a separate worksheet, the Call Distribution is imported. The format is NPA, NXX, MOU, Cost/route. Again, a new column is created which concatenates NPA & NXX. You can probably guess that we use NPANXX as a common index into all the tables.
Lastly, the CHOOSE() function allows different combinations of IXC to be selected and tried.
If you make each IXC’s routes an Excel Table object, the resulting formula is pretty simple. If you are more comfortable with VLOOKUP(), it is still possible but this is slower than using the Table Object.
Whichever approach you prefer, it’s still a big load for Excel. A typical Operator Call Distribution for a one-month interval will use more than 100,000 distinct routes. The routes table provided by each IXC has around 145,000 entries. These are all NPA-NXX routes; the D-digit was not used in this case. So in English, for each row in a 100,000+ row table, 3 tables having more than 145,000 rows must be searched. If your IXCs provide D-digit routing, those tables could have up to 455,000 rows. Excel will take some time, minutes even, to recalculate.
Conclusion
The Excel Least Cost Routing Model is far from perfect. For example, if one or more of the selected IXCs does not support a specific NPANXX, the worksheet errors. The error could be hidden using IFERROR(), but that falsely lowers the aggregate cost/MOU.
There is also no automatic way to cycle through all possible combinations of IXC. For example, if you try using 2 IXCs in the model, there are 3 possible combinations: IXC1 alone, IXC2 alone, and the combination of both IXC1 & IXC2. Each combination must be tried separately. The number of possible combinations is always 2 to the power “n” minus 1, where “n” is the count of IXCs being modeled. So with routes tables from 3 IXCs, there are 7 possible combinations. With 4 IXCs, 15 combinations to try.
Also, there can be real-world constraints. Bangkok Beach Telecom tried using the model with 4 IXCs. The Application generates over 160,000 distinct routes and an outstandingly low blended cost/MOU. However, the switch used by that operator, a Nortel MTX CDMA switch, could only accommodate up to 128,000 unique translations routes. So that switch could not fully take advantage of the 4-IXC model.
All that being said, the Model is a good use of Excel, and can quickly indicate which IXC combination, if any, is worth pursuing. Good practical examples of INDEX/MATCH, CHOOSE, INDIRECT.
The Least Cost Routing Workbook
Click the link below to get the workbook. You’ll receive an email with 2 links. The first link is a partial version. I’ve stripped out all routes with NPA greater than 209. This leaves roughly 5,200 routes, which Excel handles with no noticeable delay. This workbook is about 2 MB. I added one column to this workbook in worksheet “LCR Forecaster”.
I added one column to this workbook in worksheet “LCR Forecaster”. The column is called “Improvement” and it shows the route-by-route impact of the new routes. Excel cannot easily produce a file of switch translations which limits the value of this approach. But you can manually implement the most impactful routes. Sort this worksheet by the Improvement column in descending order. Row 2 will show the route that will save the most money. Assuming you already have links to the IXCs you test, create your own translations entry for this route and immediately begin realizing savings. Repeat for row 3 and as many other rows as you want.
The second link I’ll send you is the full version. This version has more 100,000 routes in the Call Distribution, and each IXC quotes more than 145,000 routes in their Routes Tables. Around 27 MB, and it might challenge your computer running Excel. I did not add the Improvement column to this workbook. Let’s call that an exercise for the reader.
To use either one in your situation, replace the existing call distribution with yours, and replace the existing IXC routes tables with those of your IXCs, and start the saving. Be sure to document the impact! And give Bangkok Beach Telecom a call if you want help lowering your Long Distance OpEx.
If you would like me to write more about using Excel in the Telecoms environment, please use my survey
Thanks!
Leave a comment