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. Thanks for the quick reply and that you consider to include my suggestions into the next version.

    Just to let you now a little “bug” (with no effect on function – just “cosmetical” ;-):
    I started to set up projects and tasks and wanted to check how the selection between “All Tasks/Projects/Resource” works on the Task Schedule works.
    Therefor I switched from “All Tasks” to “Project” and then selected one of my projects in the second pulldown.
    When I switched back to “All Tasks” it showed all tasks again, but the second pulldown stayed on the selected project and can’t be “cleared”.

    1. You are welcome.

      Thanks for the feedback on the drop down menu. I will address it in the next version. Yes, you are correct. The template functions correctly.

      Best wishes,

  2. Hi,
    although I just downloaded the template and still don’t know, if it meets my requirements, I think its really great.
    At the moment I have 2 questions/suggestions:
    1. Is it possible to get a selection into the task schedule, where you can change the view of the right chart in concern of “timelinedetails”? Meaning, that it will show either all days or only weeks per month or only months? Because when you plan for a year, the daily view will be very long and sometimes, you just want an small overview of the year.
    2. In projectplans there are often milestones defined, which I kind of miss in the template, in the way of defining and visualizing them. It would help in the timeline-views and also in controlling the input. For example if a milestone is in definied on 8/20/2015 every task which is assigned to this task will show an alert message, if the expected end date of the tasks will be later then this. Will writing this, I realize it will be kind of tricky to decide, whether the milestone is assigned to a project or a task. Because in my situation, my projects would have several milestones during the year.
    Sorry for the long comment and again thanks for providing this professional template.

    1. Thanks for the feedback.
      The template doesn’t address both the scenarios. But I will definitely consider these for the next version.
      Thank you,

      1. Hello ind zara

        The template works fine for me, but the use of milestones should be very handy. Did you already consider it for a next version?
        Greetz

        1. Thanks for purchasing. I am glad to know that the template is useful. Yes, ‘Milestones’ is in the list of features considered for the next version of this template. Unfortunately, I don’t know the exact date when it will be published.

          In the new Project Manager Excel template, I have incorporated Milestones as well. https://indzara.com/product/project-manager-excel-template/

          Thanks for your support,

  3. How can i add more than 25 resources?

    1. The template was designed for up to 25 resources. Adding more than 25 would require updating formulas in more than 1 sheet. In the next version of the template, attempt will be made to increase the number of resources. Thank you.

  4. Hi,
    just wondering if the template can manage the following scenario:
    1 resource can work 8 hours per day but for the next two weeks he will work 4 hours in one tasks and the remaining 4 hours on another task.

    From what I understood reading the features “Resource can be assigned to multiple tasks on the same day” but I am not sure if my scenario can be managed after trying the template.

    Thanks in advance for the clarification

    1. If I understand your scenario correctly, it cannot be done in the template. The template is based on the following concept. It will schedule tasks such that a resource always works on the highest priority task that can be worked. On any given day, if there are two tasks, the resource will try to complete the higher priority task first before going to the lower priority task.
      You can control which task is higher priority easily. You can also control which task can be worked. You can provide the ‘preferred start date’ for each task and the template will only schedule that task to begin on or after the ‘preferred start date’.

      Please let me know if this doesn’t answer your question. Thanks.

  5. Can the Resource – Personal Leave have a half day leave?

    1. Thanks for the question.
      Availability of resources for each weekday are in hours. Task duration is also in hours. However, the personal leave and company holidays have to be full days. I would consider implementing more flexibility in next version. Thanks for the feedback.

  6. You are welcome.

    I will try to expand the scope and features of the template as much as possible, while keeping it simple and light. Currently, having 250 tasks and 24 months, seems too much for Excel to handle. It becomes too slow to respond. I will have to come up with a different design which can handle larger volumes of data.

    Thanks for the feedback.

  7. Thank U for the task, regarding project period, normally contracts are 12 to 24 months, can U extent the period up to 18 – 24 months?

  8. Thanks! Just a quick one: I understood that if I buy this version I will get also the new future release but my question is: let’s assume that I start using the current version, when a new version is available does it mean that I have to insert all the information again or there is a way to export and import projects?

    Thanks again

    1. When building future upgrades, I try to make sure that the fields used are as similar as possible so that it’s easy to migrate. I don’t have any plans for automatic export/import right now, but I can promise that it will take less than 5 to 10 minutes to migrate to the future version. It will involve copying data from current version and pasting in new version. I will also provide a video tutorial on how to migrate data. Hope that helps. Please let me know if there are any questions.

    2. Thanks for the clarification, sounds great!

    3. You are welcome.

  9. Woud it be possiible to increase the duration up to 9 months? I am working in a bank and 120 days for my projects is not enough. Thanks

    1. Thanks for the feedback. I am looking into this.
      How many tasks would be sufficient, from your perspective? Currently, 250 are allowed. Would 150 be sufficient? Please let me know.

    2. Hi, i think 150 could be enough but what really count is the overall duration of a project that should not be less than 9 month .

      Thanks for following up!

      Mauro

    3. Thank you. I have updated the product. Now, it can handle up to 366 days. Number of tasks remains the same at 250. The file size has now increased to 5.4 MB. Please try and let me know if this works. If the product does not meet your needs, we will issue a full refund without any questions.

  10. 120 days is too short. Is it possible to change to 6 months, 9 months or 12 months duration?

    1. Thanks for the feedback. I am looking into this.
      How many tasks would be sufficient, from your perspective? Currently, 250 are allowed. Would 150 be sufficient? Please let me know.

Leave a Reply

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