The Information Model

Continuing our discussion of using Excel rather than Word or Project to manage an information project.

A simple spreadsheet that I get a lot of use out of is one I call the Information Model. I use it first to plan my information content, then to estimate the development time for each topic (or group of topics) track who is responsible for each, the due date, and its status. It is a simple, flat table in Excel that has columns labeled Topics, Info Developer, Status, Estimate, Due Date, and Comments (for starters).

If you are revising an existing document, the topics in the spreadsheet can be taken directly from the existing document. If the application you are documenting has a UI already, you can list the UI pages themselves as the topics. If you are using use cases, you can add a column to help organize your topics by the use cases or scenario names that describe the user tasks you intend to support.

You can now use the same spreadsheet to assign different topics to different information developers.

You can also use the spreadsheet to size the project. For example, in one project the general scope of a screen could be described by the number of tabs it had. So I created a column called tabs and we inventoried how many tabs were on each screen. We then estimated that each tab would take a half-day to document, so we wrote a simple formula in Excel to estimate the days each topic would take by multiplying the number of tabs by .5. We then summed that column to see the total project estimate.

• Tip: Put the estimation variable (in this case we started with .5) in its own cell and point to it in the estimation formulas. For example, if the estimation variable was in cell H3, and the number of tabs for a particular topic was in B5, the estimation formula for that topic would be "=$H$3*B5" Note: The $ makes that an absolute address, so if you copy that formula for all the topics, B5 will automatically change to the appropriate cell that contains that topic's number of tabs (e.g., C5, D5, E5, etc.), but the estimation variable will always come from H3. This way you can play some easy "what if" scenarios by changing the estimation variable in H3 and instantly seeing the impact it has on the project time.

Once the topics have been sized and assigned to information developers, you can ask each info developer to schedule their assigned topics. For example, if an info developer thinks she can devote an effective 3 days a week to the project, then she could group topics into weekly chunks by assigning the same weekly due date to 3 days' worth of topics. Or if a topic would take 6 days, she could make sure she allocated two weeks to get it done.

• Tip: Have info developers make all topics due on Fridays. That way the project manager or department manager can filter by a given date to see everything due that week.

And that last tip brings up a really useful feature of Excel, the ability to apply filters. Once you have your table built, do the following:

1. Highlight the heading row for the data columns.
2. Click Data > Filter > Autofilter on the menu.

You now get drop lists at the top of each column that let you filter and sort the table by the data in that column. This lets you do things like see only the topics that Mike is working on, or only the topics that are due this Friday. You can apply multiple filters, e.g., see Mike's topics that are due this Friday or see Mike's topics where status is blank.

Of course this approach needs to be modified for what make sense in your world, e.g., I like weekly scheduling units, you might prefer monthly. But the point is, if you put your plan in a spreadsheet instead of a document, you get a more powerful database and calculator tool to help you plan and track the project.