% Complete versus % Planned

Hi!

I am trying to have two columns in my sheet: The original "% Complete" one and a "% Planned" one.

It will help me check if my projects health is ok, by meaning if its percentage is above, equal or under the percentage of the project that was estimated for that date.

I tried inserting a regular text/number column with % formatting, but parenting tasks does not sum correctly the percentage of its children tasks (because it has to consider the work for each one, is not a simple average).

Anyone here have done something alike?

Thanks!

Find more posts tagged with

Sheets

Formulas

Comments

Shaine Greenwood

Hi Natasha,

There isn't a way to have Smartsheet automatically roll up calculations of the % complete of more than one column. To get the effect you want, you'll have to create a formula of some kind in the parent row that calculates a weighted percent complete based on the children. Check out our Help Center article for a list of all available functions: https://help.smartsheet.com/articles/775363-using-formulas

Mitch.Shaffer

Natasha,

I use something like this to track task performance.

=IF(OR(State2 = "In Process", State2 = "Awaiting Info", State2 = "Stalled"), ([% Complete]2 / (NETWORKDAYS([Planned Start]2, TODAY()) / Duration2)))

The first half of the formula - IF(OR(State2 = "In Process", State2 = "Awaiting Info", State2 = "Stalled") - refers to a State column so that that performance is only tracked on tasks that have been started. The second half - ([% Complete]2 / (NETWORKDAYS([Planned Start]2, TODAY()) / Duration2))) - divides the % complete by the difference between the planned start date of a task and today's date (think of this like comparing the progress bar to the dashed line on the Gantt chart).

I also like adding a symbol column with the five arrows to graphically represent the results of the formula above.

=IF(ISNUMBER(Tracking2), IF(Tracking2 < 0, "Up", IF(AND(Tracking2 >= 0, Tracking2 < 0.7), "Down", IF(AND(Tracking2 >= 0.7, Tracking2 < 0.9), "Angle Down", IF(AND(Tracking2 >= 0.9, Tracking2 < 1.1), "Sideways", IF(AND(Tracking2 >= 1.1, Tracking2 < 1.3), "Angle Up", IF(Tracking2 >= 1.3, "Up")))))), " ")

CanadaJim

That is a good start, Mitch. However, for standard project reporting of Schedule Performance Index (SPI) you need to be able to calculate planned value versus earned value. You can't necessarily do that at a summary level (start/finish) because the project work could be front-loaded or end-loaded. For example if 80% of the work happens in the 2nd half of the project, your %complete based on date will be wildly inaccurate. If you could measure how much planned work should be complete, that would be the missing piece.

SPI = EV / PV

EV (Earned Value) is the value of the actual work completed (Total budget of project * % of total project work actually complete as of today)

PV (Planned Value) is the value of work expected to be completed (Total budget of project * % of total project work planned to be complete as of today)

Without PV, which is reliant on understanding how much work SHOULD be done by now, you can't calculate SPI.

It would be great if SmartSheet had a "Project Statistics" screen or report where all of this information is calculated automatically. Maybe, Natasha, you could use the Submit Product Enhancement Request link to help get this added. Anyone else interested in this capability should also make the request so that the product team knows how to prioritize this request.

J. Craig Williams

CanadaJim,

Yes and no (mostly yes).

If I have 4 week task and 50% of the product is expected to be complete in the first week, then there needs to be some metric that can be pointed to that says, yes, we are indeed 50% of the way there. If not, it is all subjective argument.

Assuming you have the metrics, then ideally either you break out those deliverables/milestones/gates into separate sub-tasks and track those.

The roll-up is handled at the parent level and the PV and EV are easier to calculate and measure.

If, on the other hand we are talking about widgets - I need to 100 documents produced and I think I can produce 2 per day ... so 10 weeks, then the PV and EV are calculated based on how many are produced, the person assigned the task just updates when they finish one.

I have widget based system. The PM/team start with total needed, target per day. That calculates duration and expected run-rate. Once started, the employee updates how many are done each day.

The system spits back EV, PV, SV, SPI, actual run-rate, days remaining at current run-rate, % complete, and expected finish date.

Throw in cost and it will also give you AC, CV, and CPI.

Add in Zapier and I move the details elsewhere and get an activity log to build run rates or burn down rates.

It would not be too hard to use a Sight or Power BI dashboard to get your project statistics too.

Craig

CanadaJim

Good points Craig. It works really well for those widget-based activities as you say.

Not so well for cross-functional projects or larger programs, however... No matter how well you break them down, the overall view is hard to "measure" numerically. And at this time I'm restricted in any further expenditures for project management, which leaves Sights and BI out.

I still think it should be a basic feature, as it's a basic component of project management... But that's for SmartSheet to decide. All we can do is submit our requests...

J. Craig Williams

Agreed.

There are some things about project management that are hard and time consuming that shouldn't be.

Craig