Sunday, July 14, 2024

Building and Using A Practical Portfolio Benefits-Realization Plan with MS Project (Part - 2)


In the earlier part, we understood the fundamentals and importance of a Portfolio Benefits-Realization Plan. We also took a number of steps to build this plan. In this part, we will conclude and give a finishing touch to the plan, followed-up with a video and concluding remarks.

This series: Part – 1

Step – 6: Segregation of Components and Strategic Objectives

As the portfolio components are all blue color-coded, the distinction among components in the graphical side of the plan is not very clearclear. Hence, our next step is to segregate the components with the help of additional custom fields. For our plan, we will have three Boolean custom fields:

  • IsProgram: A Boolean flag indicating the component is a program.
  • IsOps: A Boolean flag indicating the component is an operation.
  • IsStrObj: A Boolean flag indicating the organizational strategic objective. 

Again, to create these custom fields, go to Gantt Chart Tools > Format tab > Columns group > Custom Fields command and add three Boolean flags as shown below. 

Next, we need have to change the conditions associated with these three flags. Whenever a new portfolio component or a new strategic objective is added, the respective bar in the Gantt Chart view will display the requisite color.  For this, go to Gantt Chart tools > Bar Styles group > Format drop down menu and choose the Bar Styles command. We will add the following tasks with respective conditions:

  • Program Task: N It’ll be a normal, active task with the ‘isProgram’ flag applied.
  • Ops Task: NIt’ll be a normal, active task with the ‘isOps’ flag applied.
  • Strategic Obj task: NIt’ll be a normal, active task with the ‘isStrObj’ flag applied.

To add these new tasks, simply use the functionalities available in the Bar Styles dialog box such as Insert Row, Cut Row, and Paste Row.  

As shown above, we have four task types, including the default task type for the component projects represented with a blue bar.

Next, we willhave to apply them in the Gantt Chart view in the following manner:

  • Set ‘isProgram’ field “Yes” for the component programs.
  • Set ‘isOps’ field “Yes” for the component operations.
  • Set isStrObj’ field “Yes” for the strategic objectives.

When you have all the respective fields set for the component projects, programs, operations as well as the strategic objectives, we will have the following view.  

As shown, the component programs (e.g., Program 1), component projects (e.g., Project 3), and component operations (e.g., Operational Work 1) are shown in green, blue, orange colors, respectively. On the other hand, the strategy and objective (e.g., Organizational Strategy and Objective I) is shown with red color coding.

As you present this bBenefits-realization plan or share with your stakeholders, you may want to just show the minimal fields. Additionally, based on your need, you can also add the component names in the graphical side of the Gantt Chart view. This is depicted in the below figure. 

As shown above, we now have complete Portfolio Benefits Realization as documented in PMI’s Standard for Portfolio Management. If you have business proposals (or cases) as another portfolio component class, you can exactly follow the same previous steps with a new custom Boolean flag, conditions,  and a separate color.

Video – Demonstration of Portfolio Benefits-Realization Plan 
*** NEW ***

I’ve put together a video depicting a portfolio benefits-realization plan. In addition, I've some more explanation with respect to this plan. My video [duration: 4m:26s] shows all the components in the plan, which we recently learned in the article. They are demonstrated with different color coding for the individual components.



Conclusion

Finally, you might be wondering,  can someone add the benefits accrued from the components into the portfolio benefits-realization plan? 

Yes, you can! But do ensure the linking and dependencies are properly managed. 

However, the PMI-SPfM puts the portfolio components, benefits, and strategic objectives, along with the benefits realization based on schedule and number of other parameters iIn a separate poa number of other parameters, in a separate portfolio report called portfolio performance variance report. As you manage your portfolios, along with key deliverables such as portfolio roadmap, portfolio reports play a significant role. Hence, while doing portfolio management and sharing the status with your stakeholders, you can use both the portfolio benefits-realization plan and the portfolio performance variance report.

Many perceive and/or believe that MS Project software tool can’t be used to create a Portfolio Benefits-Realization Plan! As we just learned, you can certainlydefinitely build one and e. You can also dynamically change the plan as per your needs. 

This series is concluded. I welcome your thoughts, reviews, and feedback in the comment section below.

This series: Part – 1

--

This article was first published by MPUG.com on 5th December, 2023. The current one has been updated with content and video.


References

[1] NEW Book - I Want To Be A PfMP, The Plain and Simple Way, by Satya Narayan Dash

[2] Article – Benefits Realization Management for Projects, by Satya Narayan Dash

[3] The Standard for Portfolio Management, by Project Management Institute (PMI)



Friday, July 05, 2024

Building and Using A Practical Portfolio Benefits-Realization Plan with MS Project (Part - 1)


Bugs smell, features tell, benefits sell.

-    From the book, I Want To Be A PfMP, the Plain and Simple Way

Portfolios fundamentally exist in an organization to achieve organizational strategies and objectives. One or more strategic objectives of an organization flow into the portfolio strategic plan. After the identification, categorization, evaluation, prioritization, and authorization of portfolio components, their execution begins and benefits are delivered. This, which in turn, helps in achieving the organization’s strategic objectives.

But then following questions come -up:

  • How do you ensure strategies are fulfilled in order to meet the strategic goals?
  • How do you find out that the portfolio components authorized earlier are actually delivering the benefits?
  • How to plan for, measure, and monitor the organization’s (business) value achievement?

The answers to above questions lie with portfolio benefits management. The Standard for Portfolio Management (SPfM®), clubs benefit management under Portfolio Performance Management. Benefits and their subsequent realization are extremely important in portfolio management. And as the opening quote tells, it's only benefit that sells! 

In this article, we will understand portfolio benefits, the benefits dependency map, and above all, how to build a benefits-realization plan with the MS Project software tool. The portfolio benefits-realization plan will be based on the template provided by SPfM from Project Management Institute (PMI®).

This series: Part – 2

Portfolio Benefits

Portfolio benefits are first identified during the definition stage of the portfolio and documented in the portfolio strategic plan (PfSP). These benefits will flow into the Portfolio Performance Management Plan (PfPMP) and are documented in the Benefits Realization section of the PfPMP. 

Benefits can be qualitative or quantitative, tangible or intangible, financial or non-financial, short- or long-term. Irrespective of the type of portfolio benefits, they need to be clearly defined and documented in the PfPMP.

The portfolio benefits actually come from the portfolio components. These benefits are aggregated at the portfolio level. The benefits in turn help achieve the strategic objectives of the organization. To understand it graphically, you need to be aware of another fundamental concept, the benefits dependency map (BDM).

Benefits Dependency Map

A simplified benefits dependency map is shown below.  

As shown in the above BDM, moving from left to right of the map, we have organizational vision (and mission) translated to strategic objectives. These objectives, in turn, translate to benefits, and then to outcome to outputs (or results). In other words, moving from left to right we are asking – “How the strategic objectives are finally executed to give results/outcomes/output”? On the other hand, moving from right to left we are asking – “Why this portfolio component (e.g., project), which is giving this result (or service or product) is undertaken in the first place?” 

As you can see, we are moving from strategic objectives at the level of portfolio to benefits at the level of portfolio components. With these fundamentals, let’s see how to build the benefits-realization plan. 

A Practical Benefits-realization plan

We will take a step-by-step approach to build the Portfolio Benefits-realization plan with MS Project software. Along the way, we will do a few customizations and apply them to the software tool. 

Step – 1: Add A ‘Portfolio’ Custom Field 

To add a custom fieldle, go to Gantt Chart Tools > Format tab > Columns group > Custom Fields command and add a Text custom field “Portfolio”. Under this custom field we will have various portfolios undertaken by the portfolio manager. 

Next, add three portfolios under this custom field – Portfolio I, Portfolio II and Portfolio III. For each portfolio we will have a number of components and we will add them shortly.  

Step – 2: Rename ‘Task Name’ Column to ‘Portfolio Component’ *** UPIDATED ***

Before adding the components, rename the Task Name the column to Portfolio Components. This can be done by right clicking on the Task Name column and choosing Field Settings option.

When you have both the Portfolio custom field showing having the above three portfolios and the renamed column of Portfolio Component, you will have the following figure. 

For the time being, do not worry about the start and finish dates for the portfolios. Abecause after we add the portfolio components and strategic objectives, we will get those dates. 

Step – 3: Add the Portfolio Components and Strategic Objectives

In our next step, we will add the portfolio components and the strategic business objectives, which will be met when the benefits delivered by portfolio components are realized. 

To add the portfolio components, fyou just have to fill -up the line entries under the Portfolio Components column with respective component projects, programs, operations, and strategic objectives. This is very much like adding the task names in a normal MS Project plan. After you add the entries, you will have the following plan.    


This is the first -cut of our Portfolio Benefits-Rrealization Pplan, which we are going to refine as we proceed. As shown above, for Portfolio I:

  • There are three component projects - Project 1, Project 2, and Project 3 - and also Component Program 1.
  • Organizational Strategy and Objective I will succeed the completion of the project and program components. 

Similarly, we have components, including operations, for other portfolios such as Portfolio II and Portfolio III in the evolving plan. 

Step – 4 (mini one): Ensure Proper Timescale

Did you notice on the right side of the above figure that the timescale has changed?! This is important because, without proper timescale adjustment, you won’t be able to visualize the long running portfolio components such as a program or a long-term project. 

In our case, I’ve used the below timescale customization.  

As shown above, we have:

  • Two tiers – Middle Tier and Bottom Tier.
  • For the middle tier, Units used is Years, whereas for the Bottom Tier, Units used is Quarter.
  • The Preview is shown belowbelow, and the exact same view is available in the previous figure.

Step – 5: Build the Dependencies

Now that we have the right time-scaling, we must ensure the dependencies between the portfolio components and the strategic objectives which will be met when they are complete. 

After you add the dependencies, the view will come as shown below. For an in-depthTo understanding of know in-details about dependencies, lead, and lag, , you can use this course.

 
Interpreting the above figure and dependencies, one can say:
  • Component Program 1 has finish-to-finish (FF) dependency with Component Project 1 and Component Project 2. 
  • Organizational Strategy and Objective I has FF dependency with Component Project 3 and Component Program 1. 
  • Organizational Strategy and Objective I also has FF dependency with Component Project 3 and Component Program 1.
In other words, you can say that Organizational Strategy and Objective I will be achieved when component Project 1, Project 2, Project 3, and Program 1 are completed and associated benefits are realized. This is important to understand.

Similarly, as you can see in the above plan, other organizational strategic objectives are associated with other portfolio component projects, programs,  and operations.

In the next part, we will follow few more steps such as seggregating the components based on their classes or categories. We will conclude with a video demonstration.

This series: Part – 2





Wednesday, June 26, 2024

Practical Risk Management with Primavera Risk Analysis – Working with An Oil and Gas Industry Project


The Practical RMP with Primavera Risk Analysis course is used by risk management practitioners around the world, including PhD candidates pursuing their doctorates. Professionals from construction, software, EPC (engineering, procurement and construction), space and other industries use this course.

Currently, I received questions on its usage on in the oil and gas (O&G) industry. The question is on practical applicability in that industry. The Practical RMP course is industry transparent and uses its own project and builds-up step-by-step from risk planning, identification to risk monitoring and tracking. Along with the theory, you will learn to do risk management in a practical, hands-on manner. The later part, hands-on, is crucial. Above all, you can apply your learning in any industry.

Coming to the Oil and Gas industry, the Practical RMP course can be used and the risk management concepts can definitely be applied. For this article, I’m going to take a sample risk management plan. This is a .plan file as called in Primavera Risk Analysis (PRA) software tool. 

The .PLAN file

This plan is taken from the provided sample plans available in PRA tool. When imported to the software, it pops-up a message about cost uncertainty in this oil and gas project. 

Uncertainties can come in various ways:

  • Duration uncertainty
  • Cost uncertainty
  • Network uncertainty, among others.

You can learn more on these uncertainties in this article. All these uncertainties can be modelled

Analyzing the .PLAN file 

Now that we have imported this file, we need to have a quick look on the plan, which is shown below. 

As shown:

  • We have a number of activities under the heading “A” such as Civils, Buildings, Structural Steel & Painting, Mechanical Equipment Supply etc.
  • Each of these activities have the cost shown – minimum fixed cost, maximum fixed cost and most likely fixed cost.
  • For example, the ‘Structural Steel & Painting’ activity has minimum fixed cost of $900,000, maximum fixed cost of $1,100,000 and most likely fixed cost of $1,300,000.

The resources are fixed cost for these activities and the cost uncertainty has been shown for each. The default probability distribution has been used for them. 

The overall project statistics is shown below. This is can be seen by going to Plan menu > Plan Information … > Statistics tab.  


As you can see, the total planned cost is $19.123 M (million) and the planned finish is 31 October, 2025. I’ve changed the date of the plan. Also, you would have noticed that there are 7 resources, in total, for this cost plan.

Risks as Part of the .PLAN file

This plan is somewhat different compared to the other sample plans available. This is because the risks are added as part of this cost plan

The risks have probabilities associated, along with modelling numbers. This is because only risk can have uncertainties as they are in the future and they are uncertain. Activities are planned and will be executed. But they do have uncertainties and hence the modelling.

The risks associated with this cost plan along with the probabilities are shown in the below figure. 

As shown above three risks are directly plan of the plan, not the risk register! There are:

  • Material supply problems with 10% chance.
  • Key personal availability with 25% chance.
  • Design complexity underestimated with 20% chance.

In addition, each of these risks has minimum, most likely and maximum fixed cost. 

Running the Cost Analysis

Next, with this available plan (I’ve made some changes), we will run the risk analysis with respect to cost. This can be done by going to Risk menu > Run Risk Analysis … option. We will use the default parameters while going through analysis and will use the Monte Carlo simulation.

Post analysis, the Latin-Hypercube simulation (a modified version of Monte Carlo), we have the following representation.  

Analyzing the above report, one can say the following:

  • The chance of meeting the estimated planned budget of $19.123 is hardly 11%.
  • To have a 50% chance the budget has to go up and it has to be $19,679 M. In order to have 100% chance, the budget has to be $22, 249 M.
  • Do note that the risks are included in the analysis and they are also impacting the final cost.

Conclusion

As we just learned, any kind of O&G project can be used for risk analysis using the Primavera Risk Analysis software tool. This is a special and advanced project with only the cost aspects. For duration too, the PRA software can be used.

You need not have any apprehension about it. If you are keen to learn detailed and end-to-end risk management with a software tool, then Practical RMP course is the right fit for you.


References

[1] Online Course: Practical RMP with Primavera Risk Analysis, by Satya Narayan Dash. 

[2] eBook: I Want To Be A RMP, 2nd Edition (Updated), by Satya Narayan Dash.



Friday, June 21, 2024

Planned Vs. Actual Percent Complete – Building A Solid Histogram Report with MS Project


In the earlier article, we learned the Format () function. This function is used to format the Planned % Numbers given when you plan, baseline and track your project. In this article, we will learn how to build a histogram report using the available functionality in MS Project. 

As noted in the previous article, your stakeholders will not see the data, but the report. Hence, it’s important that you learn it well. 

Current Project Case

The current project is very similar to one we have had earlier in the article of Planned and Actual Percent Complete with MS Project, with a slight modification. 

As shown above:

  • Under Phase – 1, 
    • Work Packages A1, B1 and C1 are 100% complete – both planned and actual. 
    • Work Package D1 has been delayed and it’s actually 33% complete, though per plan, it should have been completed.  
    • Phase – 1 end milestone is 50% complete.
  • Under Phase – 2, 
    • Work Packages A2 and B2 are 75% and 50% complete, respectively. Ideally, per plan, they should have been completed.
    • Work Package C1 started late when compared with the baseline (baseline date is shown in the left column) and it’s actually 14% complete, though per plan, it should be 20% completed.  

It’s important to note that the status date is set as September 15, 2025 (9/15/25). It’s highlighted above. 

At this stage, I’d strongly recommend that you read the article of Planned Vs. Actual Percent Complete to get the best value out of this article. 

Create A Fresh Histogram

We will start to create a histogram by going to the Report tab > New Report > Chart, as shown below. 


It’ll pop-up a message to give a name to the report. Provide the name as “Planned Vs Actual - Histogram Report”. A dummy report will be auto-created. You can change the name to “Planned Vs. Actual Percent Complete - Histogram Report” to have more clarity. 

 


As shown, we have three fields in the above dummy report – Actual Work, Remaining Work and Work. We will remove these fields and have out fields (Number 3 and % Complete) added to it. 

Remove the Default Fields in the Histogram

To remove the fields, select the Chart Area, right click and choose “Show Field List” command. Next from the Field List pane, select the fields and remove them by using “Remove Field” command. You can also deselect the fields from fields available under the “Work” section. 

Add Our Fields into the Histogram

Now that we have removed the fields, we have to add our own custom fields. We will add two fields:

  • Number3 custom field: It’ll be available under Number > Custom.
  • % Complete field: It’s available under Number. 

Both of the above fields are Task related fields. With it, the histogram will be created by taking Number3 and % Complete field.  

And don’t forget to change the Outline Level to Level 3 from Level 1. Because our work packages are at Level 3!

Next, change the labels of these fields to be displayed on the report. Do the following changes:

  • Number3 will be changed to Planned % Complete.
  • % Complete will be changed to Actual % Complete.

This can be done by selecting the respective field, right clicking and choosing “Field Settings”. In the popped-up message, provide the labeling. This is shown below. 


Add and Format the Data Labels

Next, we add the data labels to the columns of the histogram. There are two columns for the two fields of “Planned % Complete” and “Actual % Complete”. These are represented in blue and orange color, respectively. For these we have the data labels, i.e., the % value. 

To add the data labels for the columns in the histogram, select any column, right click and choose “Add Data Labels” command. This should be done for both types of columns. Once you are done, the report will come as shown below. 

As you can see above, there are data labels added for both Planned % Complete and Actual % Complete. But for the former, the % notation next to the number is missing. For this purpose, we will provide the formatting. To do so, select the Planned % Complete Data Labels, right click and choose “Format Data Labels” command.

In the shown Format Data Labels pane (below), select the Label options and then select the Custom category for the Number. This is shown below. 

It’s important to note that the category of label here is custom and type given is “#,##0%”. This way the proper formatting will happen for the labels with respect to the Planned % Complete field, which is shown below.


As shown above, the formatting for Planned % Complete field is clearly shown with % in the notations for the blue colored columns in the histogram. 

In addition, we have to change the data series overlap and gap width so that there is a clearer visualization. The data series are with respect to the columns. To do so, select the Planned % Complete Data Labels, right click and choose the “Format Data Series” command. Then provide the needed value, which is shown below.

Isn’t much clearer now than before?

Change the Color Coding 

Our next and final step is to change the colors for the columns in the histogram. For the Planned % Complete column of the histogram, I’ve taken green color, whereas for the Actual % Complete I’ve taken the purple color. This can be done by going to the Task tab > Font group > Theme colors command.  

In addition, we will also make the labels bold and respective color coded. I’ll also change the rotation of the X-axis to 45%, which can be done by selecting the X-axis (the work package notations), right click and choosing the Format Axis option. 

As shown above, the custom angle given is -45 degree. 

Final Touches

Our final histogram comes as shown below. 

 


This can be then transported to a PDF file, an image file or a power point presentation. For example, one can use the export to PDF function of MS Project using the backstage view. 

 

When exported to the PDF, we will have the following view. 


Last Words

That’s it! It might look like a number of steps, but if you go through sincerely and practice a few times, you can easily create this report in a matter of minutes. 

MS Project is a complex tool, but comes with a lot of powerful functionalities. The reporting functionalities given are quite good and you can use them to generate the report for Planned Vs. Actual % Complete. 

I receive a number of questions on the creation of this histogram. I hope this article helps you to prepare the needed histogram anytime in your project planning and tracking. 


References:

[1] Online Video Course: MS Project Live Lessons, Guaranteed Learning or Your Money Back 

[2] Article: Understanding Planned Vs. Actual Percent Complete with MS Project

[3] Article: Planned Vs. Actual Percent Complete – Understanding the Format () Function in MS Project



Tuesday, June 11, 2024

Book Review: I Want To Be A PfMP – A Definitive Guide for PfMP Certification

By John P S Oliver, PfMP, PgMP, PMP, PMI-RMP, PMI-ACP, PMI-PBA, PMI-SP



Why this Book?

The PfMP Exam Prep Book, I want To Be A PfMP, is a result of the author’s knowledge and experience in the project, program and portfolio management domain. This book covers the entire portfolio management topics in a simple and practical manner so that any portfolio management practitioner or anyone aspiring to practice portfolio management would find it easy to absorb the concepts and practice it in real world scenarios.

This book along with the practice standard for portfolio management is a one stop solution to ace your PfMP exam. The author has covered concepts from all reference books for the PfMP exam to give us a single source material for the PfMP exam. 

This book comes with sample chapter end questions along with a full set of exam level questions and various financial calculations and formulas required for the exam.


PfMP Book – I Want To be A PfMP

The key aspect of this book is that it maps each of the knowledge areas /processes to the PfMP exam content outline tasks which helps the readers to understand context and ultimately help them answering the questions in the exam. 

The other distinct feature of the book is the vision and revision tips for each significant topic. This helps the reader to remember and review the important points. 

Another highlight of this book is the way in which the process interactions have been explained graphically, for every knowledge area. This method simplifies the complex interactions and makes it very useful to grasp the knowledge of how the various processes interact with one another which is not available in most of the other books.

A distinct feature of this book is the availability of videos for Risk Response Strategies, Monte Carlo Analysis, EVM and Portfolio Management Process interactions. These videos help the learner in understanding the concepts effectively.

The various snapshots using MS Project and Primavera provide the learners with real life utilities in applying the concepts.

The other prime feature of this book is calling out the ITTO’s for each process and explaining their relevance. For example, the same tool or technique is used in different ways in different processes. Since most of the questions from the PfMP exam are based on ITTO’s, this understanding is essential to get the right answer in your PfMP exam.

The chapter end questions provide you with an opportunity to revise your knowledge of the chapters while the full-length question set helps you prepare for the certification exam. 

Overall this book is indispensable for aspiring PfMPs to get certified in the first attempt.


Chapters in the Book – I Want To be A PfMP

Chapter 1 – Welcome: This chapter provides you a sneak peek of the contents of the book while setting the context for each chapter. This is a good place to start as it helps you to understand how to use this book along with the Standard for Portfolio Management v3 and Exam Content Outline. This chapter also provides you information on the PfMP exam which is invaluable for your preparation.

Chapter 2 – Introduction: As the title suggests, this chapter introduces us to Portfolio, Portfolio, Program, Project and Operations Management. The main highlight of this chapter is that it simplifies some crucial concepts of Portfolios, Programs, and Projects in layman’s terms for our understanding. This understanding is crucial to acing your PfMP exam.

Chapter 3 - Portfolio Management and Organization: This chapter talks about the various life cycles, stakeholders, roles and responsibilities of portfolio management and the common inputs and outputs of portfolio management processes. The various life cycles and their differences are explained in a simple manner which is easy to understand.

Chapter 4 - Portfolio Management Process Groups: This chapter covers the portfolio management process groups, knowledge areas and the processes. The author has done a wonderful job of explaining the sequence and interaction of the 16 processes across the process groups and knowledge areas which is the highlight of this chapter. This sequencing acts as a process map / work flow of the processes for portfolio management. 

There are also videos explaining the interaction of the processes. This helps the reader in understanding how the processes work within the portfolio management process groups and knowledge areas which is important from the PfMP exam point of view.

Chapter 5 - Portfolio Strategic Management: This is the first knowledge area in the portfolio management and the author explains the 4 processes of this knowledge area in a graphical manner which is easy to understand. This chapter also calls out the ITTO’s of the processes and provides the contents of each key deliverables like the portfolio strategic plan, portfolio charter and portfolio roadmap. These are very important points in the context of the exam.

Chapter 6 - Portfolio Governance Management: The highlight of this chapter is the flow chart of Portfolio Component states and the explanation of various tools and techniques which are important for the exam. The various portfolio component states help us understand the processes and the resultant output clearly.

Chapter 7 - Portfolio Performance Management: This chapter provides critical information on performance management of the portfolio including the KPIs and the various tools and techniques used to measure the KPIs. You can expect a good amount of questions based on EVM metrics in the exam.

Chapter 8 - Portfolio Communication Management: Here the author has talked about the importance of communication in portfolio management. This chapter provides information on various communication terms like communication models and communication methods. One other important aspect covered in this chapter is the stakeholder identification, analysis and classification for communication.

Chapter 9 - Portfolio Risk Management: This chapter delves into the portfolio risk management knowledge area and its processes. It talks about the sources and types of risks, categories of risks and the various tools and techniques used in the processes. 

Chapter 10 - Portfolio Management Financials, Charts and Calculations: This chapter covers the various financial calculations, charts and formulas used in the portfolio management domains.

The last 2 chapters provide information on the PfMP credential including the qualifications required and the process for applying and getting certified.


Brief Profile:

Name: John P S Oliver, PfMP, PgMP, PMP, PMI-RMP, PMI-ACP, PMI-PBA, PMI-SP

Current Role: Lead Business Accountability Specialist

Brief experience: 25+ years of experience in Operations, Project, Program and Risk management across Healthcare, Retail, Telecom and BFSI verticals.



New Book Available for PfMP Exam:
You may also like:

Saturday, June 08, 2024

Planned Vs. Actual Percent Complete–Understanding the Format () Function in MS Project


One of the most read articles in this website is Planned and Actual Percent Complete with MS Project. I periodically receive questions on it as MS Project practitioners need this functionality. By default, the ‘% complete’ (Actual % Complete) field is available in MS Project software, but not the Planned % Complete. 

The Problem

Now, while data is needed, your stakeholders would like to see the reports – most likely in a histogram. I realized it’s easier said than done for many as they struggle to format the field in certain special situations and hence their report doesn’t come properly. For example, when you add a special task into the already baselined plan, and try to determine the Planned and Actual % complete, you will get the following numbers.


As shown:

  • I’ve added a new task: Special Task of 2 days duration.
  • When tracked, not so readable numbers of 68.8888888888889% is coming for the Top Summary Task and 36.3636363636364% is coming for the Phase – 2 Summary Task. 

The above numbers are not properly readable and hence, won’t be visualizable with our histogram report. You can see the above numbers in this video at 4m:37s.

Current Formula Used

The formatting given for Number 3 with cStr () function, doesn’t help much as this what the cStr () function does according to MS Project custom fields in Project Desktop

CStr

Coerces an expression to data type String.

Syntax

CStr( expression )

expression  Any valid string or numeric expression.

In our case, I’ve concatenated the “%” into the ‘Number 3’ custom field and have this expression. It’s noted as: 

Text1: cStr ([Number3] & “%”)

The Format () Function

The Format () function available for MS Project custom fields is quite useful in this scenario. In this article, we will understand more of it. In an upcoming article, I’ll show how to build the Planned Vs. Actual % Complete in a histogram.

Again, the format function is noted in this link of project functions for custom fields for MS Project. While it’s noted for MS Project 2019, these fields and functions are applicable for later versions of MS Project.

For the Format () function, the documentation notes the following.

Syntax

Format( expression[, format[, firstdayofweek[, firstweekofyear]]] )

expression  Required; any valid expression.

format  Optional; a valid named or user-defined format expression.

firstdayofweek  Optional; a Constant that specifies the first day of the week.

firstweekofyear  Optional; a Constant that specifies the first week of the year.

It’s clearly saying that the Format () function must have a valid expression. It's followed by a ‘format”, which is optional and it can be user-defined format. The final two – firstdayofweek and firstweekofyear – are optional. 

Examples of Format () Function

Let’s take some examples to understand as there is no better way to learn! As shown below, I’ve two custom fields:

  • Number1 – A number custom field. There is no formula given for this field. 
  • Formatted Number1 – A text custom field to have the formatted expression for Number1. The formula given for this custom field is Format([Number1],"#0.000"). It tells to format the Number1 with user defined format of ‘#0.000’.

Do note that I’m directly using the Gantt Chart view of MS Project software. 

In the Number1 custom field, I’ll enter a variety of numbers (positive, negative, with decimals etc.) and the formatted expression will be auto-populated in the Formatted Number1 custom field.

Next, as I enter the numbers, the formatted expression of these numbers is shown in the next column. This is depicted below.


Let’s understand the above formatting:

For 23, it returns 23.000. In other words, Format (23, “#0.000”) returns 23.000.

  • Format (45.55, “#0.000”) will return 45.550.
  • Format (3.35, “#0.000”) will return 3.350.
  • Format (-4, “#0.000”) will return -4.000.
  • Format (-5.76, “#0.000”) will return -5.760.
  • Format (0, “#0.000”) will return 0.000.

You’d have understood why that is the case. The Format () function is taking the number and formatting with “#0.000” expression. 

  • The ‘#’ in the expression, before the decimal point, denotes any number.
  • The ‘0’ in the expression, before the decimal point, is specifically to include the zero. If this is not given, then Format (0, “#.000”) with only a “#” will return ‘.000’. Note that a zero is missing before the decimal. We don’t want that! Do we?
  • The three zeroes, after the decimal, results in the expressions including 3 points after the decimal. If you have four zeros, then it will have 4 points after the decimal.

Other Styles of Formatting

In our previous example, we used Format([Number1], “#0.000”) with the user-defined format expression of ‘#0.000’.  Others can be:

  • String = Format(177.8, "###0.00")  will return “177.80 ".
  • String = Format(4499.7, "##,##0.00") will return "4,499.70".
  • String = Format(9, "0.00%") will return "900.00%".

Interpreting the above formatted expressions:

  • In the first case, we have three hashes (#) followed with a zero (0), before the decimal and two zeros after the decimal. We received the corresponding formatted number.
  • In the second case, we have a comma before the decimal and hence, a comma is included in the formatted output string. 
  • The last case is interesting to note! We don’t have any hash before the decimal, and we have a “%” included and hence, the resulting formatted expression has been multiplied by 100!

Solution to Our Problem

Remember the first problem we started with the for Planned Vs. Actual Percent Complete? 

Now, I believe, you can address the problem. We have to simply change the formatting of the Number3 custom field. The formula used will be the following:

Planned % Complete = Format([Number3],"#0.00") & cStr("%") 

I’ve additionally used the cStr (“%”) to display the % notation next to the number. In the custom field it will be used as shown below.

Next, when you apply the above formula, the value will come properly as shown below.

As shown above:

  • Now, for the top summary task, instead of 68.8888888888889%, we have 68.89% being shown.
  • For the Phase – 2 summary task, instead of 36.3636363636364%, we are having 36.36%

Aren’t these numbers more readable?

Conclusion

There are a number of custom fields and functions available in MS Project software. I'd definitely suggest you keep this link handy, if you want to know more on custom fields and working on them. You can also read the following two foundational articles:

The Format () function is quite helpful if you are using MS Project custom fields. As we learned in this article, this function can format any type of number in the way you want.

In addition, with this Format () function we will have a better report, which we are going to see in the next article.

References:

[1] Online Video Course: MS Project Live Lessons, Guaranteed Learning or Your Money Back

[2] Article: Understanding Planned Vs. Actual Percent Complete with MS Project