The Automated Resource Planning Template for Excel
- Manage team member utilization in a better way
- Super-easy to use
- Runs in Excel. No other tool needed
So I asked my friend:
“How do you plan your resources?”
I was curious because he oversaw a hefty portfolio of 30+ projects for big-name clients and headed a 25-people team, so careful resource management was the only way for him to stay sane and deliver projects on time!
His answer struck me:
“I haven’t found a good solution yet”
Wait …. 30 intense projects run simultaneously, ultra-tight deadlines, demanding clients. Yet no good system for resource planning.
Then I dug deeper.
‘How do you plan work so that you are not running into resource conflicts?’
“Well …” He paused, trying to pull up his complicated process from his memory.
It sounded like a messy process – querying his people about their availability, copying data from other sheets and apps, preparing a spreadsheet, tweaking Excel formulas, styling and formatting … a tedious process he performed all by himself and late after work while his team members were already at home, enjoying dinner with their families.
Why do this manually when there are apps out there who can help you with resource planning? I wanted to know.
For my Team Leader friend, the answer was simple:
“My company doesn’t want to invest in a resource planning app”
And paying for an app out of his own pocket was not an option.
And even if there were apps out there who could help him, my friend didn’t want to upload personal information to the net.
He took data privacy very seriously.
I realized there was a need for a resource management tool. A simple but effective solution. Based on Excel, because everybody has Excel installed on their computer and people are already familiar with it.
So my mission was clear:
Create a resource planning template which allows one to quickly allocate resources across multiple projects and then provides an instant picture of our team members’ true workload.
I went on LinkedIn to find an experienced Excel developer.
How the Resource Planning Template was built
In a Zoom call I shared my idea with the developer. He immediately understood how to solve the problem in Excel.
The ‘problem’ was not that you couldn’t do resource planning with Excel. The problem was that the process was extremely cumbersome: you constantly had to adjust formulas and re-style your spreadsheet whenever you added a resource or projects or made a change to the timeline.
The problem we had to solve was: Any formulas as well as styling had to be updated automatically whenever we added or removed data from the spreadsheet.
My developer genius had an idea.
After a week of work my Excel developer came back with a solution.
I couldn’t believe what I saw.
I immediately started playing around with the Resource Planning Template.
Man, was I amazed:
- I could add new team members with the click of a button
- I could add or remove projects with the click of a button
- I could assign new projects to team members … with the click of a button.
You get the idea … THE TEMPLATE WAS A BREEZE TO USE!
And it all was based on standard Excel that worked out of the box. Just macros, but no plugins.
Let me show you the template.
The Automated Resource Planning Template
The Automated Resource Planning Template is an Excel-based tool for resource planning. It gives you a clear picture of your team’s workload. You can plan work and take on new projects with more confidence. It’s like having a roadmap that shows when and where to assign tasks, making it easier and less stressful to decide what comes next. The tool is built for a professional context with team members being involved in multiple projects.
Key features:
- 1-Click Planning Sheet Creation: The tool automatically generates a fully-styled planning sheet for you for the desired timeframe, showing your team members and project assignments. All in a simple and intuitive layout.
- Assign People to Projects: Maintain your project list first, then assign each employee to the projects they are being involved in. Based in your input, the tool will automatically generate a planning sheet for the desired timeframe (see next point).
- Easy Resource Planning: Enter the number of hours each employee is going to work on a particular project for each week. The tool aggregates the weekly numbers and compares it against each employee’s total capacity.
- Non-Working Days Considered: The tool takes into account non-working days such as public holidays for evaluating people’s capacity. Non-working days can be maintained in a separate tab. Individual absences due to holidays, sick leave or other reasons can be tracked by assigning a dummy “Vacation/Days off” project.
- Track Billable Hours Effortlessly: If your projects involve billing clients based on hours worked, this tool is a lifesaver. You can easily track billable hours, giving you and your clients a clear view of the time spent on each project.
- Weekly Utilization Monitoring: The tool lets you monitor resource utilization on a weekly basis, helping you make informed decisions about future project allocations and team capacity.
- Built-in automation: The tool automatically adjusts the planning sheet, including all formulas and styling, whenever you add team members or extend the calendar. Thus, you never have to touch formulas or tweak the styling manually as you would typically have to in Excel. It’s all done for you!
See the resource planner in action!
Step 1: Enter your team members, projects and customize the template
The way the Resource Planning Template works is that you first generate a customized planning sheet. It includes your team members, with their working hours, the projects they’re working on, the timeline you want to use etc.
You enter your team members, including their standard working hours (e.g. 40 hours per week). For any part-timer employees, you enter the respective total capacity, such as 20 or 30 hours a week.
Then you add all projects and tasks to the list. Add current projects as well as upcoming projects your team members will be contributing to in the future. You may want to add a general task like Admin and Meetings (or something like that) because usually your team members are going to spend a few hours every week in meetings or doing admin stuff.
A few more inputs you need to make before you can generate the planning sheet. The first field is called Monday of week one. This is the starting day of your timeline in the planning view. Also specify the number of weeks you want to plan for. Enter 52 if you want to create a one-year resource plan.
The template shows you the available full-time equivalent (FTE) capacity for your team. For this feature to work, you need to specify the average working time for a FTE in your country. Often used in our hemisphere: 176 hours per month – that’s the average working time for a full-time employee. Adjust that number based on your HR standards handbook.
Step 2: Generate your Resource Planning Sheet
Now we are ready to generate the planning sheet. Don’t worry, you can always add more team members or projects later on (directly in the planning sheet).
Click the Generate Planning Sheet button. Wait a few seconds for the macro to generate your planning sheet.
Step 3: Maintain the Resource Planning Sheet tab
Go to the Planning Sheet.
Enter the estimated weekly effort each of your team members will be contributing to each of the projects (in hours).
Once you have maintained the effort data, look at this –
You can instantly see the total effective working hours for each resource:
Step 4: Making ongoing changes
Adding new team members or projects, assigning projects to people, deleting entries – you can do all this directly in the spreadsheet.
What is great about the Resource Planning Template: you can perform all these steps with the click of a button.
Just choose the action you want to perform:
Watch me using the resource planner!
New feature: Support for other workweek formats
The Resource Planner now supports all of the following workweek formats:
- Monday-Friday (Saturday & Sunday off)
- Monday-Saturday (Sunday off)
- Sunday-Thursday (Friday & Saturday off)
Hi, I'm Adrian Neumeyer
As a Senior Project Manager I have managed large corporate projects for more than 10 years: SAP implementations, projects in logistics and engineering as well as finance projects (read my story). In 2017 I Tactical Project Manager to share hands-on tips and useful tools to help you deliver high-stakes projects with success.
Your Questions Answered
The Resource Planning Template requires a desktop installation of Microsoft Excel. Macros must be enabled. It works on Windows PCs and Mac machines with Intel chipset.
The template uses a weekly timescale.
No, the template can be used for any year. Using the built-in macro (accessible via a button) you can generate a Planning Sheet for any year. It is very easy.
Not directly, but there’s a simple way to factor in personal holidays for team members: Set up a Vacation task for each team member and maintain the hours to show their absences.
The data is stored on your computer. It’s just an Excel file.
No. The template only works with Excel.
Yes, you can enter the holidays for any country and the template will properly account for these days.
No. You can add as many team members, tasks or project as you need.
Very simple: I’m here to serve you and provide you great tools that make your life easier. If you discover the template does not work for you, send me a note via the contact form and I’ll issue you a refund (within 30 days after your purchase).
Send me your question via the contact form at the bottom of the page
What customers are saying
Ask your question here!
Get the Resource Planning Template for Excel
- Easily allocate working time for a large team working on many projects
- Keep track of your team members’ bandwidth
- Enter your country’s public holidays, and the tool will automatically account for these non-working days
- Microsoft Excel file (requires macros enabled)
- Unlimited number of resources and projects
- 30-day money-back guarantee if you are not happy
- You will get an email with the template
- Secure payment via FastSpring, a US payment provider
- Contact form for support or questions
