Toolkit: Visual Event Timeline

Skype suffered a major outage in December 2010.  The company provided on its blog a postmortem from the CIO, explaining the cause of the failure, describing how it was addressed in the short-term, and discussing plans to prevent it from happening again.  The company was forthcoming and provided sufficient information to give its customers an understanding of what occurred.

When a negative event like this happens, it is important to communicate well both during the event and after the failure is resolved.  Communications during the event keeps those impacted abreast of the situation so they can plan and make decisions.  Communications after the event are important to explain what happened, why it happened, and what has been done or will be done to prevent it from happening again (or lower the risk of it occurring again to a more acceptable level).

I have been asked to lead post-mortems or to step in to situations where a problem has occurred and needs to be fixed.  One technique in my toolkit is a visual event timeline.  I saw this technique from a colleague of mine a decade ago when he gave me an update on a problem that occurred in his area.  A combination of a visual event timeline and a detailed narrative can be an effective way to communicate.  I believe the addition of a visual event timeline in the Skype blog entry would have enhanced the communication of the outage event.

This post introduces you to the visual event timeline technique by:

• Presenting the concepts of the technique.
• Providing real-life examples.
• Sharing some tips on creating a visual event timeline.
• Applying it to the Skype outage event.

The examples have been altered to avoid disclosure of any confidential information.

CONCEPTS OF A VISUAL EVENT TIMELINE

The visual event timeline has a simple purpose:  visually depict along a timeline the series of key facts uncovered, actions taken, and observations noted to effectively communicate what occurred during an event.

A visual event timeline contains the following elements:

• A timeline.  The timeline is presented as a horizontal or vertical line, bar, or rectangle.  The unit of time (for example, days, hours, minutes) displayed on the timeline is dependent on the event.  "Days" is appropriate when several days elapse between the time the event started and all issues were resolved.  For the Skype incident, "hours" is the more appropriate unit of time.
• Key messages.  These are the most important messages that help communicate what occurred.  They can be a description of the situation ("the cluster of Skype support servers for offline instant messaging became overloaded"), an observation ("Windows clients running Skype version 5.0.0152 were not properly processing"), an action performed ("disabled the overloaded Skype servers and eliminated client requests to them"), or other facts considered important in explaining the event at a point in time.  A line or arrow aligns each key message with its appropriate point in time on the timeline.
• Statistics.  These are data points, such as numbers or percentages, that provide additional information on the severity, magnitude, or status at a point in time.  For the Skype incident, the percentage of supernodes functioning properly or the number of customers affected would add relevant information.  This is optional and is dependent on the event and availability of statistics.
• Icons.  These visually highlight missed opportunities, points of failures, or points of success.  Coloring the icons red can also be used to emphasize missed opportunities and failures.
• Important lessons learned and action items.  For postmortems, the most important lessons learned and what has been done (or will be done) should be briefly noted.

REAL EXAMPLES OF A VISUAL EVENT TIMELINE

I created this visual event timeline as part of a postmortem.

(Click image to enlarge)

In this example, the timeline is displayed horizontally in the middle of the page.  The units are in days as the event began on June 30 and was not fully resolved until July 21.

The key messages are listed above and below the timeline.  The items listed above the timeline tend to refer to actions or observations of a negative nature and the items listed below the timeline contain the interactions with a vendor (SAP), although this was not fully adhered to due to space limitations.  The action that started the event (human error) and the discrepancies are highlighted in red.

No statistics are provided for this event.

Icons used include an "X" for missed opportunities and darkened circles for points of failure.  The visual event timeline clearly highlights two missed opportunities:  it took six days before the error was detected, and five days were lost due to misdiagnosis of the error.  The happy face icons show points of progress and resolution.

Because this was a postmortem, key learnings and action items are highlighted in the lower left.

This visual event timeline was used in both short debriefings as well as a long debriefing with the AR teams and management affected by the event.

Below is another visual event timeline I created when asked to help resolve an issue that had existed for a while.  Creating a visual event timeline helped me get up to speed on the history.

(Click image to enlarge)

In this example, the issue was still in existence and was being worked on.  Statistics are shown to show the number of employees or contractors impacted during this time period.  The missed opportunities and points of failure are clearly noted.

TIPS FOR CREATING A VISUAL EVENT TIMELINE

Keep the following points in mind when preparing a visual events timeline.

Be clear.  The purpose is to effectively communicate.  Do not be vague.  Avoid jargon, acronyms, and abbreviations unless they are known and obvious to the intended audience.

Be honest.  There should not be any information on the visual event timeline that is inaccurate, misleading, half-truth, or opinion.

Keep it to one page.  The visual event timeline is a communication tool to highlight the key points.  It is designed to communicate well in a 5-10 minute debriefing as well as an hour conversation.  Details of the event should be contained in accompanying written documentation (suggestion:  use Information Mapping to prepare the written document).

In some cases, a portion of a timeline can be exploded out on a separate visual event timeline if it requires a different unit of time.  For example, if a critical 30-minute period of time contained lots of failures and missed opportunities, but the overall event lasted days or weeks, a separate timeline for the same event is helpful.  For example, the BP oil spill in the Gulf of Mexico could have an overall timeline and another timeline exploded out for the hours when the incident occurred.

Include only the key messages and statistics that matter.  Part of the skill of communicating effectively is knowing what to include and what not to include.  A lot of actions and observations occur during an event, but many of them are secondary to what is important.  Do not leave out important information, but do not include tangential information.  Remember that a detailed document should accompany the visual event timeline and can be referenced when discussing the event.

APPLYING IT TO THE SKYPE OUTAGE EVENT

Below is the start of a visual event timeline for the December 2010 Skype outage based on information Skype provided on its blog.  It is not complete, and I made some assumptions simply for illustrative purposes.

(Click image to enlarge)

In creating the illustrative example from the blog entry and reading the details, I determined insufficient statistical information was available to highlight the number of impacted customers and the number of supernodes available.  I also learned that the event lasted more than 24 hours to restore all services and return to normal, so a four day time period would have been more accurate.  I do feel this type of visual event timeline, nicely formatted and worded for a general audience, would have added value and enhanced the communication.


Happy timelining!

Toolkit: Results Chains and Benefits Registers

Many programs and projects undertaken in organizations fail to get their intended benefits.  In one survey from 2005, only 2% of organizations achieved targeted benefits all the time and 86% of organizations lost up to 25% of target benefits across their entire project portfolio (KPMG Global IT Project Management Survey - clicking this link downloads a pdf of survey).

My experience suggests the actual situation is much worse because business cases are often a one-time document (used to get funding and not updated) and many businesses do not track benefits during or after the project.  The same survey makes a similar observation, noting: “A remarkably high 59% of organizations have no, or only an informal benefits-management process.  Only 18% of those who have a formal process stringently enforce it.  An interesting implication of this is that with benefits so poorly defined and/or measured, how can the 86% of survey participants claim they only lost up to 25% of their targeted benefits? The loss may be far higher.”

Two techniques I have leveraged over the past decade to clarify and track program and project benefits are Results Chains (a benefits modeling technique) and Benefits Registers (a benefits tracking technique).  Both techniques are related and should be used together as part of a benefits realization or value management discipline.  They can be used by both profit and non-profit organizations.  And they can be used by IT and non-IT programs and projects.

This post introduces you to Results Chains and Benefits Registers by:

• Presenting an introduction to the concepts of the two techniques.
• Providing a real-life example of both techniques.
• Sharing some practical tips when using the techniques.
• Listing additional resources if you are interested in learning more.

Note:  The remainder of this post will use the term project to refer to either a program or a project.  Technically, a project is "a temporary endeavor undertaken to create a unique product, service, or result" and a program is "a group of related projects managed in a coordinated way to obtain benefits and control not available from managing them individually" (both definitions from the Project Management Institute).

CONCEPTS OF THE RESULTS CHAIN

The Results Chain modeling technique for benefits realization was formally introduced in the book The Information Paradox: Realizing the Business Benefits of Information Technology by John Thorp and DMR’s Center for Strategic Leadership in 1998.  A key premise of the book is that benefits must be proactively managed if you want to achieve them; completing a program or project as intended does not automatically result in achieving the desired benefits.

A Results Chain shows the connections (linkages) between activities (work) and outcomes (results) and any assumptions.  The process of building a Results Chain can help you define the activities to be done, clarify the outcomes to be achieved, and manage the realization of the benefits.  Below is a simple example:

(Click to view)

There are four objects used in a Results Chain model (plus a fifth as a connector object).

Graphic	Object Name	Description
	Activity	The work that contributes to an outcome. This can be a program, project, or activity, depending on the level of detail being modeled.
	Outcome	The result of an activity and/or other outcome. This can be an intermediate outcome or an ultimate outcome of the project.
	Connection	The linkage between one object and another. The arrow shows the direction of the connection.
	Assumption	Any consideration not in control of the project that is required to achieve an outcome.
	Connector	A symbol to represent a continuation of the connection from one page of a Results Chain to another (or on the same page to prevent connections from crossing).

Activities should be worded to start with a imperative verb, such as Identify, Document, Configure, or Test.  The wording should clearly state what the activity is, but it should not be too wordy.  Additional documentation can specify more details about each activity.  Each activity should be assigned a reference number.

Outcomes should be worded with a verb in past tense to refer to the achievement of the outcome, such as created, reduced, increased, and eliminated.  Some practitioners have a set list of words they use.  These outcomes should be measurable and will appear on a Benefits Register.  Each outcome should be assigned a reference number.

Each activity should have a connection originating from it and terminating in an outcome.  There should not be an activity-to-activity direct connection as each activity should produce an outcome.  Outcomes may generate other outcomes (for example, as a newly implemented process matures and participants get more comfortable with it) or a combination of an outcome and another activity may generate an outcome.

The model is not a timeline and does not depict the elapsed time to perform an activity or to achieve an outcome.  It does show dependencies through the connections so there is some relative order.  However, it will not always be clear as to when an activity is performed or outcome is achieved relative to other activities or outcomes that are not directly connected.  Some outcomes may be achieved immediately after the completion of an activity.  Other outcomes may require some time before they are fully achieved.

The model should flow left to right.  The ultimate outcomes (the primary benefits of a project) are depicted on the far right of the model.  Other outcomes that come before them are sometimes referred to as intermediate outcomes.

REAL EXAMPLE OF A RESULTS CHAIN

The Results Chain shown below was created to help plan and document the outcomes of a segregation of duties project.  Segregation of duties (SoD) is a primary internal control intended to prevent or decrease the risk of errors or fraud by assigning conflicting duties to different personnel in an organization.  This project was designed to document SoD rules for the organization, automate the detection of SoD violations, and remediate them.

(Click image to view)

The activities are represented by the rectangles.  Each activity has a unique reference number, such as T-100 or T-108.  Each activity starts with a verb and is written to be descriptive, such as "Test SoD rules and eliminate rule violations".  Each of the activities has a connection that links it to an outcome.

The outcomes are represented by the large circles and have unique reference numbers.  Each outcome is written to end in a verb, such as "SoD rules documented".

The connections are represented by the lines and show the linkage in the direction of the arrow.  In this example, the completion of activity "T-100 Identify risks" results in the outcome "O-101 Business risks identified".  This is an intermediate outcome that is necessary to achieve other outcomes.  In this example, the outcome of business risks identified and the activity "T-102 Define SoD rules" both contribute to the outcome "O-103 SoD rules documented".

The ultimate outcome in this example is "O-125 SoD and sensitive access risks reduced" which is achieved through two other immediate predecessor outcomes "O-119 SoD violations reduced" and "O-121 Timely visibility to inappropriate sensitive access increased".

One assumption is depicted in the upper right part of the model: "A-11 Monitoring and remediation of SoD violations (operational)".  The monitoring and remediation of SoD violations is outside the scope of the project.  However, it is critical activity to achieve the outcome "O-119 SoD violations reduced" that it has been highlighted in the model.

There are several connectors represented by the small circles.  The S-3 connector was created to avoid the crossing over of connection lines (which can make diagrams difficult to read).  The S-3 connector connects outcome O-111 to O-119.  The other connectors show connection of outcomes with other Results Chains.  In this example, an outcome "P-1 Accountabilities established" depicted on a different Results Chain is linked to "O-117 SoD processes established".

BENEFITS OF RESULTS CHAINS

The benefits of Results Chains include:

• Confirmation of project benefits.  The Results Chain should clearly identify and display the ultimate outcomes on the far right of a Results Chain model.  These benefits should match the expectations of the sponsor and stakeholders.
• Sanity check of business cases.  The Results Chain can be used to validate the benefits and to identify cost areas of the project.  Cost areas include each of the activities and any of the intermediate outcomes that have costs to sustain them.
• Agreement of project scope.  The Results Chain highlights the major activities of the project.  These activities are the "what" needs to be done, and they should match the expectations of the sponsor and stakeholders.
• Input to project planning.  The activities in the Results Chains can serve as a starting point in preparing project plans.
• Exposure of key assumptions.  The Results Chain can highlight key assumptions so you can communicate and monitor those throughout the project.
• Analysis of impacts if changes occur.  If an assumption changes (e.g., the operational processes are not put in place), an activity is curtailed (e.g., the testing was done but the identified violations were not remediated), or if an related external event was delayed or canceled (e.g., the automated tool was not implemented), you can immediately see the impact to the project and its benefits (e.g., we will not be able to reduce SoD violations to the extent expected).
• Communication of benefits.  The Results Chain can be a good tool to communicate to certain audiences the high level activities, outcomes, and relative sequencing.

TIPS FOR CREATING AND USING RESULTS CHAINS

If you get two different groups to create a Results Chain for the same project, I guarantee the two Results Chains will look different.  Even two experienced practitioners will come up with different models.  Do not worry about it.  The important aspect is to get key stakeholders involved in creating, approving, and using the Results Chain.

Involve your key project participants in creating the Results Chain.  It is the process of creating the model where you can get agreement on the activities, outcomes, ultimate benefits, assumptions, and linkages.

Keep it simple.  Do not make it overly complex.  It is input to project planning.  It is not intended to contain all project tasks.

Create the Results Chain using both left-to-right and right-to-left approaches.  Some practitioners use the left-to-right approach, focusing on the key activities to determine the outcomes.  Others prefer to start on the right side with the ultimate benefits or goals of the project and work back to the activities.  Do both as they give different perspectives.

Know your audience before communicating with a Results Chain.  Many audiences may be overwhelmed by the information contained in a Results Chain.  One approach is to simplify the Results Chain by raising it up a level or two to highlight the key activities, outcomes, connections, and assumptions you want to communicate.  If your audience wants to drill into the details, you can then pull out the more detailed version.

Recognize that many outcomes, particularly the ultimate outcomes, are achieved after the project has been completed and is in operational mode.  For example, if a goal is to have improved efficiencies of 10% by implementing a new computer system, this efficiency improvement will not be achieved on the day the system goes live.  It takes a period of time before the users become comfortable with the new system and the efficiencies are achieved.

Keep it live.  Revise the model when changes occur.  Show progress against it by coloring or shading activities completed and outcomes achieved and by highlighting those activities in progress.

Track the progress of the outcomes on the Benefits Register.  While the Results Chain can highlight outcomes achieved, the Benefits Register can show the details.

CONCEPTS OF A BENEFITS REGISTER

A Benefits Register is a listing of all outcomes of a project with details of how to measure the achievement of the outcome.  The outcomes are taken from the Results Chain for the project.

The following fields are contained in a Benefits Register.

Field	Description
Measurement #	A unique number given to the measurement.
Project	The name of the project.  This is useful for a program with multiple projects being planned and tracked.
Outcome	The name of the outcome on the Results Chain.
Outcome #	The reference number of the outcome on the Results Chain.
Measurement	A clear and specific description of what will be measured for the outcome.
Metric Value	The standard of measurement to be used.  Examples include:  number, percentage, yes/no.
Frequency	The frequency the measurement will be taken.  Many will be one-time and others will be measured periodically.
Measurement Method	A clear and specific description of how the measurement will be taken.
Baseline Value	An actual value measured before the outcome was achieved.  This is the baseline that can be compared to a target value expected to be achieved by the outcome.  For example, if the outcome is "Sales increased by 10% over last year", the baseline value should be last year's sales.
Baseline Date	The date the baseline value was taken.
Target Value	The value of the result expected for the outcome to be achieved.  If the outcome is "Sales increased by 10% over last year", the target value should be 110% of the baseline value.
Target Date	The date by which the target value should be measured.
Tolerance Limit	If applicable, an acceptable range around the target value that will consider the outcome achieved.  For example, if the outcome is "Errors eliminated" and the target value is zero, it may be agreed that if there were five errors or less measured, then the outcome was achieved.
Accountability	A person assigned responsibility for achieving the outcome.

The listing of outcomes should be ordered in the general sequence the outcomes are achieved.  This makes it easier to update the Benefits Register.

A Benefits Register typically contains one row per outcome.  You can easily turn it into a Benefits Register for tracking purposes by adding columns or rows and using the grouping/outlining feature of Microsoft Office to hide and expand the tracking detail.

EXAMPLE OF A BENEFITS REGISTER

The portion of the Benefits Register shown below is related to the Results Chain shown earlier.  I have expanded the detail for the first outcome to show how it was used for tracking purposes as well.

(Click image to view)

Intermediate outcomes in a Results Chains may often be simple measurements.  In this example, the first outcome "Business risks identified" is measured by the existence of a Risk Analysis Document.  The measurement is a yes or no and measured one-time.  The measurement is taken by viewing a Risk Analysis Document signed off by the Business Process Owner, which is considered proof that the Risk Analysis Document exists and the business risks have been identified.  A baseline measurement was taken on March 1 and no Risk Analysis Document was found.  The target value is "yes" (a signed-off Risk Analysis Document).  The date the outcome should be achieved is documented in the project plan.  There is no tolerance limit; it must be a "yes" for the outcome to be achieved.  The Rulebook Owner is accountable for achieving the outcome.

The outcome in this project example is unique in that the measurement is performed per process area, that is, there are several process areas to be tracked.  The green columns allow detailed baseline and target information to be tracked multiple times, one row per process area.

Here is another outcome on the Benefits Register from the project.  It is closer to an ultimate outcome where stronger benefits to the organization come in to play.

(Click image to view)

This this example, the frequency of measurement is monthly.  One row is used per month to track the baseline and target values.  Notes can be captured to provide more detail on the results of the measurements.

TIPS FOR CREATING AND USING A BENEFITS REGISTER

Create the Benefits Register after the Results Chain has been created but before the Results Chain has been finalized.  The process of determining how the outcomes will be measured can help clarify the wording of the outcomes and ensure it can be measured.

Keep the accountable parties informed of the measurements, the target dates, and the status.  And keep the Benefits Register updated.  These are two of the management activities needed to realize the benefits.

Celebrate benefit successes.  My experience has shown organizations celebrate key project milestones (like go-lives or project completions).  Milestones are nice, but they are rarely the benefits of the project which are the real reason to celebrate.  In fairness, most projects are completed and project teams disbanded before the ultimate outcomes are achieved (and as noted in the KPMG survey, most projects do not achieve the intended benefits or do not track them.)  With Results Chains and Benefits Registers, you can finally celebrate what should be celebrated.

ADDITIONAL RESOURCES

The book The Information Paradox by John Thorp contains the original description of the Results Chain modeling technique.  It is a good read.  Here is the link in Amazon for more information.

Here is a link to a Results Chain and Benefits Register created by the Treasury Board of Canada.  This gives you another real example.

Happy chaining!

Toolkit: Pivot Tables in Microsoft Excel

The applications in the Microsoft Office suite (including Word, Excel, and PowerPoint) are extremely powerful tools.  The majority of users touch on a fraction of the capabilities of these tools, either because (1) their work does not require it or (2) because they do not know certain capabilities exist.  The first reason is understandable; no need to use or learn the powerful equation editor if you have no need for it.  The second reason — not being familiar with key features — is unfortunate.  If users would enhance their knowledge of these tools beyond the basics they would be much more productive and produce better results.

I will have a series of postings on a few of the powerful features of Microsoft Office that everyone should learn once they get past the basics of these applications.  They are part of my consultant’s toolkit because they both add value and save the client time and money.

The capabilities I will share are not “rarely applicable” or designed to “show-off your skills”.  They are frequently useful, meaningful, and beneficial.  I list a few here so you get an idea of the features I am referring to:

• Pivot tables in Excel
• Style sheets in Word
• Tables in PowerPoint

If you are not familiar with these capabilities or do not use them, I encourage you to research them, learn more about them, and apply them.

This posting introduces you to pivot tables in Microsoft Excel by:

• Providing a simple real-life example of a pivot table and its benefits
• Presenting an overview of the key concepts of pivot tables
• Sharing several real-life examples to give you additional ideas on where to apply it in your work
• Sharing some practical tips on using pivot tables
• Listing additional resources to learn more about pivot tables.

I focus on the concepts and the big picture of pivot tables in this posting.  I do not provide step-by-step instructions.  Microsoft Excel has changed both the location of where to create/manipulate pivot tables and the look and feel in their releases over the last several years.  Please refer to the help within Microsoft Excel to learn how to specifically perform the pivot table operations in your version of Excel.  The concepts and examples apply to all versions.

Note: The pivot tables depicted in this posting are based on real-life uses.  The data values (e.g., numbers, names, amounts) are fictitious, however, to remove any proprietary data.

SIDEBAR - AN EXAMPLE OF WHY TO LEARN NEW FEATURES A client sent me an Excel file with a long spreadsheet filled with sales data.  He said he spent a full day each month formatting the information.  Specifically, he spent time inserting new rows in the spreadsheet to put in column headings each time a page break occurred. I asked him if the “Rows to repeat at top” feature in Page Setup would work for him.  He was not familiar with it.  I showed him the feature and the few clicks needed to use it.  If someone had not showed him this built-in capability of Excel, he would still be doing this work at considerable expense (eight hours versus one minute).  This is independent of whether the formatting task itself added value to the company and could have been eliminated altogether. I asked him what else he did with the sales data to see if I could help him further.  He mentioned he spent a couple of days each month computing sales by salesman, sales by customer, sales by customer grouped by salesman, and a few other views on the data.  I asked him if he was familiar with pivot tables.  He was not.  Using an export from the sales system I showed him with pivot tables how he could create his reports each month in a couple of hours and with less likelihood of error. Bottomline:  Three days each month was reduced to a less than half a day by learning and leveraging just two of the features of the tool he used.

REAL-LIFE EXAMPLE OF A PIVOT TABLE AND ITS BENEFITS

One typical activity of a project manager is to track expenses on a project.  For large projects, a project management tool or an accounting system may be used to track these expenses.  For smaller projects or when you want more control over what and when you track expenses, a spreadsheet may be a good choice.

Here is a small portion of a spreadsheet I have used track expenses for multiple programs with multiple projects.

(Click image to enlarge)

Each row in the spreadsheet is an expense.  Each expense notes which program and project it is associated with, the month incurred, the company and person or asset billing the expense, the expense type, the funding type and who will fund it, and the expense in dollars.

A project manager with this information can now ask and receive answers to multiple questions:

• How much have we spent to date? By month? What is our trend?
• How much have we spent on each project? On each program which has multiple projects?
• How much have we spent on consultants? On software?
• How much have we spent on each phase?
• Hold it…how much have we spent each month by phase and by project?

Answering these questions (and more) is a mouse drag or mouse click away using a pivot table.

How much spent to date on all expenses tracked? One mouse drag gives the answer.

(Click image to enlarge)

What about by month? One additional mouse drag gives the answer.

(Click image to enlarge)

On the each project? Two drags displays the expenses broken down by project within program.

(Click image to enlarge)

Let’s jump to the last question…how much have we spent each month by phase by program?

(Click image to enlarge)

Several benefits of pivot tables can be seen by this example.

First, pivot tables can answer many questions important to people who use data to make decisions.  By viewing the data in multiple ways the user can grasp a large amount of detailed data more easily, spot potential issues or trends, or find out more about an existing issue.  Pivot tables allow this viewing of the same data in multiple ways.

Second, it is easy to change the way data is viewed.  Once the data is set up in a spreadsheet and a pivot table structure has been created (fairly straightforward using Microsoft Excel’s built-in PivotTable Wizard), it literally is a mouse drag or mouse click to change the view of the data.

Third, the computations in pivot tables are done automatically.  When you change the view, the calculations are handled for you. You do not need to write formulas.

Alternatives to pivot tables include writing formulas, using the subtotal feature of Excel, using filters on the data, or writing macros / purchasing add-ons to help redisplay or analyze the data.  There may be cases where these alternatives are more appropriate or provide a view pivot tables do not support (there are some limitations to pivot tables.)  But these alternatives tend to be more error-prone.

KEY CONCEPTS OF PIVOT TABLES

Three key concepts are the data table, the pivot table, and crosstabs.

Data Table

A data table stores the data to be viewed, analyzed, and summarized.  The top row of the data table contains the column headings.  The rows below the top row contain the data with each row representing one record of data.  Each cell on a data row contains a data value for that record.

(Click image to enlarge)

Each row in a data table needs to contain all the data for that record.  This may mean that some data is duplicated on multiple rows.  For example, if you want to be able to view data (in the earlier example) by project and by program you need to fill in the data value "TRW Program" in the program field on each row of the “Turtle Project” even though it the “Turtle Project” is always associated with the “TRW Program”.

The key design aspect of your data table is to determine how you may want to view your data.  By month?  By day?  By region?  By city?  By product?  By product grouping?  By ship date?   By month? A column needs to be set up and the data needs to be stored in the table for each dimension you would like to view.  Each piece of numerical data you want to compute with (sum, average, minimum, maximum, etc.) should also be a column, such as units, sales, and costs.

Pivot Table

The pivot table is where you view the data from the data table.  The pivot table and the data table are two separate tables.  The pivot table points to the data table behind the scenes.

The pivot table is also where you change the view so you can see the data in different ways ("slice and dice the data" or pivot it).  You do this by moving the field names around, filtering which values you want to see (for example, only show the "Turtle Project"), and setting field values for advanced computations (such as sum, count, variance, running totals, percent of).   Changing the view does not impact any of your data in the data table.  For example, removing a field from your pivot table does not change the underlying data table.

Crosstab

A cross-tabulation (or crosstab) is the intersection ("crossing") of two or more dimensions so that the intersection points represent the computations (sum, count, etc.) for each combination of dimensional values.  Below is a subset of a data table.

(Click image to enlarge)

There are two dimensions in the data table above:

• Gender, with possible values of Male and Female
• Handedness, with possible values of Left and Right

A cross-tabulation on these two dimensions can compute how many Males are Lefthanded, Females are Lefthanded, Males are Righthanded, and Females are Righthanded. Below is the crosstab with counts from 732 data rows.

(Click image to enlarge)

Excel can also display the results of the crosstab in percentages.

(Click image to enlarge)

The pivot tables in Excel are quite powerful.  It can perform these crosstabs on more than two dimensions at the same time.  If we had another column heading of “Eye Color”, we could compute the number of Blue-eyed Female Lefthanded people compared to the other combinations of dimensional values.

ADDITIONAL REAL-LIFE EXAMPLES

I have created scores of pivot tables over the years.  I find value-added uses for them in many situations.  Here are a few more examples to give you ideas.

I created this pivot table to track the status of over 200 individual projects each month while managing a large program.

(Click image to enlarge)

Microsoft Excel also has the capability to create pivot charts so you can view the data visually.

(Click image to enlarge)

How about this?  Leverage a tool like SurveyMonkey to capture survey information online through a web browser.  I used it to capture over 200 survey responses to a company event.  SurveyMonkey will allow you to export the data in a format perfect for a data table.

(Click image to enlarge)

Then create several pivots on the same page with the results.

(Click image to enlarge)

TIPS ON USING PIVOT TABLES

Start simple and try it out.  Build a simple data table with the information you think you need, put in column headings and a few sample data rows of mock data, build a pivot table using the PivotTable Wizard, and try it out.  Does it answer the questions you have?  Go back and make adjustments to your data table until you feel you have the right model.  Once you get comfortable with the pivot table concepts and its capabilities, designing data tables properly will become natural.

Include computed fields in your data table, if appropriate.  In the first real-life example, the Total Cost field is computed by multiplying the Units field by the Cost per Unit.  Color this column (to remind you it is computed with a formula and not entered directly) and place at the far right of the data table.  As you add rows to your data table, you can then copy the formulas to the new rows.

You can even use computed fields based on look-up values.  For example, if the “Turtle Project” will always be associated with the “TRW Program”, you could make the Program field a computed value with a VLOOKUP in a Project-Program cross-reference table.  Just be careful.  Make sure that the association will always be a true statement.  For example, a project manager may be leading the “Turtle Project” today but could be replaced by a new project manager later in the project.  If you want to view the data by project manager, you should not compute this field but manually fill it in each time for each record.

A pivot table is based on a range of rows in your data table.  If you insert rows outside of this range, your pivot table will not know to use this data.  To prevent this from happening, clearly mark the last row in the range of your data table.  When you insert rows above and inside the range, the pivot table will automatically adjust and include the new rows.  Here is an example:

(Click image to enlarge)

You can create multiple pivot tables in the same spreadsheet against the same data table.  Put one pivot table on a tab labeled “Playground”.  This can be used to slice and dice anyway you want -- it is your playground to play around with the views.  Put on separate worksheets pivot tables with the specific view and format that you always want to see whenever the data table is updated.  Multiple pivot tables allow you to have your "standard" pivot reports always available and a version of the pivot table you can always change.

While pivot tables are less prone to errors than other approaches, there are still some gotchas you should always check.

• First, refresh the values in the pivot table by pressing the exclamation point refresh icon.  This makes sure the table includes any changes to the data table since the last time the pivot table was refreshed.
• Second, make sure the pivot table is referring to all of the data rows in the data table.  Check that the range for the pivot table includes all of the rows you want in your results.
• Third, make sure there are no filters within the pivot table omitting some values from the results, unless that is what you wanted.  Pivot table functionality allows you hide and filter certain dimensional values.
• Fourth, make sure the calculation on the crosstab is the calculation you want.  A common mistake is for the computation to be a Sum when you want a Count or vice versa.
• Finally, as with the use of any spreadsheet, spot-check the answers to make sure they make sense and are not out of line of what you expect.

ADDITIONAL RESOURCES TO FURTHER YOUR KNOWLEDGE

Here are some places to find more information about pivot tables:

• The Help feature in Microsoft Excel. While it is not as extensive as it should be, it should be your first stop.  More recent versions of Excel include "courses" to step you through simple examples and how-to's within your particular version of Excel.
• The Microsoft website with support for Excel.
• Any of the free Pivot Table tutorials online.  Do a search on "Excel pivot table tutorial".  I found this example on a website called ProductivityPortfolio. This tutorial provides examples in Excel 2007.

Happy pivoting!

Toolkit: Information Mapping for Business Writing

One of the most important tools in my consulting toolkit is Information Mapping, both a formal method (approach) to writing and a format (visual style) for writing.  I personally have written thousands of pages of documents in this method/format, from memos and proposals to project charters and training materials.   I recommend it for most business writing for the advantages it offers both the writer and the reader.

I learned Information Mapping in 1993 as a manager in consulting at KPMG.  I brought the method to Coca-Cola Enterprises Inc. in 1994.  It became the standard for training documentation and technical writing by the Information Technology department, and many of my colleagues adopted the format for their own use.

This post introduces you to Information Mapping by:

• Providing a real-life example of traditional business writing and how it was improved by the Information Mapping method/format.
• Presenting an overview of the concepts of the method and the typical formatting.
• Sharing some practical tips on using the method and addressing some of its shortcomings.
• Listing additional resources if you are interested in learning more about Information Mapping.

REAL-LIFE EXAMPLE

Read the following two one-page memos written with the same purpose:  to communicate to the company’s customers a very important change to a procedure.

The first memo displayed below was the original draft given to me to proofread.  I suggested rewriting the memo in the Information Mapping method, asked some questions for clarification, and quickly drafted a new version.  The revised memo displayed below was the one actually sent to the customers.

Example 1 - Original Draft (click image to view)	Example 2 - Revised Memo (click image to view)

The original draft (Example 1) uses a typical memo style of writing used in business.  The procedure changes are embedded in the first paragraph and are difficult to follow.

The revised memo (Example 2) uses the Information Mapping method of writing.  The change, benefits, new procedure, and contact information are clearly presented to the customer.  The process of organizing the information in the memo exposed gaps in content in the original draft.

Since the original draft memo was not distributed, it is not possible to measure before and after results.  A comparison of the two memos suggests the revised memo anticipates and addresses concerns and questions that would have arisen with the original draft, reducing confusion, e-mail follow-ups, and telephone customer service calls.

OVERVIEW OF CONCEPTS

I consider Information Mapping to be both a method and a format.  I make that distinction because many people become aware of the look of an information-mapped document (the format or visual style) without realizing there are underlying principles and techniques (the method) to the writing.

The Method

In traditional business writing, the basic unit of writing is the paragraph, made of several sentences.  In Information Mapping, there are two basic units of writing:

• Information block:  One or more sentences, a diagram, a table, and/or a list about a limited topic.  The content is based on the purpose or function for the reader (not the writer).  This replaces paragraphs.  Note in the example revised memo presented above, there are four information blocks:  Change (with some sentences about the change), Benefits (with a list of benefits), Procedure (with a table presenting the steps and actions to take), and Questions.
• Information map:  A collection of relevant information blocks about a limited topic.  In the example revised memo, the one page is an information map about the change to the ordering process.

Another example of an information block and and information map is presented below.  In this example two pages of the same information map is displayed.

Example 3

(click image to view)

Information Mapping is based on several principles applied to writing.  These principles are based on research conducted by Robert E. Horn, who founded a company (Information Mapping, Inc.) to commercialize the findings.

Principle	Description
Chunking principle	All information should be grouped into small, manageable units. Each information block in Example 3 illustrates chunking.  And each information map is a chunking of an overall document or section.
Relevance principle	The information grouped in a unit should relate to one relevant point based on its purpose or function for the reader. The "Description" information block in Example 3 only gives a description, nothing more.  The "Sources of information" block only lists the sources.
Labeling principle	Each relevant unit of information should have a label to “advance organize” the content of the unit for the reader. The map title ("Information Maintained in IS Project List") in Example 3 is a label for the map.  "Description", "Sources of information", and "Fields set at creation time" are labels for the information blocks.  A reader can scan these labels to find a chunk of information more easily as a result of this principle.
Consistency principle	Information presented should use consistent words, labels, formats, organizations, and sequences. Business writing is not creative writing; the same word should always be used consistently.  In Example 3, the word "project" is consistently used throughout the document.
Integrated graphics principle	Diagrams, tables, pictures, and other visuals should be incorporated as an integral part of the text, not as an afterthought added after the writing is completed. Example 3 includes a table to visually depict the data, rather than narrative which buries the data in text.
Accessible detail principle	Writers should write at a level of detail that makes the information the reader needs readily accessible and put what the reader needs where the reader needs it. Example 3 shows the "Sources of information" abbreviations immediately above the table that uses those abbreviations.  The reader should not have to flip to an appendix to find out what the abbreviations mean.
Hierarchy of chunking and labeling principle	Small, relevant units of information should be organized hierarchically into larger groups and provide labels for each larger group. Example 3 only shows one information map.  It is part of a larger document that contains additional maps which are organized under a chapter of a document.  The document as well as the chapters, information maps, and information blocks are all labeled.

The Formatting

Information mapped documents are easy to spot because of certain characteristics.  There are usually:

• Labels (map titles) at the top of a page (or a title with a continued tag)
• Labels (block labels) to the left of an information block
• Lines (block lines) in between information blocks
• Several tables and bullet point lists

These are all depicted in Example 3.  The Information Mapping company sells a template for Microsoft Word that helps writers create and format Word documents in this style more easily.

PRACTICALITIES AND SHORTCOMINGS

There are specific rules prescribed by the Information Mapping method.  It is good to learn them so you fully understand the concepts and why they are important.  But once you learn them, adapt them to meet your own needs.

As an example, the method leverages Miller's Law, which states the number of objects an average human can hold in memory is 7, plus or minus 2.  Thus, the Information Mapping method states a bullet point list should not have more than nine items, a table no more than nine rows, a map no more than nine blocks.  It is a nice guideline to keep in mind, but there are examples where following this rule would split information unnecessarily.  Use your good judgment.

Because I have been writing this way for many years, I have a feel for when a map needs to be broken down into multiple maps.  This occurs throughout the writing process.  The document morphs and changes as I write, adding new information, creating new blocks, breaking apart maps, identifying new maps.  I think and write at the same time and use an infomap template to facilitate the process.

I find few shortcomings to business writing using Information Mapping.  Here are a couple I will point out, along with suggestions on how to address.

• “A fool with a tool is still a fool.”  Just because it looks like an information-mapped document with map titles and block labels does not mean it is written well.  The writer may not have an understanding of the underlying principles or the writing skills to put them to use.  I address this twofold:  first, by determining who has the aptitude to write well (some people do not have the aptitude), and secondly, by training people on the method and on writing.  On small projects, I typically provide introductory training myself with the group in less than three hours, and I include Microsoft Word training in the mix.  (Side note to reader:  It is amazing how many users of Microsoft Word do not understand style sheets and tables, two of the most powerful features of the software.)  I would recommend full-time writers that want a deep understanding of the method and want to practice and get feedback from experienced Information Mapping instructors take a formal class from the Information Mapping company.
• Documents written in the Information Mapping format tend to be larger, both in pages and file size.  There is more whitespace on infomap formatted documents.  The block labels take up a column of space on the left of each page.  The block lines spread out the text more.  Information blocks that do not fit at the bottom of the page are moved to the next page, adding to whitespace at the bottom of the page.  The pervasive use of tables and graphics tend to increase the size of a document.  The key, though, is whitespace and better presentation make it better for the reader and for communication.  I somewhat address this by making my own infomap Word template and reducing the text size from 12 point to 10 point.  This reduces the number of pages since more text fits on a page.  It also usually means more blocks can fit on a page (if you do a good job chunking), so there is less wasted space at the bottom of each page.

ADDITIONAL RESOURCES

If you want to learn more, I suggest one or more of the following:

• Attend an Information Mapping course.  The Information Mapping company provides training programs on the method, and it comes from the owners of the method.
• Read a description of the seven principles at TechWriter Wiki.
• Read the 1993 paper by the founder of Information Mapping, Robert Horn, titled “Structured Writing at Twenty-five” (a pdf document).  The content is good.  Unfortunately, it is written in neither Information Mapping nor Structured Writing.  Other links to his writings are here at his Stanford University website.

Happy mapping!