Smartsheet Baseline Register#

This sheet is used for storing project baseline data. It serves as a source for monitoring of any deviations of the actual and forecasted values from the project baseline as well as for generating a project S-curve. Filling in of the baseline data is done by the GTE Portal as a manually triggered action in the Portal (Create/Revise the Baseline). The data are then read from the project schedule, processed and filled into the Baseline Register. For details of the process, see ... (link to be added).

Baseline curves are generated from datapoints contained within the Pxxxx_BaselineRegister sheet. These datapoints are filtered in the Pxxxx_S-CurveReport using the Is Curve Datapoint column. The Is Curve Datapoint is checked/true when either:

A row is part of the “Active Baseline”, or
A row is part of a previous baseline, and its Data Date is less than the Active To Date

A row is part of the “Active Baseline” when its Revision # is equal to the Latest Revision # sheet summary field, which is simply the maximum Revision #.

Column Summary#

Title	Date Source	Brief Description
% Complete	Snapshot Register	% complete value for the corresponding period
Active To Date	Portal	Indicates date until which the baseline dataset is active
Actual [hrs]	Snapshot Register	Actual hours for the corresponding period
Actual [$]	N/A	FOR FUTURE USE - Not yet implemented
Baseline Planned [hrs]	Schedule (by Portal)	Baseline hours planned for the corresponding period
Baseline Planned [$]	N/A	FOR FUTURE USE - Not yet implemented
Budget at Complete [hrs]	Schedule (by Portal)	Total baseline hours as indicated in the schedule in the time of baseline creation
Budget at Complete [$]	N/A	FOR FUTURE USE - Not yet implemented
Burned % Complete [hrs based]	Formula	Calculates for each period in the past the actual % complete
Burned % Complete [$ based]	N/A	FOR FUTURE USE - Not yet implemented
Data Date	Portal	The date to whcih each particular baseline data relate
Earned % Complete [hrs based]	Formula	Calculates for each period in the past the earned % complete
Earned % Complete [$ based]	N/A	FOR FUTURE USE - Not yet implemented
Earned [hrs]	Snapshot Register	Earned hours for the corresponding period
Earned [$]	N/A	FOR FUTURE USE - Not yet implemented
Expected % Complete [hrs based]	Formula	Calculates for each period the planned % complete
Expected % Complete [$ based]	N/A	FOR FUTURE USE - Not yet implemented
Expected [hrs]	Expected Register	Expected hours for the corresponding period
Expected [$]	N/A	FOR FUTURE USE - Not yet implemented
Expected at Complete [hrs]	Expected Register	Total expected hours at complete (FAC) as currently indicated in the schedule
Expected at Complete [$]	N/A	FOR FUTURE USE - Not yet implemented
Is Active	Formula	Indicates that the corresponding values on the row is active i.e. relates to the latest baseline revision
Is Curve Datapoint	Formula	Indicates that the correseponding values on the row is to be displayed in the S-curve
Planned % Complete [hrs based]	Formula	Calculates for each period the planned % complete
Planned % Complete [$ based]	N/A	FOR FUTURE USE - Not yet implemented
Revision Date	Portal	Indicates the date when the respective baseline data was added to the baseline register
Revision #	Portal	Indicates the respective baseline data revision number

Column Details#

% Complete#

Reads in the % complete value for the corresponding period (as refered to by the Data Date) from the Snapshot Register. If the data for the period is not available in the Snapshot Register (future date or snapshot has not been done), the value is left blank. This means that only values since start of the project to date will be populated.

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {1000_SnapshotRegister - % Complete},
            {1000_SnapshotRegister - Data Date}, [Data Date]@row,
            {1000_SnapshotRegister - Project ID}, [Project ID]#
        ),
    1),
"")

Active To Date#

When creating a new baseline the Active From Date is gathered from the user. The new baseline will supersede all prior baselines for data points from this date on. The Active To Date for the new baseline will be null/blank, indicating that this baseline is active through to the end of the project. The Active To Date for the previous baseline will be set to the provided Active From Date (less one day - see the Note below). The display of any baseline points that fall before the Active To Date (i.e. their Data Date is earlier than the Active To Date) will be taken from the past baseline datasets.

Note

The Portal actually sets the Active To Date to Active From Date - 1 for any existing points that are currently blank. Typically, this will only be the previous baseline (as described above) unless manual changes have been made.

Actual [hrs]#

Reads in the actual hours for the corresponding period (as refered to by the Data Date) from the Snapshot Register. If the data for the period is not available in the Snapshot Register (future date or snapshot has not been done), the value is left blank. This means that only values since start of the project to date will be populated.

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {1000_SnapshotRegister - Actual [hrs]},
            {1000_SnapshotRegister - Data Date}, [Data Date]@row,
            {1000_SnapshotRegister - Project ID}, [Project ID]#
        ),
        1
    ),
 "")

Actual [$]#

FOR FUTURE USE - Not yet implemented

Baseline Planned [hrs]#

Value populated by Portal. It indicates for each period amount of hours that are planned to be spent using the baseline start date, finish date and budgeted hours of the individual activities in the project schedule and summarized up to the project level.

Baseline Planned [$]#

FOR FUTURE USE - Not yet implemented

Budget at Complete [hrs]#

Value populated by Portal. It does not relate to specific period, but is constant for the whole baseline set. It contains total baseline hours at completion as indicated in the schedule in the time of the baseline creation.

Budget at Complete [$]#

FOR FUTURE USE - Not yet implemented

Burned % Complete [hrs based]#

Formula

Burned % Complete [$ based]#

Calculates for each period the actual % complete. Applicable only to the periods in the past - future values are left blank. Used in ???

Formula

=IFERROR(
    100 * [Actual \[hrs\]]@row / [Budget at Complete \[hrs\]]@row,
"")

Data Date#

Indicates the date of the first day of the period, to which the remaining baseline data on the row relate.

Earned % Complete [hrs based]#

Calculates for each period the earned % complete. Applicable only to the periods in the past - future values are left blank. Used in ???

Formula

=IFERROR(
    100 * [Earned \[hrs\]]@row / [Budget at Complete \[hrs\]]@row,
"")

Earned % Complete [$ based]#

Formula

Earned [hrs]#

Reads in the earned hours for the corresponding period (as refered to by the Data Date) from the Snapshot Register. If the data for the period is not available in the Snapshot Register (future date or snapshot has not been done), the value is left blank. This means that only values since start of the project to date will be populated.

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {1000_SnapshotRegister - Earned [hrs]},
            {1000_SnapshotRegister - Data Date}, [Data Date]@row,
            {1000_SnapshotRegister - Project ID}, [Project ID]#
        ),
    1),
"")

Earned [$]#

FOR FUTURE USE - Not yet implemented

Expected % Complete [hrs based]#

Calculates for each period the expected % complete. Used in ???

Formula

=IFERROR(
    100 * [Expected \[hrs\]]@row / [Expected at Complete \[hrs\]]@row,
"")

Expected % Complete [$ based]#

FOR FUTURE USE - Not yet implemented

Expected [hrs]#

Reads in the expected hours for the corresponding period (as refered to by the Data Date) from the Expected Register. If the data for the period is not available in the Expected Register, the value is left blank.

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {P----_ExpectedValue - Expected [hrs]},
            {P----_ExpectedValue - Data Date}, [Data Date]@row,
            {P----_ExpectedValue - Is Curve Datapoint}, 1
        ),
    1),
"")

Expected [$]#

FOR FUTURE USE - Not yet implemented

Expected at Complete [hrs]#

Reads in the total expected hours at complete (FAC) - as currently indicated in the schedule - from the Expected Register. It does not relate to specific period, but is constant for the whole baseline set.

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {P----_ExpectedValue - FAC Including Change [hrs]},
            {P----_ExpectedValue - Data Date}, [Data Date]@row,
            {P----_ExpectedValue - Is Curve Datapoint}, 1
        ),
    1),
"")

Expected at Complete [$]#

FOR FUTURE USE - Not yet implemented

Is Active#

The checkbox is ticked, if the corresponding data on the row relate to the latest revision of the baseline.

Formula

=IF(
    [Revision #]@row = [Latest Revision #]#,
    1
)

Is Curve Datapoint#

Indicates that the correseponding values on the row are to be displayed in the S-curve. Used for filtering purposes in the S-curve report.

Formula

=IF(
    OR(
        [Data Date]@row <= [Active To Date]@row,
        [Is Active]@row = 1
    ),
    1
)

Planned % Complete [hrs based]#

Calculates for each period the planned % complete. Used in ???

Formula

=IFERROR(
    100 * [Baseline Planned \[hrs\]]@row / [Budget at Complete \[hrs\]]@row,
"")

Planned % Complete [$ based]#

FOR FUTURE USE - Not yet implemented

Revision Date#

Value populated by Portal. Indicates the date when the respective baseline data was added to the baseline register.

Revision ##

Value populated by Portal. Indicates the revision number, which starts with 0 at the first baseline creation and increases by 1 at each subsequent baseline data addition.