WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Work Breakdown Structure in Excel

Work Breakdown Structure in Excel

By Twinkle SethiTwinkle Sethi | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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?

You can download this Work Breakdown Structure Excel Template here – Work Breakdown Structure Excel Template

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:

Example 1.0
Tabular View

This looks as below:

Work Breakdown Structure in Excel Example 2

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:

Example 1.3.0

Work Breakdown Structure in Excel Example 1.4.0

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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:

Example 1.5.0

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.

Work Breakdown Structure in Excel Example 2.1

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Work Breakdown Structure Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More