Site icon INDZARA

Project Planner 2023 – Free Excel template for Project Planning

Project Planner Excel Template

Project Planner Excel Template - Project Scheduling, Timeline, Gantt Charts, project report & Resource management (utilization)

If you are looking for a project management tool or software that can make project planning simpler, you have come to the right place. In this post, you will learn about a simple project planning framework and how to plan your projects smartly. You can download the free Project Plan template and start planning your projects immediately in Excel.

Project Planner Excel Template – Project Scheduling, Timeline, Gantt Charts, project report & Resource management (utilization)

This post is written such that even if you are new to project planning, you will be able to quickly get started. By the end of this post, you will learn how to do the following using the template

Video Demo

What is Project Planning?

As a Project Manager, one of the key responsibilities is to plan the project before beginning the actual project work. This project planning is very crucial in the success of the project. For example, if our project is to build a home, then we want to have a clear project plan as to how we will build before we start building.

Outcome of Project Plan

Let’s assume you are the project manager and you are responsible for planning multiple projects at the same time. The outcome of the planning stage should address the following questions:

  1. When will all my projects complete? Will they complete by the target deadlines?
  2. How much will each project cost?
  3. How efficiently have I used the resources on project? (Utilization Rate)
  4. How can I communicate to my team so that they know which work needs to be done when?

If the answers to the above questions are not satisfactory, we will have more questions. We will address that later in this post.

Now, why is not easy to answer these questions? The foundation to answering these questions is to build the project schedule (who is going to work on which task for how long).

Project Schedule

To build project schedule, we need to consider a variety of factors

Based on all the above input, we need to build the project plan schedule. This can get easily out of control if we do it manually. This is where we need the help of computers and software to build the schedule for us. Popular tools like Microsoft Project can do this. Now, you can also do it with a free project management tool.

Automatic Scheduling Engine

The template takes the above-mentioned inputs and automatically builds a project schedule. It lays out how we will complete the work needed for the project using the resources available. It clearly states who will be working on what task for how long on a given day.

It takes care of all the computations needed and saves us a ton of time and effort. For more, please read How does Automatic Scheduling Engine work.

Output of Project Planning template

Project Planning Template – Input and Output

The template provides the

What if you are not happy with the project plan?

It may take a few iterations to land on a schedule that is acceptable. For example, in the first iteration, it might take really long to complete the project, in the next iteration it may cost a lot.

The levers a Project Manager has are

Once we land on an acceptable schedule, the Project Manager finds the answers to the original questions and shares in a project report with the Project sponsors to get approval. Once approved, what we have is a project plan. This will include key dates (milestones), the detailed schedule, cost estimates and resource utilization rates.

Project planner Excel Template

Benefits of project planner

Free Downloads

 How to plan projects in Excel?

Overview of steps

  1. [Data Entry] – Enter basic settings such as planning period, resources’ availability and company holidays
  2. [Data Entry] – Enter Project list
  3. [Data Entry] – Enter tasks, assigned resources and duration of work
  4. [Automated] – View Project plan summary, timeline view and resource summary report
  5. [Automated] – View Gantt Charts – Schedule for all tasks, one project or a resource
  6. [Automated] – View Resource Availability and Scheduled hours

Detailed Steps

The template has the following sheets: Home, Settings, Projects, Tasks, Plan Summary, Task Schedules, Resource View and Help.

If you are new to using Excel templates, please read this article on 3 tips before using Excel templates.

If you are not familiar with data entry in Excel tables, please read Introduction to data entry with Excel Tables.

Step 1. Enter basic settings

We begin by first selecting the planning period in the Settings sheet.

Project Plan Excel Template – Enter Project Planning Period Start and End Dates

For example, I have entered the Nov 1, 2016 as the Planning Period Start Date and Dec 30, 2016 as the End date. The template can handle 60 days of planning period.

Next, we enter the list of resources (or employees) that are available to work on our projects.

Resources Available for Project Planning – Hours by Weekday and Cost Per hour

Then, we choose which days are weekends in our organization.

Choose Company Weekends to display on Gantt Chart

As we have already entered availability for each resource separately, company weekends don’t provide any additional input to availability calculations. But this is used to display company weekends in the Gantt charts (which we will discuss later in this post).

Finally in the Settings sheet, we enter Company Holidays.

Enter Company Holidays to exclude from scheduling planned work

Step 2: Entering Project Data

Now that we are done with entering resource availability data, we move to the Project information. Let’s enter a list of our Projects.

Entering project Details in project planner template (click to see larger image)

Step 3: Entering Task Data

The last part of data entry is the task level detail. We would enter each task in all our projects. Let’s look at required input fields first.

Entering Task details for project planner – Required Inputs for planning

There are optional fields that are available to handle additional scenarios that you may face in project management.

Entering optional information about tasks – Preferred dates, Predecessors, Priority & Milestone

Now, let’s discuss the very important Task Plan Result field.

View Task planning result instantly – will complete on time or late or not complete at all

Task Plan Result is a calculated field that will instantly inform whether the task will complete on time, complete late or not complete at all within the planning period. If there is any missing data input, it will also show an error.

This instant feedback is great for a project planner as you can change the resource assignment to see if the task will complete then.

Once we enter all the tasks we are done with our data entry. In the image above, I have entered 13 tasks for 3 projects in total. 1 task (task ID 2) seems to be completing late (Expected End date > Preferred End Date). Task ID 13 will not complete at all within the planning period (by Dec 30, 2016).

Step 4: Viewing Project Plan Summary

The Plan summary sheet is all automated. It has 3 sections (Projects’ Summary, Timeline view and Resource Summary).

Projects’ Summary
Each project’s plan details are shown.

Project Plan – Summary of projects’ planning status, expected dates and cost

Dates when the project is expected to start and end are clearly shown.

You can just export to PDF and share the summary with your stakeholders.

Project Timeline view

We can also see the project timeline visually.

Project Timeline view – Visually see project schedule – planned duration with preferred end date

Resource Management Summary

One of the crucial parts of project management is effective resource management. Having a clear view of resource utilization during the project is essential to delivering a successful project.

The Resource Summary table shows each resource’s utilization rate and cost.

Utilization Rate = Hours scheduled / Hours Available

Resource Summary Report with Resource Utilization Rate and Resource Cost

In addition, you can view the detailed number of Hours Available, Hours Scheduled, Days Available and Days Scheduled.
With the Plan Summary sheet, we have seen project level and resource level reporting. Next we can see the task level reporting as well.

Task Schedules

Task Schedules sheet provide three types of detailed schedules. All Tasks, Tasks from one specific project and Tasks assigned to one specific resource.

Three views of task schedules – All Tasks, Project view or Resource view

Once we choose a schedule type, there will be three sections of information.

Summary Metrics

For ‘All Tasks’, the summary metrics appear like this.

Task Schedule Summary Metrics for All Tasks

If we choose a specific project, then the summary metrics display change to present relevant project metrics.

Task Schedule Summary metrics for chosen project

If we choose a specific resource, then the summary metrics present relevant resource management metrics.

Task Schedule resource management metrics for chosen resource

Detailed Task level Details

We can see each task listed below along with the key details such as expected start and end dates, hours required and scheduled, and the task plan result.

Task Schedule Report with Task Planning Result

Gantt Chart view

In the Gantt Chart, we have option to view daily Gantt chart or weekly Gantt chart. We can also enter a start date of our Gantt chart.

Gantt Chart view of Project schedule – Number of hours of work scheduled

Gantt chart shows the number of hours scheduled for each day for the specific task. It also has the legend that explains the meaning of the different colors.

  1. Green: Work is Scheduled on this day.
  2. Gray: It is a company weekend day (resources may be available to work on weekends)
  3. Purple: Company holiday and no work will be scheduled on holidays.
  4. Red: Assigned Resource is not available to work on this task. This would indicate an opportunity to assign a different resource to see if that improves project completion.
  5. Blue: Milestone tasks’ completions are shown in Blue.

Resource View

Finally, we have the Resource View sheet which shows two key pieces of detailed information about resources.
We see the original resource capacity – number of Hours available by each resource on each day during project planning period.

Resource Management – View Original Capacity (hours available) for each resource by day

This capacity is calculated based on the availability data we entered for each resource in the Settings sheet, and the Company holiday information.

Next, we also see how many hours each resource is scheduled for project work. In other words, this is the planned utilized time.

Resource Management – View Scheduled hours for each resource for each day

The difference between Hours Available and Hours scheduled is the Idle time or unutilized time.
From the above two images, we can see that the Web Designer is available 8 hours a day from Nov 1st to Nov 4th, but utilized only for 8 hours on Nov 4th. So, Nov 1st to Nov 3rd, Web Designer is not utilized at all.

I hope you find the template useful. Please provide your feedback via comments below. If you like the template, please share with others.

This basic template can plan up to 25 projects at a time and a total of 150 tasks for maximum Planning period of 60 days. To plan for more than 25 projects and 150 tasks and with a planning period of up to 1 year, please see Project Planner Advanced Excel template.

For more functionality like managing a project, creating customized Gantt charts, resource planning, check out our other products.

Exit mobile version