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

46 thoughts on “Project Planner (Advanced) – Excel Template – v1 – Support

  1. Just purchased and am playing with scenarios we often face when planning capacity. One is a resource is assigned to multiple projects and may work a percentage of their day on a project, then another percentage on another. For example, my DBA would work 45% of his day on Project A and 20% on Project B. The template doesn’t seem to accommodate this. Can you offer some workaround ideas?

    1. Thanks for purchasing. I am currently working on the next version of the template where this feature will be available. I expect to publish the next version before Monday. All existing customers will receive the upgrade for free. Please let me know if there are any questions. Thanks.

    2. I hope you received the file I sent by e-mail. The v2 of the template is now officially published. Current customers can login to their accounts and download the new file for free. I am also working on an e-mail that I will send to all current customers with relevant information and links. I expect to send that out later this week. Please let me know if there are any questions. Thank you & Best wishes.
      Project Planner (Advanced) Excel Template: https://indzara.com/product/project-planner-advanced-excel-template/
      v2 Support Page: https://indzara.com/2015/08/project-planner-advanced-excel-template-v2-support/

  2. Hi IndZara,

    I’m really enthusiastic about your project planner template. I have one questions, since working in projects is all about team work is it possible to assign more than one resource to the same task? This way it would give me more inside in who is working on what exactly and it would also give more precise resource views.

    Thank you in advance for your response.
    Best,
    Mirjam

    1. Thanks for the feedback. The template currently does not support assigning one task to multiple resources. It is one of the features that have been requested. I will do my best to incorporate that in the next version.
      The template still provides visibility to who is working on what and the utilization of each resource and several other aspects.
      Thanks & Best wishes.

  3. Can you change the hours to days? All of my projects are man days.

    Many thanks,

    1. Thanks for purchasing. The template was designed for tracking at hourly level.
      Have you tried entering the number of days as input in places wherever number of hours is required? Also, please enter 1 or 0 to indicate whether a resource is available on specific weekdays or not.
      Please let me know if you see any issues with this approach.

      Thanks & Best wishes,

  4. I found two bugs or issues with the copy of my Project Planner (Advanced).

    1) In the Setting Tab, the cells under Resources ID and Names as well as Resource- Personal Leave, do not automatically alternate clor highlighting the same way as other cells in other tabs.
    2) In the project time view if the Plan Summary tab, the month of May can not be displaced. It shows “###” instead of “May”

    Please advise how to fix this problems.

    Thanks,
    K

    1. Thanks for purchasing.

      1) I understand what you are referring to. This is a formatting gap. To fix this, once you selected any cell inside the table, press Ctrl+A. This would select the entire table. Now, from the ‘Editing’ menu section (far right in the Home ribbon), choose Clear–> Clear Formats. This would update the format.
      2) You are correct. Please select the column(s) and right click and choose Column Width. Please increase the column width and then the entire text ‘May’ will appear.

      Both do not impact the functionality of the template.

      I will also e-mail you this information. If you would like help with this, please feel free to send me the file and I can make the changes. Thanks again for purchasing. Best wishes.

  5. Thank you. I never received the password when I purchased the planner.

    1. There should have been an automated e-mail with the subject line ‘Getting Started with Project Planner’, which has password information. I am not sure why that was not sent. I apologize. I have e-mailed the password to you now. Please let me know if there are questions. Thanks for purchasing.

      1. Great. Thank you for your prompt response.

  6. Hello,

    Is it possible to get the password to allow editing?

    Thank you.

    1. Yes, password will be sent upon purchase. Please let me know if there are further questions.

  7. I work in cinema and I used your “Project Planner” to help me. I am working on several films at the same time and I would like to know if it is possible to associate a color project.
    The “Projects” = “Films titles” and “Tasks” = “Scenes”. I would like every film appear a different color in the calendar of “Task Schedules”. Would it be possible when I double click on a green square in the calendar of “Task Schedules” that it open up a new page or a link with other information on the selected task?

    1. Thanks for purchasing. I am glad that it is useful.

      Conditional formatting can be used to create a different color for each project. This video can help http://www.youtube.com/watch?v=S3JgPrYWgqo Please let me know if there are questions.

      Double clicking on the green square to open a new page is not possible. I am sorry about that.

  8. The scenario you described is correct due to the functions of your template, but not due to my needs:

    1.) You are right. But as I’m using the template to plan 20 projects in the time period of 1 year, the scenario that one of the projects can’t be completed in 365 days is rare. On the other hand quite often it can’t be completed by the preferred end date, although it is necessary, that a project is finished by e.g. end of April. So in my case, it its important that it is highlighted, when not completed within the preferred end date.

    2.) You are right. My concern is more the “illustration” as you do it on the right side of the task schedule. Those kind of Gantt-Charts are good for a quick overview, where problems between “Plan” and “Reality” can occur. Specially, if you have 250 tasks to handle. Therefor I would expect, that the highlighting would happen here, instead on the left side in the list.

    Anyhow – don’t mind. As I said, I already found a solution for this with another template, so I’m fine.

    But meanwhile, I sat up a new project plan with sample data, shorter time period (1 month) less projects and less tasks in order to try more of the functions of your template. This time my focus was on resource planning and there is one thing, where I would like to know if I did something wrong or if I’m misunderstanding the function:

    I have 7 resources working together on the same projects with different tasks. Due to holidays, one of the resources has only 144 hours available within the planning period. To understand what will be shown, when his tasks will take more than 144 hours, I allocated 164 hours to him.

    In my opinion, following things should be shown with this resource in the template:
    a.) 100 % utilization on the Resource Summary/Utilization Rate Chart (plan summary)
    – but it only shows 89% with 128 hours

    b.) Some highlighting/warning either on the right side of the task schedules or on the resource view, to show the conflict between “plan” and “reality”. Ideally on the concrete dates where (and how many) hours are over the “available” time.
    – but it only shows the hours allocated until the maximum hours of each day

    And also on the Resource view within the table of “Hours allocated” not all hours are shown for each day for each resource (e.g. only one hour for resource A one one day, although 8 hours together on several days have been allocated – which is correctly summed up at the end of the table)

    And finally one question/suggestion: if you are really interested in my feedback (specially as they are tending to be quite detailed/long), I would suggest, that you provide an emailaddress, so I don’t overload the blog with my comments 😉
    Best wishes for New Year!

    1. I love this type of feedback. My e-mail is indzara at gmail. Please send me the file with the data that reflects the scenario you are referring to. Thanks.

  9. I already mentioned that I miss the set-up of milestones as fixed and regulating factors in the project plan. Now, after I put some details into the project plan, it became clear, that the template won’t fit my needs. Because I need something, were you can fix the end date of a project and this end date should stay the same in the schedule, even after all tasks are assigned to it. In your template, the schedule escalates the end date to the date, where it is expected to end, due to the summed up hours. I understand, that you provide the information, that a project can’t be completed, but in your scenario it still can be completed, even if it doesn’t hit the preferred end date. And in my opinion the schedule should help with a quick overview in question of “where will be a problem in hitting the preferred end date”. But it only shows the expected end date and therefor is in a way contra productive, because you could get the impression, the project is in time, although it isn’t.
    I found another template on the internet, which meets this needs. Therefor other things like resource and cost calculation are missing. They also play a role in planning but for me to begin with just a second or third role.
    As you said that you are working on updates all the time, I’m will have a look every now and then, how the template is proceeding. For now I can’t use it.
    Best wishes

    1. Thanks for your feedback. Please let me know if I am not understanding the scenario correctly.

      1) If a project cannot be completed by the ‘Planning Period End Date’ the template will show as ‘Not Completed’. The Task Schedules sheet will provide the list of tasks and highlight in red those that will not complete. This allows us to re-assign the tasks to other resources in order to drive project to completion by Planning Period End Date.

      2) If a project can be completed by the ‘Planning Period End Date’ but cannot be completed by the Project’s ‘Preferred End Date’, then the template will show as Completed but ‘Not on Time’.
      Currently, I flag projects that are not on time. I think one enhancement I can make is to highlight tasks that are not on time. That should clearly show why project is not on time. Please let me know your thoughts.

      Thanks again for valuable input.

      Best wishes.

  10. I have just started populating my template but am already anticipating that 250 tasks will not be enough. Is there anyway to get more added? Also, I am not able to see the highlighting on the task schedule tab for the personal leave days I entered.Thoughts on why that would be?

    1. Thanks for purchasing. Excel becomes very slow when more tasks are added. I have tried to balance number of tasks along with other features (resource view, holidays, vacation, etc.) so that the template can be comprehensive. In the next version, I will try to push the limits again and come up with alternative design that can accommodate more tasks.

      Regarding the personal leave days – as long as they are entered correctly ‘inside the table’, it should get highlighted. Please e-mail me file and I can look into it.

      Thanks & Best wishes.

Leave a Reply

Your email address will not be published. Required fields are marked *