Whether your running complex product manufacture or projects such as continuous improvement, being able to manage your resource requirements and getting an overview of your resource demand can be a complex challenge.
Without an adequate resource plan, your project planning is probably bust – you’ll be thinking you’ve got that resource to do that important task where in reality you haven’t and your schedule goes up in smoke.
This is where the resource plan comes in.
What Is a Resource plan?
A resource plan is a single source of data that represents what work is being undertaken & when vs the resource that will be used to undertake the task.
it can be used to show the overall demand on your available capacity.
Why would you want to do that?
If you are running a team, you’ll want to identify what work that team is undertaking. Perhaps you’re looking to see whether you can support new projects or tasks, you might want to look at how balanced the workforce is (i.e are some individuals undertaking more tasks than others?)
The key benefit around Resource plans is that they help support decision making around running a team i.e.
- They can help you ensure that your resources are working at an appropriate capacity,
- They can help in future resource planning decisions (i.e new staff recruitment) or whether or not you can take on new projects.
This is especially important if you’re a business that charges for resource time.
Problems with Excel resource planning templates.
Resource planning can be fraught with difficulty. In complex businesses (those with many projects) planning can be challenging because generally you don’t see what’s coming down the line and a resource plan can help in analyzing these situations.
There can be a multitude of reasons why resource planning/tracking is difficult, these include:
- Your business might not have a good view of what work is coming up and is, therefore, unable to plan effectively.
- Your workers might be employed on various projects which makes tracking complex
- Most businesses assume they are more effective than they actually are and capacity often gets calculated incorrectly with businesses assuming inaccurate levels of productivity.
What’s your business like? sound familiar?
As projects flex (often due to additional tasking being loaded or problems arising) businesses can find themselves trapped in a vicious circle of calculating resource plans time and again where tools are not used.
Without some foresight, resource planning can become extremely demanding.
This is where businesses require two things.
1) A resource plan
2) A review cycle with key owners/stakeholders.
Let’s cover the resource plan first. The good news is that there are a huge variety of tools out there you can use to plan/track resources (we’ll cover how to create your own in Excel later in this article, it’s easy to do and can produce great results).
The hard bit is coaxing out of the business what the demand forecast looks like.
If your business is like mine you’ll find competing projects/departments with over-optimistic views on their project schedules and demanding requirements that actually need more hours than the workforce can practically commit to.
While this sounds a little daunting, with a resource plan in place this can quickly and easily be highlighted and the business can then take the required action.
OK so let’s get to it – How to create a resource plan in Excel.
What this template isn’t
Ok, so point one. Of course, there are a lot of funky pro tools out there you can use, e.g those you can use that link into your MS project plans or corporate MRP systems, your resource plan Excel template isn’t that. The examples below are usable (and effective) methods of using a simple Excel workbook to track resources & tasks in small/medium sized teams.
These are not business-wide tools. Why? well despite Excel being a fantastic tool it does have some drawbacks when used for Enterprise planning eg.
- Updating an excel tool for the whole enterprise can be time-consuming/challenging (near on impossible)
- Providing access to the resource plan for the whole Enterprise has its challenges (by the time you’re done with emailing to everyone it’ll probably need updating and you get trapped in a vicious cycle!)
There is still a ton of organizations that use Excel as their primary resource planning tool. For a large organization, it can be resource hungry and impossible to keep on top of when you consider the amount of effort to build that corporate picture.
However, if you’ve got a small team (or just looking for a tool for yourself) then Excel has its merits. If you are familiar with Excel then it’s relatively easy to create your own template.
These templates work great with conditional formatting, if you’re unfamiliar with that then check out this link on the subject (click here for conditional formatting Microsoft tutorial>
So if you’re looking to build your own template I’ve covered the basics down below so feel free to expand on them if you see the need (we’d love to hear how you build your resource plans in the comments section below)
Depending on what you’re trying to achieve there are a few different methods that we’ll cover below in our three examples
1/ Simple – a template you can use to track your own tasks
2/ Group – a template you can use to track group resources
3/ Load based – a template you can use to track resource requirement against a given demand
First things first you need to consider the ingredients that we need to include in the template:
1/ The resource being tracked
Yes, it’s obvious but exactly who are you going to track? Your options here are individuals or groups. For strategic planning, you’ll probably be considering groups of people, for more detailed planning you may want individuals.
2/ Capacity
This is the time available by the worker/group. There are a number of options here this might be
a) Forecast availability – e.g. the best estimate of time available
b) Productive time available – e.g. you might forecast 80% of the working time as productive, so based on a 40hr working week that would imply you have 32hrs a week capacity. I highly recommend that you consider productivity when building your template. In assuming that all working hours are available you are probably going to underestimate your needs.
c) Working hours available – e.g. if your individual is employed for 40hrs a week your capacity is 40hrs (i.e. it assumes maximum productivity).
d) Time period being monitored, this could be days, weeks, months or other. You may want to account for shift work/working times (e.g. do you include/exclude weekends??)
e) Demands/Tasks – This is where you list the resource requirement e.g. Project A requires 100hrs of effort from this resource group to undertake a certain task.
Ok so now you have your data let’s have a look at how to build the template.
In each of the three examples below I’ve includes screenshots to show the row/columns and how the templates have been created so that you can copy and build your own.
1/ A simple Resource planning excel template
This is the first example, this one is a simple plan and can be used to track capacity over time of an individual across a set of tasks.
This spreadsheet is easy enough to setup requiring the following inputs
1/ The person’s name your tracking
2/ The tasks they are working on
3/ The forecast of what hours they will work on what task over the timescale your monitoring
4/ Thier total available capacity per week.
You can see how I’ve created a table to track the tasking/hours and I’ve then totaled the forecasted working hours and used some conditional formatting to show Green if it’s within capacity and Red if it’s over.
2/ Resource plan excel template that tracks multiple projects
This second template can be used to track resources against multiple projects for an individual.
Again, this template requires:
1/ The person’s name your tracking
2/ The tasks they are working on
3/ The forecast of what hours they will work on what task over the timescale your monitoring
4/ Thier total available capacity per week.
Again you can see from the image above how the table is set up and again I’ve used the same conditional formatting from the earlier example as I’m interested in using the colors as an alert for being over capacity.
3/ Load based Resource plan Excel Template
The layout of this example is a bit different from the first two.
This third template can be used to track multiple resources against a resource load/requirement over time.
In this example, it shows a Quality Inspection team and their need to inspect parts coming off an assembly line.
This template requires a little more work than the first two examples and requires the following inputs:
1/ Resource needs. In order to calculate the “resource requirement,” I’ve taken Production Qty X Hrs per unit = requirement.
2/ The names of the individuals your tracking
3/ Thier availability/capacity per week
The template totals up the available capacity and then uses condition formatting to show whether that total meets the need. I.e. in Column E in the screenshot below shows you have 200 hrs capacity vs requirement of 50hrs therefore Green. However, in Column H the need is 250 against a capacity of 200 therefore Red.
So there you have our 3 examples, if you’re looking to build your own Excel resource tracking tool I hope you found the article useful.
As ever we’d love to hear your feedback in the comments section below.