SPI and PLnned Value % in Smartsheet

Cesar Martinez

Hi Everyone, I'm dealing with implementing Earned Value Management in our Project Portfolio as stated in PMI PMBOK guide..

However, I am not sure yet in how to calculate the % of Completion Planned for the whoe project as well for each activity since the new functionality of baseline in projects calculates for each activity the delay days and not the Completion % expected up today.

What i du today is to have a replica sheet of the baseline and I update in that replica the completion butr It involves a lot of manual work and assumptions.

Can you give me a hand?

Julio S.

Hi @Cesar Martinez, 

If you'd like to request functionality to implement baselines to % completed values, when you have a moment, please let our Product team know about your feedback by filling in this form, here. Thank you!

For the time being, you may use formulas based on the

TODAY Function, AVG Function, NETWORKDAYS Function and IFERROR Function to perform the desired calculations. E.g:

• - To calculate the expected % completed up to today's date, =IF(Finish@row <= TODAY(), 1, IFERROR(NETWORKDAYS(Start@row, TODAY()) / NETWORKDAYS(Start@row, Finish@row), 0)) should accomplish it
• - To calculate the overall expected project completion, =AVG([% expected completion]:[% expected completion]) - Note that % expected completion would be the specific field in your sheet that contains the previous formula
• - To calculate actual overall % completed to today's date: =AVG([% Complete]:[% Complete])

These formulas assume that the the Percentage formatting has been applied to the whole column where they are inserted.

You may also want to use Sheet Summaries to include some or all of this project calculations. 

I hope this can be of help. 

Cheers! 

Julio