Project Planner (Advanced) – Excel Template – v2 – Support

Posted on

Welcome to the support page for Project Planner (Advanced) template v2. This page will contain instructions on how to use the template and other relevant links.

Links

 

Version History

  • Current Version: 2
  • v1 Archived in August 2015

Overview of instructions

  1. Enter information in Settings (Planning Period, Resource Availability, Company Weekends and Holidays, Resources’ Personal leave)
  2. Enter Projects’ basic details (Project ID, Name and Priority)
  3. Enter Tasks (Task ID, Name, Resource assigned, Hours required).
  4. View the Task Plan Result whether the task will complete on time, late or will not complete.
  5. ‘Plan summary’ sheet provides printable report of projects’ summary, timelines and resource summary
  6. ‘Task Schedules’ sheet provides customizable Gantt chart that visually presents the schedule
  7. ‘Resource View’ sheet displays available and scheduled hours for each resource

 

Demo

Detailed Instructions

Step 1: Enter Information in Settings’ sheet

The Settings sheet contains important information elements that the template would use as inputs.

1) Planning Period Start and End Dates

This is the duration in which the template will plan the projects. Maximum allowed period is 366 days.

Project Planner (Advanced) Excel Template - Settings - Period
Project Planner (Advanced) Excel Template – Settings – Period

 

2) Resources

a. Enter list of resources (up to 30)
b. Enter their regular availability for each weekday.
c. Enter the cost per hour for each of the resource. This will be used in calculating project costs.

Project Planner (Advanced) Excel Template – Settings - Resources

Project Planner (Advanced) Excel Template – Settings – Resources

 

3) Company Weekends and Holidays

a. Choose Yes for weekdays which are usually company’s weekends.
b. Enter the list of Company holidays.
c. Template will not schedule any work during weekends and holidays.

Project Planner (Advanced) Excel Template – Settings – Company Weekends and Holidays
Project Planner (Advanced) Excel Template – Settings – Company Weekends and Holidays

 

4) Resource – Personal Leave

a. Enter any personal leave each resource will take. Enter hours that the resource will take for leave. This will be used to calculate the availability of resources that will drive the schedule.

Project Planner (Advanced) Excel Template – Settings – Resource Leave
Project Planner (Advanced) Excel Template – Settings – Resource Leave

 

Step 2: Enter Projects’ information

Project Planner (Advanced) Excel Template – Projects
Project Planner (Advanced) Excel Template – Projects
  • Required
    • Enter Project ID and Name. These should be unique and are required
    • Enter Project Priority (required).
      • Project Priority is used to determine the order of scheduling tasks. 1 represents the highest priority. For example, project with Priority 1 will be given importance and scheduled first before scheduling Project of priority 2.
      • If Project Priority is left blank, the project will not be 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.
  • Optional
    • Enter Preferred Start Date (date before which Project should not start)
      • If Preferred Start Date is left blank, Planning Period Start Date will be used.
    • Enter Preferred End Date (date by which the Project should end)
      • If Preferred End Date is left blank, project will be considered completing ‘ontime’ if it is estimated to complete.
    • Enter Project Fixed Cost.
      • This is the cost other than the resource cost which will be calculated by the template.
  • Calculated
    • Project Plan Status gives instant feedback on whether there are any errors associated with the Project. If there are no errors in data, then it will display ‘Planned’.

 

Step 3: Enter Tasks’ information

Project Planner (Advanced) Excel Template – Tasks
Project Planner (Advanced) Excel Template – Tasks

 

  • Required
    • Enter Task ID (unique values)
    • Enter Project Name (that the task belongs to) and Task Name
    • Enter Resource assigned to the task
    • Enter Hours Required to complete the task
  • Calculated
    • Task Plan Result field will display error if there are any. If there are no errors, it will display ‘Will Complete OnTime’ if the task will be completed prior to Task Preferred End Date and ‘Will Complete Late’ otherwise. It will display ‘Will Not Complete’ if the task cannot be completed.
Project Planner (Advanced) Excel Template – Tasks – Optional Fields
Project Planner (Advanced) Excel Template – Tasks – Optional Fields

 

  • Optional
    • Task Preferred Start Date can be used to indicate if a task cannot begin until a certain date.
    • Task Preferred End Date can be used to indicate if a task should end by a certain date. This is used to flag whether a task completes on time or late.
    • 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.
    • Task priority is provided so that you can control which task gets done first if the same resource is assigned to multiple tasks within the same 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.
    • Max Daily Allocation % is the % of a resource’s time every day that the task should be limited to. For example, if a resource is available for 8 hrs on a day and you had set 50% max daily allocation, then the template will only schedule the resource up to 4 hrs for that task. This allows a resource to work on multiple tasks in parallel.
    • Milestone: Leave it blank if a task is not a milestone. Otherwise, choose Yes. This will be displayed on the Gantt chart.
  • Calculated
    • Overall Priority will show ‘ERROR’ if required fields are not populated. Tasks that have errors will not be included in plan. Lowest number means the highest priority.

With this step, the data entry in this template is complete. All the following sheets will be automatically created.

 

Step 4: PLAN Summary

Projects’ Summary:

Project Planner (Advanced) Excel Template – Summary - Project Summary
Project Planner (Advanced) Excel Template – Summary – Project Summary
  • Summary of each project’s key metrics and attributes
  • Projects that will not complete will be highlighted.

 

Projects – Timeline view

Project Planner (Advanced) Excel Template – Summary - Project Timeline View
Project Planner (Advanced) Excel Template – Summary – Project Timeline View
  • Visual representation of the projects’ timeline (weekly).
  • It also highlights whether the project will complete on time, late or will not complete.
  • The blue vertical line indicates the Preferred End Date of projects.

Resource Summary

Summary of statistics for each resource

Project Planner (Advanced) Excel Template – Summary - Resource Summary
Project Planner (Advanced) Excel Template – Summary – Resource Summary

Resource Utilization Rate of each resource

Project Planner (Advanced) Excel Template – Summary - Resource Utilization
Project Planner (Advanced) Excel Template – Summary – Resource Utilization

 

5. Task Schedules

This sheet provides the schedules (Gantt charts) that are customizable. First, you can choose from three different schedule types. (Project, Resource or All Tasks). By default, All Tasks will be shown.

 

Project Planner (Advanced) Excel Template – Summary - Task Schedule Options
Project Planner (Advanced) Excel Template – Schedule – Task Schedule Options

 

You can choose PROJECT and choose one of the Projects. You will now see three things.
1) Project Summary at the top with key statistics

Project Planner (Advanced) Excel Template – Summary - Project Schedule
Project Planner (Advanced) Excel Template – Schedule – Project Summary

 

2) Tasks with their details. 50 tasks are shown at a time.

Project Planner (Advanced) Excel Template – Schedule - Project Tasks
Project Planner (Advanced) Excel Template – Schedule – Project Tasks

 

3) Gantt Chart

Project Planner (Advanced) Excel Template – Schedule - Project Gantt Chart
Project Planner (Advanced) Excel Template – Schedule – Project Gantt Chart

 

In order to see schedule for a specific resource, you can choose the resource schedule type.

Project Planner (Advanced) Excel Template – Schedule – Resource Schedule
Project Planner (Advanced) Excel Template – Schedule – Resource Schedule

 

Tasks that will complete late will be labelled as orange background and those that will not complete will be shown in red.

Project Planner (Advanced) Excel Template – Schedule – Resource Tasks
Project Planner (Advanced) Excel Template – Schedule – Resource Tasks

 

Gantt chart can be customized by entering any start date and choosing from Daily/Weekly/Monthly. You can also change the start date of the Gantt chart.

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart customization

 

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Weekly

 

Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly
Project Planner (Advanced) Excel Template – Schedule – Gantt Chart Monthly

 

Step 6: Resource View

 

The ‘Resource View’ sheet displays the hours available for each resource for each day and the hours they have been scheduled.

Project Planner (Advanced) Excel Template – Resource View - Available
Project Planner (Advanced) Excel Template – Resource View – Available

 

ProjectPlanner_Advanced_ExcelTemplate_ResourceScheduled
Project Planner (Advanced) Excel Template – Resource View – Scheduled

34 thoughts on “Project Planner (Advanced) – Excel Template – v2 – Support

  1. I have two questions that I invite any suggestions for a possible fix.
    1) I have a resource that is dependent on two others before it is required to start. I have set up those resources as predecessors 1 and 2. The dependent resource, however on every occasion, gets scheduled to start on the same days at the predecessors and not the day after the end date of predecessor as it should. This happens only with this particular resource. Other resources behave as required. I’ve tried removing the resource from the resource table and re-adding.
    Do I need to check any data in hidden sheets that drive the scheduling?
    2) I also have more than one resource that is used in the same project more than once. As an example, Resource C consists of more than one worker and each project has a daily allocation assigned and is required to carry out X many days of work. The project then needs to flow to Resource D for X number of days. The project then needs to go back to Resource C for completion of resources assigned to that resource area. Is there a work around that removes errors in duplicated resources for the same project that still addresses the resource capacity check? I still need an accurate treatment of the Resource C to identify bottle necks in this resource and to know when capacity is reached.
    All feedback appreciated

    1. Thanks for using the template.
      Is it possible to send the file and highlight the specific scenarios? Please email support@indzara.com
      1. The dependent resource should start the date after the predecessor completes.
      2. Can you please clarify what it means by ‘Resource C consists of more than one worker’? If a task by C has to be done and then for D to do something and finally come back to C, those would be three tasks.
      Please let me know.
      Thanks & Best wishes.

      1. Thanks you for replying to my post.
        “1. The dependent resource should start the date after the predecessor completes.”
        All but the odd one that doesn’t. Capacity is mostly free. I can fix by forcing a task start date.
        ” 2. Can you please clarify what it means by ‘Resource C consists of more than one worker’? If a task by C has to be done and then for D to do something and finally come back to C, those would be three tasks.”…

        Responding to your comment and to provide clarification, yes you have it right in that task C needs to be first done, then D then back to C .

        I am attempting to use the project planner to manage a production manufacturing plant.
        Task C in my example represents a team of workers in one area of the production line. There are 4 workers available for 8 hours per day. Their resources are allocated a percentage of the total available resource for the day, e.g., 25% of the available 32 hours available.
        Task C (step 3) and it’s workers carry out a fabrication role. The project then moves to task D (step 4), which in this case is painting. The painting team is also a pool of workers and are responsible for carrying out task D. This team is however, completely seperate to the task C team. After task D ‘painting’ is completed, the same team and resource used in task C carry out reassembly of parts (stage 5).
        I could just set up three seperate resources as you have stated.
        The problem is that in my case the same workers and pool of hours used for stage 3 are also used in stage 5. I am relying on the resource capacity function to appropriately schedule the days and skip days when resource capacity is met based on my allocation of resource to the hours required to complete the task.
        The plant at capacity produces 25-30 projects with tasks ranging from 6 to 20 assigned to each project.

        I hope that I have clarified my example well enough?

        I have found the odd other quirky response. I have a work around but will delay discussion for now.

        The product you have created is very clever and useful. What I’m thinking is that it would be great if it was possible to have duplicate resources in the same project and have a way of managing their order of operation and to be able to split multiple blocks of consumption of that resource . Perhaps I’m looking at it all wrong and there is a simpler solution that still allows me to address the scheduling and capacity concerns I speak about.

        Yes, I can e-mail the file with highlighted area.

        Thank you.

        1. Please email file to support@indzara.com
          Thanks & Best wishes.

  2. Hi, Thank you for the wonderful Project Planner tool. I am trying it out now and have noticed an issue. I would like to enter data for “resource – personal leave” to cover multiple days. When I enter hours that span over many days (say 6 days); I notice that in the resouce view only the first day of the leave is set to zero. For example, if a resource is available for 6 hours a day and I enter 36 hours of leave, I expect 6 days are set to zero availability. Is this the functionlaity I should expect or am I doing something in-correct?
    Regards

    1. You are welcome. Glad you find the template useful. Please post your review on the product page https://indzara.com/product/project-planner-advanced-excel-template/ Thanks for your support.
      In this template, each personal leave day has to be entered separately. If there are 6 days, we should enter them individually in 6 rows in that table.
      Please let us know if there are any questions.
      Best wishes.

  3. Hi,
    Can the planning period be extended beyond 366 days?

    1. Hello

      The planning period can be of maximum 366 days

      Regards

Leave a Reply

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