Gantt Charts in Project Manager Excel Template

Posted on

In this article, we will cover the options and features available in the Gantt Charts in Project Manager Excel Template. Gantt Chart is a commonly used visualization method to display project timelines. Project Manager template allows planning and managing a project from start to finish. It includes a GANTT sheet which builds automatic Gantt charts. It also provides a lot of options to customize the Gantt charts.

For more details on the template, please see video on Highlights of Project Manager Excel template

 

If you prefer video demo to text, please watch the video below, else continue to read further for the tutorial with screenshots and text.

VIDEO DEMO

 

Before we get into the details, it will be good to get an overview of the components or sections of the Gantt sheet.

6 SECTIONS

Gantt Chart - Overview
Gantt Chart – Overview

 

1. CONTROL PANEL: Here is where we have the options to customize the Gantt chart. We will see more details later in this article.
2. TASK LIST: List of up to 30 tasks that qualify based on the options chosen in the Control Panel. Task ID and Task Name are listed.
3. SCROLL: If there are more than 30 matching tasks, we can see them by changing the beginning number.
4. LEGEND + DATES: Depending on the View chosen in the Control Panel, the Dates and Legend will update. The dates are the start and finish dates that will be used to create the Gantt bars. The Legend will help in interpreting what the Gantt bars represent.
5. DATE HEADER: Dates on the timeline axis controlled by options in the Control Panel.
6. GANTT BARS: Actual bars reflecting the chosen View for the tasks selected

 

Now, let’s take a closer look at the sections.

CONTROL PANEL

The control panel has 5 fields.

PERIOD

You can choose the Gantt chart to be Daily, Weekly or Monthly.

Gantt Chart - Period (Daily/Weekly/Monthly)
Gantt Chart – Period (Daily/Weekly/Monthly)

 

When you change this, the Date Header changes and the Gantt bars update accordingly.

Gantt Chart - Daily
Gantt Chart – Daily

 

Gantt Chart - Weekly
Gantt Chart – Weekly

 

Gantt Chart - Monthly
Gantt Chart – Monthly

 

VIEW

There are four possible views: Baseline Plan, Current Plan, Actual and Current Plan Vs Baseline Plan.

Gantt Chart - 4 Views
Gantt Chart – 4 Views

 

Each view updates the Legend + Dates and the Gantt Bars.

Gantt Chart - Baseline Plan view
Gantt Chart – Baseline Plan view

 

Gantt Chart - Current Plan view
Gantt Chart – Current Plan view

 

Gantt Chart - Actual view
Gantt Chart – Actual view

 

Gantt Chart - Current Plan Vs Baseline Plan view
Gantt Chart – Current Plan Vs Baseline Plan view

 

FILTER

This allows us to control which tasks are represented in the Gantt Chart.  We have 3 options here 1) display all tasks, 2) display only tasks for one specific resource or 3) only milestones. This will update the Task List.

Gantt Chart - Filter options
Gantt Chart – Filter options

 

Milestones are tasks that are flagged with M in the MILESTONE column in the TASKS sheet.

 

RESOURCE

If we had chosen SELECT RESOURCE in the Filter, then we have to provide a resource name. Then, only those tasks that are assigned to the chosen resource will be listed.

Gantt Chart - Select Resource
Gantt Chart – Select Resource

 

START DT

We can control where the Gantt Chart begins by typing any Start date.

 

SCROLL

The matching 30 tasks are represented in the Gantt chart. If we would like to ‘scroll’ and see the next set of 30 tasks, we can. For example, in the image below, there are 50 matching tasks.

Gantt Chart - Choose any set of 30 Tasks
Gantt Chart – Choose any set of 30 Tasks

 

By typing 21 in the cell, we will be able to view tasks 21 to 50 (30 at a time).

 

DATE HEADER

The date header will display the period end date. If the period chosen is Weekly, then, it displays the Week End Date. Similarly, if Monthly period is chosen, it displays the Month End Date.  In addition, holidays, weekends and status date are highlighted.

Gantt Chart - Date Header
Gantt Chart – Date Header

 

GANTT BARS

The Gantt bars represent the duration of each task. Let’s take a sample task, as shown in the image below.

Gantt Bars - Example
Gantt Bars – Example

 

The task begins on Mar 4th and ends on Mar 11th. It does not mean that work is scheduled for every day in between. It just means the task begins on 4th and ends on Mar 11th. There may be days in between where no work is scheduled.

 

PRINTING

The Gantt chart and the data are set up for printing. We can use the standard printing method in Excel. Press Ctrl+P to open print preview and print as needed. If you would like to change the print area, just select the area for printing and choose ‘Set Print Area’ in Page Layout ribbon.

 

EXPORTING TO PDF

We can easily export the Gantt chart to PDF as well. From File menu, choose Export –> Create PDF/XPS

Export to PDF
Export to PDF

 

Then, give a name to the PDF file and select the folder in your computer to store the PDF. Click Publish.

Publish PDF
Publish PDF

 

 

LIMITS

The template allows 30 tasks to be displayed at a time for a maximum of 52 periods. The template comes with the password to unprotect and hence these limits can be extended by the user if needed.

 

If there are any questions about the Gantt charts or the template, please post them in the comments. Thank you.

 

RELEVANT LINKS

To create just Gantt charts without any other Project Management feature, try Gantt Chart Maker Excel Template

To plan multiple projects (along with Gantt charts), try Project Planner (Advanced) Excel Template

Terms used in Project Manager Excel Template

Posted on

The Project Manager Excel template uses the following terms. This list is also provided in the template itself. The list includes user input fields and calculated ones. The calculated ones are shown in green font below.

Click to know more about the features in the template

 

TASKS

  • TASK ID Unique Task ID (Required)
  • TASK NAME Task Name (Required)
  • RESOURCE NAME Name of resource assigned  (Required)
  • HOURS REQUIRED Work to complete task in hours (Required
  • TASK DESCRIPTION Description of Task (Optional)
  • TASK PREFERRED START DATE Date before which the task should not start (Optional)
  • TASK PREFERRED END DATE Date by when you prefer task to be completed (Optional)
  • PREDECESSOR 1 ID Task ID for first predecessor task (Optional)
  • PREDECESSOR 2 ID Task ID for second predecessor task (Optional)
  • TASK PRIORITY Priority of the task (Optional)
  • MAX DAILY ALLOCATION % Maximum Daily Allocation % for Task (Optional)
  • MILESTONE Enter M if this task is a milestone (Optional)
  • HOURS REQUIRED – CHANGE How has the work effort required changed after locking Baseline Plan? (Optional)

 

ACTUALS

  • TASK ID Task ID of task where actual work was performed
  • RESOURCE Resource that performed actual work on the task
  • DATE Date when actual work was performed
  • HOURS COMPLETED Hours of actual work performed
  • COST CHANGE Any change in cost from initial cost per hour for the resource. Enter cumulative change.
  • COST Cost for performing the task = (Cost per Hour * Hours Completed) + Cost Change

 

PROJECT REPORT

  • STATUS DATE Date as of which actual work has been performed
  • PROJECT STAGE Indicator of stage of project as of STATUS DATE. Baseline Planning, Project In Progress, Project Complete

BASELINE PLAN

  • STATUS Status as per Baseline Plan. Will Not Complete, Will Complete
  • BASELINE START Date when project is expected to begin as per Baseline Plan
  • BASELINE FINISH Date when project is expected to finish as per Baseline Plan.
  • HOURS REQUIRED Total Hours required to complete the project’s tasks as per Baseline Plan
  • HOURS SCHEDULED Total Hours scheduled as per Baseline Plan.
  • HOURS NOT SCHEDULED HOURS REQUIRED – HOURS SCHEDULED
  • RESOURCE COST Sum of cost for all resources [(Cost per Hour * Hours Scheduled in Baseline Plan)]
  • OTHER COST Enter any additional non-resource cost to be incurred.
  • TOTAL COST RESOURCE COST + OTHER COST
  • NUMBER OF TASKS Number of rows entered in the TASKS table
  • TASKS THAT WON’T COMPLETE Number of tasks that have a status ‘Won’t Complete’ in Baseline Plan
  • RESOURCE UTILIZATION (Hours Utilized in Baseline Plan/Hours Available as per Baseline Plan)
  • RESOURCES UNDERUTILIZED Number of resources with less than 100% utilization rate in Baseline Plan

 

CURRENT PLAN

  • STATUS Status as per Current Plan. Will Not Complete, Will Complete
  • CURRENT PLAN START Date when project is expected to begin as per Current Plan
  • CURRENT PLAN FINISH Date when project is expected to finish as per Current Plan.
  • HOURS REQUIRED Total Hours required to complete the project’s tasks as per Current Plan
  • HOURS SCHEDULED Total Hours scheduled as per Current Plan. = ACTUAL HOURS COMPLETED as of STATUS DATE + Remaining HOURS SCHEDULED
  • HOURS NOT SCHEDULED HOURS REQUIRED – HOURS SCHEDULED
  • RESOURCE COST Sum of cost for all resources: Actual Cost as of STATUS DATE + [(Cost per Hour * Remaining Hours Scheduled in Current Plan)]
  • OTHER COST Enter any additional non-resource cost to be incurred in Current Plan.
  • TOTAL COST RESOURCE COST + OTHER COST
  • NUMBER OF TASKS Number of rows entered in the TASKS table
  • TASKS THAT WON’T COMPLETE Number of tasks that have a status ‘Won’t Complete’ in Current Plan
  • RESOURCE UTILIZATION (Hours Utilized in Current Plan/Hours Available as per Current Plan)
  • RESOURCES UNDERUTILIZED Number of resources with less than 100% utilization rate in Current Plan

 

ACTUAL

  • START DATE Actual Start Date of Project
  • FINISH DATE Actual Finish Date of Project
  • HOURS COMPLETED Actual number of Hours of work completed as of STATUS DATE
  • HOURS REMAINING Number of hours of work remaining = HOURS SCHEDULED in Current Plan – HOURS COMPLETED
  • PLANNED VALUE Cost associated with work that should have been performed as of STATUS DATE as per Baseline plan
  • EARNED VALUE Cost associated with work that has actually been performed as of STATUS DATE. Uses the cost rates in Baseline Plan.
  • ACTUAL COST Actual Cost incurred for the work that has actually been performed as of STATUS DATE
  • REMAINING COST RESOURCE COST in Current Plan – ACTUAL COST
  • COST PERFORMANCE INDEX CPI = EARNED VALUE/ACTUAL COST
  • SCHEDULE PERFORMANCE INDEX SPI = EARNED VALUE/PLANNED VALUE
  • COMPLETED TASKS Number of Tasks where Actual Work performed >= Hours Required
  • REMAINING TASKS NUMBER OF TASKS – COMPLETED TASKS
  • RESOURCE UTILIZATION (Hours Actually Utilized as of STATUS DATE/Hours Available as of STATUS DATE)
  • RESOURCES UNDERUTILIZED Number of resources with less than 100% utilization rate as of STATUS DATE

 

TASK REPORT

BASELINE PLAN

  • BASELINE BUDGET COST Resource Cost for the task as per Baseline plan
  • BASELINE STATUS Status of the task as per Baseline Plan. Will Complete by Pref Date, Won’t Complete by Pref Date, Won’t Complete

CURRENT PLAN

  • EARLIEST POSSIBLE START DATE Date by which the task can begin, considering constaints (Predecessors, Task Preferred Start Date)
  • PLAN STATUS Status of the task as per Current Plan. Will Complete by Pref Date, Won’t Complete by Pref Date, Won’t Complete
  • FINISH VARIANCE STATUS Comparison of Current Plan’s Finish Date (CPFD) vs Baseline Plan’s Finish Date (BPFD). If CPFD>BPFD, “Behind Schedule”, If CPFD<BPFD, “Ahead of Schedule”, If CPFD=BPFD, “On Schedule”.

ACTUAL

  • ACTUAL STATUS Actual status of task. Not Started, In Progress, Complete
  • % OF CURRENT PLAN COMPLETE % of Work Complete. = (Actual Hours Completed/Hours Scheduled in Current Plan)*100
  • ACTUAL VS BASELINE VARIANCE STATUS % of Current Plan Complete as of STATUS DATE / % of Baseline Plan complete as of STATUS DATE; On Track, Late, Ahead

 

RESOURCE_AVAIL

Presents the number of hours a resource is available each day. This is before any work is scheduled for the resources. This captures the standard availability, any vacation/overtime and holidays. This does not represent the unutilized time. This does not factor in post-baseline vacation/overtime.

RESOURCE REPORT

BASELINE PLAN

  • HOURS AVAILABLE Hours available by a resource as of the Baseline Plan Finish Date
  • HOURS SCHEDULED Hours scheduled for a resource as per Baseline Plan
  • UTILIZATION RATE (HOURS SCHEDULED/HOURS AVAILABLE)*100
  • COST COST PER HOUR * HOURS SCHEDULED

CURRENT PLAN

  • HOURS AVAILABLE Hours available by a resource as of the Current Plan Finish Date. Includes post-baseline vacation/overtime.
  • HOURS SCHEDULED Hours scheduled for a resource as per Current Plan. Includes Actual Hours of work completed as of STATUS DATE.
  • UTILIZATION RATE (HOURS SCHEDULED/HOURS AVAILABLE)*100
  • COST ACTUAL COST as of STATUS DATE + (COST PER HOUR * HOURS REMAINING in Current Plan)

ACTUAL

  • HOURS COMPLETED Hours of Actual Work Completed by a resource as of STATUS DATE
  • HOURS REMAINING HOURS SCHEDULED in Current Plan – HOURS COMPLETED
  • UTILIZATION RATE (HOURS COMPLETED/HOURS AVAILABLE as of STATUS DATE)*100
  • COST ACTUAL COST as of STATUS DATE

 

If there are any questions, please leave them in the comments. I will respond as soon as I can. Thank you.

Project Manager Excel Template – User Guide

Posted on

In this article, we will cover the overview of how to use the Project Manager Excel Template. Then, we will use a simple project to illustrate how to use the template.

SIMPLE OVERVIEW OF INSTRUCTIONS

PLANNING

  • Enter basic inputs in SETTINGS
  • Enter task details in TASKS
  • Review Baseline Plan summary in PROJECT_REPORT
  • Take action to improve plan if needed (use TASK REPORT, RESOURCE REPORT, TASK SCHEDULE)
  • Finalize Baseline plan

 

MANAGEMENT (when project work actually begins)

  • Enter actual work performed in ACTUALS
  • If work estimate for tasks change, update TASKS input
  • View Project and Resource Reports to monitor project progress
  • Share reports with project members and stakeholders
  • Take action to address delays if any (use TASK REPORT, RESOURCE REPORT, TASK SCHEDULE)
  • Save/Share final report when project closes

SAMPLE PROJECT – DEMO

In this section, we will be using a sample project to demo how to use the template.

PLANNING

Let us imagine that today is Feb 15th, 2016. We are ready to plan our project. We will begin by entering Project Name and Project description.

Project Manager Excel Template - Settings
Project Manager Excel Template – Settings

 

We want to work on this project beginning March 1, 2016. Let’s enter Project Period. The template can handle a period of up to 1 year.

Project Manager Excel Template - Settings - Project Period
Project Manager Excel Template – Settings – Project Period

 

Then, we will choose SAT & SUN as weekends. The weekend days will be highlighted on the Gantt Chart that we will see later.

Project Manager Excel Template - Settings - Weekends
Project Manager Excel Template – Settings – Weekends

 

Let us enter 3 holidays that we are going to have during our project. Work will not be scheduled by the template during these days.

Project Manager Excel Template - Settings - Holidays
Project Manager Excel Template – Settings – Holidays

 

It’s time to enter resources. We have 5 resources or employees that are available to work on the project. So, we will enter their standard availability by weekday. We will also enter their hourly rate.

Project Manager Excel Template - Settings - Resources
Project Manager Excel Template – Settings – Resources

 

To wrap up the resource availability information, we will enter details on vacation/overtime to be taken by our resources. Vacation will be entered as negative values while Overtime will be positive values.

Project Manager Excel Template - Settings - Vacation/Overtime
Project Manager Excel Template – Settings – Vacation/Overtime

 

We are ready to move on and enter the tasks in the TASKS sheet. We have 7 tasks in our project. Required information here are the TASK ID, TASK NAME, RESOURCE NAME and HOURS REQUIRED.

Project Manager Excel Template - Tasks
Project Manager Excel Template – Tasks

 

If tasks have any constraints on start dates, we can enter the TASK PREFERRED START DATE. The template, while scheduling the work will follow this constraint and will not schedule any work for a task prior to the TASK PREFERRED START DATE. Similarly, if there is a preferred end date, we can enter that too. The template cannot guarantee that the work will be completed by that date. However, it will highlight if tasks will not complete by TASK PREFERRED END DATE.

Project Manager Excel Template – Tasks - Optional Fields
Project Manager Excel Template – Tasks – Optional Fields

 

Most often tasks are dependent on completion of other tasks. We can establish dependency using the two columns (PREDECESSOR 1 and PREDECESSOR 2). We will enter the TASK ID of the predecessors.
Now that we have entered the necessary input, let’s jump to the PROJECT_REPORT sheet to see how our project plan looks.

Project Manager Excel Template - Project Report - Baseline Plan Summary
Project Manager Excel Template – Project Report – Baseline Plan Summary

 

We can see that the project will complete during our project period. It will start on Mar 1st and finish on Mar 25th. Total work effort is 168 hours, costing resource cost of 2820. Resource utilization is 34%.

Let’s say we are happy with the plan and so we submit to our leadership or sponsors for approval. After it is approved, we are ready to begin actual project work on March 1st. This completes the Planning Stage.

 

MANAGEMENT

Let’s fast forward time. March 1st arrives and we begin the project work. As we are about to begin, we realize that our original work estimates for a couple of tasks were incorrect. So, we go to the TASKS sheet and enter the HOURS REQUIRED – CHANGE field. This will not impact our baseline plan, but will impact our current plan.

Project Manager Excel Template - Change in Hours Required
Project Manager Excel Template – Change in Hours Required

 

To see what work needs to be done for the next seven days, we can view the TASK_SCHEDULE sheet.

Project Manager Excel Template - Task Schedule
Project Manager Excel Template – Task Schedule

 

If we enter March 1st in the first cell, then we can see how many hours of work need to be performed for each task for the next 7 days.

 

We begin work on March 1st and, at the end of the day, we enter the work performed on that day. We do the same on March 2nd as well. The data is entered in the ACTUALS sheet as shown below.

Project Manager Excel Template – Entering Actuals
Project Manager Excel Template – Entering Actuals

 

Please note that the task can be performed by a resource other than the one originally assigned in the baseline plan. Also, more than one resource can perform actual work (planning is limited to one resource per task).

Cost is automatically calculated based on cost per hour of the resource that worked on the task. If additional or less cost was incurred for any work performed, we can enter that in the COST CHANGE field. COST field will include that additional cost.

After entering actual work performed, it’s time to check how we are progressing towards our project goal. Let’s switch to the PROJECT_REPORT sheet. Enter Mar 2nd as the STATUS DATE.

Project Manager Excel Template – Project Report - Status Date
Project Manager Excel Template – Project Report – Status Date

 

We can view the summary of baseline and current plans.

Project Manager Excel Template – Project Report - Plans
Project Manager Excel Template – Project Report – Plans

 

We can see that we have increased hours of work to do (and thus the cost) in the current plan Vs baseline plan. However, the current plan will still finish on schedule due to the increased utilization rate.
We can see the progress visually on the Gantt Chart.

Project Manager Excel Template - Gantt Chart
Project Manager Excel Template – Gantt Chart

 

In the TASK_REPORT, We can see individual tasks to identify if any are late (compared to baseline). We can also view the % Complete (Actual hours completed/total hours planned in current plan).

Project Manager Excel Template - Task Report
Project Manager Excel Template – Task Report

 

Resource Report will present the resource side of performance measures.

Project Manager Excel Template - Resource Report
Project Manager Excel Template – Resource Report

 

This is how we can monitor the project progress. As we input actual work performed and update status date, the reports will reflect the new data input.

 

That’s how simple it is to use the template. It does have a lot of features and lot of access to relevant data about the project. I have tried to balance the need for transparent useful data Vs simplicity in operation. I look forward to your feedback.

 

Project Manager Excel Template – Support Page

Posted on

This page will host support material for Project Manager Excel Template.

To read about the template’s highlights and purchase, please visit Project Manager Excel Template.

If there are questions about the product, please leave them in the comments section below.

 

SUPPORT ARTICLES

  1. Fundamentals
    1. Project Management Framework
    2. Automatic Scheduling – What is it and why we need it?
  2. Getting Started
    1. User Guide – Step by Step instructions to use the template
    2. Terms used in the template with their definitions
    3. Options and Features in Gantt Charts

Coming Soon

  1. All about Task Report
  2. All about Task Schedule
  3. All about Resource Report
  4. All about Project Report
  5. How to modify Status Date
  6. Differences between Project Planner and Project Manager

VERSION HISTORY

  • v1 published in Feb 2015
  • v2 published in Mar 2016

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