Indzara

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

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

$30Add to cart

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

36 Comments

  • Is there anyway I can use the personal leave portion for vacations? If so how do I go about doing that?

    Reply
    • Yes, Personal leave table is to enter any vacation or time off that resources take. Please let me know if there are any questions.
      Thanks & Best wishes.

      Reply
      • when I put in 40 for the hours it is only reflecting on the date. So does that mean I would have to put that person in 5 times. One for each day off?

        Reply
        • Yes, you are correct. Each day taken off should be entered separately.
          Please let me know if there are any questions.
          Thanks & Best wishes.

          Reply
  • Hello Indzara!

    I would like to know if it would be possible to extend the number of resources. Unfortunately, 30 is a little short for my needs. I would like to double that, possibly going up to 80.

    Is it just a matter of tweaking the formulas or is it an Excel limitation here?

    Thanks in advance for the answer!

    Reply
    • It is technically possible to extend the number of resources, but it would require changes to formulas in multiple sheets in the workbook. It is not straight forward though.
      Best wishes.

      Reply
  • I found the formula by unprotecting the sheet.

    I fixed the issue i reported above by modifying the formula in “Hours not scheduled column” to =IFERROR(ROUND([@[HOURS REQUIRED]]-[@[HOURS SCHEDULED]], 4),””)

    Thanks again for the great template.

    Reply
  • The template is great. I did find a small bug. There is some rounding or fractional error sometimes, for some projects it will show required hours and scheduled hours with matching values, but there will be a very small remainder of “hours not scheduled” – like 1.7053E-13 or some similar very small value. This small remainder will cause it to show “WILL NOT COMPLETE” as the plan result. Modifying the amount of hours required can force it to recalculate, but it would great if you could fix by counting any hours less than .0001 as 0 or something similar. I tried to modify but cant find the code that is generating the “Hours not scheduled” calculation.

    Reply
  • Hi ,

    I would like to how do i mark a task completed in Project Planner v2_3.

    Thanks,
    Nitin Patil

    Reply
  • Hi Indzara,
    First of all thank you for such a useful planner.

    I would like to know how the status of activities will change to complete once the activity is completed. The utility shows if the activity will get complete on time or not but i could not see where to mark the activity completed/not completed if target date is passed.

    Thanks
    Nitin Patil

    Reply
    • Thanks for your feedback. I am glad that you find it useful.

      To just mark tasks whether they are complete or not, you can definitely add another column in Tasks table to track that. However, it won’t be used in populating the Plan Summary or other sheets. Please note that this tool is for planning one or many projects ahead of time. The Project Manager template https://indzara.com/product/project-manager-excel-template/ allows planning and tracking actual progress of tasks. That template is designed for only one project at a time. Due to the complexities in the calculations, they are separate. The Project Manager template is ideal if you are planning and managing one project. Project Planner is ideal for planning multiple projects.

      Please let me know if there are any questions. Best wishes.

      Reply
  • In your Step 4: Plan Summary, Project Summary image above, Project One has a ‘Preferred End Date’ of 11-Sept-15 (which is the Friday of week-beginning 07-Sept-15), and says ‘Will Complete On Time’. But on the Timeline View chart, the graphic looks like Project One extends into the end of the week-beginning 14-Sept-15.

    This is what I see on my Timeline View too with my data (e.g. project with preferred end date Friday 29-Jul-16, all green, actually look like it will finish in week beginning 4-Aug-16).

    Everything’s green, yet the dates along the top definitely look like projects finish a week late.

    Is this a bug or expected behaviour?
    Thanks

    Reply
    • Edit: I mean “project with preferred end date Friday 29-Jul-16, all green, actually look like it will finish in week beginning 2-Aug-16”

      Reply
      • Graeme, the date shown is the week end date. Please let me know if that answers your question. Thanks & Best wishes.

        Reply
        • Thanks for the reply, ok will work with what it does I guess. A project that completes on time on Friday 11-Sep-15 shows on the plan view a full green cell with Monday 14-Sep-15 above it – just never seen a week that ends on a Monday before. :S

          Reply
          • You are welcome, Graeme. The template is designed so that the week end date is dynamic. Since I had chosen Sep 1 (tues) as Planning Period Begin Date, Sep 7 (mon) is displayed, as it’s the completion of the week beginning Sep 1. If you enter a Monday as Planning Period Begin Date, then Sunday will be week end date. It is completely flexible. Please let me know if there are any questions. Best wishes.

    • Please email me at indzara@gmail.com with error message you may be receiving. Which browser and which payment method are you using? Thanks for your interest. I will do my best to help.

      Reply

Leave a Reply

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