Work Breakdown Structure in Excel

Excel Work Breakdown Structure

A work breakdown structure (WBS) in excel is used to visually represent the ordering of different tasks and project activities, schedule of resources during project planning. It allows us to divide or split the project into more manageable parts by classifying the project tasks into a hierarchy of events that are further split into a series of tasks that are not dependent on any other task for completion. Also, it is used to allocate matching equipment, costs, materials, labour, and time duration for completion of each task.

How to Create a Work Breakdown Structure in Excel?

Example #1

There are templates available for Work Breakdown Templates in Excel. With these templates, we can create two kinds of work breakdown structure:

Tree Structure

This structure looks as follows:

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Work Breakdown Structure in Excel (wallstreetmojo.com)

Tabular View

This looks as below:

Step 1: Gathering inputs

Before the creation of WBS, several inputs are required. The inputs of WBS consist of:

• Project Scope Statement: This is a detailed description of the tasks and deliverables of the project
• Statement of Requirements: This is a detailed description of the result that would be delivered
• Organizational Process Assets: This includes policies, guidelines, templates, procedures, plans of the organization
• Project Scope Management Plan: This is a document that helps to understand how changes to the project’s scope can be dealt

Step 2: Gathering Team and Stakeholders

This step involves identifying who can help create the WBS. It requires collaboration with team members. For instance, the team members can help jot down all the tasks for project completion.

Step 3: Determining representation and approach of WBS

The representation could be a tabular view or a tree structure as described above. For instance, for a particular project, the two views of WBS are as follows:

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Work Breakdown Structure in Excel (wallstreetmojo.com)

Step 4: Defining the main deliverables and levels

This includes breaking down the main deliverables into additional components and levels. There are three levels in a WBS. The first one is the complete project, the second includes all the deliverables required for project completion, and the third includes division or breakdown of deliverables into smaller parts like work packages. The work packages have a rule associated with them, i.e. they should not be completed in less than 8 or more than 80 hours. This 8/80 rule is applied only to work packages as these are the elements which are assigned to team member along with an assigned deadline.

Step 5: 100% Rule

This rule states that each level of breakdown in the WBS must have all the deliverable elements that represent 100% of its parent deliverable. This rule is essential to ensure that there are no duplicates. For this, it is necessary to ensure that sum of all WBS’s deliverables adds up to 100% of the complete project.100% should be applied to all the sub-levels for optimal results.

Step 6: Numbering Scheme

After defining the WBS components, each component is to be assigned a number that can be referred to as a WBS ID. This represents the location in the WBS structure at which each level of WBS is placed. In the first level, the first number or ID should be 1, and the following components numbered thereafter sequentially. For instance, 1.1 would be assigned for the first sub-level element, 1.1.1 for further sub-level elements. 1.2 would be assigned for another first-level sub-element.

Step 7: WBS Dictionary

This is a document that describes all the elements of WBS.

So after all these steps, a WBS template would like below:

The task descriptions can be indented either manually using the ‘Indent’ buttons on the ‘Home’ tab or using conditional formatting. Also, another approach could be to create a listCreate A ListA list can be created in Excel to define a list of items/values as predefined values. It may be created using the Data Validation tool so that users may select from a list rather than entering their own values.read more in which the numbering scheme is also a part of Task Description so that no extra indenting is required.

Example #2

On decomposing the project, we now set up Excel for accessing or receiving the data. Now let us see below how we can create one such template in Excel:

Step 1: Create the below columns across the top:

• Task ID
• Task Description
• Predecessor
• Owner
• Role
• % Completion
• Start Date
• End Date
• Deliver To

Step 2: After the creation of these columns, the cells are to be formatted. First, we format the cellFormat The CellFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user's preferred format. The formatting of the cell depends upon the nature of the data present.read more Task ID. To format it, we first highlight the column that is designated for Task ID numbers by clicking on the letter at the top of the column. Right-click on the selected column and then select ‘Format Cells’ -> ‘Number’ and set decimal places to 1. Next, we format column: Predecessor, which will track task dependencies. This is to be set up the same way as ‘Task ID’. ‘Start Date’ and ‘End Date’ are to be set up to accept dates entered.

Step 3: Enter the data.

Step 4: Next we can use conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more for various tasks, for instance, say we wish to highlight tasks that are associated with a particular deliverable, or are assigned to a particular member, or tasks which are due within a given time range

Step 5: We can see also the ‘data bar’ feature to represent a column like ‘% Completion graphically’. This feature can be accessed in the ‘Conditional Formatting’ dropdown.

Things to Remember

• To create an efficient WBS, nouns should be used rather than verbs to keep the focus on deliverables.
• A hierarchy or tree structure should be used for illustrating the relationship of deliverables for the project scope.
• While drilling down through levels of WBS, 100% rule should be followed.
• WBS should be built collaboratively with all the members.
• The top-level of WBS represents the final project.
• Each task duration should not be more than 10 days.
• Each task should be unique, and there should be no duplicates.
• It can help in detailed cost estimationCost EstimationCost estimate is the preliminary stage for any project, operation, or program in which a reasonable calculation of all project costs is performed and thus requires precise judgement, experience, and accuracy.read more.

Recommended Articles

This has been a guide to Work Breakdown Structure in Excel. Here we discuss how to create it along with examples and downloadable excel template. You may learn more about financing from the following articles –

• Project Timeline Template
• Project Planner Template
• Project Budget Template
• Project Management Template in Excel