Apr 11, 2016

Business Review Charts - Makeover #1 [Singapore Airlines Bar Chart]

We make lots of charts during a business review. Here is one such business review chart from the financial results of Singapore Airlines. We will analyze the chart and do a step-by-step makeover as well. You can also download the excel file by clicking here.

Original Chart of Singapore Airlines

The airline has broken even in 2 quarters and has not done so in the other 3. However, the gap was pretty small. This is how I look at this chart. What is the intended key message of this chart? Well, we do not know because the company has not stated it. The key message could well be, 'the last quarter was our best as we broke even comfortably'.

Here is my diagnosis of this chart:
  1. No key takeaway for the audience. Different people will interpret it differently.
  2. Complex to understand
  3. Visual clutter

Chart Makeover

There are many ways to makeover this chart. The correct makeover depends on what is the intended key message. Here is one option that I recommend.

It has been assumed that the main message of the chart is to talk about how good the operating performance is during the current quarter.

This chart shows the performance (above or below breakeven) using colors which are much more intuitive. Red means we missed breakeven and green means we broke even. All the numbers are passenger load factors.

Since the key message is about how good the current quarter was, the exact breakeven load factors of all 5 quarters have been ommitted. If the presenter wants to show exact numbers, he/she should simply put a table below this chart. There is no need to clutter the chart in any way.

What have I done in my makeover?

  1. Decided on a key message and stated it properly
  2. Simplified the chart and made it easy to understand
  3. Enhanced the visual appeal of the chart by making it free of excess colors and data points

How was this bar chart created?

Here are the instructions in MS Excel 2013. Once you understand the instructions, you can easily replicate these in other versions.

Step - 1 Enter data in MS Excel
Column 1 will contain Quarters. We will not enter passenger data in one column and breakeven data in another. Our desire is to overlap the bars and hence, the numbers we will enter in the excel will be all smaller numbers in one row and all bigger numbers in one row

Step - 2 Create Bar Chart
Choose the first option under vertical bar chart. It is called Clustered Column.

Step - 3 Change Vertical Axis
Right click on the axis -> Format Axis -> Under Axis Options set Minimum Bound to 60. We are making sure the vertical axis starts at 60. Usually the vertical axis should always start at 0. But that will make the differences between the numbers very small, no one will be able to notice. The entire point will be lost.

Step - 4 Overlay Smaller Bar on Larger Bar
Click on any smaller number bar. All the bars will get selected. Right click -> Format Data Series -> Under Series Options set Series Overlap to 100%. Now both the bar graphs will come over each other.

Step - 5 Color the Smaller Bar
Click on the smaller bar. All bars will get selected. Now go to Chart Tools on top, under Format -> Shape Fill -> Choose a light gray color

Step - 6 Color the Larger Bar (which is behind)
There are two quarters where the company broke even. We have to color them in green.

Choose the first bar (FY11-12) by left clicking on it and then click on it again. This bar gets selected. Go under Chart Tools - Format - Shape Fill and color it Green. Similarily, color all the bars. Red for not breaking even and green for breaking even.

Step - 7 Enhance the look and feel of the bar graph
Left click on the vertical axis and choose the font color as white. This will make the axis and axis labels dissapear.

Click on the horizontal axis, increase the font size and change the font color to black. Make the font bold.

Click on the gridlines and click 'delete'.

Enter the chart title. Change the font color and size to what looks good to you.

Remove the legend.

Step - 8 Labeling the Bars
Click on the larger bar. Click on the + sign to the right of the chart. Now add Data Labels (Outside end). Click on the smaller bar and add the labels on Inside end.

We want to show only the Passenger load factors. We need to hide the Breakeven load factors. Click on each number, one by one. If it is the Passenger load factor, change the font color to black and increase the font size.

If the number is breakeven load factor, hide it. To hide numbers on white background, change font color to white. To hide numbers on gray background, choose font color as gray.

You are now done! With practice these steps will take less than a minute and will enhance the look and feel of your chart significantly. But more importantly, you will now have a very effective chart.

Your audience will understand the key message and get the 'message' quickly and easily. You may download the excel file by clicking here. Do you have a chart which you want to makeover? Share it with me. You can write to me at: vivek [at] jazz factory [dot] in.

No comments :

Post a Comment