Be honest: do you know which incremental billing model is included in your mobile phone or fixed line tariff? No worries, I suppose most people do not know. However, incremental billing models represent a considerable part of mobile or fixed line operators’ revenues.

But what is incremental billing? It means that carriers are pricing calls in slices longer than a second. Full minute billing means – for instance – that you are paying two full minutes, although your call was only 61 seconds long.

How big is this effect of additional revenues? How much revenues do carriers make by using incremental billing?

Today’s post presents a simulation model to reveal and evaluate the revenue potential of different billing increment models. As always including the Microsoft Excel workbook for free download.

The background

What does incremental billing mean exactly? A billing increment is the smallest slice of a call a carrier will charge. Billing increment models usually have a nomenclature consisting of two numbers:

• Model 1/1 means a true per second billing: talk for 20 seconds, pay for 20 seconds.
• Model 60/1 means a minimum of 60 seconds and per second billing beyond the first minute. Talk for 20 seconds, pay for 60 seconds, talk for 61 seconds, pay for 61 seconds.
• Model 60/60 means full minute charging: talk for 20 seconds, pay for one minute, talk for 61 seconds, pay for two minutes and so forth.

These are the most common ones. However there are a variety of others (60/30, 10/10, 6/6, 18/6, etc.), working accordingly.

Let’s call a spade a spade. If you are having a tariff with a billing increment larger than one second, you will pay for air-time you never used. The effect of billing increments on the total amount of your phone bill depends not only on the incremental billing model, but also on the distribution of the call durations: the more short duration calls you have, the higher the impact and vice versa.

So, billing increments mean additional revenue potential for the carriers. But how large is this potential? How big are the additional revenues of different billing increments? A simulation model will help us to answer this question.

The probability of call durations

First and foremost, the additional revenue potential of a billing increment model depends on the call duration distribution, i.e. what is the probability of any given call duration?

For sure, this will be a positive or right skewed function as shown in the screenshot on the left. The mass of the distribution is concentrated on shorter call durations, e.g. a call lasting 3 minutes is more likely than a call lasting 30 minutes.

One function that reflects this is a log-normal distribution, i.e. a probability distribution of a variable whose logarithm is normally distributed. There are other options as well (like the Weibull distribution), but the log-normal distribution will do the job in our simulation model.

The implementation and the calculations

The simulation workbook consists of only two worksheets: the dashboard (see next section) and the calculation worksheet.

The used formulas on the sheet calculations are not too complex. There is one formula to create the log-normal probability function using the functions NORMDIST and LN. The formulas calculating the billable seconds per model use the functions QUOTIENT and MOD, but I think they are still pretty straight forward and easy to understand. To support the visualizations on the dashboard, there are some more calculations using MAX, MEDIAN, VLOOKUP, INDEX and MATCH, but still no rocket science. Everything else are simple basic arithmetical operations.

I am sure you will easily figure out how this works without further explanations.

For the sake of completeness: to limit the workbook to a reasonable size, the simulation is restricted to call durations up to 1,800 seconds, i.e. 30 minutes. Agreed a limitation, but no great effect on the results because of the right-skewness of the probability function.

The visualization

The probability function of the call duration is visualized using a standard XY scatter chart and 3 additional points to display the most important values at a glance:

the mode (the call duration that occurs most frequently), the median (50% of all calls are shorter, 50% are longer than the median) and the average call duration.

The distribution function shows the cumulative probabilities, i.e. the probability that a call is less or equal than a given call duration. The according chart (see left) visualizes how fast the probabilities converge to 100%. Again, this is an XY scatter chart and the additional point highlights and displays the threshold, where 80% of all calls are shorter or equal than this call duration.

An area chart compares all billing increment models and visualizes the additional revenues compared to the model 1/1. This chart visualizes the way the models are working and at which call durations the most additional revenue is made.

Finally, a very simple table and bar chart displays the most important results: how much additional revenue is made by each model compared to a billing per second (model 1/1). Furthermore, the user of the simulation workbook is able to define his own models by changing the values of the first and the following billing increments in seconds.

The simulation

The simulation part of the model focuses on the call duration probability function. By changing the mean and the standard deviation of the log-normal distribution (using the sliders), you may change the shape of function and see immediately the impacts of your changes on the dashboard.

The workbook is restricted to 5 different billing increment models. However, you can easily change the predefined models by typing in new values for the first and the following bill increments in the cells M27:N31 on the dashboard. Thus you can adapt the simulation to your own requirements and what you consider to be reasonable.

The results

Here are two examples of simulation results:

Let’s select a mean of the log-normal distribution of 6 and a standard deviation of 1.1. Most calls have a duration of 117 seconds, the median is 300 seconds and the average duration of a call is 488 seconds. A billing increment model 60/60 results in 6% higher revenues for the carrier than a model charging per seconds air-time (i.e. model 1/1). Not too bad, in my humble opinion.

If you choose a less right skewed distribution (mean: 6.4, standard deviation: 0.7), most calls last 363 seconds, the median is 411 seconds and the average call duration is 659 seconds (i.e. almost 11 minutes on average). The model 60/60 still produces 4.5% more revenues than a billing increment model 1/1.

Please be advised that both examples are arbitrarily chosen examples. I would assume they might represent reasonable probability functions. However, the parameters are made up, i.e. the given distribution is not based on real data of a real carrier.

The dashboard and the download link

Here is a larger screenshot of the one-page interactive simulation dashboard:

And here is the Microsoft Excel workbook for free download:

Download Simulation Billing Increment (Microsoft Excel 2003, zipped 425.8K)

Using mathematical functions in Microsoft Excel simulation and planning models are a very interesting topic. I am planning to write one more post on this during the next weeks. Furthermore I am having some more Tableau tips and workbooks in the pipeline.

Stay tuned.

The recent article FIFA World Cup Statistics with Tableau included a dashboard visualizing statistics of the FIFA World Cups from 1930 to 2006 by team, provided on Tableau Public.

Today’s post is a follow-up to that article: FIFA World Cup scorers statistics from 1930 to 2006 on two different dashboards.

The data source

The scorers data is coming from the same source I already used in the previous article: the official website of the FIFA: FIFA.com - Previous FIFA World Cups™

Dashboard 1: Scorers Statistics per Team

This dashboard visualizes the scorers’ performance of one selected team during one or several tournaments:

• select one team from a drop down list and one or several tournaments
• visualize matches played and minutes played per player (scorer) using sorted bar charts
• visualize the wins, draws and losses per scorer using a sorted stacked bar chart
• visualize different measures regarding goals, shots, corners, etc. per scorer (bar charts again)
• visualize activity (shots vs. minutes played) and effectivity (goals vs. minutes played) of scorers using scatter charts

Here is the dashboard:

[click on the image to open the interactive visualization on Tableau Public]

Dashboard 2: Scorers Statistics per Tournament

This dashboard uses the same data source, but it slices the data in a different way: the global filter is the tournament now, i.e. the visualizations compare all scorers of all teams for one selected FIFA World Cup. After selecting one tournament from a drop down list, the dashboard displays the same measures using the same types of visualizations as on dashboard 1 (except for the activity scatter plot). This time however, all scorers of all teams are shown, including the team names.

Here is the dashboard:

[click on the image to open the interactive visualization on Tableau Public]

The Limitations

Please be advised that the limitations (incomplete data source, consolidated team names, missing flags) described in the previous article also apply to the 2 dashboards above (details see here: FIFA World Cup Statistics with Tableau).

What’s next?

I am still planning to write one or two posts on Custom Shapes of Tableau. However, the FIFA World Cup 2010 is still ongoing, so I am a tempted to do another football visualization first.

Stay tuned.

Daniel Ferry’s blog Excel Hero is a source of permanent inspiration for me. In a recent article called Animate cumulative data with Tableau, I described how to use a custom SQL data connection to show cumulative data using Tableau’s page shelf. This post was inspired by one of Daniel’s great articles: Excel Location Mapping.

In true tradition of stealing Daniel’s ideas, today’s short post contains another replica of one of his Excel workbooks using Tableau Software: Daniel’s implementation of the "1,000 Blocks" sculpture by Curtis Steiner (unfortunately without the soundtrack, of course…).

Here is an animation of a selection of 10 out of 78 slides:

Tableau Public does not support the slide show of pages using the playback controls. Thus, I decided not to publish on Tableau Public. Instead, here is the Tableau packaged workbook for free download:

Download 1,000 Blocks Celtic Muse (Tableau Packaged Workbook, 2640.7K)

To open this workbook you need Tableau 5.2 (14-day free trial) or the free Tableau Reader.

Last, but not least:

Daniel, many thanks again for sharing your fabulous work, for the time you took to review my Tableau workbook and for your permission to use your idea here. Special thanks go also to Daniel’s wife and daughter who helped him with the encoding of 78,000 (!) tiles. Thank you very much.

I have to admit, I am a little late: The FIFA World Cup 2010 in South Africa already started yesterday. And of course there have already been a couple of interesting posts on visualizing the World Cup statistics elsewhere:

Chandoo had a couple of nice posts using Microsoft Excel: FIFA World Cup Excel Spreadsheets, Football Betting Sheet Template and Official FIFA World-cup Soccer Balls since 1930 in an Excel Chart.

Ross Perez provided a very interesting visualization of the History of the World Cup on Tableau’s own blog, focused on winning and tie percentages.

Better late than never. Today’s post includes my 2 cents: a visualization of the FIFA World Cup Statistics since 1930, using Tableau Software and focused on the performance and match statistics per team.

The data source

Where would you go to download FIFA World Cup raw data? Of course, the official website of the FIFA, right? So did I. Here is the link to the data source:

FIFA.com - Previous FIFA World Cups™

The Visualization with Tableau Software

The basic idea of the visualization is to focus on the performance of one selected team during all World Cups:

• select one team from a drop down list
• visualize the results of this team in all World Cups
• visualize the wins, draws and losses per World Cup
• visualize the goals for and the goals against
• compare some match statistics like matches played, shots on goal, etc, and
• compare some fair play measures like yellow and red cards and fouls

Here is the dashboard:

[click on the image to open the interactive visualization on Tableau Public]

The Limitations

There are some specifics and limitations coming with the raw data and the visualization:

• Not all match statistics (e.g. like shots on goal, shots wide or free kicks, etc.) were recorded consistently from 1930 on. The source data of this visualization includes what the FIFA provides.
• In order to consolidate the raw data as far as possible, I decided to merge some team names. I replaced the former Soviet Union by Russia, the former Republic of Yugoslavia by Serbia, Czechoslovakia by Czech Republic, etc. Agreed, this is not really correct, but it simplifies the visualization.
• I am well aware of the fact that England, Scotland, Wales and Northern Ireland have flags of their own. Unfortunately those flags were not included in the icon set I used. Thus, I used the Union Jack for all of them. Not really correct, but not totally wrong.

What’s next?

I struggled a little bit using Custom Shapes with Tableau for this visualization. Therefore I decided to write one or two posts on Custom Shapes of Tableau: their power, their limitations and some tricks and workarounds.

Stay tuned.

Combining tables and charts is a very powerful technique for creating Microsoft Excel dashboards. It allows you to integrate texts, values and visualizations into one table. This ensures to have the maximum of information at a glance, including a direct comparability row by row.

I already used this technique in several posts before, like the Sparklines for XL showcase or the Software Project Dashboard examples. Today’s article provides another showcase for a dashboard combining tables and charts.

Football rules the world, especially these days. We are all impatiently waiting for the FIFA World Cup in South Africa, aren’t we? My friend Chandoo recently had a very nice post on visualizing the different footballs used in the World Championships since 1930. That’s remarkable, because Chandoo lives in India and I suppose he is more interested in cricket than football. But as I said, football rules the world these days.

That’s why it somehow suggests itself to use a football-related visualization for today’s post. But I will not go for the FIFA World Cups. Not yet. Today’s article shows how to visualize national football league statistics using a dashboard that combines tables and charts. As always including the Microsoft Excel workbook for free download.

The show case

The idea of this show case is as simple as can be. Let’s assume we have all results of all seasons of a national football league in one Excel table, i.e. the season, the match day, the home team, the away team, the goals of the home team and the goals of the away team:

The challenge is to automatically transfer this raw data into an interactive dashboard showing the actual table for any given match day in any given season, including an overview of the results of the selected match day, further statistics on the match day and some sort options:

The structure of the workbook

The workbook contains 6 worksheets:

• Dashboard

The display: the name is self-explanatory, isn’t it? This worksheet displays the dashboard as shown in the screenshot above

• Results

The raw data: all match results of several seasons (1996/97 till 2009/10 in our example) for one or several football leagues (German 1. and 2. Bundesliga in our example)

• Calculation Table

The heart of the workbook: this is where the action is. The worksheet contains all data consolidation, the sorting algorithm, the set-up of the charts, the match day and overall statistics, etc.

• Calculation Results

The data snapshot: this worksheet provides the relevant snapshot of the raw data, i.e. the results of the season and the league selected by the user

• Calculation Positions

The history: this worksheet contains a data table with all positions of all teams during the whole season. The table is not calculated by using formulas, but rather created by a simple VBA routine

• Control

The interactivity control: the worksheet stores the selection lists and the target cells for the interactive form controls on the dashboard

As already explained here, this reflects the structure of all my Excel models: the data, the calculations and the display on separate worksheets.

The techniques

Creating a football league table, additional visualizations and more statistics from the raw results requires some more complex functions and operations like OFFSET, INDEX, MATCH, LARGE, SMALL, array formulas and others. Furthermore a little trick is needed to implement the formula based sorting algorithm. Detailed information on the used functions can easily be found on other websites and blogs and the sorting technique is already described here. Thus, I will not go into the details of the calculations. I am rather limiting myself to the following two other techniques used in the workbook:

Technique 1 – combine tables and charts on a dashboard

The idea of integrating charts into a table is obvious. The steps to prepare your dashboards are simple:

• Create a table on the dashboard
• Increase the height of the rows. Usually 35 points are enough
• Insert columns where you want to add your visualizations
• Increase the widths of these columns to e.g. 15 to 30 points
• Insert either one chart covering all rows in your table (bar charts or stacked bar charts) or one chart per cell (column charts or line charts)

Especially for the latter, I recommend using sparklines like Fabrice’s Sparklines for XL or Excel’s built-in sparklines (if you are already using Microsoft Excel 2010). If, for whatever reason, you do not want to use sparklines, you can create the charts using standard Excel as well. However, especially the set-up of the charts, the resizing, the positioning and the formatting is laborious work. You have to remove everything from the charts besides the visualization itself, i.e. the gridlines, the caption, the data values, the fill color of the plot area, etc. Sometimes you have to set the scaling of the axes as well.

As I said: laborious work. Here are two tips which might save you some time:

1. The ALT-key
   
   Holding the ALT-key pressed during resizing and positioning a chart will make the edges of the chart snap to the cell grid of the Excel workbook. This helps aligning the charts. In Excel 2007 and later, this trick does not only work for the chart object itself, but also for the plot area
    
   
2. Copy the chart and change the data source
   
   If you have to create several charts (e.g. the column charts or line charts in our example), I recommend creating one chart first and doing all the formatting, axes scaling and resizing for this master. Then copy the master, change the data source of the copy and position it to the right place on your dashboard. Usually this technique is much faster than doing all the formatting again. Another option would be to create all charts, format the first one, select and copy it and paste special formats to the other ones. However, this does not transfer e.g. changes in the axes scaling to the other charts. Thus, I recommend creating a master and simply copy the chart, position the copy and change the data source. From my point of view this is much faster.

Technique 2 – use the workbook calculation and VBA for creating a history table

The basic calculation of the table is based on consolidating the results for one selected match day. However, for visualizing the team trends on the line charts, we need the positions of all teams during the last 10 match days. Of course you could add additional 9 calculation worksheets referring to the according match day. However, this would bloat your workbook, make it harder to maintain and probably decrease the performance.

So here is a better way: use a small VBA routine to create a history table containing the position of all teams during the whole season:

Sub RecalculateTableDev()
  Dim varmatchday As Integer
  Dim rngMatchDay As Range
  Dim rngTableDevStart As Range
  Dim rngTablePosition As Range

  Application.ScreenUpdating = False
  Set rngMatchDay = Range("myMatchDay")
  Set rngTableDevStart = Range("myTableDevStart")
  Set rngTablePosition = Range("myTablePosition")
  Range("myTableDevRange").ClearContents
  For varmatchday = 1 To 34
      rngMatchDay.Value = varmatchday
      rngTableDevStart.Offset(0, varmatchday).Value = rngTablePosition.Value
  Next varmatchday
  rngMatchDay.Value = 34
  Application.ScreenUpdating = True
End Sub

The technique is simple: Loop through all match days, let the Excel workbook do the math and write the positions of the teams to the history table. Finally set the actual match day to the last one (i.e. display the final league table).

Agreed, there is a drawback as well: the VBA takes some to time to create the history table. Though, an update of the history is only necessary if the user switches either to another season or another league. From my point of view this drawback is acceptable.

The download links

Here is the example workbook for the German 1. and 2. Football Bundesliga for free download:

Download Bundesliga (Microsoft Excel 2003, zipped 223.4K)

I can already hear you saying: “who cares for the German Bundesliga”? Well, I do. If you don’t, maybe you will be more interested in the following workbook for the English Premier League:

Download Premier League (Microsoft Excel 2003, zipped 203.0K)

Please be advised that both workbooks are based solely on the match results. Thus, the tables provided within these workbooks do not reflect any possible point penalties because of e.g. breach of license terms, match manipulations, fan riots, etc. The tables are reflecting the sports, nothing else. They do not necessarily represent the official tables.

What’s next?

As already foreshadowed in the introduction, I am planning to do another post on visualizing FIFA World Cup statistics. I am not sure whether I will make it in time (less than 2 weeks left), but I am working on it.

Stay tuned.