Smartsheet Project Data

Column Summary

Title	Brief Description
Cost Health	Indicates overall health of the project from a cost perspective.
Schedule Health	Indicates overall health of the project from a timing/schedule perspective.
GP Health	Indicates overall health of the project from a gross profit perspective.

Column Details

Cost Health

Indicates, as a Red/Yellow/Green traffic light, the cost health for the project. This is based on the overall project CPI as compared to established global performance thresholds. The thresholds are defined in the Performance Parameters Smartsheet.

Formula

=IF(
    [Project ID]@row = [Project ID]#, 
    IF(
        CPI@row >= INDEX({Performance Parameters - Value}, MATCH("CPI-Green", {Performance Parameters - Key}, 0)), 
        "Green", 
        IF(
            CPI@row >= INDEX({Performance Parameters - Value}, MATCH("CPI-Yellow", {Performance Parameters - Key}, 0)), 
            "Yellow", 
            IF(
                CPI@row > INDEX({Performance Parameters - Value}, MATCH("CPI-Red", {Performance Parameters - Key}, 0)), 
                "Red", 
                "Gray"
            )
        )
    ), 
    ""
)

Schedule Health

Indicates, as a Red/Yellow/Green traffic light, the schedule health for the project. This is based on the overall project SPI as compared to established global performance thresholds. The thresholds are defined in the Performance Parameters Smartsheet.

Formula

=IF(
    [Project ID]@row = [Project ID]#, 
    IF(
        ISERROR(SPI@row), 
        "Gray", 
        IF(
            SPI@row >= INDEX({Performance Parameters - Value}, MATCH("SPI-Green", {Performance Parameters - Key}, 0)), 
            "Green", 
            IF(
                SPI@row >= INDEX({Performance Parameters - Value}, MATCH("SPI-Yellow", {Performance Parameters - Key}, 0)), 
                "Yellow", 
                IF(
                    SPI@row > INDEX({Performance Parameters - Value}, MATCH("SPI-Red", {Performance Parameters - Key}, 0)), 
                    "Red", 
                    "Gray"
                )
            )
        )
    ), 
    ""
)

GP Health

Indicates, as a Red/Yellow/Green traffic light, the Gross Profit health for the project. This is based on the project actual revenues and costs and is compared to established global performance thresholds. The thresholds are defined in the Performance Parameters Smartsheet.

Formula

=IF(
    [GP%]@row = 0,
    "Gray",
    IF(
        OR(Type@row = "Secondment", Type@row = "Onselling"),
        IF(
            [GP%]@row < INDEX({Performance Parameters - Value},MATCH("GP-Yellow-SecOn", {Performance Parameters - Key}, 0)),
            "Red",
            IF(
                [GP%]@row >= INDEX({Performance Parameters - Value}, MATCH("GP-Green-SecOn", {Performance Parameters - Key}, 0)),
                "Green",
                "Yellow"
            )
        ),
        IF(
            [GP%]@row < INDEX({Performance Parameters - Value}, MATCH("GP-Yellow", {Performance Parameters - Key}, 0)),
            "Red",
            IF(
                [GP%]@row >= INDEX({Performance Parameters - Value}, MATCH("GP-Green", {Performance Parameters - Key}, 0)),
                "Green",
                "Yellow"
            )
        )
    )
)