Work Breakdown Structure with automatic progress visualization

Why not use the work breakdown structure with automatic progress visualization for the project? The work breakdown structure is the mother of all plans in a project. In addition to the structure, it contains performance data, dates, costs and resources.

In my professional practice, the work breakdown structure is not often used. And that’s a shame, it helps to always focus on the planned project scopes. I use it now for each of my projects.

For designing a WBS you need some time for thinking. The visual design can be done supported by technology. But I have already shown that in the article Work Breakdown Structure in 3 minutes. An automatic graphic design with Microsoft Excel and a little coding.

More work breakdown structure, please

To bring the work breakdown structure more into focus, maybe a solution is to use it more often.

The status report for the client is a decision report. The client must be able to make decisions from the document. One part is the progress of the project.

For the scheduling view, there are different methods. E. g. the IPMA suggests using the milestone trend analysis. It compares planned to reached milestones. The progress is usually represented by a percentage bar.

This is sufficient for a first look. However, in order to see as decision-makers where faults actually occur, two areas must always be considered.

The work breakdown structure includes by definition all the performance properties of a project. In the work packages related information are included:

The beginning and end of a work package
Time commitment of a work package

The progress of a work package is anyway protocolled, so it is very easy to re-use.

So why not use the same as the work breakdown structure as a visualization? The graphic structure is perfect for showing the progress of the work packages. Services, dates, resources and also costs can be shown at the point of their creation – in the respective work package.

Here, however, is usually then the problem. The work breakdown structure must be built time-consuming by hand. For each of the different reporting dates of the project requires a current version. If WBS comes automatically from a project management software, the effort is limited. But if it is a manual process, it is a tedious and cumbersome process to do so.

Here we can start with technology and coding. With the appropriate extension of the macro Automatic_WBS.xlsm ought also to be to find a solution for the visual representation of project progress.

Presentation of the progress bar

First, we need to clarify some issues. What should be shown in concrete terms? Which components should be visualized? The work breakdown structure should not be overloaded with information. In a status report primarily include key figures because decisions only are possible. That’s why a traffic light representation fits mostly good. But it can at best be only one for each WBS element for clarity. Which one depends on your client and project. But a good point is always the percentage of completion.

Classic green – yellow – red suits not fine. For perhaps begun work packages require no attention to what could possibly be caused by yellow. Represent not begun work packages in red suits either.

Therefore, my suggestion is instead of red neutral gray and yellow instead of a blue background. Green for completed work packages is fine.

status	progress	color
not started	0%	gray
in processing	> 0% and <100%	blue
completed	100%	green

Examples of formatted work breakdown structure elements

Text modules in work breakdown structure

In the work packages themselves, there often fit customized information of the status better.

Depending on the status is at not begun the planned start date and the planned expenditure interesting work packages. For elements in progress there are more information: planned and realized starting and the planned target date, as well as the planned and done effort. For completed work packages planned / achieved sufficient futures and effort.

The $PROGRESS variable in each box will be replaced, therefore. It is filled with the contents of the corresponding status (see picture setting options for progress ). This can then be $PROGRESS variable again to progress as a percentage display.

Position progress bar — Position the progress bar for variable $PROGRESS

Automatic color selection in the Work Breakdown Structure

Given this, we can expand the macro. The colors themselves must be adjustable by the user. The definition can be done in the sheet Setup. Format the corresponding cell with your favorite background color.

Setting Projekstrukturplan with progress — Setting options for progress

However, the work breakdown structure should continue to offer the possibility of performing an output without color formatting. Therefore there is a field that can switch the function on and off. Formatting takes place only with J (yes).

Kennzeichenflag for progress — Flag J for progress formatting

Generating the WBS with automatic progress

You can now easily create a variety of WBS. In the example below, it looks as usual – with no progress formatting.

Work Breakdown Structure without automatic progress

If you type in „J“ in the field Use progress for color, Excel automatically generates this version of the work breakdown structure.

Work Breakdown Structure with automatic progress

This allows you to easily switch between the different variants. And always have the same database.

Sometimes it is annoying to always insert a Create button to generate the WBS. For this reason, there are developer tools -> macros in Excel. The macro can also be called up in this dialog. However, this is only possible within the workbook, since the Start and Setup sheets are referenced.

Calling work breakdown structure on Macro menu

Linking data source

Depending on where you get your information, it makes sense to link the sheet Start with it. Is your source e. g. Microsoft Project? Then, usually more items are placed in an export file,.e. g. tasks or milestones. But we did not need them in the work breakdown structure.

Therefore it makes sense to build the WBS from this information. We will get the latest data later using the WBS code and a link. This means that you no longer need any maintenance. Unless there are changes in the work breakdown structure.

In Excel that goes with the VLOOKUP function.

Perhaps you can also use the extended macro for your work breakdown structure profitably. And the automatic progress indicator helps to transfer further routine time into valuable project time.

I wish you successful projects!

Technology and Coding?

Techpluscode deals primarily with technology and coding. The code is entirely absent from this article. But how does the macro now works? What has to be programmed to the graphical work breakdown structure to automatically format the progress? Why are there columns like X, Y or Count in the sheet Start?

You can see this in the next blog article Macro for the automatic „work breakdown structure with Excel“ soon. Get started now. Go deeper into the Visual Basic programming and adapt the function exactly to your work breakdown structure.

I am looking forward to your version. Send me a message from your WBS if you like.