Reporting Earned Value vs Planned Value for SPI Metrics

PM_Mr Bill

Good afternoon everyone,

I'm new to the SmartSheet community, but my company and I have been integrating SS into our PM work processes for about 1.5 years. We're a VOSB DOD contractor, and we use SS to create simple project schedules that include start/end dates, duration, dependencies, and % complete. We do not resource load (no labor hours/employee or $$ values for ODCs & materials).

My challenge now is to take simple project schedules and use them to track and report % complete in terms of EV vs. PV to generate a basic schedule performance index (SPI) for primary project milestones ("parent tasks").

I'd like our SS project schedules to include columns that calculate EV, PV and display schedule variance for a given report date. 

I've come up with a formula in Excel that produces the desired metrics, but I'm curious to know if SS has a "canned" formula solution. If not I'd like to know how others may have handled such a requirement.

Any takers?

L_123

If you post the excel formula or describe it in more depth people might be able to help you. No one can really help without understanding exactly what you are trying to do.

PM_Mr Bill

Luke,

Thanks for asking.  As I understand, SmartSheet already calculates parent task % complete based on roll up of children tasks (% complete x duration). What I don't have is the planned value at any point in time.

The formula calculates planned value as a condition of the Report Date relative to children tasks' Start and End dates:

-If task Start is in the future the current PV = 0.

-If the task End is in the past the PV is simply the task duration.

-If the report date is in the middle of a task, the PV is the prorated task duration where ((End - Start)/Duration) * Duration = PV for any point in time.

The formula is:

=IF(AND($M$1>$F6),IF($G6>$M$1,SUM(($M$1-$F6)/($G6-$F6))*$E6,1*$E6),0)

where

M1 = Report Date

E6 = Task Duration (days)

F6 = Start Task

G6 = End Task

Does this explain the scenario better? The formula works in my SS export into Excel. I add the EV, PV and SPI columns and formulas in Excel but I can't figure out how to produce the same results in SmartSheet itself.

L_123

The sum formula is incorrectly used for smartsheet. You don't even need to replace it, just get rid of it. Also, get rid of the AND. You have a nested if statement, that is an AND in itself. IF A is true then IF B is true, C, D. 

TKh

I am trying to calculate schedule performance index SP- EV/PV by using start date and end date. Is using start and end date to calculate SP a correct approach?