The Microsoft Excel version of the Site Catchment Analysis of Germany including the calculation of the optimal center
In October 2010 I used a show case of a site catchment analysis of Germany to demonstrate the power of Calculated Fields in Tableau. During the weeks that followed we had quite an avalanche of follow-up posts on this article: Site Catchment Analysis with Tableau 6 described an upgrade of the original workbook to the new version 6 of Tableau, including the roadblocks I hit during the upgrade. In a fantastic guest post series called “Another Look at Site Catchment Analysis with Tableau 6” (part 1, part 2, part 3), Richard Leeke showed how to overcome most of the disadvantages in my implementation.
I suspect you may already be fed up with this example. Despite the risk of boring you, I decided to stay a little longer with this topic, mainly for the following 3 reasons:
- The use case turned out to be a very interesting and yielding example for the discussion of features, techniques, workarounds and performance facets of Tableau Software. I guess everything has been said (especially by Richard) with regards to Tableau. But what about Microsoft Excel?
- Furthermore, finding data points within a certain radius from a given center has many more use cases than just a site catchment analysis. You may analyze sales performance KPIs, customer behavior or logistical statistics within a certain area. I am convinced, a Microsoft Excel version of the site catchment analysis can be very useful for some of your analyses of geographical data.
- Last, but not least, the Tableau implementation allowed to display the results of any user selected postcode, but it lacked a feature to calculate the optimal center of any given radius. A task tailored for Microsoft Excel.
Thus, today’s article will provide a Microsoft Excel workbook to conduct a site catchment analysis and to compare the results of the optimum with any given center, including some nice interactive features on the dashboard. As always the Excel workbook is provided for free download.
The Background and the Reference
As already mentioned in the introduction, an example used for a Tableau how-to tutorial (Calculated Fields in Tableau) was the starting point. The following screenshot shows the original dashboard:
A map displaying the center and the catchment area, a Google Map view of the center and a couple of bar charts. However, we will not rebuild this dashboard using Microsoft Excel. Instead, we will add some considerable improvements and enhancements:
The Idea – Automatic Calculation of the Optimal Center
The idea of how to enhance the Tableau workbook is simple and obvious. Displaying one selected center is only half the battle won. The main reference point is missing: the optimal center, i.e. the postcode with the maximum catchment area.
A task tailored for Microsoft Excel: we will exploit Microsoft Excel’s flexibility and calculation power to compute the optimal center and compare its main statistics to any center selected by the user of our workbook.
The Dashboard
The Map
The map of Germany with all postcodes (PLZ2, i.e. the first 2 digits of the 5 digit German postcode) displays the optimal center (dark blue) and its catchment area (light blue), the selected center (red) and its catchment area (orange) as well as the overlap of the 2 catchment areas (dark red):
The size of the bubbles represents the population of the postcodes.
Geographic visualization at a glance. You can immediately identify the location and the covered areas of the compared postcodes in Germany.
The Comparison of the Selected Center and the Optimum
Like in the Tableau workbook, we want to display the Google Map view of the selected center in a Webbrowser on the dashboard. Furthermore, we compare some selected measures of the selected center and the optimal center: some descriptive statistics like the names of the cities and the population, the total number of covered postcodes and – most important – the population covered within the given radius:
Take a good look. Pie charts, that’s right.
I do not like pie charts very much, I know all about their disadvantages and therefore I rarely use them. In this specific case, however, I am convinced they are the best way to visualize the results: Only 2 data points and no trend to be displayed. I can’t think of any chart type visualizing this type of data better than a pie chart. Please notice that the colors of the pie series are corresponding to the colors used on the map.
The Range of Areas
The last visualization on the dashboard is a table of selected postcodes. My first approach was displaying the top ten postcodes in terms of the maximum catchment areas. However, all top ten are usually pretty close to the optimal center and this does not provide much insights. That’s why I decided to show a range across all postcodes. The optimal center, number 10, 100, 250, 500, 1000, etc. This way, combined with the bar chart included in the table, you get a better impression of how the data is distributed:
The Interactive Features
The workbook provides pretty much the same interactive features I already described in Bluffing Tableau Actions with Excel: Besides selecting one specific postcode by entering it in the grey cell top right, you may also click on any given data point on the map, on a row of the data table or on a data point of the bar chart. After doing so, the selected center will be updated on the whole dashboard and the Webbrowser will show the Google Map view.
The Results
Here is a screenshot of the complete dashboard:
Lots of information, highly interactive, but still a one page display including more or less all you need to know.
The Drawback
Please be advised that this workbook is pushing Excel’s computational capability to its limits. From time to time, Excel may complain about not having enough resources to finish the calculation. Don’t worry. Simply close the workbook and open it again. Most of the time it is working like a charm. From time to time, however, Excel seems to surrender…
The Download Link
Here is the Microsoft Excel workbook for free download:
Download Site Catchment Analysis Germany (Microsoft Excel 2003, zipped, 2156.9K)
Acknowledgements
Many thanks go to the usual suspects: Giedre Aleknonyte, Matt Grams and Richard Leeke have been kind enough to spend some of their precious time reviewing my workbook (again!). Thanks Giedre, Matt and Richard for your excellent and invaluable feedback.
What’s next?
After today’s plain description of the background and the features of the model, the second part of this little series will go into the details of the implementation: the charting techniques, the extensive use of named formulas and the VBA to calculate the optimal center.
Stay tuned.
Update on January 22, 2011:
Richard Leeke kindly pointed me to an issue in the workbook originally provided with this article. Richard was "lucky" enough to find a bug with his first click on a postcode (04838). Excel didn’t crash, but the map didn’t show the correct catchment area and the cells supposed to show the measures of the selected center displayed ugly error messages.
I was able to reproduce the bug and tracked it down. It seems to be a numeric accuracy issue: after rounding latitude and longitude in the raw data down to 8 decimal places (instead of 13), all seems to work well again. I don’t have a clue why, though. All postcodes had geodata with 13 decimal places in the original workbook, but only one postcode broke the calculation. I have no idea why.
Thanks for the heads-up, Richard. I updated the file provided for download now.