You can’t start a fire without a spark (2)

A Price Benchmarking Tool for Mobile Phones – A Sparklines for XL Showcase

In my recent review of Sparklines for XL, I announced a second post, providing a real life example of a model and a dashboard using Sparklines for XL. Here it is.

This showcase is a price benchmarking tool I implemented for a mobile phone retailer. Limited in size (number of products and competitors), but regarding the functionality pretty close to the original implementation.

This is a screenshot of the dashboard:

Price Benchmarking Dashboard - click to enlarge

Since it is a real life example, not all charts and visualizations are sparklines. The showcase uses a combination of different techniques and visualizations to create a professional price benchmarking dashboard. It is not exclusively dedicated to sparklines, but from my point of view this model provides a good impression of how to use sparklines in real life dashboards.

Interested in getting on fire for a dashboard with Sparklines for XL?

Here you go.

The Showcase Model

This model is a minimalist price benchmarking and competitive comparison tool for a mobile phone retailer. Actually it has been implemented for a mobile phone retailer, but there are no special features tuned for mobile phones. Thus it might be used for any retailer, regardless of the type of products. The purpose of the model is to provide all necessary information about the own product portfolio (sales prices, purchase prices, gross margins) and the prices and price development of the competitors on a weekly basis. All information is available on one interactive dashboard.

The Structure of the Workbook

The workbook contains 6 worksheets:

  • Dashboard: the name of the sheet should be self explanatory
  • Purchase Prices: input sheet for purchase prices of all products on a weekly timeline
  • Sales Prices: input sheet for sales prices (including VAT)
  • Benchmark Prices: input sheet for sales prices (including VAT) for all competitors and all products
  • Parameter: definition and handling of general settings (VAT, start of time line, list of manufacturers, products and competitors, margin targets, etc.)
  • Consolidation: the calculation sheet to consolidate and sort the data according to the user inputs

This reflects the basic concept of structuring workbooks that I am using in all of my models: I always try to separate the data input sheets, the consolidation / calculations and the dashboard(s) as strictly as possible. This requires some discipline during the implementation of the workbook, but it definitely pays off. It is easier to debug, easier to understand, easier to enhance with new features, more secure and less error-prone.

The Calculation and Formatting Techniques

The following techniques are used in this showcase to calculate the results and to format the dashboard:

  • A spin button to select a calendar week and 2 combo boxes to select a manufacturer and a model / product on top of the dashboard
  • Conditional formatting to display a ‘traffic light’ background of the selected product’s margin depending on defined gross margin thresholds (in €)
  • Conditional formatting to highlight ‘our company’ in the price benchmarking table and the selected product in the product portfolio table with an orange background fill color
  • Limitation of the tables on the dashboard to 10 respectively 20 items. Scroll bars and OFFSET formulas allow the user to scroll up and down directly on the dashboard. You will find an according how-to-tutorial on Chandoo.org
  • Option buttons and a formula based algorithm (using LARGE, MATCH and INDEX) to allow the user to sort the portfolio table descending by any given metric (sales price, purchase price, margin in € and margin in %). Once more you will find a detailed description on Chandoo.org

Advanced Excel skills and some tricks and hacks necessary, but no rocket science.

The Visualizations

Detailed information on the selected product

Gross Margin - click to enlarge Sales price, purchase price and the relative position to the cheapest competitive offer are displayed as raw numbers. The most important Key Performance Indicator (KPI) – the gross margin – is additionally visualized by a traffic light background (regarding the defined thresholds for the gross margin in €) and a bullet chart (regarding the gross margin in %).

Price Benchmarking for the selected product

price_benchmarking_table

An interactive table of the company’s and all competitors’ offers of the selected product. The table is sorted in ascending order by the sales prices including VAT and displays 10 companies. The scrollbar enables the user to scroll up and down and walk through the complete list of all companies. The row with ‘our company’ is highlighted by an orange fill color.

The sparklines in the right column of the table visualize the price development over the last 12 weeks, including an average line for these 12 weeks and highlighting the maximum (blue dot) and minimum value (red dot). Sparklines are appropriate here because the data tables already provide more detailed, precise information. Here, the most important information is the actual sales price. The sparklines are complementing this by providing a quick overview of the latest 12-week historical trend. You do not need to have the precise values of the historical development. Thus the sparklines are the perfect complementary visualization.

Pricing and profitability of all products in our own portfolio

An interactive table visualizes the selected model within the context of all our products, including sales prices, purchase prices, gross margins in € and gross margins in %. The table is limited to 20 items and – again – the scrollbar allows the user to walk through the whole portfolio. As before, the row with the selected product is highlighted in orange.

Profitability Table - click to enlarge

The option buttons on top of the columns allow to sort the table (in descending order) by any given KPI.

With regards to the used visualizations: This is where I have to prove that I am willing to eat my own dog food. In the review post, I recommended “[…] to always carefully consider whether or not sparklines are the best choice for your purposes.” This is exactly what I did here. Small word-sized charts within data tables are the ideal use case for sparklines and I used the bullet charts of Sparklines for XL to visualize the margin in % compared to the defined target and the poor-satisfactory-good areas. I could have used Sparklines for XL to create the bar charts of the other 3 KPI as well, but I let Excel’s chart engine do the job. Only 3 standard Excel bar charts, no tweaks or dummy series necessary and formatting and positioning didn’t take much more time than using Sparklines for XL. The advantage: standard Excel charts are faster and more fluid than Sparklines for XL charts. This improves the performance of the dashboard.

The relative position of the selected product in our portfolio

Product Portfolio - click to enlarge

The purpose of this visualization is to compare all products in our portfolio regarding the sales prices on the x-axis and the relative competitive position on the y-axis. A dot at the upper right corner of the chart shows a high-price model where our price is the highest compared to the competitors. A dot at the bottom left corner represents a low-price model where we are offering the best competitive price.

The visualization is implemented with a simple standard Excel XY-scatter chart with 2 data series (all products and the selected product).

The competitive price corridor

The price corridor visualizes the development of our prices for the selected product during the last 12 weeks compared to the price range of all other competitors in this period. The orange line visualizes our prices. The grey area represents the range between the minimum and the maximum prices of all competitors. The closer the orange line is to the bottom of the grey area, the more price competitive we have been and vice versa.

Price Corridor - click to enlarge

This chart is realized with a combination chart (line and area chart).

The Simplifications and Limitations

This showcase is just based on a real-life implementation. In order to be able to provide a stand alone workbook for download, I had to make several substantial simplifications and limitations:

  • Maximum 5 manufacturers
  • Maximum 6 products per manufacturer (i.e. maximum 30 products in total)
  • Maximum 15 competitors
  • Maximum 6 months time frame (27 calendar weeks)

Agreed, the showcase is easily too small for using it in real life. In the original implementation, the Excel workbook was used as a front-end tool, retrieving the relevant data from an SQL database. As a showcase, however, it should be sufficient to demonstrate the usage of sparklines on a professional dashboard.

The Download Link

Download the showcase workbook for free:

The file is an Excel 2003 stand alone workbook, including the VBA, i.e. no need for installing the Sparklines for XL add-in. All you have to do is to enable macros when you open the file.

All data in this workbook is made up.

Final Remarks

In the review post, I mentioned that you do not have to understand, let alone change the VBA code of Sparklines for XL. For this showcase, however, I made some minor changes:

  • I like my workbooks lean and simple and I try to reduce them to the max. When using Sparklines for XL in my models, I always delete the VBA code of all other chart types that are not in use. This case is no exception. The workbook for download contains only the bullet graph, the inverse scale lines and the line chart. All other UDFs of Fabrice’s original implementation are not available in this model.
  • In order to keep the UDFs simple and effective, Fabrice reduced the number of input variables as far as possible. This is perfect because it keeps the UDFs easy to use. Thus, some settings are hard coded, e.g. the width of the lines or the size of the tags. It is a matter of taste, but I wanted to have a higher visual impact on the line chart tags (red/blue dots for minimum/maximum values) and the bullet graph target (red line). Therefore, I increased the hard coded values in Fabrice’s code of the model.

Well, I think the time may have come to go to Canossa: tampering with someone else’s work is bad manners and rude. Especially if it is such a brilliant piece of work. Fabrice, I am really sorry. I guess I am a stubborn old man. Here I stand. I can do no other. Please accept my apologies.

Merci!

Last but not least: a big thanks to Fabrice, Nixnut, Gustavo and anybody else who contributed to this great open source project. From my point of view Sparklines for XL are the best Excel open source project I have seen so far.

Merci mille fois!

Add a Comment

Your email address will not be published. Required fields are marked *