Reporting Earned Value vs Planned Value for SPI Metrics

PM_Mr Bill
PM_Mr Bill ✭✭
edited 12/09/19 in Formulas and Functions

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?

Comments

  • L_123
    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.

  • 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
    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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!