Not everyone has access to Microsoft Project in order to create their project schedules. As an alternative, you can use our Excel template linked at the bottom of this FAQ to create a task list and import this into PPO.
If you open the template, you will see it has 4 sheets:
- The "Read Me" sheet contains some basic guidelines on using the template as well as an explanation of the columns on the other sheets.
- The "Project Plan" sheet is where you capture the details of your tasks.
- The "Resources" sheet contains the PPO resources that you want to assign to the tasks. It drives the Resource dropdowns on the "Project Plan" sheet.
- The "Holidays" sheet can optionally be used to capture holidays or other non-working days. This is only used if you implement duration driven scheduling as explained later in this FAQ.
You may notice that the spreadsheet contains some hidden sheets and columns. You should not delete these sheets or columns in order for the template to work correctly.
Populating the Resources sheet
The Resources sheet contains the PPO resources that you want to assign to the tasks and it drives the Resource drop-downs on the "Project Plan" sheet. If you will be assigning the resources to tasks in PPO then you do not need to populate the sheet.
An easy way to populate this sheet is to run the "Resource Sheet" report in PPO which exports a list of the employees and their keys in the format expected by this template. This report is under the Employees report category. If you do not see the report, contact your system administrator to grant you access to the report. Simply open the resultant report in Excel and copy the data to the "Resources" sheet. For more information about this report read the following knowledge base article.
Capturing your tasks
The “Project Plan” sheet is ultimately where you will build your project schedule. By default, some sample tasks have been populated which you can overwrite with your own tasks. There are however some important points to note:
- The UID column is used to uniquely identify each task and when you import it into PPO is used to populate the TaskCode. It is important that this number should be unique. Once you have given a key to a task, you should not change it as it will result in PPO incorrectly matching to an existing task in PPO if you re-import the plan. If you want to insert a task somewhere in the middle of the plan, you should still use the next available UID. Note that the order of the tasks is determined by the row number and not the UID.
- The Type of the first task in your plan should always be Project Task (a special type of summary task which spans the entire duration of your project). This task has special significance in PPO and is used in some of the reports in PPO.
- A Start and Finish date must be captured for each task. Alternatively, you can use duration driven start and finish dates, using formulas as explained later in this FAQ.
- You select a Resource by selecting it from the dropdown. If you do not see the resource you are looking for, update the Resources sheet as described above in "Populating the Resources sheet".
- Optionally, you can specify the Planned and Actual Progress % for each task. Note that if you re-import tasks, it will over-write these values in PPO, if they have been changed in PPO.
Importing your plan into PPO
Once you have finished capturing the details of your tasks, save the file as an XML Data file (*.xml file) by using the File, Save As option in Excel.
Then to import the Excel template, click on the Import Tasks action button from the Project View page.
On the Import Task Wizard View page, select the saved XML file and select the Import button.
Duration driven scheduling
Microsoft Project has some powerful features that make it easy to create schedules. Two of these features are duration driven scheduling and task dependencies. This automatically calculates the Start and Finish dates of your tasks based on the duration, and makes the start date of a task dependent on the finish date of another task. You can accomplish something similar in Excel using formulas.
The following steps will guide you through the process:
Step 1: Add a “Duration” column where you will capture the duration of the task in days. For the project task, we will make it calculated.
In cell E2 enter the value 0 and copy it down. Next paste the formula below (shown in purple) into E2.
E2 = NETWORKDAYS([@Start],[@Finish])
Step 2: Make the “Start” date calculated, except for the start date of the project task which is manually entered (highlighted in orange above). The first regular task (Task 1 in row F3 highlighted in red above) uses the start date of the project while all the other tasks (from F4 onwards) use the previous task's end date + 1 working day.
Formula for F2: Enter the start date of your project e.g. 2017-09-01
Formula for F3 = F2
Formula for F4 = WORKDAY(G3,1,Holidays[HolidayDate]) then copy this formula down
Step 3: Make the Finish date calculated as well. For the project task it is the finish date of the last task (highlighted in green in the screenshot above). For other cells it is the start date plus the workings days of the Duration.
Formula for G2 = G7 (This should be the finish date of the last task in your project)
Formula for G3 = WORKDAY([@Start],IF([@Duration]=0,0,[@Duration]-1),Holidays[HolidayDate]) then copy the formula down.
Step 4: Finally, capture a Duration for each of the tasks (except the Project Task) and see how the Start and Finish dates change!
Step 5: The formulas above automatically take into account any holidays that have been captured on the "Holidays" sheet of the template. You can either capture these manually or by extracting the “Public Holidays” report from PPO. Access the following knowledge base article for more information.
Linked below is the Excel template that can be used.
If you require any additional assistance in using the Excel template, please feel free to log a support call, and the PPO support team, will gladly assist.