Fighting IT? Prototype an Executive Dashboard in Excel

I am a moderately advanced Excel user. This means “a dangerous person” for the IT department, but I like this daily fight, and Excel dashboards are among my preferred weapons. Let’s see how they can be used.

Excel is the best tool for executive dashboard prototyping, because of its flexibility and development costs. Creating a fully functional prototype is not hard and it should be available for user feedback in a matter days. So, make sure that, every time you spot a dashboard project, a prototype in Excel is included.

Since most business intelligence applications are notorious for their lack of basic chart formatting options, it shouldn’t be hard for you to create a simply set of charts that the IT is unable to implement. If needed, use some advanced Excel charting techniques (including dummy series), but make sure they add real value to the user experience. Interactive features like visual what-if analysis are always cool and the users love them.

When presenting your project, do your best to convince your audience that you are technology-agnostic and all you care about is to create the best answer to users needs.

IT will try to change your project, naturally. Try to avoid the “security bomb” (their favorite). You know how poor their expensive BI toys are, and you should know what they can and can’t do with them. Minor concessions can earn you some points. When they tell you they can’t implement your core ideas be prepared to fake genuine surprise, compare costs (again) and emphatically say that their options clearly don’t meet the organization’s needs.

Pissing off the IT department is one of the most enjoyable games in corporate life, but be a gentleman and don’t make them look stupid. They don’t usually have a good sense of humour and take their quest to conquer the world very seriously. If you really want to implement the dashboard, don’t make it an island if you can avoid it (connect it to the tables in the IT infrastructure, instead of copy/pasting data). 

Seriously: Excel is a great tool for dashboard prototyping. You can easily create multiple alternative user interfaces, get feedback from users or find design flaws. The end result should be much better than trying to capture some ill-defined requirements and send them to the IT, where user interface design usually ranks very low in their priorities list.

15 thoughts on “Fighting IT? Prototype an Executive Dashboard in Excel”

  1. The real danger of end-user computing (doing core business processing via Excel, SAS, Access) is the lack of control. You may have a very good understanding of your tools, the data, and what needs to be done. You even take the time to test it before passing along results.

    But what happens when you move onto the next thing? Management then goes and asks Bob K. Stupid to update the cool report that you wrote two years ago because they need to add a new data field. Bob isn’t as smart as you and screws up the data model giving management widely inaccurate results in a report that then goes into managing company finances and results in misstated earnings.

    Testing and IT Control have their place…

    /rant off

  2. Matthew: IT doesn’t like office applications because of the lack of control. I know that this can expose the organization to risks that the end user isn’t aware of. But instead of trying to help and create a safe framework where those desktop applications could be used, they often prefer to dismiss them to implement their own agenda (“what is good for the IT is good for the organization”).

    I don’t care if a business report is created in Excel or in Cognos. I just want to know what is the best tool to answer a business need. IT should help me, and that’s not always the case.

  3. “IT should help me, and that’s not always the case.”

    Generally because they have higher priorities elsewhere in the organisation. And your “strategy” as outlined above simply make their job harder and in turn drags the entire organisation down. == $$$$.

    That you regard fighting them as a positive says it all really. I’d have thought you were all on the same side and with the same goal – making the organisation succeed. So why aren’t you? Why are you deliberately making them more inefficient as they are forced to respond to your provocations?

    You call it control, I call it data integrity. Which somewhat unsurprisingly is one of the three pillars of security. Garbage in == Garbage out.

    Jorge you do write some good stuff, but please re-examine what you’ve written above. It shows some serious errors in judgement and understanding. Childish point scoring should remain back at primary school. Not be a cornerstone for how you make a business succeed.

  4. @ Steve: Excel is a good tool for dashboard prototyping. That’s the only serious point in this post (and the island thing, also). However, I do think that the IT sometimes misinterprets its role in the organization as a whole.

    If I am a visualization expert I don’t want them to rewrite what I want to do with the user interface. I can try to design something that can be done with the BI tools, but until recently they all seemed to be pretty useless for serious reporting (they could do some tables…).

    And I would prefer to create something that the IT can support without me, but communication is not easy (from both sides).

    I was going to say that users vs IT is like Windows vs. Mac, but actually that may not be the case. I’m very curious about Microsoft’s project Gemini. Maybe that’s the missing link.

  5. I have always had problems with IT departments when working on a BI project commissioned by some other dept (e.g. Finance).

    While IT departments are extremely important for keeping the IT infrastructure running smoothly, they sometimes can’t comprehend the business need for a quick solution, which can be delivered only by external (i.e. dangerous) consulting companies with more expertise than their (i.e. safe) own . Their mistrust of consultants is usually based on their belief that a quick new application will not be adequately supported and when something breaks nobody within the organisation would be able to fix it.

    Instead of getting involved in the design and implementation of the new solution, which can normally save them a lot of time and resources, they try to isolate/suffocate the new BI project making us fight for it (because we know it is good for the people that need it). If only all IT depts could understand that we are not trying to take anything from them, but rather help their companies with better tools, our and their life would ultimately be a so much easier.

    I don’t like this constant fight with IT depts and lately I am trying to involve them as much in the design and planning process, so they can understand from the early stages what I am trying to achieve and I spend a lot of time showing them how the solution I am building is beneficial for the business that they are supposed to help at the first place. When they don’t get it and when the business users themselves fail to convince them, I am forced to resort to fighting. An uphill battle, since all depts in a company get paid from the same money pile and inherently trust each other.

    The joys of consulting 🙂

  6. Jorge, this is like World War 1. So much waste before each side realises the futility of war.

    In our organisation (like many others) the data source that you would likely be accessing (a data warehouse) is located in a different country. Since Excel is a desktop tool, you will be pulling a lot of data across comms lines into your spreadsheets. If not, you will certainly need to know SQL, and possibly how to optimise it to reduce the volume of data retrieved. Now you are a psuedo IT guy and keeping with the anaology, you would be “sleeping with the enemy” 🙂 Such an environment needs a web based architecture, not a desktop architecture.

    In our organisation (like many others), we have many guys exactly like you, who all produce their spreadsheets. They don’t just like fighting with IT, they like fighting with each other about whose is “the best”. At the same time, we have management who are increasingly asked to work as part of a global team. This means standardisation. Your bosses are starting to question why so many different versions of the same thing exist. As business becomes increasingly globalised and consolidated, standardisation will be more important. In keeping with the analogy, your “top brass” will make some strategic decisions that will eventually render your front-line tactics irrelevant.

    Here’s what to do. Divide and conquer.

    Define “standard reporting and dashboards” as the routine reports and dashboards that management need to see “what” is happening in their business. Standardise that. Let IT handle it, optimise it for performance and security etc. Business side must insist on a seat at the software selection table in order to ensure it is effective (friendly). Call this team “the infantry”.

    Next, define all the non-routine stuff as “analytics” that provide management with the “why” aspect of their business. This requires your skills. IT needs to let you do what you do best with Excel. Get them to supply the data you need for each analysis in an efficient way, but don’t use it to do the infantry work and build routine reporting and dashboards. You are the “crack commando squad”, you need to be deployed effectively.

    When this clear separation of roles and responsibilities is respected, it means the end of war and peace amongst men (and women).

  7. I have a totally different view. The IT department is only a service bureau. I, as a data analyst, whether being a marketing analyst or a financial analyst, is beholden to all company and regulatory agency rules, not to mention budgetary constraints. That being said, I know the data, the business rules and how do use it. IT does not. It is not the function of the IT department to know how to do such things. Even if they have programmers who are going to create Enterprise reporting, they have to follow the business owner’s specifications. That means Finance, Marketing, Operations, etc. have to tell IT how to do what they will program. IT does not know the first thing about data visualization, the proper way to show a time series, how to calculate the daily average average balance, and stuff like that. The BI and Marketing Database projects I am currently involved in are controlled by the business unit owners. IT is not a business unit owner so they have no input in business matters. I rely on my IT group to make sure the products I am evaluating will work in their infrastructure and my Security/Risk group to make sure the companies I am dealing with are safe to deal with. When this works, it works great. As somebody said above we are all in this together.

    Having said all that, I have worked at companies in the past where the IT departments were out of control and it negatively affected the bottom line.

    So how does prototyping Dashboards in Excel relate? Simple, it is one of the most powerful engines to create very sophisticated dashboards given the proper tools. Microsoft gave Excel powerful connectivity underpinnings (just as Lotus did 1-2-3 before Excel) to analyze data. So if you have hundreds, thousands, millions, or more records to analyze and want a dashboard and a framework for adhoc analysis, Excel is an amazing tool.

    My favorite weapons I used with Excel over the years:
    New Fav – XLCubed (just testing now)
    MicroCharts (have been testing since beta)
    Spark Lines for Excel at http://sparklines-excel.blogspot.com/
    Cognos OLAP and Reporting tools
    plain old ODBC connection to my data

    @dmgerbino

  8. As pointed out by the Forrester Group in april 07, “Ouch! Get Ready — Spreadsheets Are Here To Stay For Business Intelligence” – Excel remain one of the major tools for management & dashboard reporting – http://www.forrester.com/Research/Document/Excerpt/0,7211,41687,00.html

    This report was one of the reasons I wrote my book “Construire un tableau de bord pertinent sous Excel” (ie « How to build an efficient dashboard with Excel » released in France end of 2007. Companies and managers at all levels (from CEO downward) are now facing the challenge of having near instantaneous dashboard capability in order to face the tough questions from above (ie shareholders, CEO, CxO and the top management downward).

    That being said, we all know there is a wide range of enterprise out there, from small-home based business up to Fortune 500 companies. Not all of them have the resources and funds to have top of the line BI tools and IT staff.

    Even in those few (compared to the overall number of companies) that do, when there is a need for a new dashboard there is no guarantee that it can be “reconfigured” quickly in existing dashboard / reporting tool already managed and supported by the IT department. Doing it in Excel always seems to be the shortest way.

    I concur with Jorge on using Excel for a prototype an executive dashboard. On many occasion, as a consultant, I helped companies devised such Excel-based dashboard prototype, even for CEO level. This shouldn’t be considered as a threat by IT Department as long as the following guidelines are followed:
    • Data are extracted from reliable sources with the help of IT whenever required to ensure integrity
    • Data transformation (whether in Excel or Access, or any other tool) is performed and documented
    • Excel dashboard architecture is designed to ensure smooth compilation and prevent data-loss and data corruption (separating data storage from calculation from printouts)
    • Proper documentation is performed and a “technical manual” is reviewed and validated by someone from the IT department
    • The “expected lifetime” of such prototype is discussed between business stakeholders with IT being involved in this process.
    o This is perhaps one of the most critical aspects of the topic to avoid “World War 1” syndrome described in the posts above.
    o Dashboards will have to pass the “acid-test” of daily/weekly/monthly usage.
    o Adaptations will be required whether simply adding extra data / indicators, reformatting graphic based information visualization or performing additional computations such as rolling 3 month trends… Excel flexibility in that “usage based refinement” stage is fantastic

    All that being said once this prototyping phase is finished, bringing IT onboard to study full scale deployment even on non Excel based technology must be performed.

  9. Dashboards are just plain stupid. Like the Corporate Mission Statement before them, and the “quality” and “excellence” programs that Corporate American has wasted their money on in the past, dashboards too will fade away with time. Executives would be better off listening to their IT departments who know far more about the business than their little junior executives. What a fool believes he sees, no wise man has the power to reason away.

  10. @Tom: I can’t agree with you, for obvious reasons. Data visualization allows you to manage and get insights from large datasets. This is note a fad. Now, you can call that dashboards and some time in the near future you can call it something else, but the basic principles remain. And a business is much more that the IT infrastructure.

  11. Ahhahaha… Jorge, I completely agree. I am in the middle of one such.. uh.. ‘collaboration’. IT hasn’t yet given me ODBC access yet, and I just know that I could create some good dashboards for use in specific situations that arise, say, once in a month or once a quarter, for which it is unnecessary to use IT resources or reinvent the wheel but which is completely vital when we need to make a financial decision. Scrambling around last minute to get your BI tools to give you the answers, or in the usual case, exporting from BI into Excel (!) to calculate the answer, can be avoided with dashboards.

    My thoughts on it being a fad – not as long as Excel 2007 and VBA are still alive and people with graduate degrees in Finance and Accounting still think of Excel spreadsheets as a chore instead of thinking of ways to make Excel work for them. Pivot tables are a mystery, VBA is another government agency and the extent of formula knowledge is composing a nested-IF statement. How can dashboards be a fad if they haven’t even permeated all the organizations yet?

  12. BI tools are all good and capable but they are always wrapped around corporate policy.

    If you believe in data governance, then Excel is not the way to present information. There are lot of parameters that go around defining Data governance, data security etc. Excel is good for prototyping and it should be used just for that. The prototype should be passed to the IT team and let them build it using their BI application.

  13. our experiences were really good and accepted by IT and management end users when we used excel on top of a multidimensional OLAP database which received its data from an ERP system. Data and structures managed by IT and reports created and used by end user and/or IT analysts.

  14. Actually, IT tends to be mostly useless and seem to forget they are simply overhead, hopefully a necessary overhead like say telephones. However most of the time they are simply wasteful. They want to build systems that are “cool” or look good on their CV and usually can’t be bothered to learn what end-users actually do and need. Every now and then you meet someone from IT who actually contributes in a meaningful way to the company’s P&L and they are like gold-dust.

    There should never ever be any “fights” with IT anymore than there should be fights with the chairs. It should simply be a case of this is what we need and how and your sole job is to deliver it.

  15. There is a solution that both IT and business users can support: Excel Automation. From the IT perspective, Excel Automation is just another server-based software tool that accesses corporate databases – easy to install and manage. From the business user perspective, Excel Automation means a lot less time running and emailing the same old spreadsheet with today’s data. So, you can take advantage of Jorge’s great training and consulting to make amazing spreadsheets – and once you’ve created them, they can be scheduled, run and distributed automatically. The Excel Automation tool doesn’t care if it is a simple table or an advanced executive dashboard with many charts and graphs.

Comments are closed.