How to calculate Planned % and Actual % using Weight

‘Schedule % Complete’ in Primavera P6 (represents the planned %) calculates its percentage using the cost resource. But if you wanted to measure the ‘Schedule % Complete’ by the weight of the activities, here we will have to use Excel to perform such a task.

This process will be performed using two main tools: Primavera P6 and Excel.

Primavera

First of all, from “Project” in the toolbar, choose “Resource Assignments”.

Choose “Filter” to filter the resource type as we need only the weight of the activities.

In case you do not have a weight assigned to your activities, you can create a resource called “Weight”. From “Enterprise”, Choose “Resources”.

Add a new resource and name it “Weight”, then, choose the resource type as “Nonlabor” and “Finish”.

After clicking “Finish”, assign the “Weight” resource to all of your activities and choose a suitable weight for every activity by assigning this weight in the “Budgeted Units”.

In the Resource Assignment, right-click on any column heading, then choose “Columns”, then “Customize” and make sure that the “Budgeted Units” column is selected.

In the bar chart area, make sure that the displayed units are budgeted units by right-clicking on any point in the display area. From “Spreadsheet Fields”, choose “Budgeted Units”.

Before copying our activities to Excel, we have a final step to do. This step is determining the timescale of the project. This depends on the minimal periodic reports that we want to review, do we want to have daily reports? or weekly?

In this article, we will be working on the weekly report. So, right-click on any point in the display area and choose “Timescale”. In the “Timescale” window, choose “Month/week” option from “Date Interval” dropdown box. In case you are working on a monthly report, you will “Quarter/Month” from “Date Interval”.

Now, choose the WBS levels that you want to show, click on the first WBS row then move down to the last WBS row (Without clicking on it), click “Shift”, then click on the last WBS row then “Ctrl+C”.

In this screenshot, we want to show the overall progress of the following WBS: Preliminaries, Surveys and Mapping, Engineering, Procurement, Construction, Testing and commissioning, and Project Closeout without any further details. So, we collapsed the WBS level to level 2 which includes these WBS. Let’s move on to Excel.

Excel

Press “Ctrl+V” to paste the data you copied from Primavera P6.

In the blue box, you can see the WBS of the project as you chose with the corresponding overall budgeted nonlabor units of each WBS. On the other hand, the red box includes the distribution of these budgeted nonlabor units over the weeks (the time interval that we chose).

Note that row 2 includes the overall budgeted nonlabor units of the project in the blue box and the budgeted nonlabor units per week in the red box. This note is essential for the next step.

Below the copied data, we will create 2 rows which are: “Weekly Planned Weight” and “Weekly Planned %”. The “weekly planned weight” will be equal to the cell in row 2 of the same column, e.g.: the “Weekly Planned Weight” of the week ending on 3-July-22 that lies in column “F” will be equal to F2.

The “Weekly Planned %” will be equal to the “Weekly Planned Weight” divided by the overall budgeted nonlabor units (Cell D2).

Let’s transform it into an equation, “Weekly Planned %”= F238/$D$2 as shown below.

Now, we have the weekly planned percentage for every week in the project. However, we need to calculate the cumulated weekly planned percentage to obtain the “Schedule % Complete” for any date we choose.

Below the “Weekly Planned Weight” and “Weekly Planned %”, create another two rows named “Cumm. Planned Weight” and “Cumm. Planned%”. As the equations are already set in the “Weekly Planned Weight” and “Weekly Planned %”, all we have to do is sum up the numbers. For the “Cumm. Planned Weight”, the result will be equal to the weekly planned weight plus the cumulative planned weight of the previous week.

In the below screenshot, the cumulative planned weight of the week ending on 10-Jul-22 is equal to the weekly planned weight of the week ending on 10-Jul-22 plus the cumulative planned weight of the week ending on 3-Jul-22.

For the “Cumulated Planned %”, do the same process that we did for “Cumulative Planned Weight” but using the “Weekly Planned %” and “Cumm. Planned %”.

Finally, we can use a simple HLOOKUP function to get any data we want based on the data date. The HLOOKUP function works as follows, First, you pick the cell that includes the value that you aim to look for (in our article, it is the data date). Second, choose the range of data that Excel will look within it (For us, the range is from row 237 to row 241). Then, you have to choose the row number that Excel will get its result from when it finds the value you are looking for, as below, we want the “Cumm. Planned %” which lies in the 5^th row in our range. Finally, Excel will ask you if you want an approximate result or not, choose “False” if you want an exact result.

Now, let’s move on to calculating the actual % completed using weight.

Primavera

As we did in the planned %, from “Resource Assignments”, edit “Columns” and switch the “Budgeted Units” column with “Actual Units”.

Now, choose the exact WBS you chose before while copying the planned nonlabor units, select the first row, then move down to the last row, press “Shift” and select the last row. Press “Ctrl+C” and now it is time to paste it into Excel.

Excel

In Excel, we create 2 sheets, first one is named “From P6”. In this sheet, we paste the actual units that we copied above.

The second sheet is called “Actual Data”, This sheet consists of two parts as shown below, red and blue part.

In the red box, you will find the budgeted units of the project as in the “Planned Data” sheet. In the blue box, you will find the actual units per week. But how can we calculate the actual units per week?

To calculate the actual units per week, we must follow a certain concept. Every week, the actual units increase as the project moves forward. So, if we subtract the total actual units as per last week from the total actual units as per this week, we will get the actual units for this week only.

In the screenshot below, we aim to calculate the actual units for the week of 2 July 2023, We must subtract the summation of actual units in the previous weeks from the actual units we copied from Primavera (in the “From P6” sheet).

Below, you will find that the actual units of the whole project for the week ending on 2 Jul 2023 are equal to the actual units of the whole project (Cell D2 in the sheet named “From P6”) minus the sum of all actual units before this week.

Once done, drag this equation to the end of the WBS to apply this equation to all your WBS levels.

Note: When updating the following week, copy the actual units of the last week and paste it “123” to save the data in the cells, then apply the same equation to get the actual units of the following week.

Now, you are all set to create the “Weekly actual units”, “Weekly actual %”, “Cumulative actual units” and “Cumulative actual %” as we did in the planned nonlabor units section.

After we managed to organize our data and get the output of the planned & actual % at a given date, our final step is to represent this data in the form of a chart.

To make a chart we need 3 data types: Date, Planned %, and Actual %.

For the date: we will make a smart equation to allow the data to change automatically once the data date is changed.

In the below screenshot, you can see several dates that are linked to each other. All the dates below are linked to the “Data Date”. We aim to represent the planned % and actual % at the “Data Date” and 4 weeks before the “Data Date”. So, we use the equation “Data Date – 7” to get the date of the data 7 days ago, and so on till we get the data dates of the previous 4 weeks.

The same concept can be applied to obtain the data dates of the next week by applying the equation “ Data date + 7”. So, when the next week comes we need only to copy the date”9-Jul-2023” and paste it using the “123” format in the cell of the “Data Date”.

Now, once the “Data Date” is changed, all the dates will change accordingly.

For the planned %: we will use a “HLOOKUP” equation to get the planned data for the dates we want. In the HLOOKUP equation, we will search for the date “4-Jun-23” which is located in cell “M4”. The lookup range will be between the 10^th and 14^th row in the “planned data – weight” page to get the cumulative weight %, in our example, the cumulative weight %is located in the 5^th row of our range.

Once done, do the same steps with the other dates.

For the actual %: we will use a “HLOOKUP” equation to get the actual data for the dates we want. In the HLOOKUP equation, we will search for the date “4-Jun-23” which is located in cell “M4”. The lookup range will be between the 10^th and 14^th row in the “actual data – weight” page to get the cumulative weight %, in our example, the cumulative weight %is located in the 5^th row of our range.

Once done, do the same steps with the other dates.

For the planned weekly %: we will use a “HLOOKUP” equation to get the planned data for the dates we want. In the HLOOKUP equation, we will search for the date “4-Jun-23” which is located in cell “M4”. The lookup range will be between the 10^th and 14^th row in the “planned data – weight” page to get the weekly weight %, in our example, the weekly weight %is located in the 3^rd row of our range.

Once done, do the same steps with the other dates.

For the actual weekly %: we will use a “HLOOKUP” equation to get the actual data for the dates we want. In the HLOOKUP equation, we will search for the date “4-Jun-23” which is located in cell “M4”. The lookup range will be between the 10^th and 14^th row in the “actual data – weight” page to get the weekly weight %, in our example, the weekly weight % is located in the 3^rd row of our range.

Once done, do the same steps with the other dates.