Do you use Excel Pivot Tables? If you do, then you know that Pivot Tables are a great way to categorize, sum, and average a table with many rows of numbers. If you aren’t yet using them, you really should. This article assumes you already use Pivot Tables comfortably.
But more importantly, Pivot Tables are very important for automating your reports. This may seem obvious, but to automate your reports, you must eliminate the manual steps. Duh!
The #1 Excel Telecom Trick for automation is to use Pivot tables and the GETPIVOTDATA() function.
Dynamic Named Ranges are unquestionably one of the most important tricks for automating Excel Telecom Dashboards and recurring reports. Dynamic Named Ranges (DNR) are exactly what the name implies: an Excel named range whose size can change automatically.
It’s hard to imagine a real-world example of a recurring report which does not benefit from Dynamic Named Ranges (DNR). DNR makes many complex things simpler, and some otherwise impossible things possible. You’ll see huge benefits if you begin using DNR in your reports after reading this article.
I’ve already written many Excel Telecom Tricks. If you’ve followed along and applied these tricks, your Dashboards and reports should already have improved dramatically. You should be well along to complete Excel Automation.
This trick might be the key piece you are missing. Why do you need a DNR? Here are some typical use cases.
It happens all the time: equipment in a Telecoms network stops reporting traffic statistics. It’s often a problem, though not always the one you think.
The problem can be caused by a failing component somewhere along the data delivery path. You might think that’s the problem.
More likely, the device is under excessive load, and it stopped reporting stats to dedicate all its processing power to providing service.
Of course, it is up to you to figure out which it is. Fault Management systems might help by alerting you to the failing component. But if the issue is excessive load, the solution lies on a different path.
Many telecoms components are designed to provide service as their number one priority over all others. When that priority is threatened, low-priority tasks are discontinued. From the perspective of the equipment, we say it’s designed to shed low-priority load when under stress.
Stress is often caused by an anomalously high load, or by a capacity shortfall. An anomalous load could be triggered by a natural disaster, weather, a civil disturbance, or anything where large numbers of people begin using the network at the same time.
A capacity shortfall means you best start preparing a Capital budget request to fund a capacity augment.
But those aren’t the problems I wanted to discuss. I want to discuss how you can design your Excel Dashboards and recurring reports to handle errors and data dropouts. The types of data dropouts I described above happen all the time. Having an Error Correction strategy built-in will save you tons of time and avoid long explanations. Continue reading “Excel Telecom Tricks – Error Correction”
Here’s a problem I’ll bet you struggle with all the time. You’ve got sites in your network with problems. High handover failures, high dropped calls, other failing KPIs. It might include sites on the edge of a coverage area with no handover neighbors, or sites which are close to lakes and other bodies of water, where RF skip from distant sites is a problem, or maybe sites with dodgy microwave backhaul links. There are tons of situations on a mobile network which can cause these persistent, hard-to-fix problems.
The image shows a list of “Top 10” sites with high dropped call rates. On the left, is the current list. This is before exclusion has been applied.
On the right is the list after the troublesome sites have been excluded. Now all the sites on the list are problems that the team can actually solve.
If your cluster or network has enough of these problematic sites, then the awful statistics they produce can clutter your dashboards and recurring reports, masking other problems which could be fixed and really should. If your reports display a “Top 10” worst-performing sites, these known, unfixable problems will always appear, hiding other problems that you could be fixing.
I’ve seen many teams manually remove these sites from each report. This improves the usefulness of the reports by hiding problems which cannot be fixed. But manually removing these sites is a laborious and time-consuming manual process. It’s a tremendous waste of time. Continue reading “Excel Telecom Tricks – Exclusion”
An objective common to almost every Telecoms activity you’ll do in your career is to share your work with others. Sharing in this sense can take many forms:
Document steps taken.
Identify a problem.
Teach a task to others.
Reveal a hidden truth.
Propose another plan.
The context for each of these tasks is Telecoms. But the actual process might seem only distantly related to the courses you took and theories they taught. Yet it is this process of data collection, manipulation, analysis, and, most importantly, presentation and sharing, which is at the very core of Telecoms.
Communicating your ideas so that others can understand them is a huge challenge. The technological skills and comprehension of your audience can be so unpredictable. Their goals and objectives may differ wildly from your own. So to achieve your goals and objectives, it’s up to you to communicate them in a way which is suitable for your audience.
There are entire courses taught on data presentation and visualization. It’s a big topic with lots of angles. In this article, I’ll discuss a very narrow technique from the field. It’s called “normalization”. Normalization is a way to present data so that it’s meaning or implications are more clear. I’ll show you several approaches for using Excel to normalize your data. Continue reading “Excel Telecom Tricks – Normalization”
We make lots of reports in Telecoms. Telecoms seems to be built on reports. Reports and acronyms. Reports for every piece of equipment, for every circuit, for every service, for every product. We make reports for forecasting, budgeting, both CapEx and OpEx, analyses, models. Tons of reports. Reports of all the reports.
One of the most common types of a report in Telecoms is a seasonality report. What is Seasonality?
It’s easy to find examples of seasonality. Here are some:
The traffic Busy Hour is an example of daily seasonality. A graph of traffic load looks pretty much the same day by day.
Weekend data traffic might be higher than weekdays because people have more time to stream movies. This is weekly seasonality
Government or military employees might cause revenue seasonality by buying more phones and prepaid service when they get paid on the 1st and 15th every month. This is monthly seasonality
Some US carriers experience a boom in Smartphone sales in March-April-May when people receive their government tax refund. That’s a form of yearly seasonality.
With any tool you use frequently to solve many problems, there’s sure to be lots of tricks and shortcuts that can make your job so much easier. Microsoft Excel is one of those tools for Telecoms, and there certainly are tricks and shortcuts. Learning them will help you become more proficient with Excel, and give you a pocketful of shortcuts for doing real work quickly.
This will do more than simply save your time. It allows you to derive insights and identify opportunities. You can actually start to understand what the data mean.
A common problem in Telecoms is that machine-generated raw data isn’t in the format you want. Or maybe there is data missing which is required to complete your analysis.
In this article, I’ll show you a technique I call Synthetic Fields. Synthetic fields allow you to modify data, or add data related to the raw data. Often Synthetic Fields are used to provide additional ways for Pivot Tables to summarize your data. This helps when you want to automate your Dashboards and recurring reports. Continue reading “Excel Telecom Tricks – Synthetic Fields”
Here’s a challenge we face all the time in Telecoms: we need a list of the most recent months of data in a table. For example, a Telecoms Dashboard, which might show data for each of the most recent 12 months.
For example, your Dashboard for May 2018 might show data from May-18, April-18, March-18, etc., all the way back to June of 2017. Those are the most recent 12 months.
A typical Telecoms Dashboard shows one or more Key Performance Indicators (KPI) by month for each of the last 12 months.
Using the most recent 12 months is quite common in many industries. So common, in fact, that is has a special name: Trailing Twelve Months, often abbreviated TTM. Ideally, the Trailing Twelve Months (TTM) are re-calculated as soon as you add data. That is, if you automate it.
In this article, I’ll show you how to set that up. Like so many things in Excel, it’s surprisingly simple. It’s more work to explain it than to actually do it. But it’s worth figuring it out. It’s a big part of automating your Dashboards. Continue reading “Automate Telecoms Dashboard Dates”
As a Telecoms Technical manager, your job is often about speed. Make a report faster. Prepare a presentation faster. Forecast usage faster. Analyze a problem faster. Create a budget faster. See the pattern?
Youʼll use Excel to create models in all these examples; make assumptions and build them into those models. Often, there will be several use cases for you to model.
How do you do that? What’s your style? Do you work on one case to completion, then copy/ paste to start the next case?
If you want to impress your boss and propel your career, learning how to properly model different use cases is an indispensable technique. A key trick to effectively modeling different use cases is called Abstraction. Abstraction is also an important trick for automating your dashboards and recurring reports.