Project Tracking with Free Master Excel Project Manager

One of the key motivations for me to put this template together was the fact that we tend to document a lot of things about the project in various spreadsheets. For example, we have separate spreadsheets for the schedule, budget, resources, risks, issues, and so forth. 

Excel Project Management
Excel Project Management Template

Why we need the Excel Project Management Template (Motivation)

  • It tends to get a bit tiring when you have information spread across so many spreadsheets, and you want the latest status report or you just want to know how your project is doing. 
  • It's very difficult to go to all the different spreadsheets and get the information
  • And that is where I have designed this template with different tabs so there's one tab for schedule, one tab for budget, resources, risks, actions, change requests, and most of the things a project manager would like to track. 
  • And with that it also has a dashboard which gives an overall view of the project. 
  • It's easy for a project manager to find out what the problem areas are, and what should be focused on.

Features of the Excel Project Management Template

  • The project manager template covers all the key aspects of project management.
  • The template has schedule planning, budget tracker, resource management, risks, issues, actions, decisions and a dashboard.
  • The schedule planner has Gantt Chart and Traffic Lights for task tracking. 
  • Budget tracker is simple and covers 3 different types of project cost categories.
  • Resources tab in the sheet can be used to track resource allocations and related costs.
  • Risks, Issues, Actions, Decisions and Change Requests are lists.
  • The excel template also has dashboard which gives a high level view of the all the project KPI.

About the Main Dashboard

  • This is the type of dashboard that I personally would like to see when I come to the office. 
  • In the morning just open this up and say this is what I want to work on, or my risks are too much or my issues are too much, and so forth. 
  • So the first is the Schedule Health graph which tells you how many tasks are on track, delayed, or other tasks you should be worried about. 
  • Then there's the Task Status graph, which tells you the progress of your tasks. Budget Health tells you how much was planned, how much was spent, and what is remaining from your project budget. 
  • There's Resource Health which gives you a very high level view of how you are utilizing your resources. Open risks and open issues, again, are a list of risks and issues for critical, high, medium, and low. 
  • Action items, change requests, and pending decisions, these numbers give you an idea of how many items are open.

Project Schedule Tracking


Excel Gantt Chart
Project Schedule
  • And moving on to the schedule tab, it has a traffic light indicator, task names, and task numbers. 
  • It's pretty self-explanatory, the resource comes from the resourcing sheet. There's a predecessor column if you want to add a predecessor. 
  • There's a start that you need to configure and there's a finish which is auto-calculated. It is based on start date and effort that you put in. 
  • There's an effort column. There's an actual column which is the actual progress so far. So this is the data that you should get from your business analyst and anybody working on the project to note the latest progress. 
  • Then there is the days planned which again is auto-calculated. So the blue fields are something that isn't auto-calculated. 
  • The status is green, amber or yellow, and red. So green is when everything is good, yellow, or amber as we call it, is when it's five percent. This is the task that you should be worried about it slipping, and red is when it's already gone wrong. 

  • For example, you can look here. These are one hundred percent, most of them are one hundred percent, should be one hundred percent by now but they are not and that is why most of them are flagged as red. 
  • Some are based in July so they should be fine, and the 15th of June should be okay, so that's how the table works. So you can start your view at the latest time by changing this column. So you say the 1st of June it changes the timeline. 
  • The blue indicator is for today, then the timeline days you can change it if you want to see the timeline for two days, you can change it for three days, or you can change it back to one. 
  • I would suggest leaving it for one because that's how the template has been designed, and it works best with a timeline view of one day. 
  • Another feature is that you can see some of these dates are highlighted in red, so this is to indicate that these dates fall on a weekend and you should probably look at your tasks to see if you can move the dates around.

Project Budget Tracking

Project Budget Tracking
Project Budget Tracking
  • It's a simple spreadsheet, you have different categories here. Resource costs, software, hardware, and other costs.
  •  If you want to add a cost you just type it in and the gray becomes white, and then you enter a number here and you enter a number here. 
  • So that's pretty easy to use, this is a very basic but important part of the cost budget.

Project Resource Management

Resource Manager
Resource Manager
  • Resource sheet allows tracking of resource allocation, and what we're looking at here is a weekly timeline, resource name, the type of resource, what the daily rate is, and how much allocation you have. 
  • And this field task comes from the schedule that you have. And then of course the cost is calculated based on the number of days and the rate. 
  • There are two things to Project Resource Management. One is you do resource allocation and then you do your schedule. 
  • So you do your allocation and you say, "I want this person from this date to this date," and then you have to make sure as a project manager that you are allocating enough tasks to them.
  • For example, if we look at John you've had him for ninety days but you allocated the task for only fifty days. If we just go back and filter John, it's pretty clear that it's only fifty-six days. 
  • So this is where you should probably go and look at his allocation and this is what the resource feed is meant to do. 
  • It's meant to give you an idea of where you're not using your resources adequately and this is where the whole resource health is  also drawn from, so that gives you an idea.

Project Risks, Issues, Decisions and Change Requests


Project Risks, Issues, Decisions and Change Requests
Project Issues
  • Risks, issues, actions, change requests, and decisions are pretty much simple spreadsheets, so you just keep on adding them. 
  • Adding items there, and the only thing I suggest you do is once you close an item you probably just move it down and keep it there for some time and then you can move it out of your list so you know what you have closed recently. 

Project Config and Data

Project Config and Data
Project Cofig and Data
  • The configure section is where you configure your holidays. 
  • You also have severity, priority status, risk rating, and approval. I would highly recommend not to change this because there's a lot of numbers that have been written on this and it might end up messing up the dashboard a bit. 
  • But if you have to absolutely do it, you should, and then I would suggest look at changing the others. You can change the resource type or leave it as it is, but just make sure you change it across the board. 
  • The data, there is nothing to be changed here, this is just some background calculations that I need for the dashboard. So that's it guys, thanks for listening, and let me know if you have questions.

Click Here To Download The Excel Project Management Template

Swapnil Wale

Written by

Swapnil Wale is an IT Professional based in Sydney, Australia with over 10 years of experience in technology and project management. He is a passionate blogger and focuses on project management and BRMS articles.

4 comments:

  1. In the schedule page if a task gets over on Friday then the sheet takes saturday as the next task start date which is incorrect instead it should take the next working day i.e Monday. We cant even move the date manually because of the formula. PLease look into it and let me know the solutions

    ReplyDelete
    Replies
    1. This should not be happening as I have used workday function in excel to calculate the finish date. Can you send your plan on my email? Also, one easy of moving the date ahead is by increasing the effort so the end date is a day or two later.

      Delete
  2. Hello - when the downloaded workbook only appears to show the task sheet with the Gannt chart. How do I access the other tabs?
    Thanks

    ReplyDelete
  3. apologies - worked it out - no action needed. Thanks anyway

    ReplyDelete

 

© 2013 Techno-PM. All rights resevered. Designed by Templateism

Back To Top
Real Time Web Analytics