This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.
“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”
—Stephen Few, Information Dashboard Design (2006)
The dashboards were judged on the clarity and effectiveness of their design, particularly
- Clean and clear organization
- Effective table and chart design
- A single-screen display, properly designed for the web, screen or print outs
Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for
- The Dashboard layout
- Data management, pulling data from a database or data warehouses
- Data logic and calculation : YTD figures, variances, etc….
- Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook
Today we will review the winning entry, Wades Stokes Bank Dashboard:
“When I took over a CFO position a couple of years ago, I found a company ruled by PowerPoint. The company controller was spending inordinate amounts of time preparing for the country board 25-page PowerPoint presentations on the monthly results, and we spent hours arguing over numbers rather than discussing the business.
At the time, I had just read Lou Gerstner’s autobiography, in which he described how he resuscitated IBM. In one key scene early in the book, just after taking the CEO position, Lou Gerstner leaned over at the beginning of one presentation from a division head, turned off the projector machine, and asked something like, “Why don’t we just talk about your business?”
In my position, I felt just the same. I wanted to talk about the business, not argue on numbers rolling off an endless procession of slides. So, we needed to get all the main monthly numbers for our bank on one A3 or A4 piece of paper so that we could talk about our business, not argue on the details. On the dashboard I did not include the banking specific KPIs on asset-liability management or treasury issues since they were covered in a different regular meeting – my focus was on the country boards, which was comprised of the business unit heads and the heads of support groups.
Now, I have been a fan of information visualization since my sister gave me Edward Tufte’s first book in 1989, and I had found MicroCharts from reading Stephen Few’s blog where he described the winning entry in a contest from a few years ago. So, needing a quick start, I started playing with this tool. In the dashboard, I incorporated a number of graphs I think I got from Charles Kyd’s e-book on dashboards (www.exceluser.com), but I also used a basic layout from Timothy Dearden of Morgan Stanley that I saw at a budgeting conference in Amsterdam. The result came together in a couple of weeks between other projects.
I do have to admit that it didn’t stem the PowerPoint tide dramatically – our operations still live and breathe slides. However, though I have taken a new job, I think the report is still “live”, and I do think that a number of the themes and tools (sparklines, small multiples, high data-ink ratios) have started to catch hold and are infiltrating throughout the group’s reporting.”
Wade Stokes has been working as the Head of the Change Management Office, Baltic Banking, in the Swedbank Group, for the past year. Prior to this he worked as the CFO of one of the Baltic banks within Swedbank. A U.S. citizen, during the past fifteen years, Wade has worked as CFO for a number of mobile telecommunications companies in the Baltics, Russia, and Denmark.
Having in a board meeting a printed A3 report with all relevant information available, summarized in detail, truly achieves More Information per Pixel – or shall we say More Information per Dot.
“All history of improvements in human communication is written in terms of improvements in resolution: to produce, for viewers of evidence, more bits per unit time, and more bits per unit area. Slideware is contrary to that history. Trading in reductions in resolution for user convenience or for pitching may useful in mass market products or in commercial art, but not for technical communications.”
— Edward Tufte, Ask E.T Forum, Nov 10 2006
Printed dashboard reports have a much higher resolution than on-screen dashboards, and therefore allow you to show more KPIs, charts, tables and sparklines on a single page. When a certain KPI is being discussed, a person can express his opinion and refer to a specific chart, table or figure available for everybody in the meeting.
One page A3 reports have a long history in Toyota. Toyota CEO Katsuaki Watanabe recently urged employees do use one page reports and to stop the wasteful practice of using PowerPoint.
For the country board meetings the Excel Dashboard is converted to a PDF file, distributed as an email attachment and printed out on a high resolution color printer. The reports gets printed out with the data centered on the page, with empty space on both sides to take notes, make annotations, and record comments during the monthly board meetings.
The PDF format is ideal to share the A3 dashboard report. All charts, fonts, including the MicroCharts fonts, are embedded in the document and the recipient needs no additional software to print the report. Fonts (MicroCharts are fonts) are stored as vector graphics and scale very well, when printed with high DPI settings, which results in razor-sharp sparklines in the printouts.
The basic layout of the Bank Dashboard was inspired by a financial dashboard layout suggested by Card Timothy Dearden of Morgan Stanley:
A clean and well thought through organization, all information needed for the monthly country boards meeting is available on one A3 page. The left side of the page contains the P&L sheet of the Bank, where the Income and Expense are detailed in tables in the bottom left. A small ranking table shows the top and flop performer in Revenues per Head. The right side shows a Lending and Saving analysis, at the top, and measures the execution of the Bank strategy in a nice Bullet Graph Score Card in the bottom.
This layout also gives the most relevant and important information, the P&L sheet, the most emphasis by putting it in the top left part of the dashboard. Various studies have shown that we have a tendency to read a screen in a certain order, beginning in the top left and ending in the bottom right.
I my last blog post I introduced the The Dashboard Squint Test. A very simple and effective method to assess the organization of a dashboard . You squint your eyes and make an assessment on the overall layout, of elements that stand out, the visual balance and other characteristics of an effective dashboard.
The clean and well thought through organization of the tables and panels, makes the Bank dashboard looking balanced and well organized. The dashboard elements align well in the visual grid. What draws the most attention are the orange dots, orange lines, and orange titles. The Orange alert icons pop out immediately, which is fine, they indicate that something is is out of control and action is required. The effectiveness of the alert icon is slightly undermined by the fact that the color orange is already used in the charts and titles. Using the same color for multiple purposes can weaken the pop out effect. To further improve this we could make the titles black and use a Gray for last Year lines in charts.
Let examine some of these panels more in detail:
Wade formatted the income and expenses numbers to million EUR and one decimal place. This keeps the numbers short and easy to read. Employee figures and financial ratios lack a unit label, which is not a problem as the % sign provides an implicit labeling and the Employee figures are clearly separated using some white space.
This month, last month, this year to date, and last year to date figures are shown in an easy to scan table. The measures are enriched with the targets, MoM% and YoY% ratios. The small in-cell sparklines, created with MicroCharts let you compare the trends over the last year.
The sparklines highlight the Low and High values using a bright and fully saturated color which makes a lot of sense. The Low and High dots occupy very little screen estate and we have to format them with bright and fully saturated colors, otherwise the dots would not stand out enough. What is a bit problematic is use of the color Red for the high values as it competes with the Orange of the alert icons.
Wade put in a set of small Excel charts that show the last year performance of important figures below the P&L sheet. The small charts are grouped in panels that share a common scale, which allows you to compare absolute values in detail, e.g. Net Fee Income versus Revenue.
The bottom right part of the dashboard shows a well designed score card. The KPIs are thematically grouped, and use sparklines to show the last year trend and bullet graphs to show the performance against the score card targets. Orange alerts indicate that a given KPI is out of control.
Wade used a camera object to solve a layout problem very elegantly. The Scorecard needed a different grid layout than the rest of the dashboard. The Scorecard has different row heights and column widths to the rest of the dashboard tables.
The Scorecard camera object basically is bitmap copy of an Excel range that can be located a different sheet. It can be positioned freely on the dashboard sheet like an image object, and crucially, need not obey the Excel grid rules.
The Scorecard is maintained and calculated in a separate sheet and whenever the Scorecard sheet changes the Scorecard camera object in the dashboard is updated.
Setting up the camera object on your toolbar
The camera object icon can be setup on one of the Excel toolbars by going to Tools>Customize. In the Commands tab, select Tools in the Categories list. In the Commands list, scroll about 60% of the way down the list, where you’ll see the Camera icon. You can then click and drag this icon to any convenient toolbar in Excel.
The flexible nature of the Excel grid, the Excel formulas, Excel charts and MicroCharts allowed Wade to put together this dashboard in a couple of weeks between other projects. Doing this with a standard dashboard tool, would have been more time consuming if possible at all, as Stephen Few explains in this article.
In summary, a well deserved winner, and hopefully one which we can all take some inspiration from. –Well done Wade!
