Resource Capacity Planner Excel Template – Step by Step User Guide

Posted on

In this blog post, we will see step by step how we can use the Resource Capacity Planner Excel template. We will take a simple example planning scenario and see how the template can help us in smarter planning.

The Resource Capacity Planner template is designed to determine the available resource capacity, compare with demand and identify surplus/deficit in capacity. It also allows us to modify the resource availability or demand (to meet our objectives) and see impact of the changes instantly.

For more on template’s features, please visit the product page.

Template Version

This user guide is for the latest version v3 of the template. If you are using a previous version and need the new file, please email support@indzara.com with your Order Number. If you need support with the previous version, please email your questions to support@indzara.com

Overview of steps:

  1. Enter Settings
  2. Enter Resource list with standard availability and allocate Capacity to Skills and Projects
  3. Enter vacation and overtime
  4. Enter Demand (tasks) data
  5. Refresh Calculations
  6. View Dashboard to evaluate plan
  7. Address any over-utilization and under-utilization
  8. View Calendar to ensure desired utilization at granular time periods

 

Step 1: Enter Settings

In the Settings sheet, we will enter the basic settings.

Planning Period
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.

Step 1 Enter Planning Period Dates
Step 1 Enter Planning Period Dates

We will use a 3 month period from Apr 1, 2019 to June 30, 2019.

Weekends
In most work places, we have days during a week where we don’t work (also called weekends). We can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.

If you don’t have any weekends, then leave this input blank. Just click the cell and press the Delete key. It will remove the contents of the cell. Please do not delete the column or row.

Enter Weekends
Enter Weekends

We will choose SAT & SUN as weekends for this tutorial.

Enter Settings - Weekends
Enter Settings – Weekends

If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Excel Tables

In the next set of inputs, we use Excel Tables. Entering the data correctly inside the Excel tables is critical to the functioning of the template. If you are new to Excel Tables, please see our article on How to use Excel Tables for data entry?

Tip: Start entering data right below the Header of each Table

 

Holidays
Since most companies have holidays (other than weekends), it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.

We will enter a couple of days in the Holidays table.

Step 3 - Enter List of Holidays
Step 3 – Enter List of Holidays

If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Projects
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.

Enter Settings - Project List
Enter Settings – Project List

 

Skill Groups
Skill Group is how we can group resources for planning.

Skill Group and Skill are used interchangeably in the template.

In our example, we have resources from 2 skill groups: Project Mgmt and Development.

Enter Settings - Skill Group List
Enter Settings – Skill Group List

Resources
Enter list of Resources available for planning.

The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used.

Let’s assume we have 6 resources in total.

Step 6 - Enter list of Resources
Step 6 – Enter list of Resources

Step 2: Enter Resources’ Capacity

Now, we move to the Capacity sheet in the template.

We must enter the resources, provide start and end dates of availability, available hours every weekday and other information related to capacity.

Tip: To help with data entry, you can copy the values you entered in the Settings sheet, for Resources, and then paste in the Capacity sheet. Please make sure that you paste as values.
After copying the values, right click on cell A4 in Capacity Sheet. In the menu that pops up, choose Values under Paste Options. This ensures that only the values are carried over, without impacting the formatting and drop-down validations.

Pasting Resource Names as Values
Pasting Resource Names as Values

After entering resource names, we enter the Start Date of each resource.

Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.

Enter Resources Capacity - Available Work hours by Weekday
Enter Resources Capacity – Available Work hours by Weekday

We enter the number of hours each resource will be available each weekday. For example, Project Planner 1 is available 8 hours a day from Mon to Fri. The template can handle varying hours by weekday as well.

We assign the 6 resources each to a Skill Group and Project.

We can provide the End Date of each resource if there is an End Date for the availability. If no end date is provided, the template will assume that the resource is available through the end of the planning period.

Resources Capacity Table - Entered
Resources Capacity Table – Entered

Finally, we enter the Cost per Hour for each resource.

Important: The last column is a calculated field that is critical to the functioning of the template. Please do not edit/delete it. If you don’t want to see it, you can hide the column

How to allocate same resource to multiple skills?

You can allocate each resource to one or more skills.

To allocate one resource’s capacity to multiple skills, add a new row to the table and assign a different skill. Ensure the available work hours are distributed between the two skills.
For example, if Developer 4 was available 6 hours in total and can perform Development role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.

Entering same resource with multiple skills
Entering same resource with multiple skills

Resource name will be same Developer 4, but work hours may vary, and Skill Group will be different. In this case, we assigned to same Project B.

How to allocate same resource to multiple Projects?

You can allocate each resource to one or more Projects.

To allocate one resource’s capacity to multiple projects, add a new row to the table and assign a different Project. Ensure the available work hours are distributed between the two Projects.

For example, if Developer 4 was available 6 hours in total (Development skill), and will work 4 hours for Project B and 2 hours for Project A, we would enter as shown below.

Entering same resource with multiple Projects
Entering same resource with multiple Projects

Resource name will be same Developer 4, but work hours may vary, and Project will be different.

How to allocate same resource to multiple Projects and Skills?

You can allocate same resource to one skill in one project and a different skill in another project.

For example, if Developer 4 was available 6 hours in total, and will work 4 hours in a Development role for Project B and Project Mgmt role for 2 hours for Project A, we would enter as shown below.

Entering same resource with multiple Projects and Skills
Entering same resource with multiple Projects and Skills

How to shift same resource to a different Project after a certain date?

If we want a resource to work in one project for a certain date range and then change to a different Project, we can use the same approach. We can use the END DATE column to enter the end date of a certain Project assignment and then enter a new row with a different project.

Assigning a resource to different projects over time
Assigning a resource to different projects over time

In the above illustration, Developer 4 works on Project B for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project A.
You can apply the same method if you want a resource to change skill from a certain date.

Let’s go with the simple setup now where each resource is assigned to only one skill and one project.

Limitation:

Now, the above examples would have made it clear that this Capacity table can have more than 1 record per resource. Though the default Resource limit is 100, this table can accept 300 entries.

For example, if you have 50 resources, each resource assigned to 2 skills on average and 3 projects on average, that would require 50 X 2 X 3 = 300 entries.

300 unique Resource-Skill-Project combinations can be entered.

Step 3: Enter Vacation and Overtime

Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime.
We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.

Enter Vacation Overtime taken by Resources
Enter Vacation Overtime taken by Resources

Project Planner 1 is taking 3 days off (8 hrs each day) from 16th Apr to 18th Apr.

Two important things to note here:

  1. It is 8 hrs of vacation each and hence totally 24 hours in total for all 3 days. However, we only enter 8 hrs as the column is for daily hours. The template will calculate automatically the total.
    • If the employee’s vacation hours vary over the three days, then we must create separate rows.
  2. We enter -8 since it is vacation. If it is overtime, we will enter 8.
    • If vacation hours for a day is greater than the standard availability of that resource, then the result will be set to 0 to avoid negative availability.

Project Planner 2 is taking one day off (8 hrs) on 21st May. We enter 21st May as Start and End Dates. It is important that we fill both dates.

Finally Project Planner 2 also does overtime on 25th May. We enter 8 (not -8) in Hrs, since this is overtime.

This 25th May is a Saturday and is a weekend day. However, overtime can be done on weekends and holidays as well. The template supports that.

Overtime is positive to capacity and Vacation is negative to capacity.

This completes our capacity inputs. Now, we move to the Demand data input.

Important:

If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).

Step 4: Enter Demand data

In this template, demand is entered as Tasks in the Demand sheet.

Enter Demand as Tasks assigned to Resource Project and Skill
Enter Demand as Tasks assigned to Resource Project and Skill.

Required fields: Date of the task, Resource assigned to do that task, Hours of work required, Project assigned and Skill Group.

Detail column is optional to enter any details about the task or any notes.

The three calculated cells on the right should not be edited or modified. That would break the functionality of the template. Please do not delete or edit these columns.

Do Not Edit Calculated Fields
Do Not Edit Calculated Fields

Planning granularity

An important concept to understand is planning granularity. We have entered daily level of tasks in the above image. What level of tasks to be entered depends on the planning granularity you need.

When we say ‘weekly planning’ it means that our goal is to ensure all the tasks for the week are completed by end of week. If we are behind on Monday, Tuesday or Wednesday, that is okay. As long as we complete by end of week, we will be accomplishing our goals. In this scenario, you don’t have to enter daily level tasks. You could enter just one record for a resource for each week.

If you want to ensure you will be meeting your demand every single day, then, you should enter daily tasks individually.

Thus, the template can accommodate more granular demand entry (daily – even multiple tasks per day) or aggregated demand entry (weekly or monthly or quarterly).

Once we complete entering the demand, we are done with data entry.

 

Step 5: Refresh Calculations

The template uses pivot tables and hence every time we change input data, please refresh calculations.

Data ribbon –> Refresh All.

Refresh All calculations
Refresh All calculations

 

Step 6: Review Dashboard

Dashboard

Dashboard sheet shows the summary of metrics across all projects, skills and resources.

Capacity Planning Dashboard
Capacity Planning Dashboard

On the left side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).

Dashboard Slicers
Dashboard Slicers

At the top, you will see the overall summary of metrics.

Capacity Planning Dashboard - Summary Metrics
Capacity Planning Dashboard – Summary Metrics

You can see the number of Projects (2), Skills (2) and Resources (6) in the Plan.

On the left side, you can see the Capacity (2472 hrs), Demand (2287 hrs), Surplus of 185 hrs at 93% Utilization Rate.

On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,554 while the demand tasks will cost $23,273 resulting in a surplus of $2,281 at 91% utilization.

We also see that there are 2 over utilized resources and 3 under-utilized resources.

Why is this important?

The overall aggregate utilization rate will not provide the complete picture. If there is a Developer resource who is over utilized (utilization rate > 100% or capacity < demand) has 50 hrs over utilized and another Project Mgmt resource who is under-utilized (utilization rate < 100% or capacity > demand) with 50 unutilized hrs, they cancel each other when we aggregate. Though the overall utilization rate may be 100%, individual resources have surplus and deficit and that must be addressed.

Hence it is important to view utilization at resource level.

Changing Currency

To change the currency from US$ to another currency, please follow the steps below:

Press Ctrl+G and select CURR named range.

Changing Currency - Press Ctrl G and select CURR
Changing Currency – Press Ctrl+G and select CURR

Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.

Press Ctrl+1 and Change Currency
Press Ctrl+1 and Change Currency

Now, the currency on the dashboard display will change accordingly.

Let’s continue with the rest of the dashboard.

Project level Capacity vs Demand
Project level Capacity vs Demand

The above visual presents the capacity vs demand for each of the Projects.

On the left, you will see projects where there is deficit – that is, capacity is less than demand. In this case, we don’t have any.

A project will either be in deficit or surplus – not both.

On the right, you will see projects where there is surplus – capacity is greater than demand. Project A has capacity of 1236 hours while demand is only 1097 hours. The surplus is 139 hours.

Project B has capacity of 1236 hours while demand is only 1190 hours. The surplus is 46 hours.

Skill level Capacity vs Demand
Skill level Capacity vs Demand

The above visual presents the Skills which are over utilized (on the left side) and under-utilized (on the right side).

Development has a total capacity of 1488 hours but demand of 1495 hours. 7 hours overutilized (deficit).

Project Mgmt has a total capacity of 984 hours but demand of 792 hours. 192 hours under-utilized (surplus).

A Skill Group will be in Surplus or Deficit or neither; never in both.

Resource table shows which specific resources are over utilized and under-utilized.

Resource level Capacity vs Demand
Resource level Capacity vs Demand

Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them. Meanwhile, 3 other resources have a lot of additional hours available but not utilized.

In the next page of the dashboard, you can see the charts of Capacity vs Demand in Hours.

The charts show the overall capacity vs demand for each Project, Skill group and Resource.

Capacity Planning Dashboard - Hours - Capacity vs Demand - Charts
Capacity Planning Dashboard – Hours – Capacity vs Demand – Charts

In the final page of the dashboard, you can see the charts of Capacity vs Demand in Cost.

The charts show the overall capacity vs demand for each Project, Skill group and Resource.

Capacity Planning Dashboard - Cost - Capacity vs Demand - Charts
Capacity Planning Dashboard – Cost – Capacity vs Demand – Charts

The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed.

3 resources have unutilized hours that will be costing the team. In this tutorial, I am assuming the under-utilization is not a major concern. In some project scenarios, underutilization can also be an issue. You can follow similar steps as explained below to address that too.

Now, let’s see how the template will help us address this.

Step 7: Addressing Over-utilization and Under-Utilization

In order to address the over-utilization and under-utilization, we must do one or more of the following:

  1. Change Capacity
    • Adjust standard availability of resources in the Resources sheet.
      1. Increase (to increase capacity) or Decrease (to reduce capacity) available hours
    • Add overtime (to increase capacity) or vacation time (to reduce capacity)
  2. Change Demand
    • Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
  3. Change Allocation/Assignment
    • Change assignment of tasks (demand) to a different resource.
    • Change capacity allocation of resource to Projects.

The first option Change Capacity is straight forward and don’t need much explanation. The impact is that it will cost more to increase capacity.

The second option Change Demand is also straight forward to implement in the template (update the demand sheet), though it may be hard to get project stakeholders to reduce the demand (or scope).

We will focus on the 3rd option which may neither increase the cost nor reduce the demand. It is the re-assignment of capacity and demand (tasks) to existing resources to remove over-utilization.

Changing Assignment of Tasks to a Different Resource

Let’s filter the dashboard to Project A by using the slicers on the left of the Dashboard.

The Resources table shows the following.

Assigning Resources in Project A - Capacity Planning
Assigning Resources in Project A – Capacity Planning

Developer 2 is overutilized  by 5 hours while Developer 1 has 37 unutilized hours.

In the Demand sheet, Filter to Resource Developer 2. Find a task for 5 Hours and re-assign to Developer 1.

Reassigning a task to Developer 1
Reassigning a task to Developer 1

Let’s refresh the calculations. Data ribbon  –> Refresh All.

That will update the dashboard calculations.

Project A - Resource - Capacity vs Demand - After assignment
Project A – Resource – Capacity vs Demand – After assignment

Now, there are no overutilized resources in Project A.

Let’s clear the Project filter in Dashboard.

Now, this is what the Resources table shows.

All projects - Resource - Capacity vs Demand
All projects – Resource – Capacity vs Demand

Developer 3 has 39 overutilized hours, but we don’t have a Developer with that many unutilized hours.

Project Planner 2 who also works on Project B has 85 unutilized hours. To demonstrate how this template can handle a resource having multiple skills, we will assume that Project Planner 2 can handle Development tasks.

Now, we need to assign some of the capacity of the Project Planner 2 to Development skill.

Assign Project Planner 2 Development capacity
Assign Project Planner 2 Development capacity

Important things to note here:

  1. A new record must be created in the Capacity Sheet as shown above (last row). Resource Name will be same, but Skill will be Development. Cost can vary and can be entered any value.
  2. When we enter the standard work hours of the new record (we have entered 1 hour a day), we must review the work hours already assigned for the resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours for Project Mgmt and assigned 1 hour to the Development. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
  3. When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
    1. In this case, Project Planner 2 has vacation and overtime entries. We create additional entries for the new skill and update the previously entered entries.
Review Vacation and Overtime entries when splitting resources
Review Vacation and Overtime entries when splitting resources

Now that we have created the capacity allocation for Development skill, we need to assign tasks from Developer 3 to Project Planner 2 in Demand Sheet. Select enough tasks to switch to overcome the overutilization.

Assign tasks from Developer 3 to Project Planner 2
Assign tasks from Developer 3 to Project Planner 2

Let’s refresh the Dashboard again.

All projects - Resource - Capacity vs Demand - Final
All projects – Resource – Capacity vs Demand – Final

We have no resources over utilized. 🙂

Overall metrics look like below.

Capacity Planning Dashboard - Summary Metrics - After Optimization
Capacity Planning Dashboard – Summary Metrics – After Optimization

All the resources will have enough capacity to meet the demand. We have 185 hours surplus capacity and are at 93% utilization.

This was just an illustration of how to use the template to modify capacity and demand. The target utilization rate can vary in each organization.

The Dashboard provides the metrics for the entire planning period duration in aggregate. If, in your organization, it is important to ensure a certain utilization every day, then you can update the planning period to a day or use the Calendar sheet to view capacity/demand at a daily level.

Step 8: View Calendar for granular time periods

Calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard.
The below shows the capacity in hours for each resource every week.

Calendar - Capacity by Resource - Hours - Weekly
Calendar – Capacity by Resource – Hours – Weekly

There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.

Resource Capacity Planner Calendar Settings
Resource Capacity Planner Calendar Settings

 

Dimension
The calendar can be created for the list of resources or list of skill groups or list of projects.

Calendar Settings - Dimension Resource Skill or Project
Calendar Settings – Dimension Resource Skill or Project

 

Measure
You can select to display Capacity data or Demand data or Surplus/Deficit data.

Calendar Settings - Measure Capacity Demand or Surplus Deficit
Calendar Settings – Measure Capacity Demand or Surplus Deficit

 

Unit
The options for Unit are Hours and Cost.

Calendar Settings - Unit Hours or Cost
Calendar Settings – Unit Hours or Cost

 

Time Dimension
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.

Calendar Settings - Time Dimension Daily Weekly Monthly Quarterly or Annual
Calendar Settings – Time Dimension Daily Weekly Monthly Quarterly or Annual

 

The template will display up to 53 periods (53 days, 53 weeks, 24 months, 8 quarters, 2 years).

When you make the selections, the calendar display will update automatically to reflect the selections.

For example, let’s see Surplus/Deficit Hours by week.

Calendar - Surplus Deficit by Resource - Hours - Weekly
Calendar – Surplus Deficit by Resource – Hours – Weekly

We can see that though overall there may be no deficit for a resource based on total hours, some weeks have deficit which are made up by surplus capacity in some other weeks.

If we want to ensure that the team completes the tasks every week, then we need to follow the steps outlined earlier on modifying the capacity or demand.

 


If there are any questions about this template, please see the Product Support page for more information.

How to extend limits (tasks, time periods) in Gantt Chart Maker?

Posted on

Our Gantt Chart Maker Excel Template allows creation of instant customized Gantt charts with a variety of options and features.

Gantt Chart Maker Excel Template
Gantt Chart Maker Excel Template

 

In order to keep the template file very light and fast, we establish some limits on the scope.

For example, the template handles up to 100 tasks, as the calculations are set up to handle only 100 tasks. The actual Gantt Chart display is designed in such a way that it fits in one page when printed. Hence it is limited in the number of time periods (52) and number of tasks (40) that are displayed.

In most common uses, 100 tasks should be sufficient. However, you may have to deal with a project with more than 100 tasks sometimes. Also, we may need to see more than 52 days at a time or more than 40 tasks at a time on the Gantt chart.

As all our templates are designed to be flexible, we can increase these limits with just a few quick steps.

 

EXTENDING THE LIMITS OF GANTT CHART MAKER

In this article, we will learn how we can extend the limits of the Gantt Chart Maker.

To demonstrate this, we will

  • Increase tasks processed from 100 to 110
  • Increase tasks displayed from 40 to 80
  • Increase number of time periods from 52 to 104.

 

DEMO VIDEO

If you prefer video demos, please watch the video below where I walk through the steps. If you prefer screenshots and text, please continue further below.

https://youtu.be/bAo6Aj4-teg

 

OVERVIEW OF STEPS

  • Extend number of tasks supported by template
    • Extend the table in the hidden CALC sheet
  • Extend number of periods shown on Gantt chart
    • Unprotect sheet
    • Unhide all columns.
    • Copy column BL. Select extra 52 columns and Insert copied cells.
  • Extend number of tasks shown on Gantt chart
    • Copy Row 54. Select extra 40 rows and Insert copied cells.
  • Change print settings to print the new expanded Gantt chart

 

STEP BY STEP INSTRUCTIONS

 

INCREASE NUMBER OF TASKS FROM 100

In this demonstration, I have entered about 110 tasks in the DATA_ENTRY sheet.

  1. First, unhide the hidden CALC sheet.

Right Click on the DATA_ENTRY sheet label and choose Unhide.

Right Click on any sheet name and Choose Unhide
Right Click on any sheet name and Choose Unhide

 

Select the CALC sheet and Click OK.

Select the CALC sheet and click OK
Select the CALC sheet and click OK

 

  1. Go to end of the calculation table

In the newly visible CALC sheet, go to cell AQ 102 (this is the end of the calculation table). One way to quickly get there is to press Ctrl+G. This opens the Go To dialog box as shown below.

Go to cell AQ102 which is the end of the calculation table
Go to cell AQ102 which is the end of the calculation table

Type AQ102 in the Reference and click OK. This will move your cursor to AQ102 and make that as the active cell.

 

  1. Extend the formulas down

If you click on any cell outside the table, you can see that there is a small arrow at the bottom left of the cell AQ102.

Symbol for end of Excel Table
Symbol for end of Excel Table

 

Now, click on that arrow and drag the mouse down. This will now expand the table. The table that originally had 100 rows (from row 3 to row 102) will now expand as you keep dragging down.

Extend the formulas down to more rows
Extend the formulas down to more rows

 

In this example, I am extending by 10 rows to row 112 and that will increase the number of tasks from 100 to 110.

You can increase to more tasks if needed. However, please keep in mind that the more we extend, more processing Excel has to do and can become slower gradually. So, extend only to as many tasks as you need.

  1. Hide the CALC sheet, as we don’t need it anymore.

 

Later in this article, we will prove that the template is now processing 110 tasks by displaying task #110 on the Gantt chart.

 

INCREASE NUMBER OF TIME PERIODS FROM 52 to 104

 

1. Go to the GANTT sheet and unprotect it.
Click on Review ribbon. Click on Unprotect sheet. Then, enter indzara as Password and click OK.

Unprotect Gantt Sheet by entering the password
Unprotect Gantt Sheet by entering the password

 

2. Now, we need to make room for the extra 52 columns.

Press Ctrl+A. This will select all cells in the sheet.
Now, in the HOME ribbon, in the Cells section, click on Format. Choose ‘Hide & Unhide’ and then select ‘Unhide Columns’

Unhide all columns
Unhide all columns

 

Now, you will see all the columns.

Right Click on column BL and click on Copy.

Copy Column BL
Copy Column BL

 

Select columns BM to DL and then right click to select ‘Insert Copied cells’

Insert Copied cells
Insert Copied cells

 

This will now insert 52 columns to our Gantt Chart display.

 

INCREASE NUMBER OF TASKS DISPLAYED FROM 40 to 80

1. Right click on row 54 label and choose ‘Copy’

Copy Row 54
Copy Row 54

 

2. Select rows 55 to 94 (40 rows) and right click to choose Insert Copied Cells.

Insert Copied cells in Rows 55 to 94
Insert Copied cells in Rows 55 to 94

 

Now, the Gantt chart display has expanded to 40 new tasks, resulting in total 80 tasks.

If we enter 31 as the task that we want to begin with, then we will see tasks 31 to 110 (80 tasks displayed).

Change Task Number to start from, on Gantt Chart
Change Task Number to start from, on Gantt Chart

 

If you remember, we added support for up to 110 tasks earlier in this tutorial. Now, it is clear that the template is processing 110 tasks and can display 80 at a time on the Gantt Chart.

 

CHANGE PRINT SETTINGS

As we have expanded the width and the height of the Gantt chart, we have to change the print settings if you plan to print the entire Gantt chart. Originally, the Gantt chart printed on 1 page. Now, we will change to print on 4 pages, as we have doubled the height and the width of the Gantt chart.

1. Click on Print Titles in the Page Layout ribbon.

Click on Print Titles
Click on Print Titles

 

2. Select Rows 2 to 14 to repeat at Top. This will allow displaying the dates at the top in each page.

Select K and L columns to repeat at Left. This will allow displaying the Task name in each page.

Set up Page Titles
Set up Page Titles

 

3. Click on Page tab.
Select ‘Adjust to’ and enter 60% of Normal size.

Change Print Scaling
Change Print Scaling

 

Click OK to close the window.

 

4. Now, press Ctrl+P to open the Print menu. The Gantt chart is now set up to print on 4 sheets.

Click on Print to print.

Print Gantt Chart
Print Gantt Chart

 

 

If you have any questions or suggestions, please post them in the Comments section below.

Project Pipeline Tracker – Free Excel Template

Posted on
Active Pipeline Report

PURPOSE

This Excel template allows tracking projects and calculating effectiveness in completing them. This is applicable for scenarios where all the projects go through the same set of pre-defined stages in sequence. In a simple example, each building project may have three stages: Plan – Design – Build. Once planning is completed, designing begins and after designing is completed, build begins. We will be tracking the completion of each stage of each project.

It is very simple to use. It can provide visibility to how long it takes for our projects to complete. It can also help identify where the bottlenecks are in our project cycle. We can also view the trends in terms of time each stages takes.

Project Pipeline - Active Pipeline Summary View
Project Pipeline – Active Pipeline Summary View

 

FREE DOWNLOAD

Project_Pipeline_Tracker_Excel_Template_v1_1

 

REQUIREMENTS: Microsoft Excel for Windows (2010 or above); Microsoft Excel for Mac (2011 or above);

 

VIDEO DEMO

 

HOW TO USE

The template has only 3 visible sheets. In the Data sheet, we enter our input data and the Active Pipeline sheet and Report sheet have output that is automatically calculated. The Data sheet also has a summary of metrics.

 

Step 1: Enter stages

By default the template has 6 stages (including the final closing stage).

Project Pipeline Tracker Excel Template - Enter list of stages
Project Pipeline Tracker Excel Template – Enter list of stages

 

We can rename those stages to meet our business needs. Also, we can remove stages if we don’t need 6. To remove 6th stage, just click on the 6th stage name and press Delete key.

 

Step 2: Enter Projects

Enter each project in a separate row in the Projects table.

List of Projects and their information
List of Projects and their information (Click on image to enlarge)

Enter Project ID and Name. Then, enter Start Date of project. The next 6 columns are dates of completing each stage of project.

These Stage DT fields should not be renamed.

 

Additional fields to track for projects
Additional fields to track for projects

 

The two columns (Type, Owner) are optional and can be used to store other information as you need. These can be renamed.

The last four columns in the table are calculated and should not be edited.

Project Pipeline Tracker - Calculated Fields
Project Pipeline Tracker – Calculated Fields

 

  • CURRENT STAGE Based on the dates entered for each project, the current stage will be displayed. If the project has completed the final stage, then it will display COMPLETED.
  • COMPLETION DATE If the project has completed the final stage, then that date will be displayed.
  • DURATION (DAYS) If the project is completed, the duration from Start Date to Completion Date. If the project is not completed, this is the duration from Start Date to Today.
  • VALIDATION The template checks for a couple of errors. If the dates entered for each stage are not sequential or if there is a date missing in between stages, ERROR will be displayed. Error projects are not included in metric calculations.

 

COMPLETED PROJECTS – SUMMARY

We can see an instant summary of the completed projects as we enter data.  Number of completed projects and how long on average a project takes.

Completed Projects - Summary View
Completed Projects – Summary View

 

We also see how long each stage takes to complete.

 

ACTIVE PIPELINE – SUMMARY

The number of projects that are currently being worked on and how many projects are currently in each stage are shown.

Project Pipeline - Active Pipeline
Project Pipeline – Active Pipeline

 

We can also see the detailed Active pipeline in the Active Pipeline sheet.

Here we can see the (up to 5) projects in each stage as well.

Active Pipeline Report
Active Pipeline Report

 

The ID, Type and Owner fields are displayed. Even if you rename the fields and use them to store other type of information, they will be displayed in this Active Pipeline sheet.  Thus, it can be modified to your business needs easily.

 

REPORT

The Report sheet presents the metrics (Projects Completed, Average Project Duration and Average Duration for each stage) for the past 12 months.

Pipeline Tracker - Monthly Report - Table
Pipeline Tracker – Monthly Report – Table

 

The information is also presented visually so that we can see how the duration vary over month for each stage.

Pipeline Tracker - Monthly Report - Chart
Pipeline Tracker – Monthly Report – Chart

 

EDITING

  • The Active Pipeline and Report sheets are locked with password (indzara) to prevent accidental editing of formulas. You can unprotect them and make edits if needed. Unprotecting sheets
  • Data sheet is not locked. You can edit the shapes and charts as needed as well.

 

This is the first version of this template. There are many different directions that we can take this template further. As always, I look forward to feedback and suggestions to make this more useful to a lot of people. I will do my best to implement additional features and enhance the template.

 

RELATED TEMPLATES

For more project management templates, please visit Project Management Excel Templates

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

Automatic Scheduling in Project Management

Posted on

Our previous article on Project Management Framework had set the background for how a ‘Schedule’ forms the foundation for all calculations in Project Management. One of the huge highlights in our Project Management templates (Example: Project Manager Excel Template) is the ‘Automatic Scheduling Engine’ which, as the name suggests, automates the schedule creation step and saves enormous amount of time for the Project Manager. In this article, we will understand what ‘Automatic Scheduling’ is and why we need it.

 

VIDEO DEMO

 

WHAT IS A SCHEDULE?

A schedule lays out how we will complete the work needed for the project using the resources available. It should clearly state who will be working on what task for how long on a given day. Let’s take this example 7-day schedule.

Example 7 Day Project Schedule
Example 7 Day Project Schedule

 

In the example above, we can see that RES 1 assigned to TASK 1, will work for 9 hours on that task on Mar 1st and 1 hour on Mar 3rd. RES 2 assigned to TASK 2, will work on that task for 5 hours on Mar 7th. No work will be done on TASK 3 in the entire week.

So, how do we build such a schedule? Let’s start with what we have as Inputs.

 

INPUTS

Inputs to Schedule
Inputs to Schedule

 

On one side, you know the work (also referred to as Tasks) that needs to be done. On the other side, you have the resources that are capable of performing that work. The Project Manager assigns each task to one of the resources available based on skill.  That’s it from a high level. It sounds easy but it can get complicated very quickly when we get into the finer details.

To illustrate this, let’s take this simple example where we have 3 tasks and 2 resources for our Project that begins on Mar 1st.

Input - Tasks
Input – Tasks

 

In the above image, we have the three tasks and their information.

1. HOURS OF WORK Hours of work required for each task is specified. They can vary by task. TASK 1 takes only 10 hours of work while TASK 2 takes 25 hours.

2. START DATE CONSTRAINT A task can have an external start date constraint. TASK 3 cannot start until Mar 15th. To perform the task, we need an equipment that will not arrive until Mar 15th.

3. PREDECESSORS Tasks can have dependencies between them. TASK 2 cannot begin until TASK 1 is complete. TASK 1’s output could be the input to start TASK 2.

4. RESOURCE ASSIGNMENT Each task is assigned to a resource. Our templates support only one resource assignment to a task. More than one resource cannot be assigned to a task.

There are two more slightly advanced factors. These are handled in our templates, but since this is a simplified example, we will demo these two in separate articles. For the sake of completeness, we will just review the concepts in brief here.

5. ALLOCATION % On occasions, we would like a resource to work in parallel on multiple tasks. We would like to set max % of time that a resource should allocate to one task. For example, resource is assigned to 2 tasks and both can be done at the same time. Then, we can set 60% of time to spend on first task and remaining 40% to second task.

6. PRIORITIZATION In a more realistic project scenario where we will have numerous tasks, a resource can be assigned to several tasks. We may need to control the order in which the resource handles those tasks. For example, work on TASK 25, then work on TASK 38 and then work on TASK 34. The template allows us to prioritize tasks. By default, the order in which the tasks are entered is assumed to be the priority order.

 

Now, let’s look at the Resource side.

1. STANDARD WORK HOURS Each resource may have different work hours. In this case, RES 1 works for 8 hours Mon-Fri, while RES 2 works for 5 hours Mon-Fri.

Input - Resource Standard Work Hours
Input – Resource Standard Work Hours

 

2. VACATION/OVERTIME RES 1 is available to do Overtime on March 1st for an hour, while RES 2 is going to take vacation of 5 hours on Mar 4th

Input - Resource Vacation/Overtime
Input – Resource Vacation/Overtime

 

3. COMPANY HOLIDAYS March 2nd is a company holiday and no work will be performed on that day.

Input - Company Holidays
Input – Company Holidays

 

Now, we have to build a schedule for all the three tasks, given all the above inputs and constraints.

Inputs to Schedule
Inputs to Schedule

 

Even for such a small project, it doesn’t sound so simple anymore, does it?

Automatic Scheduling Engine to help
Automatic Scheduling Engine to help

 

Not to worry. This is why we have our Automatic Scheduling Engine.

 

AUTOMATIC SCHEDULING ENGINE

An Automatic Scheduling Engine is embedded in our Project Manager and Project Planner templates. The name may sound cool, but here is the secret: it’s nothing more than a set of interconnected complex formulas. This engine takes into account all the inputs we saw above, and prepares the schedule for us, saving tons of time and ensures the rules are followed correctly.

Project Management - Automatic Scheduling Engine
Project Management – Automatic Scheduling Engine

 

To illustrate it, let’s look at the 7-day schedule again.

Example 7 Day Project Schedule
Example 7 Day Project Schedule

 

TASK 1 which needs 10 hours of work starts on Mar 1st. 9 hours of work were performed. This is taking into account the Standard Work Hours of 8 hours by RES 1 and 1 hour of Overtime. No work is scheduled for Mar 2nd, as that’s our Company Holiday (highlighted in Purple). Then, the work continues on March 3rd for 1 hour and that completes the 10 hours (Hours Required) .

TASK 2 can start only after TASK 1 is complete (Predecessors). So technically it could have started on Mar 4th (as TASK 1 completed on Mar 3rd), but RES 2 is taking that day off (Vacation). RES 2 also does not work on Saturday and Sunday according to the Standard Work Hours. So, the work only begins on Mar 7th with 5 hours.

TASKS 3 doesn’t start until Mar 15th, as we had a Start Date Constraint.

As you can see from the above tasks, the schedule is taking into account all the inputs and constraints. You can also see an overview of the schedule in the Gantt Chart below.

Gantt Chart
Gantt Chart

 

Using this automatic schedule, we can then automatically calculate project dates, cost and resource utilization (a measure of how efficiently they are utilized).

Project Plan - Summary Report
Project Plan – Summary Report

 

It may take a few iterations to land on a schedule/plan that is acceptable. As a Project Manager, one can play with the following levers.

  • List of Resources who will work for the project
  • Availability of those resources (standard hours, overtime, vacation)
  • Assignment of resource to task
  • Scope of Task(s)

When we make changes to one or more of the above inputs, our Automatic Scheduling Engine will then update the Schedule and the Plan summary. If we are happy with the result, we can then finalize the plan and lock it. Without an automated method of scheduling, the process can take a really long time and can be filled with errors.

 

SUMMARY

In this article, we covered what a Schedule is, what the inputs to a schedule are, why automatic scheduling is needed and what levers we have to tweak the schedule.

Project Management Framework

Posted on

In this article, we will discuss the Project Management Framework that we apply in the Project Management Excel Templates available from indzara.com. Projects can be of different types and Project management techniques also vary with the type of projects. The simplified framework we will see now applies to most common project scenarios.

 

PROJECT MANAGEMENT FRAMEWORK VIDEO

WHAT IS A PROJECT?

Let’s begin by defining a Project. The characteristics of a Project are below.

  • A project has a specific end goal.
  • A collection of tasks has to be completed in order to achieve the project goal.
  • Each task may take different amount of time/work, cost and skill
  • A collection of resources is available to perform work on these tasks
Characteristics of a Project
Characteristics of a Project

 

Examples of a Project: Building a home, building a software product, fixing a broken machine. It can be big or small, long or short, expensive or inexpensive, easy or hard.

Project Management is the act of achieving the project goal by using the resources available accounting for all constraints and handling any unexpected events along the way.

Project Manager (PM) is the leader of a project.

 

2 STAGES OF PROJECT MANAGEMENT

We can see Project Management or the role of a Project Manager in two stages: 1) Planning and 2) Management.

2 Stages of Project Management
2 Stages of Project Management

 

The first stage is to plan the project ahead of time and have a clear idea of the project before the project work begins. 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 actually start building.

The second stage starts when the project work actually begins. This is where the PM manages the project to completion effectively and efficiently, meeting the project goal.

 

PLANNING STAGE

During the Planning Stage, the objective of the Project Manager is to have answers to these questions.

  1. Can the project be completed by our preferred date? When will it complete?
  2. How much will it cost?
  3. How efficiently will we use our resources?
  4. Who will work for how many hours on which task on a given day?

Answering the last question will help us answer all the others. The answer to the last question is the ‘Schedule’.

 

WHAT IS A SCHEDULE?

A schedule lays out how we will complete the work needed using the resources available.

Factors in Building a Schedule
Factors in Building a Schedule

 

On one side, you know the work (referred as Tasks in the image) that needs to be done. We factor in the hours of work needed, any external constraints on starting a task, and dependencies between tasks. For example, you may not be able to start a task before another task is complete.

On the other side, you have the resources that are capable of performing that work. They have their standard working hours per weekday. They may have vacation and overtime. The company may have holidays during which work will not be performed.

On top of these, the PM assigns each task to one of the resources available based on skill. Given all this input, a schedule is built.

Project Schedule
Project Schedule

 

Using the schedule, we can then calculate all relevant project metrics such as Project Finish Date, Cost and Resource Utilization (a measure of how efficiently they are utilized). It may take a few iterations to land on a schedule that is acceptable. For example, in the first iteration, the project timeline might be too long. In the next iteration, the cost might be too high. The levers a Project Manager has are

  • List of Resources
  • Availability of those resources (standard work hours, overtime, vacation)
  • Assignment of resource to task
  • Scope of Task(s)

Once Project Manager lands on an acceptable schedule, we have answers to our original questions. The results can be shared in a Project Report with the Project sponsors to get approval. Once approved, what we have is a project plan. We will call this as Baseline Plan.

Project Management - Baseline Plan
Project Management – Baseline Plan

 

This will include key dates (milestones), the detailed schedule, cost estimates and resource utilization rates.

Now, we wait for the Project Start Date to begin actual work, and our planning stage ends.

 

MANAGEMENT STAGE

From the day actual work begins, we enter the Management stage. The first and most important activity here is to track the actual work performed. Only then we will know if our plan is being executed. The frequency of tracking may depend on the scale of project. It could be daily, for example.

This tracking allows us to monitor the progress of the project towards our goal and also monitor if we are sticking to our baseline plan. The Project Manager monitors the progress using reports and shares with project team and sponsors regularly. The reports will clearly represent what % of the work has been complete and how much more needs to be done. Clear and timely communication is an important characteristic of a Project Manager.

If actual work was performed exactly as planned, the Management stage becomes very simple. The reality is that many things may not go according to the baseline plan.

Project Management Framework - Changes
Project Management Framework – Changes

 

Here are some reasons:

  • Our actual work performed is less (or more) than Planned
  • Planned Work Estimate was incorrect
  • Scope of work has changed
  • Resource Cost is different than planned
  • Resource availability has changed

These factors may make our Baseline Plan not valid anymore. The Project Manager has to build a new plan to handle all the new challenges and factors. Let’s call this plan the Current Plan.

 

CURRENT PLAN

The Current Plan takes into account what work has actually been done so far, and plans the rest of the project, given the changed inputs such as resource availability and work estimates.

Project Management - Current Plan
Project Management – Current Plan

 

The Project Manager can increase resource availability and/or reduce scope of Tasks. Even using all the levers available, it may happen that the estimated project finish is later than in our Baseline Plan. In other words, the project will be delayed. It is to be noted that the Project Manager received approval earlier for the Baseline Plan and if the Current Plan deviates in cost or timeline significantly from the baseline plan, then a new approval may be necessary.

 

Here’s a quick overview of the difference between the two plans.

Baseline Plan Vs Current Plan
Baseline Plan Vs Current Plan

 

Using the new Current Plan, the project moves forward. It is a continuous process. This Current Plan will be updated regularly based on actual work performed and any changes that may happen with the inputs. Our reports will tell us how the current plan is progressing and is compared with the baseline to identify variances. Project Manager will continue to share these reports with the sponsors and the team as necessary.

It is possible that the schedule has to be updated or rebuilt a few times during the course of the project. Finally, when all the work is completed, the Project Manager finalizes the Project Report and shares.

This wraps up our introduction to a simple Project Management Framework. In future articles we will go further in depth into specific components of this framework.

Project Management Toolkit – Excel Templates – Support

Posted on

This is the support page for the Project Management Toolkit – Excel Templates.

Project Management Toolkit – Excel Templates

This product is a bundle of 6 individual Excel Templates. The following are links to those individual templates and their support pages. Please visit them for help material around each of them. If you have questions about the bundle, please leave them in the comments below.

 

SUPPORT LINKS

PROJECT MANAGERProduct PageSupport Page

PROJECT PLANNER (ADVANCED)Product PageSupport Page

TASK MANAGER (ADVANCED)Product PageSupport Page

EVENT CALENDAR MAKERProduct PageSupport Page

GANTT CHART MAKERProduct PageSupport Page

RESOURCE CAPACITY PLANNERProduct PageSupport Page

 

VIDEO INTRO

 

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