Techpluscode.de How To Create An Automatic Visual Work Breakdown Structure With Microsoft Excel And VBA Macro

2 ▪ Contains sub-projects, work package and tasks ▪ Consists of all project work you have to do ▪ Is the foundation of your time schedule ▪ And of the project plan What is a WBS? A Work Breakdown Structure is the base plan in projectmanagement. Creating a WBS is the central task you have to do as a project manager

3 Automatic generated Work Breakdown Structure Projekt: New assembly workplace 2 Project management 2.1 Data storage 2.2 Controlling 2.3 Marketing 3 Initialization 3.1 Definition 3.2 Aims 4 Planning 4.1 Work prozess 4.2 Workplace design 4.3 Search of suppliers 4.3.1 Requirements specification 4.3.2 Requests 4.3.3 Comparison 5 Realization 5.1 Order of components 5.2 Installation 5.3 Test phase 5.4 Optimization 6 Closing 6.1 Lessons learned 6.2 Final report • Automatic formatted WBS chart • Choose your own style • Create native Microsoft Office shapes • Use all Office effects • And all only from a simple list

4 Get it in 5 steps 1. Download the Excel macro Automatic_WBS 2. Create your work breakdown structure list 3. Add additional information 4. Define the WBS design via sheet Setup 5. Click button Create – Finished!

5 1. Download the Excel macro Automatic_WBS https://github.com/tangielsky/automaticWBS • Open github.com • Press „clone or download“ button and start downloading the macro to your computer • Open Automatic_WBS.xlsm in Microsoft Excel

6 1. Download the Excel macro Automatic_WBS • You may need to adjust the security settings because the macro uses VBA code • But you get the code without any restrictions or passwords • Open VBA editor (Alt+F11) and look at the code if you like

7 2. Create your work breakdown structure list • Open sheet Start • Enter minimum Code and Name or import them from Microsoft Project or other software • Structure your items • Start with 1 as top item and continue one level deeper • Or sort by completion Sort

8 3. Add additional information Add e. g. your special WBS code You got 10 user columns F1..F10

9 3. Add additional information A good way is to use VLOOKUP to make connections to other data sources

10 4. Define the WBS design via sheet Setup Define fonts Choose color Define scale factors Format connection line Format shapes (e. g. shadow) Insert caption (use user fields with $F1 - $F10)

11 5. Click button Create – Finished!

12 Use WBS chart for showing current project progress Add $PROGRESS • Open sheet Setup • Enter $PROGRESS as text into one or more items you want a progress information

13 Use WBS chart for showing current project progress • Enter data for progress in sheet Start • Type “J” in cell Use progress for color • Define text format and color for not started, in progress and completed

14 Use WBS chart for showing current project progress • Re-create the WBS chart • See at one glance what you are working on, what is finished and what is not started

15 Transferring to other Microsoft applications is easy • It is best to select and group all elements beforehand • Click Copy (Ctrl+C) • Paste to your favorite app (e. g. Powerpoint)

16 ▪ https://techpluscode.de/work-breakdown-structure-with-automatic- progress-visualization/ ▪ https://techpluscode.de/work-breakdown-structure-wbs-in-3-minutes/ Would like more? More information can be found at ▪ https://github.com/tangielsky/automaticWBS Current version for download at ▪ https://techpluscode.de/blog-abonnieren/ Subscribe to stay informed