team-meeting Resource planning with work plans

Resource planning helps everyone stay on target. You can build a resource plan for your team using a spreadsheet.

As a young manager many years ago, I didn't really know how to manage people. I just didn't have any training for that. So when I needed to assign staff resources to projects, I did it ad hoc. I tried my best, but it came down to asking who was interested in a project, and making a selection based on who had the best availability.

The problem with ad hoc planning is that you aren't really planning. There's very little thinking ahead.

Later in my career, I worked with someone who was a professional planner. As a former project management consultant, this person really understood how to do project and resource planning. I learned from him that there are many ways to do thoughtful resource planning, and from him I derived a method of resource planning that has served me well these many years later. I'd like to share it with you.

If you do project planning or work as a project manager, this method may seem very familiar to you. But if you haven't done resource planning before, I hope this will be helpful to you.

You don't need a fancy tool to do resource planning, although there are professional tools to do much of the work for you. But a spreadsheet works well enough, especially if you are just getting started in resource planning. So let's do this example with a spreadsheet.

The quarterly work plan

Start by thinking about a time frame. How far out can you do planning in your organization? If you're like me, one month is too short, and six months is too long. Things change more quickly than a six month time frame can accommodate, but not fast enough for a one month window. I use a three month time frame. This gives me a quarterly work plan.

There are 52 weeks in a year, 26 weeks in half a year, and 13 weeks in a quarter. So create your work plan by creating 13 columns, one column for each week. I also recommend you include the date of each week as a separate row—although for this example, I'll leave that out. Leave a few columns to the left; we will fill these in later:

work planning example

An empty work plan

Now, think about the work that you will need to get done over the next quarter. What work does your organization need to get done? What are the priorities from your leadership? What are the major projects? Put these on separate rows in the first column.

Don't forget that support activities also count here. For example, you may have systems administrators who need to keep servers running. Same for network administrators supporting your network. You need to include these too.

Add tasks and resources

Once you have the major projects, determine how the projects break down by tasks. A project consists of many steps. What are the components to complete the project? List the steps in the second column.

Then, identify the resources you have available. What projects are they best suited for? What are their skills? Where can you best direct their energies? Who would be the best contributor for each project? Don't forget that only very small projects require just one person; most projects will require more than one contributor. List the people for each task in the third column.

In this example, let's assume two "infrastructure" team members (John and Jane) and two developers (Jill and Jack). As we start the quarter, the developers are starting a new project (Project1) while they prepare to deploy a previous one (Project2).

work planning example

Tasks and resources in a work plan

As you identify new projects and tasks, keep the spreadsheet organized. In this example, see how separate projects start on their own row. Tasks are matched with a resource. I've kept my example somewhat simple by not providing much detail. If you were writing your own resource plan, you would likely need to fill in more information about the project and tasks.

Estimate time

Look ahead by weeks and consider how long each task will take. If this is your first time building a resource plan, you might "block out" some time by filling in each week with a color that you can go back to later. Let's do that in the example. I'll block out some time. Note that the support tasks should be blocked out for the whole quarter, but other tasks in other projects might take different amounts of time.

work planning example

Time blocks in a work plan

Add days per week

From here, you need to assign time or "effort" each week to every person working on every task in each project. You can do this a few different ways. Professional project managers sometimes prefer to use a four-day work week, where you always assume one day is "lost" to "overhead" (usually meetings). I prefer to use a five-day work week. I find my staff understand the five-day work week better if they can build in their own "overhead" and see where their time is going. This also allows some flexibility. For example, developers may need to attend more meetings for project planning, etc. Let's assign time to each project with the assumption that John and Jane attend fewer meetings than Jill and Jack.

work planning example

Rough time estimates in a work plan

Balance for each week

Look at every column, and make sure the total time for any one person adds up only to 5.0 every week. There are only five days in each week, you can't expect your team members to work more than five days. In the above, Jane is over-committed on "server" and "database." Similarly, Jill and Jack are over-committed across "Project1" and "Project2."

When considering the balance of time, I prefer to use a few simple rules: The smallest increment of time allowed is 0.5 days per week. That's four hours of work time, and I find you cannot really plan for time less than four hours per week.

As you balance the time committed to each project, consider the work load. For example, Jane's dual responsibilities as server administrator and database administrator may not require equal time. Perhaps she spends most of her time maintaining the server.

Plan for vacations too

And don't forget about vacations and holidays. Everyone needs time off, and you need to plan for that. Build your holidays into your work plan from the beginning. Ask staff to estimate their upcoming vacation time. While folks might take an unplanned day off here and there (or be out sick) you can still plan in advance for extended absences such as a long vacation.

work planning example

Balancing a work plan

As you can see in the work plan, Jane plans to divide her time between "server" and "database" tasks. For the first eight weeks of the quarter, Jill and Jack each will spend some time on analysis and design for "Project1," and the remainder of this time doing testing for "Project2." See under "Project2" how Jack ramps down on testing at the same time Jill ramps up, because of his balance of responsibilities.

Also, Jack gets to turn a holiday on week 2 into a four-day weekend in week 3, while Jill takes an extended vacation during weeks 9 and 10. John takes the whole of week 8 as vacation, and Jane takes all of week 11. When each person is out, notice that their time on other projects also needs to be redistributed.

That balances the time assigned to projects. No one gets overloaded. And everyone gets to take their vacations. The staff can be productive, while the manager is able to plan for their work.

With that, you have the start of a quarterly resource work plan!

Extending the work plan

You can add other features to this work plan. Here are a few ways to extend this:

Total time

As you build your work plan, it quickly becomes cumbersome to add up everyone's time each week, and ensure every person is allocated "5.0" days per week. Let the spreadsheet do the work for you. Look up the SUMIF function to make this easier. The SUMIF function is a standard spreadsheet function to add numbers from a column only if a key matches one in a list. In calculating total time, the key is the name of each staff person. I also use automatic formatting in my spreadsheet to highlight the total in a different color if the value is less than, equal to, or greater than "5.0." This helps me to quickly identify where staff are over-committed as we build the work plan.


As you progress through the quarter, how do you track when people actually work on projects? When I "borrowed" the work plan spreadsheet from my colleague, he used separate tabs in the spreadsheet for staff to report time. Entering time into the spreadsheet required staff to track hours worked against each planned work item, and provide an estimate to completion. This sounds great, but ended up being very confusing, especially for folks in a primarily "support" role. Later, I asked my teams to report their status using a simple metric: don't change the numbers in the work plan, but highlight each cell to indicate when you worked on something. Highlight in green if your project is progressing well, yellow if you encounter issues that might put your project behind schedule, and red if the project is at risk. Then I would review the spreadsheet every week as part of my staff meetings to see how things are going. If I saw any yellow or red for the previous week, we would go over that and talk about issues and how to move things forward.

Unplanned work

You can't really plan for unplanned work, but sometimes things just happen that require staff to drop everything and make some work items their number one priority. In higher education, the dean might ask us to work on a special project. This was unplanned work, and it certainly had an impact on our time. To account for unplanned work, I would add a few rows to the bottom of the spreadsheet, one row per staff member, and reserve this row for unplanned work. If you worked on an emergency project that wasn't on our list, you would record the time worked in your "unplanned work" row, and use a spreadsheet cell comment to indicate the unplanned work item. If this unplanned work impacted another planned work item, the staff member would highlight that other project's status with yellow or red to flag it for the manager. This ended up being a good balance of reporting time without over-burdening staff to fill out a weekly status report.

Roll-up reporting

Sometimes it's helpful for a manager to provide a "roll-up" report of how their team spends their time. This kind of "roll-up" requires adding a new column to the work plan to categorize the type of work. I borrowed from other professional project planners and developed a "code" to represent the type of work:

ANAnalyze: Time dedicated to understanding a project or doing background work about the project
DEDesign: Designing a solution
BUBuild: The creation of a system
TETest: Time dedicated to testing a new system
DPDeploy: Rolling out a new solution
PProject: Use this if your project doesn’t use defined phases like Analysis, Design, Build, etc
PMProject management: Usually reserved for managers who need to report time
CFChange-fix, or production support
NPNon-project time, such as vacations and holidays
UWUnplanned work: Time spent on emergencies or last-minute important work requests that were not planned (by definition, do not plan for UW)

Once you have a code entered for each task on the work plan, you can create a simple roll-up report to show how time has been allocated. Again, let the spreadsheet do this work for you. The SUMIF function can use the code as the key to generate the total time for each code. This lets you build a matrix to calculate the total time spent each week for each type of work.