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, labor, and time duration for the 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 structures:

This structure looks as follows:

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

It 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 it would deliverOrganizational Process Assets: This includes policies, guidelines, templates, procedures, and plans of the organization.Project Scope Management Plan: This document helps to understand how changes to the project’s scope can be dealt with.

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 described above. For instance, for a particular project, the two views of WBS are as follows:

Step 4: Defining the main deliverables and levels

It 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 the 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 applies only to work packages as these are the elements assigned to team members and an assigned deadline.

Step 5: 100% Rule

This rule states that each breakdown level in the WBS must have all the deliverable elements representing 100% of its parent deliverable. This rule is essential to ensure that there are no duplicates. For this, it is necessary to ensure that the 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 assigned a number that can be referred to as a WBS ID. It 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 after that sequentially. For instance, it would assign 1.1 for the first sub-level element and 1.1.1 for other sub-level elements. 1.2 would be assigned for another first-level sub-element.

Step 7: WBS Dictionary

It 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 manually using the “Indent” buttons on the “Home” tab or 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 the task description so that no extra indenting is required.

Example #2

We now set up Excel to decompose the project 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 IDTask DescriptionPredecessorOwnerRole% CompletionStart DateEnd DateDeliver To

Step 2: After creating 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 designated for Task ID numbers by clicking on the letter at the top of the column. Next, right-click on the selected column, select “Format Cells” -> “Number,” and set decimal places to 1. Next, we format column: Predecessor, which will track task dependencies. We should set it 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, we wish to highlight tasks associated with a particular deliverable, assigned to a particular member, or tasks due within a given time range.

Step 5: We can also see the “data bar” feature graphically representing a column like “% Complete.” We can access this feature in the “Conditional Formatting” dropdown.

Things to Remember

  • For creating an efficient WBS, we should use nouns rather than verbs to keep the focus on deliverables.We should use a hierarchy or tree structure to illustrate the relationship of deliverables for the project scope.While drilling down through levels of WBS, we should follow the 100% rule.We should build WBS 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.

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

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