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:
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:
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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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 list 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 cell 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 formatting 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 estimation.
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
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
