Project Register

Column Index

Refer to Project Register Column Index.

Column Formulas

Archive Project

Formula

=IF(
    OR(
        [Potential Status]@row = "Declined", 
        AND(
            OR([Potential Status]@row = "Cancelled", [Potential Status]@row = "Lost"), 
            [Proposal Outcome Received (Client Decision) Date]@row 
                < DATE(
                    YEAR(Today@row) - IF(
                        MONTH(Today@row) <= [Project Retaining Period (Months)]#, 
                        1, 
                        0
                    ), 
                    MONTH(Today@row) - [Project Retaining Period (Months)]# + IF(
                        MONTH(Today@row) <= [Project Retaining Period (Months)]#, 
                        12, 
                        0
                    ), 
                    DAY(Today@row)
            )
        ), 
        AND(
            [Project Status]@row = "Complete", 
            [Project Completed Date]@row 
                < DATE(
                    YEAR(Today@row) - IF(
                        MONTH(Today@row) <= [Project Retaining Period (Months)]#, 
                        1, 
                        0
                    ), 
                    MONTH(Today@row) - [Project Retaining Period (Months)]# + IF(
                        MONTH(Today@row) <= [Project Retaining Period (Months)]#, 
                        12, 
                        0
                    ), 
                    DAY(Today@row)
            )
        )
    ), 
    1
)

Archive Trigger

Formula

=[Archive Trigger (For PS Use ONLY)]#

Approver 1*

Formula

=IF(
    [Approver 1 Role*]@row = "Pending Risk Classification", 
    "", 
    IF(
        OR(ISERROR([Approver 1 Role*]@row), [Approver 1 Role*]@row = "NA"), 
        "", 
        IF(
            FIND("Team} Manager", [Approver 1 Role*]@row) = 0, 
            INDEX(
                {Roles Assignment - Assignment}, 
                MATCH([Approver 1 Role*]@row, {Roles Assignment - Role}, 0)
            ), 
            [Portfolio / Primary Resource Team Manager]@row
        )
    )
)

Approver 1 Role*

Formula

=IF(
    [Risk Classification*]@row > 0, 
    INDEX(
        {DoA Limits - Approval Matrix - RA & Approvers}, 
        MATCH([Risk Classification*]@row, {DoA Limits - Approval Matrix - Risk Classification}, 0), 
        2
    ), 
    "Pending Risk Classification"
)

Approver 2*

Formula

=IF(
    [Approver 1 Role*]@row = "Pending Risk Classification", 
    "", 
    IF(
        OR(ISERROR([Approver 2 Role*]@row), [Approver 2 Role*]@row = "NA"), 
        "", 
        IF(
            FIND("Team} Manager", [Approver 2 Role*]@row) = 0, 
            INDEX(
                {Roles Assignment - Assignment}, 
                MATCH([Approver 2 Role*]@row, {Roles Assignment - Role}, 0)
            ), 
            [Portfolio / Primary Resource Team Manager]@row
        )
    )
)

Approver 2 Role*

Formula

=IF(
    [Risk Classification*]@row > 0, 
    INDEX(
        {DoA Limits - Approval Matrix - RA & Approvers}, 
        MATCH([Risk Classification*]@row, {DoA Limits - Approval Matrix - Risk Classification}, 0), 
        3
    ), 
    "Pending Risk Classification"
)

Approver 3*

Formula

=IF(
    [Approver 3 Role*]@row = "Pending Risk Classification", 
    "", 
    IF(
        OR(ISERROR([Approver 3 Role*]@row), [Approver 3 Role*]@row = "NA"), 
        "", 
        IF(
            FIND("Team} Manager", [Approver 3 Role*]@row) = 0, 
            INDEX(
                {Roles Assignment - Assignment}, 
                MATCH([Approver 3 Role*]@row, {Roles Assignment - Role}, 0)
            ), 
            [Portfolio / Primary Resource Team Manager]@row
        )
    )
)

Approver 3 Role*

Formula

=IF(
    [Risk Classification*]@row > 0, 
    INDEX(
        {DoA Limits - Approval Matrix - RA & Approvers}, 
        MATCH([Risk Classification*]@row, {DoA Limits - Approval Matrix - Risk Classification}, 0), 
        4
    ), 
    "Pending Risk Classification"
)

Awarded & Submitted Values [$] Comparison

Formula

=IF(
    [Total Order Value at Award \[$\]]@row <> "", 
    IF(
        ABS([Total Order Value at Award \[$\]]@row - [Total Submitted (Proposed) Value \[$\]]@row) < 5, 
        "Green", 
        "Red"
    ), 
    "Gray"
)

Board Approval Required

Formula

=IF(
    OR(
        [Exposure to Uninsured Liabilities?]@row = "Yes", 
        [Exposure to Consequential Losses?]@row = "Yes", 
        [Exposure to Liquidated Damages?]@row = "Yes", 
        [New Material Risk Introduced?]@row = "Yes", 
        [Management Believe Risk Requiring Board Oversight?]@row = "Yes", 
        [Gross Margin Below Threshold (Non-Secondment)]@row = "Yes", 
        [Predicted Annualised GP Exceeds Threshold]@row = "Yes"
    ), 
    1, 
    0
)

Board Approver

Formula

=INDEX(
    {Roles Assignment - Assignment}, 
    MATCH("Board Representative", {Roles Assignment - Role}, 0)
)

Board Check Questions Complete

Formula

=IF(
    AND(
        [Exposure to Uninsured Liabilities?]@row <> "", 
        [Exposure to Consequential Losses?]@row <> "", 
        [Exposure to Liquidated Damages?]@row <> "", 
        [New Material Risk Introduced?]@row <> "", 
        [Management Believe Risk Requiring Board Oversight?]@row <> ""
    ), 
    1, 
    0
)

Client PO Entity Short Name

Formula

=IF(
    [Client PO Entity]@row <> "", 
    INDEX(
        {SMVO Client - Client PO Entity Short Name}, 
        MATCH([Client PO Entity]@row, {SMVO Client - Client PO Entity}, 0)
    ), 
    ""
)

Commercial Risk Approver

Formula

=INDEX(
    {Roles Assignment - Assignment}, 
    MATCH("Chief Financial Officer", {Roles Assignment - Role}, 0)
)

Complexity Rating*

Formula

=IF(
    [Complexity Score*]@row = 0, 
    0, 
    IF(
        [Complexity Score*]@row = VALUE(INDEX({DoA Limits - Complexity Rating - Score Lower}, 1)), 
        INDEX({DoA Limits - Complexity Rating - Outcome}, 1), 
        IF(
            [Complexity Score*]@row <= VALUE(INDEX({DoA Limits - Complexity Rating - Score Upper}, 2)), 
            INDEX({DoA Limits - Complexity Rating - Outcome}, 2), 
            IF(
                [Complexity Score*]@row <= VALUE(INDEX({DoA Limits - Complexity Rating - Score Upper}, 3)), 
                INDEX({DoA Limits - Complexity Rating - Outcome}, 3), 
                IF(
                    [Complexity Score*]@row <= VALUE(INDEX({DoA Limits - Complexity Rating - Score Upper}, 4)), 
                    INDEX({DoA Limits - Complexity Rating - Outcome}, 4), 
                    IF(
                        [Complexity Score*]@row <= VALUE(INDEX({DoA Limits - Complexity Rating - Score Upper}, 5)), 
                        INDEX({DoA Limits - Complexity Rating - Outcome}, 5), 
                        0
                    )
                )
            )
        )
    )
)

Complexity Score*

Formula

=VALUE(LEFT([Risk - Capability & Experience]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Capability & Experience", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0)
+ VALUE(LEFT([Risk - Schedule & Resource Availability]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Schedule & Resource Availability", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0) 
+ VALUE(LEFT([Risk - Commercial]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Commercial", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0) 
+ VALUE(LEFT([Risk - Technical & Reputational]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Technical & Reputational", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0) 
+ VALUE(LEFT([Risk - Strategic]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Strategic", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0) 
+ VALUE(LEFT([Risk - Cost Contingency]@row, 1)) 
    * IFERROR(
        VALUE(
            INDEX(
                {DoA Limits - Complexity Score - Weightings}, 
                MATCH("Cost Contingency", {DoA Limits - Complexity Score - Factors}, 0), 
                [Complexity Weighting Column*]@row
            )
        ), 0)

Complexity Weighting Column*

Formula

=IF(
    OR(
        [Potential Status]@row = "New", 
        [Potential Status]@row = "Assessing"
    ), 
    1, 
    2
)

Contract or PO #(s)

Formula

=JOIN(
    COLLECT(
        {PO Register - Reference}, 
        {PO Register - Project ID}, [Project ID]@row
    ), 
    ", "
) + IF(
        AND(
            ISERROR(MATCH([Project ID]@row, {PO Register - Project ID}, 0)), 
            ISERROR(MATCH([Project ID]@row, {PO Archive - Project ID}, 0))
        ), 
        ", "
) + JOIN(
    COLLECT(
        {PO Archive - Reference}, 
        {PO Archive - Project ID}, [Project ID]@row
    ), 
    ", "
)

Disable Workflows

Formula

=IF([Workflow Automation]# = "Enabled", 0, 1)

Formal Risk Assessment Requirement*

Formula

=IF(
    INDEX(
        {DoA Limits - Approval Matrix - RA & Approver}, 
        MATCH([Risk Classification*]@row, {DoA Limits - Approval Matrix - Risk Classification}, 0), 
        1
    ) = "Y", 
    1
)

Get %

Formula

=IF(
    OR(
        ISNUMBER([Get % Gated]@row), 
        [Get % Gated]@row = ""
    ), 
    [Get % Gated]@row, 
    VALUE(LEFT([Get % Gated]@row, FIND("%", [Get % Gated]@row) - 1)
) / 100)

Go %

Formula

=IF(
    OR(
        ISNUMBER([Go % Gated]@row), 
        [Go % Gated]@row = ""
    ), 
    [Go % Gated]@row, 
    VALUE(LEFT([Go % Gated]@row, FIND("%", [Go % Gated]@row) - 1)
) / 100)

Go-Get %

Formula

=[Go %]@row * [Get %]@row

Gross Margin Below Threshold (Non-Secondment)

Formula

=IF(
    [Predicted Gross Margin % \[Decimal\]]@row <> "", 
    IF(
        AND(
            Category@row <> "Secondment", 
            [Predicted Gross Margin % \[Decimal\]]@row < {DoA Limits - Gross Margin Threshold} * 100
        ), 
        "Yes", 
        "No"
    ), 
    ""
)

GTE Engineering Manager

Formula

=INDEX(
    {Roles Assignment - Assignment}, 
    MATCH("Engineering Manager", {Roles Assignment - Role}, 0)
)

Internal ID

Formula

=IF(
    Client@row = "GTE", 
    1009 + SUM(
                COUNTIFS(
                    Client:Client, "GTE", 
                    Key:Key, @cell <= Key@row
                ), 
                COUNTIF({PR Archive - Client}, "GTE")
            ),
    ""
)

Invoiced to Date Across All Lump Sum POs [$]

Formula

=IFERROR(
    SUMIFS(
        {Invoice Register - Net}, 
        {Invoice Register - Project ID}, [Project ID]@row, 
        {Invoice Register - Project Type}, "Lump Sum"
    ) + SUMIFS(
        {Invoice Archive - Net}, 
        {Invoice Archive - Project ID}, [Project ID]@row, 
        {Invoice Archive - Project Type}, "Lump Sum"
    ), 
    ""
)

Invoiced to Date Across All POs [S]

Formula

=IFERROR(
    IF(
        [Invoiced to Date Across All Reimbursable POs \[$\]]@row + [Invoiced to Date Across All Lump Sum POs \[$\]]@row > 0, 
        [Invoiced to Date Across All Reimbursable POs \[$\]]@row + [Invoiced to Date Across All Lump Sum POs \[$\]]@row, 
        SUMIFS(
            {Invoice Register - Net}, 
            {Invoice Register - Project ID}, [Project ID]@row
        ) + SUMIFS(
            {Invoice Archive - Net}, 
            {Invoice Archive - Project ID}, [Project ID]@row
        )
    ), 
    ""
)

Invoiced to Date Across All Reimbursable POs [$]

Formula

=IFERROR(
    SUMIFS(
        {Invoice Register - Net}, 
        {Invoice Register - Project ID}, [Project ID]@row, 
        {Invoice Register - Project Type}, "Rates"
    ) + SUMIFS(
        {Invoice Archive - Net}, 
        {Invoice Archive - Project ID}, [Project ID]@row, 
        {Invoice Archive - Project Type}, "Rates"
    ), 
    ""
)

Net Labour GP%

Formula

=IFERROR(
    IF(
        OR(
            Category@row = "Onselling", 
            Category@row = "Secondment"
        ), 
        (
            INDEX(
                {Xero Data Sales Labour - To Date}, 
                MATCH([Project ID]@row, {Xero Data Sales Labour - Project ID}, 0)
            ) - INDEX(
                {Xero Data COS Labour - Costs to Date}, 
                MATCH([Project ID]@row, {Xero Data COS Labour - Project ID}, 0)
            )
        ) / INDEX(
            {Xero Data Sales Labour - To Date}, 
            MATCH([Project ID]@row, {Xero Data Sales Labour - Project ID}, 0)
        ), 
        (
            INDEX(
                {Xero Data Sales Labour - To Date}, 
                MATCH([Project ID]@row, {Xero Data Sales Labour - Project ID}, 0)
            ) - INDEX(
                {Xero Data COS Labour - Costs to Date}, 
                MATCH([Project ID]@row, {Xero Data COS Labour - Project ID}, 0)
            )
        ) / INDEX(
            {Xero Data Sales Labour - To Date}, 
            MATCH([Project ID]@row, {Xero Data Sales Labour - Project ID}, 0)
        ) - 0.27
    ), 
    ""
)

Portfolio / Primary Resource Team Manager

Formula

=IF(
    [Portfolio / Primary Resource Team]@row <> "", 
    INDEX(
        {Roles Assignment - Assignment}, 
        MATCH([Portfolio / Primary Resource Team]@row + " Manager", {Roles Assignment - Role}, 0)
    ), 
    [GTE Engineering Manager]@row)

Potential Bid/Decline Recommendation Approval

Formula

=IF(
    [Bid/Decline Recommendation Approval(s) Status]@row = "Approved", 
    "Yes", 
    IF(
        [Bid/Decline Recommendation Approval(s) Status]@row = "Rejected", 
        "No", 
        IF(
            [Bid/Decline Recommendation Approval 1 Status]@row <> "", 
            "Hold", 
            ""
        )
    )
)

Potential Status

Formula

=IF(
    [Proposal Outcome]@row <> "", 
    [Proposal Outcome]@row, 
    IF(
        [Potential Bid/Decline Recommendation Approval]@row = "Yes", 
        IF(
            [Proposal Manager Bid/Decline Recommendation]@row = "Decline", 
            "Declined", 
            IF(
                [Proposal Submitted Date]@row <> "", 
                "Submitted/Pending", 
                "Preparing"
            )
        ), 
        IF(
            [Proposal Manager Bid/Decline Recommendation]@row <> "", 
            "Assessing", 
            "New"
        )
    )
)

Procurement

Formula

=INDEX(
    {Roles Assignment - Assignment}, 
    MATCH("Procurement", {Roles Assignment - Role}, 0)
)

Project Administration

Formula

=INDEX(
    {Roles Assignment - Assignment}, 
    MATCH("Project Administration", {Roles Assignment - Role}, 0)
)

Predicted Annualised GP Exceeds Threshold

Formula

=IFERROR(
    IF(
        (
            [Total Submitted (Proposed) Value \[$\]]@row * [Predicted Gross Margin % \[Decimal\]]@row) 
                / ([Project Expected Finish Date]@row - [Project Expected Start Date]@row
        ) > {DoA Limits - Annualised Gross Profit Threshold ($)}, 
        "Yes", 
        "No"
    ), 
    ""
)

Project Awarded Date

Formula

=IF(
    [Proposal Outcome]@row = "Won", 
    [Proposal Outcome Received (Client Decision) Date]@row, 
    ""
)

Project Code, Client and Name

Formula

="P" + [Project ID]@row + "_" + Client@row + "-" + [Project Name]@row

Project ID

Formula

=IF(
    [Internal ID]@row <> "", 
    [Internal ID]@row, 
    IF(
        [ID Helper]@row = "", 
        VALUE(Key@row) + 1691, 
        [ID Helper]@row
    )
)

Project Name Issues

Formula

=IF(
    CONTAINS(" ", [Project Name]@row),
    IF(
        LEN([Project Name]@row) > 22, 
        "Contains spaces and exceeds 22 characters", 
        "Contains spaces"
    ), 
    IF(
        LEN([Project Name]@row) > 22, 
        "Exceeds 22 characters", 
        "No issues"
    )
)

Project Status

Formula

=IF(
    [Potential Status]@row <> "Won", 
    "", 
    IF(
        [Project Completed Date]@row <> "", 
        "Complete", 
        IF(
            [Project On-Hold?]@row = 1, 
            "On-Hold", 
            "Active"
        )
    )
)

Project Value for Classification Rating*

Formula

=IF(
    Stage@row = "Project", 
    [Total Order Value at Award \[$\]]@row, 
    IF(
        [Total Submitted (Proposed) Value \[$\]]@row = "", 
        [Initial Project Estimated Value \[$\]]@row, 
        [Total Submitted (Proposed) Value \[$\]]@row
    )
)

Project Value Rating*

Formula

=IF(
    [Project Value for Classification Rating*]@row <= 0, 
    0, 
    IF(
        [Project Value for Classification Rating*]@row <= VALUE(INDEX({DoA Limits - Value Rating - Upper}, 1, 1)), 
        1, 
        IF(
            [Project Value for Classification Rating*]@row <= VALUE(INDEX({DoA Limits - Value Rating - Upper}, 1, 2)), 
            2, 
            IF(
                [Project Value for Classification Rating*]@row <= VALUE(INDEX({DoA Limits - Value Rating - Upper}, 1, 3)), 
                3, 
                IF(
                    [Project Value for Classification Rating*]@row <= VALUE(INDEX({DoA Limits - Value Rating - Upper}, 1, 4)), 
                    4, 
                    IF(
                        [Project Value for Classification Rating*]@row <= VALUE(INDEX({DoA Limits - Value Rating - Upper}, 1, 5)), 
                        5, 
                        0
                    )
                )
            )
        )
    )
)

Proposal Approval

Formula

=IF(
    [Proposal Approval(s) Status]@row = "Approved", 
    "Yes", 
    IF(
        [Proposal Approval(s) Status]@row = "Rejected", 
        "No", 
        IF(
            [Proposal Approval 1 Status]@row <> "", 
            "Hold", 
            ""
        )
    )
)

Proposal Preparation Budget Guidance [hrs]

Formula

=IF(
    [Initial Project Estimated Value \[$\]]@row < 1000000, 
    ROUNDUP([Initial Project Estimated Value \[$\]]@row / 10000, 0), 
    (100 + ROUNDUP(([Initial Project Estimated Value \[$\]]@row - 1000000) / 100000, 0))
)

Rebate Check Complete

Formula

=IF(
    OR(
        [Discounting?]@row = "Yes", 
        [Equipment Included?]@row = "Yes", 
        [Subcontracting or Reimbursable Expenses?]@row = "Yes"
    ), 
    "Rebate Potentially Exempt", 
    IF(
        [None of the Above Apply]@row = "Yes", 
        "Rebate Allowed", 
        ""
    )
)

Risk Classification*

Formula

=IF(
    [Complexity Rating*]@row * [Project Value Rating*]@row = 0, 
    "", 
    INDEX(
        {DoA Limits - Classification Matrix}, 
        [Complexity Rating*]@row, 
        [Project Value Rating*]@row
    )
)

Risk Classification @Potential

Formula

=IF(
    [Complexity Rating @Potential]@row * [Project Value Rating @Potential]@row = 0, 
    "", 
    INDEX(
        {DoA Limits - Classification Matrix}, 
        [Complexity Rating @Potential]@row, 
        [Project Value Rating @Potential]@row
    )
)

Risk Classification @Proposal

Formula

=IF(
    [Complexity Rating @Proposal]@row * [Project Value Rating @Proposal]@row = 0, 
    "", 
    INDEX(
        {DoA Limits - Classification Matrix}, 
        [Complexity Rating @Proposal]@row, 
        [Project Value Rating @Proposal]@row
    )
)

Stage

Formula

=IF(
    OR(
        [Project Status]@row = "Complete", 
        [Potential Status]@row = "Declined", 
        [Potential Status]@row = "Cancelled", 
        [Potential Status]@row = "Lost"
    ), 
    "Closed", 
    IF(
        [Project Status]@row <> "", 
        "Project", 
        IF(
            OR(
                [Potential Status]@row = "Submitted/Pending", 
                [Potential Status]@row = "Preparing"
            ), 
            "Proposal", 
            "Potential"
        )
    )
) 

Today

Formula

={Update Sheet - Today}

Total Approved Change Budget [$]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 0
    ), 
    COLLECT(
        {PBR - Expense [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 0
    ), 
    COLLECT(
        {PBR - Procurement [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 0
    )
)

Total Approved Change Budget [hrs]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [hrs]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 0
    )
)

Total Budget [$]

Formula

=[Total Budget at Award \[$\]]@row + [Total Approved Change Budget \[$\]]@row

Total Budget [hrs]

Formula

=[Total Budget at Award \[hrs\]]@row + [Total Approved Change Budget \[hrs\]]@row

Total Budget at Award [$]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 1
    ), 
    COLLECT(
        {PBR - Expense [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 1
    ), 
    COLLECT(
        {PBR - Procurement [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 1
    )
)

Total Budget at Award [hrs]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [hrs]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Approved", 
        {PBR - Initial Baseline?}, 1
    )
)

Total Order Value [$]

Formula

=[Total Order Value at Award \[$\]]@row + [Total Variation Value \[$\]]@row

Total Order Value [$] - Lumpsum Component

Formula

=SUMIFS(
    {PO Register - Lumpsum Net Component ($)}, 
    {PO Register - Project ID}, [Project ID]@row
) + SUMIFS(
    {PO Archive - Lumpsum Net Component ($)}, 
    {PO Archive - Project ID}, [Project ID]@row
)

Total Order Value [hrs]

Formula

=[Total Order Value at Award \[hrs\]]@row + [Total Variation Value \[hrs\]]@row

Total Order Value at Award [$]

Formula

=SUMIFS(
    {PO Register - Initial PO Value ($)}, 
    {PO Register - Project ID}, [Project ID]@row
) + SUMIFS(
    {PO Archive - Initial PO Value ($)}, 
    {PO Archive - Project ID}, [Project ID]@row
)

Total Order Value at Award [hrs]

Formula

=SUMIFS(
    {PO Register - Initial PO Value (hrs)}, 
    {PO Register - Project ID}, [Project ID]@row
) + SUMIFS(
    {PO Archive - Initial PO Value (hrs)}, 
    {PO Archive - Project ID}, [Project ID]@row
)

Total Requested Change Budget [$]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Requested", 
        {PBR - Initial Baseline?}, 0
    ), 
    COLLECT(
        {PBR - Expense [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Requested", 
        {PBR - Initial Baseline?}, 0
    ), 
    COLLECT(
        {PBR - Procurement [$]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Requested", 
        {PBR - Initial Baseline?}, 0
    )
)

Total Requested Change Budget [hrs]

Formula

=SUM(
    COLLECT(
        {PBR - Labour [hrs]}, 
        {PBR - Project ID}, [Project ID]@row, 
        {PBR - Budget Status}, "Requested", 
        {PBR - Initial Baseline?}, 0
    )
)

Total Requested Variation Value [$]

Formula

COMING SOON

Total Requested Variation Value [hrs]

Formula

COMING SOON

Total Variation Value [$]

Formula

=SUMIFS(
    {PO Register - Total Variation Value ($)}, 
    {PO Register - Project ID}, [Project ID]@row
) + SUMIFS(
    {PO Archive - Total Variation Value ($)}, 
    {PO Archive - Project ID}, [Project ID]@row
)

Total Variation Value [hrs]

Formula

=SUMIFS(
    {PO Register - Total Variation Value (hrs)}, 
    {PO Register - Project ID}, [Project ID]@row
) + SUMIFS(
    {PO Archive - Total Variation Value (hrs)}, 
    {PO Archive - Project ID}, [Project ID]@row
)