An interesting question was posted over on Stack Overflow recently.  The question presented a challenge which I’ll paraphrase thus: Based upon the accompanying billing system data, how would you use Excel to list the Base Station generating the highest revenue by Area, Region, and Product?

First, let’s pause for a moment of thanks that this challenge gives us a chance to deal with money, with revenue.  Engineers are far too infrequently exposed to this side of the business.  Sure, we get to dabble in financial assets for budgeting and invoice processing.  But otherwise, the Finance and Accounting departments conspire to keep a wide gap between us and money. I wonder why that is?

Excel for Telecom managers

Anyway, let me continue with this challenge. The requirements seem trivial, so let’s take a look out how to do this. Click the Link below to download the workbook to see what I’ve done.

Download the workbook

The first worksheet is a snippet of the raw data.  As billing system output, it was organized by telephone number, with the revenue for each area, region, and product on a single line.  In my experience, this is pretty typical for a billing system report.

But for Excel to satisfy the requirement easily a simpler layout would help. Excel will find it easier to handle if revenue for only one product appeared on each line. As there are 3 products, “Data”, Voice”, and “SMS”, the data would be easier to process if the current single line is changed to 3 lines.

Pre-Process the Dataset

Fortunately, only 60 or so rows of data were provided.  Splitting such a small dataset into separate rows was easily done.  I’ve skipped the steps I followed to do that.  If there was more data, I can think of 3 approaches:

1. Ideally, you could chat with the Billing Team to see if the output could easily be changed to a more Excel-friendly format.
2. If that wasn’t possible, VBA could be used, although I’m not a fan of VBA.  Are you VBA literate?  How would you do this with VBA?
3. Lastly, it might still be possible to solve this challenge with the data as provided.  But I think the Pivot tables would be problematic.  Maybe an Excel Database approach would work.  I did not consider this long before normalizing the raw data.

However you want to go about it, the approach I took was to first manually pre-process the raw data to make it more Excel-friendly. 

Set-Up the Dashboard

Here are the steps.

1. Worksheet “Raw Data” contains a sample of the original data outputted by a billing system.  Clearly, I have worked the raw data extensively to hide its provenance.  Nobody would name a Base Station “Apple”.  I also converted the data to the format described earlier in the article.
2. On Worksheet “Normalized Data” I selected all Headers and data to create a named range called “Data”.
3. I inserted new worksheets named “Dashboard”, “Area – Pivots”, “Region – Pivots”, and “Product – Pivots”.
4. On the “Area – Pivots” worksheet, I created a separate Pivot table for each Area.  Each of these Pivot Tables is filtered by that area and shows the “top 1” BTS by Revenue.  To do this, open the Field List box.  Drag Areas to the Filters area, drag Base Stations field to the “Rows” area, and drag the Revenue field to the Values area, set to “SUM”. 
5. Next, click the filter button next to “Row Labels”.  From the popup menu for “by value”, select “Top 10”, then specify  1, only a single result.
6. To use this Pivot Table’s value in the dashboard, click in any cell of worksheet Dashboard where you want to display the result, enter an equals sign (“=”), then click the value in the pivot table.  The first Pivot Table, for Area “Circle”, shows a value of  $10,685 for Base Station “Apple”.  The “Top Revenue Base_Station by Area” block on this Dashboard links directly to those Pivot tables on worksheet “Area – Pivots”.
7. Repeat the steps 4-7 for every Area defined within the dataset. Be sure leave several rows of space between each Pivot table. Otherwise, you might see an error message about overlapping Pivot tables.
8. Now I moved on to the Region worksheet.  I created a separate Pivot table for each region.  Repeat the same filters as for the Areas, and again create the summary block on the Dashboard.
9. On the worksheet named Product, repeat this approach one more time for each Product.

That is basically the end of the steps I took.   The Dashboard complies with the original request.  This set up also allows more data to be added and the results will be reflected automatically.  That’s a big win for saving you time.

 

Additional Notes

To communicate clearly what I did to set up this Dashboard, I also wrote these notes:

1. The Named Range “Data” is 100 rows larger than the current data set.  This means that up to 100 rows of data can be appended, a “Refresh All” done on the Pivot tables, and this Dashboard will still be accurate.
2. If more than 100 rows of data are imported then the Data named range will need to be extended.
3. If a new Area, Region,  or Product is included in the imported data, then additional manual work must be done to incorporate them.  Basically, a pivot table will need to be added for any new Area, Region or Product.  Automating this process was not one of the requirements.
4. Note also that charts were not specified in the requirements.  I have not created any.
5. It is left as an exercise for the reader to format the Dashboard for printing.

That’s it, our Dashboard is complete.  Note that this approach again complies with my “Update, Refresh, Share” Mantra, whereby the workbook incorporates new data with a simple 3-step process.  Being a technology manager has never been so easy!  

If you are really interested, you might also try to do more with this dashboard.  For example, at present, each record in the dataset includes a date.  How would you trend this data over time?

Click to Download the Workbook

Follow @telecomvoices