If you are looking for a project management tool or software that can make project planning simpler, you have come to the right place.

Benefits of project planner

• Have Control over Project and Task dates
  • Project Start Dates can be restricted (optional) by providing preferred start date
  • Task Start Dates can be restricted (optional) by providing preferred start date
  • Preferred Project End Dates (aka Project due dates) can be given to compute if projects complete on time (optional)
  • Preferred Task End Dates can be given to compute if tasks complete on time (optional)
• Control the order of Projects and Tasks by assigning priorities
  • Projects need to be prioritized and prioritization is followed when resources are assigned
  • Tasks can be prioritized and prioritization is followed when resources are assigned
  • Each task can have up to two predecessors (dependencies). They can be within project or from other projects. A task can begin only the day after its predecessors have completed.
• Customize Capacity or Resource availability
  • Customize Weekends
  • Can handle any number of company holidays
  • Hours available to work per day (for the duration of the planning period) is customizable for each resource.
• Notes

• Each task is assigned to one and only one resource. A project can have multiple tasks and hence multiple resources.
• Resource can be assigned to multiple tasks on the same day.
• Planning is done at the granular unit of hours
• Assign some tasks as milestones if needed
  
  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
     
     

  FOLLOW THESE STEPS !!
  

Step 1. Enter basic settings

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

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.

• We enter an ID for each Resource and the name.
• We then enter the available hours for each weekday for each resource. The template allows each resource to have varying work hours.
• We also enter the cost per hour for each resource. This input allows us to calculate the cost of the projects later.

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

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.

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.

• Project ID and Project Name are required fields. They should be unique. Duplicate project IDs or Project Names will cause errors.
• Project Priority is another required piece of information. This enables the template to schedule tasks in the order of priority. Priority of 1 indicates the highest priority project and Priority of 2 is the next important one.
• Project Description is for your reference.
• Preferred Start Date: If you would like the project to not start before a certain date, enter that date. The scheduling engine will not schedule any work for this project before this date.
• Preferred End date: If there is a deadline or due date for a project, you can enter that here. The template will indicate if the project cannot be completed by this date.
• Project Fixed Cost: The template can calculate resource cost separately. If there are other project costs, you can enter that amount here. Later, we will see that the template will add the fixed cost and the resource cost together as the total cost.
• Project Plan Status: Please do not edit the field as it is calculated with a formula. If the project is missing any key piece of information, then the status will be ‘Error’. If there are no errors in data entry, the status will say ‘Planned’

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.

• Task ID should be unique
• Enter Project Name and the Task Name
• Enter Resource assigned to the task
• Enter planned duration of the task in Hours Required

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

• Task Preferred Start Date: Just like we entered a Project Preferred Start Date in the Projects sheet, we can enter a preferred start date for tasks. The template will then make sure that no work for that task will be scheduled before that preferred start date. This is another way you have all the control on the project schedule.
• Task Preferred End Date: Similar to Project Preferred End Date, if we have a due date for a specific task, enter that here. The template will then compare the expected end date with this preferred end date and tell us if the task will complete on time or not.
• Predecessors: In most practical scenarios, tasks have dependencies between them. For example, in the image above, Task ID 2 has Task ID 1 as predecessor. This means Task #2 cannot begin before Task 1 is complete. Similarly Task ID 4 cannot begin until Task ID 3 is complete.
  • The template supports up to 2 predecessors per task. For example, Task ID 5 will not begin until both Task ID 2 and Task ID 4 are completed. This can get very complex for planning, but the template has calculations built-in to take care of this. So, we don’t have to worry about it. 🙂
  • If we don’t pay attention to setting predecessors correctly, we may end up with circular reference errors.
  • Note: In this template, the dependent tasks can begin only the day after the predecessors complete.
• Task Priority: Just like we control the order of priority of projects, we can control the order of tasks within each project. Just enter the priority number. 1 is highest priority. This informs the template that higher priority tasks should be taken first. For example, if a resource is assigned to 2 tasks (one if priority 1 and second is priority 2), the template will schedule such that first task is completed before the resource moves to work on second task.
• Milestone: Certain tasks can be labelled as milestones and they are highlighted in the Gantt Chart. Just enter ‘Yes’ for those tasks in the MILESTONE field.

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

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.

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.

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

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.

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

• Summary Metrics
• Detailed Table with task dates, hours and planning result
• Gantt chart view

Summary Metrics

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

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

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

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.

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 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.

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.

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.