Work breakdown structure / WBS in 3 minutes

Automatischer Projekt-Struktur-Plan PSP mit Excel - WBS work breakdown structure

There is always a lot of work to do in projects. Time is usually short. Especially when there is no overall project management software available for creating plans or reports. In practice, this usually means boring routine tasks instead of important problem-solving. But it doesn’t have to be that way.

A lot of time is often wasted on non-essentials. The right work on the performance objects in the project then gets too little space.

To prevent this, there is a lot of support in the form of software for the project manager. Anyone looking for “Project Management Tools” on Google will get over 500 million hits. But who wants to look for the right tool among them?

The delegation to someone else is also exhausting. What if he or she finds suitable software? Today it is usually not so easy to integrate new software into an existing IT landscape or to have it installed by IT departments. This usually ends with the topic: „that you have to do something with the software anyway and then come with an overall software package that also solves this part perfectly“.

So what’s left? Leave everything the same? Waiting for the big software package?

I think there is still a way in between. I would like to show you this on a work breakdown structure – WBS.

Why the WBS?

Many project managers use Microsoft Project to control their projects. No question, Project offers many functions to support the project manager. However, it does not generate a WBS in the classic form.

This is a pity. The WBS is ideal for getting a quick overview of the project. By definition, it contains everything that is necessary to complete a project:

performance objects
work-packages

The structure can be freely aligned to the project or company-specific requirements. The Wikipedia entry on the WBS provides a good overview of various structural forms, such as object-oriented, task-oriented or phase-oriented.

As a Microsoft Project user, you now have the choice: manual planning with mostly PowerPoint / Excel or the use of special software.

And there are a number of software apps for the WBS in particular. They range from mind map software to Microsoft Visio to special software such as WBS Schedule Pro. The overview of openpm.info shows an overview.

The difficulties described above now arise here. Install another software, keep it up to date and know and use it. It becomes more complicated if the plan also has to be distributed to external stakeholders.

Can it be easier?

In my opinion, a suitable PM software is the best choice. As long as such software is not available, you have to rethink it.

The resources of a project team should flow into the processing of the project tasks. That’s why I find the use of common standard software the second best. Which one depends on the knowledge of your project staff.

In my case, it is Microsoft Excel. Excel is installed on practically every computer and almost everyone can use it.

But extreme caution is also required here. The design and formatting of plans must not become a resource eater.

Excel offers with its functions and VBA to automate work processes. This will save resources.

Automatic WBS!?

No – the headline is deceptive – you cannot create the PSP automatically. You have to think about it yourself. But Excel can help you with the graphical view. You can take care of the structure and not the formatting.

You define the font, colors, hierarchical structure, indentation distances and texts once according to the specifications of your PMO. The Excel macro does the rest for you. It ensures that in the future all project structure plans will be presented in a uniform manner and created without manual effort.

You can find the download link at the end of the article under Download.

Excel macro: Automatic_WBS.xlsm

The document consists of 3 worksheets:

Start – Textual structure of the PSP
Setup – Settings for the graphic creation of the PSP
PSP-WBS – The generated PSP

Spreadsheet: Start

So that Excel can later convert the structure into a graphical object, a structuring element is required. The WBS code is suitable for this. If you use a code with dots separately as a WBS code, as in the example below, you can use it directly for structuring.

In other cases, you have to enter the WBS code as an additional field and still enter this form as a code:

If your WBS code does not fit and you want to output it in the graphic WBS, enter it in one of the additional fields F1-5.

Excel Spreadsheet: Start WBS (work breakdown structure) — Excel-Spreadsheet: Start

Spreadsheet: Setup

How to adapt the WBS to the PMO standard now? In the Setup sheet, you will find options for the different levels of the PSP. There you can adjust the formatting once to your wishes.

You can also specify the texts in the boxes. You can mix manual text with the structure data. If text such as the WBS code is to be inserted, enter $CODE as the text. If you want to access an additional field, use $F1 – $F5 accordingly.

Tabellenblatt Setup — Excel-Spreadsheet: Setup

Spreadsheet: PSP-WBS

There are only two self-explanatory buttons on the spreadsheet. The Create button creates the WBS from the data from the two previous sheets.

In the Delete button, all elements of the displayed WBS are deleted.

Excel spreadsheet: WBS (work breakdown structure) — Excel-spreadsheet: PSP-WBS

The macro creates the WBS with rectangle shapes and connecting lines. The formatting is taken from the elements in the Setup sheet. Would you like no rectangle shapes with rounded edges? No problem – but you have to look in the VBA source code.

I implemented the classic structure as a graphic form in the macro. Project – phases – work packages.

The project connects the phases graphically from above with lines. All work packages are connected by lines from the left.

You can determine how deep the structure should be. There are no limits to the macro. You only have to enter the WBS code accordingly.

You can also easily copy the generated graphic into other Microsoft applications, such as PowerPoint, and use it there as native elements.

I hope you can use the macro profitably for your projects and thus convert a little routine time into valuable project reflection time.

With this in mind, I wish you successful projects!