Skip to content

Smartsheet Expected Register#

This sheet is used for storing project expected (i.e. currently forecasted) 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 expected data is done by the GTE Portal as a manually triggered action in the Portal (Create/Revise the Expected Plan). The data are then read from the project schedule, processed and filled into the Expected Register. For details of the process, see ... (link to be added)

Expected curves are generated from datapoints contained within the Pxxxx_ExpectedRegister 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 Expected Dataset”, or
  • A row is part of a previous expected dataset, and its Data Date is less than the Active To Date

A row is part of the “Active Expected Dataset” 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
Active To Date Portal Indicates date until which the Expected dataset is active
Data Date Portal The date to whcih each particular baseline data relate
Expected [hrs] Schedule (by Portal) Expected hours at complete (FAC) for the corresponding period
Expected [$] N/A FOR FUTURE USE - Not yet implemented
FAC Including Change [hrs] Schedule (by Portal) Expected hours planned for the corresponding period
FAC Including Change [$] 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
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
Show on S-Curve Additional to Baseline Formula Indicates that related datapoint is out of the current baseline period

Column Details#

Active To Date#

When creating a new Expected dataset, the Active From Date is gathered from the user. The new Expected dataset will supersede all prior datasets for data points from this date on. The Active To Date for the new dataseet will be null/blank, indicating that this dataset is active through to the end of the project. The Active To Date for the previous Expected dataset will be set to the provided Active From Date (less one day - see the Noteb below). The display of any Expected 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 Expected 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 dataset (as described above) unless manual changes have been made.

When Expected dataset is saved from a specific date (entered via the "Active From Date" field), any previously saved Expected dataset points that fall before this date will have the "Active To Date" value set to the entered date (automatically, by the Portal, during baseline saving process). This will then be used for displaying any points that fall before the "Active To Date" (i.e. their Data Date is earlier than the "Active To Date") from the past Expected datasets.

Data Date#

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

Expected [hrs]#

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

Expected [$]#

FOR FUTURE USE - Not yet implemented

FAC Including Change [hrs]#

Value populated by Portal. It does not relate to specific period, but is constant for the whole baseline set. It contains currently expected (forecasted) hours at completion as indicated in the schedule in the time of the expected dataset insertion.

FAC Including Change [$]#

FOR FUTURE USE - Not yet implemented

Is Active#

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

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
)

Revision Date#

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

Revision ##

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

Show on S-Curve Additional to Baseline#

Where an Expected datapoint has a Data Date that's outside the range of the active baseline date range, this flag will bring it through to the report that drives the S-curve chart. This happens when currently forecasted plan is delayed as opposed to the baseline plan and so there are value that need to be indicated on the S-curve that fall within the period after the baseline finish date (up to the currently expected finish date).

Formula

=IF(
    [Is Curve Datapoint]@row = 1,
    NOT(CONTAINS(
        [Data Date]@row,
        JOIN(COLLECT(
            {P----_BaselineRegister - Data Date},
            {P----_BaselineRegister - Is Curve Datapoint}, 1
        ))
    ))
)