Skip to content

Smartsheet Estimate#

Column Summary#

Title Brief Description
Activity A description for the specific schedule activity if the estimate migrates to a schedule, built up from the Cost Item name and the names of parents in the WBS
Activity Contingency % The amount to markup the activity. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Expense Markup The amount to markup the expense. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Expense Type Select from standard expense types, or to enter a custom expense amount type a value in the form "$xx - Description of Expense" (description is optional but you must include the hyphen)
Activity Expense Unit Cost Indicates the cost per unit of the expense.
Activity Expense Quantity Indicates the quantity amount of the expense.
Activity ID Unique identifier for each task.
Activity Labour (hrs)   The calculated hours required for the task.
Activity Labour Rate ($/hr)   The activity labour rate for the task.
Activity Lump Sum Expense ($)
Activity Lump Sum Labour ($)
Activity Lump Sum Procurement ($)
Activity Lump Sum Total ($)
Activity Reimbursable Expense ($)
Activity Reimbursable Labour ($)
Activity Reimbursable Procurement ($)
Activity Reimbursable Total ($)
Activity Procurement Markup   The amount to markup the procurement. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Procurement Unit Cost   The cost per unit of the procurement item.
Activity Procurement Quantity   Indicates the amount of procurement quantity.
Activity Total ($)
Allocation %
Applicable Activity Expense ($)
Applicable Activity Labour ($)
Applicable Activity Procurement ($)
Applicable Activity Total ($)
Applicable Expense ($)
Applicable Labour ($)
Applicable Procurement ($)
Applicable Total ($)
Apply Conditional Formatting?
Budget Assigned [hrs] A budget in hours for the specific schedule activity if the estimate migrates to a schedule
Budget Assigned [$] A budget in dollars for the specific schedule activity if the estimate migrates to a schedule
Cost Item The description of the cost item (row) of the estimate
Client Pricing Schedule Issues Issues in this column will be represented by a symbol in the flag column if the Sheet Summary setting Flag Missing Client Pricing Schedule Ref.? is checked.
Client Pricing Schedule Ref. Reference for the client's pricing schedule
Description Use
Duration Duration in days assigned to each cost item. This can be entered or auto-calculated based on the Start and End dates.
Estimate Budget Issues Issues in this column will be represented by a symbol in the flag column.
Estimate Resource Loading
Estimated Duration For Schedule [days] Proposed value to be used as Duration for project scheduling purposes. It uses assigned hours budget.
Estimator Upon assigning an Estimator, an update request will be sent requesting estimate input.
Expected Project Award Date (Initial Value) The anticipated date of project award (as initially set in Project Register).
Filter - Tender Schedule
Finish Date The anticipated finish date for each cost item.
Fixed Price Total ($)
Flag Cross (red) indicates an issue on the row. Exclamation (yellow) indicates an issue on a child row.
Information
Labour (hrs)
Line Item Type
Location
Lump Sum Expense ($)
Lump Sum Labour ($)
Lump Sum Procurement ($)
Lump Sum Total ($)
Non-Standard Capacity Standard resource capacity is 8hrs/day. Set this value where the activity is, for example, performed on site and involves 12hr days not standard 8hr office days (in this case type '12
Predecessors
Project
Proposal Manager
Qty
Ready for Estimator Input
Reimbursable? Use this checkbox to mark which cost items are reimbursable.
Reimbursable Activity
Reimbursable Expense ($)
Reimbursable Labour ($)
Reimbursable Procurement ($)
Reimbursable Total ($)
Resource(s)
Resource Role
Revision Use this column only if a revision is made to the estimate. Type "1", for example, to indicate new or amended lines.
Scheduled Item?
Scope Description
Scope Qualification Input
Scope Qualifications
Scope Ref.
Scope Table Row#
Show with Filter Smartsheet will not always persist the user's custom roll up of rows. Using this checkbox allows a filter to be applied to restore a given level of expand/collapse detail as required
Start Date The anticipated start date for each cost item.
Start Date Notification Workflow Date A date 7 days before planned project start date, when the notification is to be sent to Proposal Manager.
Time (Hrs) The anticipated time (in hours) it will take for the cost item to complete.
WBS Descendants
WBS Level

Column Details#

Activity#

Formula

=IF(
    COUNT(CHILDREN([Cost Item]@row)) = 0, 
    [Cost Item]@row + " || " + PARENT(), 
    IF(
        [WBS Level]@row <= 3, 
        [Cost Item]@row, 
        PARENT() + " - " + [Cost Item]@row
    )
)

Activity Contingency %#

% to be applied. Do not populate unless the contingency for a specific item differs from the default set in the Sheet Summary.

Activity Expense Markup#

% to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.

Activity Expense Quantity#

Formula

=IF(
    [Activity Expense Type]@row <> "", 
    Qty@row, 
    ""
)

Activity Expense Type#

Select from standard expense types, or to enter a custom expense amount type a value in the form ""$xx - Description of Expense"" (description is optional but you must include the hyphen).

Activity Expense Unit Cost#

Formula

=IF(
    [Activity Expense Type]@row = "", 
    "", 
    IF(
        ISERROR( MATCH([Activity Expense Type]@row, {Expenses - Type}, 0) ), 
        VALUE(
            SUBSTITUTE(
                SUBSTITUTE(
                    LEFT([Activity Expense Type]@row, MAX(FIND(""-"", [Activity Expense Type]@row) - 1, 0)), 
                    "$", 
                    ""
                ), 
                " ", 
                ""
            )
        ), 
        INDEX({Expenses - Unit Cost}, MATCH([Activity Expense Type]@row, {Expenses - Type}, 0))
    )
)

Activity ID#

System autonumber column.

Activity Labour (hrs)#

Formula

=IF(
    [Time (Hrs)]@row <> "", 
    ROUNDUP(Qty@row * [Time (Hrs)]@row, 0), 
    ""
)

Activity Labour Rate ($/hr)#

Formula

=IF(
    [Activity Labour (hrs)]@row = "", 
    "", 
    IF(
        [Resource Role]@row = "Base Scope Composite Rate", 
        IFERROR(
            INDEX({Project Register - Total Awarded Budget [$]},MATCH([Project ID]#, {Project Register - Project ID}, 0)) / 
            INDEX({Project Register - Total Awarded Budget [hrs]}, MATCH([Project ID]#, {Project Register - Project ID}, 0)), 999999
        ), 
        IF(
            OR(
                Client# = "CPPC", 
                Client# = "CPM"
            ), 
            IF(
                Location@row = "Office", 
                INDEX({Roles & Rates - CPPC/CPM_Office}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0)), 
                INDEX({Roles & Rates - CPPC/CPM_Site}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0))
            ), 
            IF(
                Location@row = "Office", 
                INDEX({Roles & Rates - Standard_Office}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0)), 
                INDEX({Roles & Rates - Standard_Site}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0))
            )
        )
    )
)

Activity Lump Sum Expense ($)#

Formula

=IF(
    [Activity Reimbursable Expense ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Expense ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Expense ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Labour ($)#

Formula

=IF(
    [Activity Reimbursable Labour ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Labour ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Labour ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Procurement ($)#

Formula

=IF(
    [Activity Reimbursable Procurement ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Procurement ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Procurement ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Total ($)#

Formula

=IF(
    [Activity Lump Sum Labour ($)]@row <> "", 
    [Activity Lump Sum Labour ($)]@row, 
    0
) + IF(
    [Activity Lump Sum Expense ($)]@row <> "", 
    [Activity Lump Sum Expense ($)]@row, 
    0
) + IF(
    [Activity Lump Sum Procurement ($)]@row <> "", 
    [Activity Lump Sum Procurement ($)]@row, 
    0
)

Activity Reimbursable Expense ($)#

Formula

=IF(
    AND(
        [Activity Expense Quantity]@row <> "", 
        [Activity Expense Unit Cost]@row <> ""
    ), 
    [Activity Expense Quantity]@row * [Activity Expense Unit Cost]@row 
        * (1 + IF(
            [Activity Expense Markup]@row <> "", 
            [Activity Expense Markup]@row, 
            [Expense Markup (%)]# / 100
        )
    ), 
    ""
)

Activity Reimbursable Labour ($)#

Formula

=IF(
    AND(
        ISNUMBER([Activity Labour (hrs)]@row), 
        ISNUMBER([Activity Labour Rate ($/hr)]@row)
    ), 
    [Activity Labour (hrs)]@row * [Activity Labour Rate ($/hr)]@row, 
    ""
)

Activity Reimbursable Procurement ($)#

Formula

=IF(
    AND(
        [Activity Procurement Quantity]@row <> "", 
        [Activity Procurement Unit Cost]@row <> ""
    ), 
    [Activity Procurement Quantity]@row * [Activity Procurement Unit Cost]@row 
    * (1 + IF(
                [Activity Procurement Markup]@row <> "", 
                [Activity Procurement Markup]@row, 
                [Procurement Markup (%)]# / 100
            )
    ), 
    ""
)

Activity Reimbursable Total ($)#

Formula

    = VALUE([Activity Reimbursable Labour ($)]@row) +
    VALUE([Activity Reimbursable Expense ($)]@row) +
    VALUE([Activity Reimbursable Procurement ($)]@row)

Activity Procurement Markup#

% to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.

Activity Procurement Quantity#

Formula

=IF(
    [Activity Procurement Unit Cost]@row <> "", 
    Qty@row
)

Activity Procurement Unit Cost#

Activity Total ($)#

Formula

=VALUE([Activity Reimbursable Labour ($)]@row) + VALUE([Activity Reimbursable Expense ($)]@row) + VALUE([Activity Reimbursable Procurement ($)]@row)

Allocation %#

Formula

=[Estimate Resource Loading]@row * [Go-Get %]# * IF(
        [Resource Loading Factor]# > 0, 
        [Resource Loading Factor]#, 
        1
    )

Applicable Activity Expense ($)#

Formula

=IF(
    [Line Item Type]@row = "Rates Based", 
    [Activity Reimbursable Expense ($)]@row, 
    [Activity Lump Sum Expense ($)]@row)
)

Applicable Activity Labour ($)#

Formula

=IF(
   [Line Item Type]@row = "Rates Based", 
   [Activity Reimbursable Labour ($)]@row, 
   [Activity Lump Sum Labour ($)]@row
)

Applicable Activity Procurement ($)#

Formula

=IF(
    [Line Item Type]@row = "Rates Based", 
    [Activity Reimbursable Procurement ($)]@row, 
    [Activity Lump Sum Procurement ($)]@row
)

Applicable Activity Total ($)#

Formula

    = VALUE([Applicable Activity Labour ($)]@row) +
    VALUE([Applicable Activity Expense ($)]@row) +
    VALUE([Applicable Activity Procurement ($)]@row

Applicable Expense ($)#

Formula

=IF(
    COUNT( CHILDREN(Activity@row) ) > 0,
    SUM( CHILDREN() ),
    IF(
        [Line Item Type]@row = "Rates Based",
        [Reimbursable Expense ($)]@row, 
        [Lump Sum Expense ($)]@row
    )
)

Applicable Labour ($)#

Formula

=IF(
    COUNT( CHILDREN(Activity@row) ) > 0, 
    SUM( CHILDREN() ), 
    IF(
        [Line Item Type]@row = "Rates Based", 
        [Reimbursable Labour ($)]@row, 
        [Lump Sum Labour ($)]@row)
    )

Applicable Procurement ($)#

Formula

=IF(
    COUNT( CHILDREN(Activity@row) ) > 0, 
    SUM( CHILDREN() ), 
    IF(
        [Line Item Type]@row = "Rates Based", 
        [Reimbursable Procurement ($)]@row, 
        [Lump Sum Procurement ($)]@row)
    )

Applicable Total ($)#

Formula

=VALUE([Applicable Labour ($)]@row) +
VALUE([Applicable Expense ($)]@row) + 
VALUE([Applicable Procurement ($)]@row)

Apply Conditional Formatting?#

Formula

=IF(
    AND(
        [Conditional Formatting On?]# = 1, 
        COUNT( CHILDREN(Activity@row) ) > 0
    ), 
    "Yes", 
    "No"
)

Budget Assigned [hrs]#

Formula

=[Activity Labour (hrs)]@row

Budget Assigned [$]#

Formula

=[Applicable Activity Total ($)]@row

Cost Item#

Client Pricing Schedule Issues#

Provides details on any flagged issues such as missing references for the client's pricing schedule in the Client Pricing Schedule Ref. column. Issues in this column will be represented by a symbol in the flag column if the Sheet Summary setting Flag Missing Client Pricing Schedule Ref.? is checked.

Formula

=IF(
    AND(
        [Activity Total ($)]@row <> 0, 
        [Client Pricing Schedule Ref.]@row = ""
    ), 
    "Missing reference.", 
    ""
)

Client Pricing Schedule Ref.#

Reference for the client's pricing schedule.

Description#

Duration#

Estimate Budget Issues#

Formula

=IF(
    AND( [Applicable Activity Total ($)]@row <> 0, [Applicable Activity Total ($)]@row <> "" ), 
    IF(
        SUM(CHILDREN([Applicable Activity Total ($)]@row)) > 0, 
        "Budget also assigned on children", 
        ""
    ), 
    ""
)

Estimate Resource Loading#

Formula

=IFERROR(
    ([Labour (hrs)]@row / Duration@row) 
    / (
        COUNTM([Resource(s)]@row) 
        * IF(
            [Non-Standard Capacity]@row <> "", 
            [Non-Standard Capacity]@row, 
            8
        )
    ), 
    ""
)

Estimated Duration For Schedule [days]#

Formula

=IFERROR(
    IF(
        Location@row = "Site", 
        ROUND([Activity Labour (hrs)]@row / 13, 1), 
        CEILING([Activity Labour (hrs)]@row, 4) / 4
    ),
"")

Estimator#

Upon assigning an Estimator, an update request will be sent requesting estimate input.

Expected Project Award Date (Initial Value)]#

Mirror of the value in Project Register, displayed in the alert workflow sent to Proposal Manager when the Start Date is blank.

Filter - Tender Schedule#

Formula

=IF(
    OR(
        [Scheduled Item?]@row = 1, 
        COUNTIF(DESCENDANTS([Scheduled Item?]@row), 1) > 0
    ), 
    "Visible", 
    "Hidden"
)

Finish Date#

Flag#

Formula

=IF(
    COUNT(
        IF(
            [Flag Missing Client Pricing Schedule Ref.?]#, 
            [Client Pricing Schedule Issues]@row
        ), 
        [Estimate Budget Issues]@row
    ) > 0, 
    "No", 
    IF(
        COUNT(
            IF(
                [Flag Missing Client Pricing Schedule Ref.?]#, 
                DESCENDANTS(
                    [Client Pricing Schedule Issues]@row
                )
            ), 
            DESCENDANTS(
                [Estimate Budget Issues]@row
            )
        ) > 0, 
        "Hold", 
        ""
    )
)

Flag#

A cross (red) indicates an issue on the row. An exclamation (yellow) indicates an issue on a descendant row. The issues can be to do with the Client Pricing Schedule or Estimate Budgeting and the flags responsiveness to each is dependent on the selected options in the relevant 'Sheet Summary' settings. The specifics of the issues are detailed in the client-pricing-schedule-issues or estimate_budget_issues columns as relevant.

Formula

=IF(
    COUNT(
        IF(
            [Flag Missing Client Pricing Schedule Ref.?]#, 
            [Client Pricing Schedule Issues]@row
        ), 
        [Estimate Budget Issues]@row
    ) > 0, 
    "No", 
    IF(
        COUNT(
            IF(
                [Flag Missing Client Pricing Schedule Ref.?]#, 
                DESCENDANTS([Client Pricing Schedule Issues]@row)
            ), 
            DESCENDANTS([Estimate Budget Issues]@row)
        ) > 0, 
        "Hold", 
        ""
    )
)

Information#

Labour (hrs)#

Formula

=IF(
    AND(
        [Activity Labour (hrs)]@row <> "", 
        [Activity Labour (hrs)]@row <> 0
    ), 
    [Activity Labour (hrs)]@row, 
    SUM(CHILDREN([Labour (hrs)]@row))
)

Line Item Type#

Formula

=IF(
    [WBS Descendants]@row = 0,
    IF(
        [Proposal Type (for Display Only)]# = "Rates Based",
        "Rates Based",
        IF(
            [Reimbursable Activity]@row = 1,
            "Rates Based",
            "Lump Sum"
        )
    ),
    IF(
        COUNT(DISTINCT(DESCENDANTS())) = 2,
        "Lump Sum + Rates",
        IF(
            CONTAINS("Rates Based", DISTINCT(DESCENDANTS())),
            "Rates Based",
            "Lump Sum" 
        )

    )
)

Location#

Lump Sum Expense ($)#

Formula

=IF(
    AND(
        [Activity Lump Sum Expense ($)]@row <> "", 
        [Activity Lump Sum Expense ($)]@row <> 0
    ), 
    [Activity Lump Sum Expense ($)]@row, 
    SUM( CHILDREN([Lump Sum Expense ($)]@row) )
)

Lump Sum Labour ($)#

Formula

=IF(
    AND(
        [Activity Lump Sum Labour ($)]@row <> "", 
        [Activity Lump Sum Labour ($)]@row <> 0
    ), 
    [Activity Lump Sum Labour ($)]@row, 
    SUM( CHILDREN([Lump Sum Labour ($)]@row) )
)

Lump Sum Procurement ($)#

Formula

=IF(
    AND(
        [Activity Lump Sum Procurement ($)]@row <> "", 
        [Activity Lump Sum Procurement ($)]@row <> 0
    ), 
    [Activity Lump Sum Procurement ($)]@row, 
    SUM( CHILDREN([Lump Sum Procurement ($)]@row) )
)

Fixed Price Total ($)#

Formula

=IF(
    AND(
        [Activity Lump Sum Total ($)]@row <> "", 
        [Activity Lump Sum Total ($)]@row <> 0
    ), 
    [Activity Lump Sum Total ($)]@row, 
    SUM( CHILDREN([Lump Sum Total ($)]@row) )
)

Non-Standard Capacity#

Standard resource capacity is 8hrs/day. Set this value where the activity is, for example, performed on site and involves 12hr days not standard 8hr office days (in this case type '12').

Predecessors#

Project#

Formula

=IF(
    [Project ID]# <> ""xxxx"", 
    "P" + [Project ID]# + " - " + [Project Name]#, 
    ""
)

Proposal Manager#

Formula

=[Proposal Manager]#

Qty#

Ready for Estimator Input#

Formula

=[Ready for Estimator Input]#

Reimbursable Activity#

Formula

=IF(
    OR(
        [Reimbursable?]@row = 1, 
        PARENT() = 1
    ), 
    1, 
    0
)

Reimbursable Expense ($)#

Formula

=IF(
    AND(
        [Activity Reimbursable Expense ($)]@row <> "", 
        [Activity Reimbursable Expense ($)]@row <> 0
    ), 
    [Activity Reimbursable Expense ($)]@row, 
    SUM( CHILDREN([Expense Reimbursable ($)]@row) )
)

Reimbursable Labour ($)#

Formula

=IF(
    AND(
        [Activity Reimbursable Labour ($)]@row <> "", 
        [Activity Reimbursable Labour ($)]@row <> 0
    ), 
    [Activity Reimbursable Labour ($)]@row, 
    SUM( CHILDREN([Labour Reimbursable ($)]@row) )
)

Reimbursable Procurement ($)#

Formula

=IF(
    AND(
        [Activity Reimbursable Procurement ($)]@row <> "", 
        [Activity Reimbursable Procurement ($)]@row <> 0
    ), 
    [Activity Reimbursable Procurement ($)]@row, 
    SUM( CHILDREN([Procurement Reimbursable ($)]@row) )
)

Reimbursable Total ($)#

Formula

=IF(
    AND(
        [Activity Total ($)]@row <> "", 
        [Activity Total ($)]@row <> 0
    ), 
    [Activity Total ($)]@row, 
    SUM( CHILDREN([Total ($)]@row) )
)

Reimbursable?#

Resource Role#

Resource(s)#

Revision#

Use this column only if a revision is made to the estimate. Type ""1"", for example, to indicate new or amended lines.

Scheduled Item?#

Scope Description#

Formula

=IF(
    ISNUMBER([Scope Table Row#]@row), 
    INDEX({Scope of Work - Description}, [Scope Table Row#]@row, 1), 
    [Scope Table Row#]@row
)

Scope Qualification Input#

Scope Qualifications#

Formula

=IF(
    ISNUMBER([Scope Table Row#]@row), 
    INDEX({Scope of Work - AllQualifications}, [Scope Table Row#]@row, 1), 
    [Scope Table Row#]@row
)

Scope Ref.#

Scope Table Row#

Formula

=IF(
    [Scope Ref.]@row <> "", 
    IFERROR(
        MATCH([Scope Ref.]@row, {GTE-xxxx-EST-xxx Scope of Work - Scope Ref.}, 0), 
        "Scope reference not found in the SOW table."
    ), 
    ""
)

Show with Filter#

Start Date#

Start Date Notification Workflow Date#

Set to Start Date + 7 days - used to send alert message to Proposal Manager to either update Start Date or to cancel potential project.

Time (Hrs)#

WBS Descendants#

Formula

=COUNT( DESCENDANTS([WBS Level]@row) )

WBS Level#

Formula

=COUNT(ANCESTORS()) + 1