How to handle projected delays in projects – Advanced Scenario

Posted on

While planning for projects, you may come across scenarios where a project is projected to end later than preferred. This articles discusses options to handle such projected delays, in a scenario where tasks have predecessors. In the previous article, I addressed the scenario where predecessors are not involved. The Project Planner (Advanced) Excel template is used for this demonstration.

SETTINGS

 

Our planning period is between Nov 1st, 2015 and Apr 1st, 2016. We have 4 resources and their available hours are indicated as below.

Project Planner Advanced Excel Template - Settings
Project Planner Advanced Excel Template – Settings

 

We have one project called PROJECT ONE where our preferred end date is Dec 31st, 2015

Project Planner Advanced Excel Template - Projects
Project Planner Advanced Excel Template – Projects

 

The project has 3 tasks, Task 1 assigned for ‘Designer One’, Task 2 assigned to ‘Developer One’ and Task 3 assigned to ‘Designer Two’. The Task 3 is dependent on Task 1.

 

Project Planner Advanced Excel Template - Tasks
Project Planner Advanced Excel Template – Tasks

 

PROJECT SUMMARY

 

When we look at project summary, we realize that the project will not complete on time. The project is expected to complete on Jan 7th, while our preferred end date is Dec 31st.

 

Project Planner Advanced Excel Template - Project Summary

 

Project Planner Advanced Excel Template - Project Timeline
Project Planner Advanced Excel Template – Project Timeline

 

REASON

 

In order to better understand the reason for the delay, we look at the Task schedule.

Project Planner Advanced Excel Template - Tasks
Project Planner Advanced Excel Template – Tasks

 

It is clear that Task 3 is the one that ends the latest (by Jan 7th). This is where we need to understand that the delay of Task 3 is primarily driven by the fact that it only starts on Jan 1st. Re-assignment of Task 3 to another resource will not benefit in this scenario, as ‘Designer Two’ already works 40 hours a week and Task 3 requires 40 hours of work and so, the task is expected to take a working week. So, we need to look for ways to change the start date of Task 3.

 

SOLUTION

 

Since Task 3 depends on Task 1, let’s see if we can impact Task 1‘s expected completion date. We can assign Task 1 to ‘Designer Two’ who works more hours per week than ‘Designer One’.

 

Project Planner Advanced Excel Template - Tasks
Project Planner Advanced Excel Template – Tasks

 

Now, the project is expected to complete on time.

 

Project Planner Advanced Excel Template - Project Timeline
Project Planner Advanced Excel Template – Project Timeline

 

The Task schedule now reflects that the Task 1 is expected to end on 9th Dec and that allows Task 3 to start on 10th Dec and complete on 16th Dec.

 

Project Planner Advanced Excel Template - Task Schedule
Project Planner Advanced Excel Template – Task Schedule

 

TAKEAWAY

 

Assigning a delayed task to a different resource with increased availability will increase the chances of completion on time. But when the delayed task is dependent on another task, it is important to evaluate whether that predecessor task can be re-assigned to a different resource in order for that to complete earlier.

 

If there are any questions about this template, please leave them in the comments. Thank you.

How to handle projected delays in projects – Simple Scenario

Posted on

While planning for projects, you may come across scenarios where a project is projected to end later than preferred. This articles discusses options to handle such projected delays, in a simple scenario where tasks do not have predecessors. In my next article, I address the scenario where predecessors are involved. The Project Planner (Advanced) Excel template is used for this demonstration.

 

SETTINGS

 

Let’s take a simple scenario. Our planning period is between Nov 1st, 2015 and Apr 1st, 2016. We have 4 resources and their available hours are indicated as below.

Project Planner Advanced Excel Template - Settings

Project Planner Advanced Excel Template – Settings

 

We have one project called PROJECT ONE where our preferred end date is Dec 31st, 2015.

Project Planner Advanced Excel Template - Projects
Project Planner Advanced Excel Template – Projects

 

The project has 2 tasks, one assigned for Designer One and the second assigned to Developer One.

Project Planner Advanced Excel Template - Tasks
Project Planner Advanced Excel Template – Tasks

 

PROJECT SUMMARY

 

When we look at project summary, we realize that the project will not complete on time. The project is expected to complete on Jan 8th, while our preferred end date is Dec 31st.

 

Project Planner Advanced Excel Template - Project Summary
Project Planner Advanced Excel Template – Project Summary

 

This is also shown visually in the timeline view.

Project Planner Advanced Excel Template - Project Timeline
Project Planner Advanced Excel Template – Project Timeline

 

REASON

In order to better understand the reason for the delay, we look at the Task schedule. It is clear that Task 1 is the reason, where it is expected to go on until 8th Jan.

Project Planner Advanced Excel Template - Task Schedule
Project Planner Advanced Excel Template – Task Schedule

 

SOLUTION

Now, to address this issue, let’s see if we can assign the task to another resource. Designer Two actually has more working hours (40 compared to 25) every week than Designer One. So, let’s assign the task to Designer Two (on the condition that Designer Two has the skill to do Task 1).

 

Project Planner Advanced Excel Template - Tasks Re-assignment
Project Planner Advanced Excel Template – Tasks Re-assignment

 

I am sure you predicted the result. 🙂 The project will now complete on time.

Project Planner Advanced Excel Template - Project Timeline
Project Planner Advanced Excel Template – Project Timeline

 

We can see that in the task schedule that Task 1 is now expected to complete on 15th Dec.

Project Planner Advanced Excel Template - Project Timeline
Project Planner Advanced Excel Template – Project Timeline

 

Assigning the delayed task to a different resource with increased availability will increase the chances of completion on time.

 

Optionally, if you can increase the availability of the original resource, that would work too. For example, if we can increase the working hours of Designer One (from 25 to 40 per week) as shown in the image below, that would result in the same result of Project completing on time.

 

Project Planner Advanced Excel Template - Adding Hours of Availability
Project Planner Advanced Excel Template – Adding Hours of Availability

 

In the next article, I will discuss a slightly more complex scenario of projected project delays.

Circular References in Project Planner Excel Template

Posted on
This article discusses reasons for the ‘Circular Reference’ error that you may come across in the Project Planner (Advanced) Excel Template, and how to avoid them. ‘Circular Reference’ is the concept where one formula depends on its own result as its input. Continue reading Circular References in Project Planner Excel Template

Project Planner (Advanced) Excel Template – Expanding Number of Projects/Tasks

Posted on

This post is a tutorial on how to increase the number of projects and tasks that the Project Planner (Advanced) Excel Template – v2 can handle.

To increase the number of tasks, please do the following.

  • Unhide a sheet named Scheduler

 

Project Planner (Advanced) Excel Template - Unhiding Scheduler

Project Planner (Advanced) Excel Template – Unhiding Scheduler

 

  • Go to cell OC253. You can do this easily by pressing Ctrl+G and typing OC253 in the window.
    Project Planner (Advanced) Excel Template - Go To Cell OC253
    Project Planner (Advanced) Excel Template – Go To Cell OC253

     

  • Click the little arrow at bottom right of the cell and expand down to include more rows. You have now expanded the number of tasks.
    Project Planner (Advanced) Excel Template - End of Scheduler
    Project Planner (Advanced) Excel Template – End of Scheduler

     

ExtendingRows

Please be aware that as you increase the number of tasks, Excel needs to make hundreds of thousands of extra calculations. So, please add a few tasks at a time to see how Excel responds.

Let’s assume we increased by 10 rows (tasks).

  • Unhide H_RES sheet;
    • Select cells A252 and A253. Drag down to A273. This will add numbers 254 to 263.
    • Select B253 to NE253; Drag the formulas down to 10 more rows.
  • Unhide H_BP sheet;
    • Select cells C253 to NE253; Drag the formulas down to 10 more rows.

 

To increase the number of projects in Summary view

  • Go to PLAN SUMMARY sheet and click on the REVIEW ribbon and choose ‘Unprotect Sheet’.

 

Project Planner (Advanced) Excel Template - Unprotecting Sheet
Project Planner (Advanced) Excel Template – Unprotecting Sheet

 

  • Enter Password (provided in the SETTINGS sheet). You can now make changes to the sheet.
Project Planner (Advanced) Excel Template - Unprotecting Sheet
Project Planner (Advanced) Excel Template – Unprotecting Sheet

 

  • Go to cell P55 and extend the table as shown below.

 

Project Planner (Advanced) Excel Template - Expanding Projects
Project Planner (Advanced) Excel Template – Expanding Projects

 

To increase the number of projects in the timeline view

 

  • Select cells R55 to BT55.
  • Drag the mouse down to copy the cells (as many rows as you did earlier)
  • Press Ctrl+1 to open the formatting dialog box.
  • Click on the centre horizontal line border. This would remove the unnecessary border lines.

 

  • Project Planner (Advanced) Excel Template - Extending Tasks - Timeline view
    Project Planner (Advanced) Excel Template – Extending Tasks – Timeline view

     

Project Planner (Advanced) Excel Template – v2 – Enhancements

Posted on
Project Planner (Advanced) Excel Template – Summary - Project Timeline View

This post presents the key features that were added to the v2 of the Project Planner (Advanced) Excel template. Thanks to the hundreds of customers who have purchased the v1 and a special thanks to those who have taken the time to provide feedback on the template. As always, the current customers will get the upgraded version for free.

 

ENHANCEMENTS

1. Control Maximum Daily Allocation % of a resource’s time to a task

Project Planner (Advanced) Excel Template - Max Daily Allocation %
Project Planner (Advanced) Excel Template – Max Daily Allocation %

 

You can now assign a maximum daily allocation % to each task. This maximum % is used to limit how many hours a resource will work on that specific task daily.
For example, in the example, DEVELOPER is given max 50% allocation to Task ID 2. This is reflected in the Gantt chart where for Task ID 2, the resource can work only a max of 2.5 hrs, since the resource is available 5 hrs a day overall. (2.5 hrs is 50% of 5 hrs).

Project Planner (Advanced) Excel Template - Max Daily Allocation %
Project Planner (Advanced) Excel Template – Max Daily Allocation %

 

This allows the resource to work on another Task (Task ID 8) on the same days. Using this feature you can make sure that the resource can work on tasks in parallel.

 

2. Customizable Gantt Charts (Daily/Weekly/Monthly and any start date)

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization

 

Gantt Chart can now be modified to be Daily/Weekly/Monthly. You can also customize where the Gantt chart begins by choose any start date.

 

Project Planner (Advanced) Excel Template – Schedule - Project Gantt Chart
Project Planner (Advanced) Excel Template – Schedule – Project Gantt Chart Daily

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly

 

3. Personal Leave for resources

Personal Leave for resources can now be in hours. V1 allowed only whole days.

Project Planner (Advanced) Excel Template – Settings – Resource Leave
Project Planner (Advanced) Excel Template – Settings – Resource Leave

 

4. Increased Resources, Projects and Tasks

The template can now handle up to 30 resources.

It has also been redesigned to provide users the ability to increase number of tasks and projects as needed. Please visit How to expand number of projects and tasks page for more details.

 

 

 

5. Milestones

You can now select some tasks as Milestones. They will be highlighted in the Task Schedules and Gantt chart.

Project Planner Advanced Excel Template - Project Milestones
Project Planner Advanced Excel Template – Project Milestones

 

In the newly created column (MILESTONE), select Yes if a task is a milestone.

Project Planner (Advanced) Excel Template - Milestone
Project Planner (Advanced) Excel Template – Milestone

 

You will now be able to see those milestone tasks in Task Schedules sheet. The tasks will be shown in bold font and also the Gantt chart will show a blue filled cell where the milestone task is completed.

 

6. Highlighting tasks that will complete after preferred end dates

Project Planner Advanced Excel Template - Project Timeline view

Project Planner Advanced Excel Template – Project Timeline view

Project Planner Advanced Excel Template - Task Plan Result

Project Planner Advanced Excel Template – Task Plan Result

 

7. Fixed Project Costs

Fixed cost of projects can now be provided. It will be used to calculate overall costs. Overall Cost of the project will be Fixed Cost + Resource Cost. Resource cost will be based on hourly rates of the resources working on the project’s tasks.

Project Planner (Advanced) Excel Template - Project Cost

Project Planner (Advanced) Excel Template – Project Cost

 

8. Terminology modified to reflect that this is a planning template

There was some confusion when the v1 of the template referred to ‘Completed projects’ and had fields that were labelled ‘Completed?’. Since this is a planning template, the ‘completion’ refers to the predicted completion of a project/task if the schedule developed by the template is followed. In order to remove this confusion, the labels have been modified to now say ‘Will Complete’, ‘Will Not Complete’, etc.

Project Planner (Advanced) Excel Template – Schedule – Resource Tasks
Project Planner (Advanced) Excel Template – Schedule – Resource Tasks

Project Planner (Advanced) – Excel Template – v2 – Support

Posted on

Welcome to the support page for Project Planner (Advanced) template v2. This page will contain instructions on how to use the template and other relevant links.

Links

 

Version History

  • Current Version: 2
  • v1 Archived in August 2015

Overview of instructions

  1. Enter information in Settings (Planning Period, Resource Availability, Company Weekends and Holidays, Resources’ Personal leave)
  2. Enter Projects’ basic details (Project ID, Name and Priority)
  3. Enter Tasks (Task ID, Name, Resource assigned, Hours required).
  4. View the Task Plan Result whether the task will complete on time, late or will not complete.
  5. ‘Plan summary’ sheet provides printable report of projects’ summary, timelines and resource summary
  6. ‘Task Schedules’ sheet provides customizable Gantt chart that visually presents the schedule
  7. ‘Resource View’ sheet displays available and scheduled hours for each resource

 

Demo

Detailed Instructions

Step 1: Enter Information in Settings’ sheet

The Settings sheet contains important information elements that the template would use as inputs.

1) Planning Period Start and End Dates

This is the duration in which the template will plan the projects. Maximum allowed period is 366 days.

Project Planner (Advanced) Excel Template - Settings - Period
Project Planner (Advanced) Excel Template – Settings – Period

 

2) Resources

a. Enter list of resources (up to 30)
b. Enter their regular availability for each weekday.
c. Enter the cost per hour for each of the resource. This will be used in calculating project costs.

Project Planner (Advanced) Excel Template – Settings - Resources

Project Planner (Advanced) Excel Template – Settings – Resources

 

3) Company Weekends and Holidays

a. Choose Yes for weekdays which are usually company’s weekends.
b. Enter the list of Company holidays.
c. Template will not schedule any work during weekends and holidays.

Project Planner (Advanced) Excel Template – Settings – Company Weekends and Holidays
Project Planner (Advanced) Excel Template – Settings – Company Weekends and Holidays

 

4) Resource – Personal Leave

a. Enter any personal leave each resource will take. Enter hours that the resource will take for leave. This will be used to calculate the availability of resources that will drive the schedule.

Project Planner (Advanced) Excel Template – Settings – Resource Leave
Project Planner (Advanced) Excel Template – Settings – Resource Leave

 

Step 2: Enter Projects’ information

Project Planner (Advanced) Excel Template – Projects
Project Planner (Advanced) Excel Template – Projects
  • Required
    • Enter Project ID and Name. These should be unique and are required
    • Enter Project Priority (required).
      • Project Priority is used to determine the order of scheduling tasks. 1 represents the highest priority. For example, project with Priority 1 will be given importance and scheduled first before scheduling Project of priority 2.
      • If Project Priority is left blank, the project will not be included in plan. If two projects are given equal priority value, the tasks are ordered based on task priority and then the order in which they appear in the TASKS table.
  • Optional
    • Enter Preferred Start Date (date before which Project should not start)
      • If Preferred Start Date is left blank, Planning Period Start Date will be used.
    • Enter Preferred End Date (date by which the Project should end)
      • If Preferred End Date is left blank, project will be considered completing ‘ontime’ if it is estimated to complete.
    • Enter Project Fixed Cost.
      • This is the cost other than the resource cost which will be calculated by the template.
  • Calculated
    • Project Plan Status gives instant feedback on whether there are any errors associated with the Project. If there are no errors in data, then it will display ‘Planned’.

 

Step 3: Enter Tasks’ information

Project Planner (Advanced) Excel Template – Tasks
Project Planner (Advanced) Excel Template – Tasks

 

  • Required
    • Enter Task ID (unique values)
    • Enter Project Name (that the task belongs to) and Task Name
    • Enter Resource assigned to the task
    • Enter Hours Required to complete the task
  • Calculated
    • Task Plan Result field will display error if there are any. If there are no errors, it will display ‘Will Complete OnTime’ if the task will be completed prior to Task Preferred End Date and ‘Will Complete Late’ otherwise. It will display ‘Will Not Complete’ if the task cannot be completed.
Project Planner (Advanced) Excel Template – Tasks – Optional Fields
Project Planner (Advanced) Excel Template – Tasks – Optional Fields

 

  • Optional
    • Task Preferred Start Date can be used to indicate if a task cannot begin until a certain date.
    • Task Preferred End Date can be used to indicate if a task should end by a certain date. This is used to flag whether a task completes on time or late.
    • Up to 2 predecessors can be assigned to a task. When a predecessor is assigned, the task can be started only the day after the predecessor has been completed. Similarly when 2 predecessors are assigned, the task can begin only the day after both predecessors are completed.
    • Task priority is provided so that you can control which task gets done first if the same resource is assigned to multiple tasks within the same project.
      • If task priority is not provided, all such tasks in the project will be prioritized based on the order in which they are entered in the table.
    • Max Daily Allocation % is the % of a resource’s time every day that the task should be limited to. For example, if a resource is available for 8 hrs on a day and you had set 50% max daily allocation, then the template will only schedule the resource up to 4 hrs for that task. This allows a resource to work on multiple tasks in parallel.
    • Milestone: Leave it blank if a task is not a milestone. Otherwise, choose Yes. This will be displayed on the Gantt chart.
  • Calculated
    • Overall Priority will show ‘ERROR’ if required fields are not populated. Tasks that have errors will not be included in plan. Lowest number means the highest priority.

With this step, the data entry in this template is complete. All the following sheets will be automatically created.

 

Step 4: PLAN Summary

Projects’ Summary:

Project Planner (Advanced) Excel Template – Summary - Project Summary
Project Planner (Advanced) Excel Template – Summary – Project Summary
  • Summary of each project’s key metrics and attributes
  • Projects that will not complete will be highlighted.

 

Projects – Timeline view

Project Planner (Advanced) Excel Template – Summary - Project Timeline View
Project Planner (Advanced) Excel Template – Summary – Project Timeline View
  • Visual representation of the projects’ timeline (weekly).
  • It also highlights whether the project will complete on time, late or will not complete.
  • The blue vertical line indicates the Preferred End Date of projects.

Resource Summary

Summary of statistics for each resource

Project Planner (Advanced) Excel Template – Summary - Resource Summary
Project Planner (Advanced) Excel Template – Summary – Resource Summary

Resource Utilization Rate of each resource

Project Planner (Advanced) Excel Template – Summary - Resource Utilization
Project Planner (Advanced) Excel Template – Summary – Resource Utilization

 

5. Task Schedules

This sheet provides the schedules (Gantt charts) that are customizable. First, you can choose from three different schedule types. (Project, Resource or All Tasks). By default, All Tasks will be shown.

 

Project Planner (Advanced) Excel Template – Summary - Task Schedule Options
Project Planner (Advanced) Excel Template – Schedule – Task Schedule Options

 

You can choose PROJECT and choose one of the Projects. You will now see three things.
1) Project Summary at the top with key statistics

Project Planner (Advanced) Excel Template – Summary - Project Schedule
Project Planner (Advanced) Excel Template – Schedule – Project Summary

 

2) Tasks with their details. 50 tasks are shown at a time.

Project Planner (Advanced) Excel Template – Schedule - Project Tasks
Project Planner (Advanced) Excel Template – Schedule – Project Tasks

 

3) Gantt Chart

Project Planner (Advanced) Excel Template – Schedule - Project Gantt Chart
Project Planner (Advanced) Excel Template – Schedule – Project Gantt Chart

 

In order to see schedule for a specific resource, you can choose the resource schedule type.

Project Planner (Advanced) Excel Template – Schedule – Resource Schedule
Project Planner (Advanced) Excel Template – Schedule – Resource Schedule

 

Tasks that will complete late will be labelled as orange background and those that will not complete will be shown in red.

Project Planner (Advanced) Excel Template – Schedule – Resource Tasks
Project Planner (Advanced) Excel Template – Schedule – Resource Tasks

 

Gantt chart can be customized by entering any start date and choosing from Daily/Weekly/Monthly. You can also change the start date of the Gantt chart.

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization

 

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly

 

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly

 

Step 6: Resource View

 

The ‘Resource View’ sheet displays the hours available for each resource for each day and the hours they have been scheduled.

Project Planner (Advanced) Excel Template – Resource View - Available
Project Planner (Advanced) Excel Template – Resource View – Available

 

ProjectPlanner_Advanced_ExcelTemplate_ResourceScheduled
Project Planner (Advanced) Excel Template – Resource View – Scheduled

Project Manager – Excel Template – Support

Posted on

PLEASE VISIT PROJECT MANAGER EXCEL TEMPLATE FOR THE CURRENT VERSION.

PURPOSE

This Excel template can be used to plan and manage a project. The template saves time by auto-scheduling of tasks. It provides smart visual feedback (example: identifying tasks that will not complete) and thus helping to tweak the project plan and improve. Actual work can be tracked and project plan would update instantly. You can visualize the project in a dashboard, report, Gantt charts and Burn down chart.

Project Planner (Advanced) – Excel Template – v1 – Support

Posted on
This is the support Page for Project Planner (Advanced) Excel template v1. A new version of Project Planner is now available.
————————————————————————–
Plan your projects quickly with the new Project Planner (Advanced) v1 template.

The template can help you to
  • Determine if and when projects will complete given each project’s tasks, task duration, resource assignment to tasks, resource availability and priorities.
  • Determine if project will complete by due date
  • Prepare project, resource and task schedules at daily level
  • Understand resource utilization and identify opportunities for re-assignment to improve project completions.
  • Determine how many hours of work are still pending so that you can request more resources.
  • Estimate the cost of each project 


REQUIREMENTS

You need a copy of Microsoft Excel to open this file. The product has been tested in 

  • Microsoft Excel 2007, 2010 and 2013 for Windows 
  • Microsoft Excel 2011 for Mac


FEATURES

  • Can plan up to 50 projects at a time and a total of 250 tasks
  • Up to 25 resources can be included in planning
  • Maximum planning period is 366 days
  • Planning is done at the granular unit of hours
  • Instantly create resource, task and project schedules
  • A project can have multiple tasks and hence multiple resources. However, each task is assigned to one and only one resource.
  • Resource can be assigned to multiple tasks on the same day.
  • Projects need to be prioritized and prioritization is followed when resources are assigned
  • Tasks within a project can also be prioritized
  • 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.
  • Project Start Dates can be restricted (optional)
  • Project due dates can be input (optional) to compute if projects complete on time
  • Choose your company holidays
  • Customize company weekends (non-working days) according to your needs
  • Each resource can have different work hours for each weekday
  • Each resource can have personal leave days
  • Each resource can have different cost per hour

SETTINGS

Enter the list of resources, their work hours for each weekday and their costs.

Project Planner - Excel Template - Resource Settings
Project Planner – Excel Template – Resource Settings

You can choose days that are considered as weekends in your company. You can also enter company holidays. The template will not allocate any work on these days.

Project Planner - Excel Template - Company Holidays and Weekends
Project Planner – Excel Template – Company Holidays and Weekends

You can enter any personal leave days for each resource. Work will not be allocated to the resource on leave days.

Project Planner - Excel Template - Resource Personal Leave
Project Planner – Excel Template – Resource Personal Leave

PROJECTS

Enter Planning Period Start and End Dates. Then, enter project details (Project ID, Name and Priority are required).

Project Planner - Excel Template - Projects Settings
Project Planner – Excel Template – Projects Settings
  • If Project Priority is left blank, the project will not included in plan. If two projects are given equal priority value, the tasks are ordered based on task priority and then the order in which they appear in the TASKS table (explained below).
  • If Preferred Start Date is left blank, Planning Period Start Date will be used. If Preferred End Date is left blank, ‘On Time calculation’ will be set to blank.
  • Project Plan Status field will display error if there is any. If there are no errors, it will display ‘Planned’.
  • Up to 50 projects can be entered.

TASKS

Enter Task details (Task ID, Task Name, Project Name, Resource Name and Hours Required).
Project Planner - Excel Template - Tasks Settings
Project Planner – Excel Template – Tasks Settings
‘Task In Plan?’ field will display error if there are any. If there are no errors, it will display ‘Yes’ if the task will be completed. It will display ‘No’ if the task cannot be completed.
 Project Planner - Excel Template - Tasks Optional Settings

Project Planner – Excel Template – Tasks Optional Settings
In addition, the template provides further control. You can enter a Task Preferred Start Date that can be used if a task should not begin until a certain date. Up to 2 predecessors can be assigned to a task. When a predecessor is assigned, the task can be started only the day after the predecessor has been completed. Similarly when 2 predecessors are assigned, the task can begin only the day after both predecessors are completed.In addition, the template provides further control. You can enter a Task Preferred Start Date that can be used if a task should not begin until a certain date.
You can also provide task priority (priority of a task among other tasks in a project). If task priority is not provided, all such tasks in the project will be prioritized based on the order in which they are entered in the table.
Up to 250 tasks can be entered.

PLAN SUMMARY

This sheet provides summary statistics at project level and resource level.
 Project Planner - Excel Template - Plan Summary

Project Planner – Excel Template – Plan Summary

PROJECT SUMMARY

  • In Plan?: If the project does not have a priority and/or tasks, then it will not be included in plan.
  • EARLIEST POSSIBLE START DATE = Earliest date when the project can begin, provided there are no resource limitations on the project. MAX(Project’s Preferred Start Date, Planning Period Start Date, Task Preferred Start Date, Predecessor 1’s Expected End Date + 1, Predecessor 2’s Expected End Date + 1).
    • IF error, then Planning Period Start Date + 366. For example, if the predecessor does not have an expected end date (meaning predecessor cannot complete), then this task cannot begin. So, it will show Planning Period Start Date + 366.
  • EXPECTED START DATE: Date when the project can actually begin, given resource limitations;
  • DELAY IN START: Days between Earliest Possible Start and Expected Start. If there is delay in a project, you can try reducing it by changing resource assignments.
  • PREFERRED END DATE: Date you would like the project to end by.
  • EXPECTED END DATE: Date when the project will end based on the assignments.
  • HOURS REQUIRED: Number of hours required for the project.
  • HOURS ALLOCATED: Number of hours allocated in the assignments.
  • HOURS PENDING: Difference between Hours Required and Hours Allocated.
  • COMPLETED?: If Hours Pending is 0, the Project will be completed. If not, it will not be completed.
  • ON TIME COMPLETION?: if the Expected End Date is on or before the Preferred End Date, then the project is ‘On Time’. If not, it will be ‘Not on Time’. If Preferred End Date is not set for the project, then it will be blank.
  • PROJECT SPAN (DAYS): Time between Expected Start Date and Expected End Date.
  • PROJECT COST: Total cost for all the tasks. For each task, it will be calculated as (Hours allocated * Cost per hour for resource assigned).
Project Planner - Excel Template - Plan Summary Timeline view
Project Planner – Excel Template – Plan Summary Timeline view
Project Planner - Excel Template - Resource Summary
Project Planner – Excel Template – Resource Summary
RESOURCE SUMMARY
  • HOURS AVAILABLE: Number of Hours resource is available.
  • HOURS ALLOCATED: Number of hours allocated to tasks for a resource.
  • UTILIZATION RATE: Hours Allocated/Hours Available
  • DAYS AVAILABLE: Number of days where the resource is available.
  • DAYS ALLOCATED: Number of days the resource has been allocated work.
  • DAYS NOT ALLOCATED: Number of days the resource has not been allocated work.
  • NUMBER OF TASKS ASSIGNED: Number of tasks that a resource has been assigned to.
  • COST: Cost for each resource. Hours Allocated * Cost per Hour
Project Planner - Excel Template - Resource Utilization Rate Chart
Project Planner – Excel Template – Resource Utilization Rate Chart

TASK SCHEDULES

You can view three types of task schedules. (Gantt Chart in Wikipedia) You can see tasks for one project, tasks for one resource or all tasks.

Project Planner - Excel Template - Gantt Chart - Schedule Type
Project Planner – Excel Template – Gantt Chart – Schedule Type

PROJECT SCHEDULE

Project Planner - Excel Template - Gantt Chart - Project Schedule
Project Planner – Excel Template – Gantt Chart – Project Schedule

RESOURCE SCHEDULE

 Project Planner - Excel Template - Gantt Chart - Resource Schedule

Project Planner – Excel Template – Gantt Chart – Resource Schedule
You can also view the schedule visually. Work Allocated, Company Holidays, Company Weekends and ‘Resource Not Available’ days are highlighted in different colours.
Project Planner - Excel Template - Gantt Chart - Resource Schedule
Project Planner – Excel Template – Gantt Chart – Schedule
Project Planner - Excel Template - Gannt Chart
Project Planner – Excel Template – Gannt Chart
RESOURCE VIEW
In this sheet, you can view the hours available and hours allocated for every day for all the resources.
Project Planner - Excel Template - Gantt Chart - Resource Availability
Project Planner – Excel Template – Gantt Chart – Resource Availability
Project Planner - Excel Template - Gantt Chart - Resource Schedule
Project Planner – Excel Template – Gantt Chart – Resource Schedule

Updates planned for Project Planner (Basic)

Posted on
I have received multiple requests for expanding the Project Planner (Basic) Excel template. Thanks for the feedback. The following are the enhancements I am planning to make to the template. 

  • Increase planning period (currently set as 60 working days)
  • increase number of projects (currently limited to 25)
  • increase number of resources (currently limited to 15)
  • increase number of tasks (currently limited to 150)
  • Increase number of holidays (currently limited to 10)
  • Option to choose paid time off for each resource
  • Option to choose weekends
  • Option to set availability for each resource for each weekday

I will try to expand the template without adversely impacting the performance. If you have any additional comments or suggestions, please enter them in the comments below. 

Videos: Project Planner (Basic) – Excel Template

Posted on
One of the first templates that I had published is the Project Planner (Basic) Excel template. Now, I have created a three part video series about the template. 
 The first part introduces the template and the benefits.

 

 

The second video discusses how data is entered in the Projects and Tasks worksheets.
 

 

The third and final part presents the project schedule, resource schedule and the summary report sections. 
 


For more about the template and to download it, please visit Project Planner (Basic) Excel template