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

Return a value based on 2 matching criteria, one of which is a dropdown menu.
I am working on a formula that will match a date from my reference sheet to my working sheet, and a word from a dropdown menu in a reference sheet and return the word "Completed". None of these work
Date Function Returning #INVALID COLUMN VALUE - even in clean new sheets
Hi all. I’m running into an issue that I can’t seem to resolve, even after a lot of testing. Hoping someone here might be able to help. Basically, any formula I use that involves a date function, like ISDATE() or TODAY(), is returning #INVALID COLUMN VALUE — even in completely new, empty sheets. Here’s what I’ve tested so…
Formula Help - Phase identifying
Good morning :) I'm trying to figure a way that the row is labeled by the phase of the project that the line is in without having to manually do it. Please see below expected output for 2 phases. I have also attached an excel sheet. I would greatly appreciate any help on this please.