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

Check the template tour video here:

SAMPLE PROJECT - DEMO

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

PLANNING

SETTINGS SHEET

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

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

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

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[/caption]

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

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

TASK SHEET

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

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

Most often tasks are dependent on the 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.

The other optional settings available are the ability to assign a task priority, this is a numeric field, say if you want the development of the project to take higher priority to that of the testing phase, then assign the "Development" task a higher priority. Also, you can mark if a particular task is a milestone project or not. 

Now that we have entered the necessary input, let’s jump to the PROJECT_REPORT sheet to see how our project plan looks.

Just enter the STATUS DATE as the start of the project for this.

Project Manager Excel Template - Project Report - Baseline Plan Summary

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

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

If we enter January 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 January  5th and, at the end of the day, we enter the work performed on that day. We do the same on January  2nd as well. The data is entered in the ACTUALS sheet as shown below.

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 May 1st as the 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

We can see that we have increased hours of work to do (and thus the cost) in the current plan Vs baseline plan. Also we can note that the project is projected to finish behind schedule, thus we need to increase utilization rates of resources.

We can see the progress visually on the 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

Resource Report will present the resource side of performance measures.

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.