Reporting Earned Value vs Planned Value for SPI Metrics

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?

Find more posts tagged with

Construction

Formulas

Government

Reports

Services

IT & Operations

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

#NO Match on a column with a formula
My formula works in my index/match when I type the employee number in, but when it is a formual, it give me #NO MATCH. See below
How do I include field changes in an email (Automation Question)
Hello: I am creating automations for several of my SmartSheet projects. I have successfully created an automation that alerts me when there are changes to any of 5 critical values in a row within my sheet. However, I am trying to determine how to include which of those 5 values changed in the alert email.
Easiest way to vlookup off a file
Hello, I'm new to Smart Sheet. I have an 'intake form' that pulls user-entered data into my Smart Sheet, and one of the fields that is entered I would like to lookup off of a Workspace I uploaded (Excel document). Are there any tips/tricks to help get this done? I linked a cell to it, but then it put a blue arrow and I…