Aggregates by Equipment Category

Plot a project “S” curve based on Activities

This MS Excel utility will aggregate the Activities, Work Requests, and Equipment Categories. Plotting the data into a chart that looks like this image.

The below process will need to be executed each time you would like to generate a fresh output of the data.

Versions:

• March 20, 2022 - 1.0, Initial Release

• March 25, 2022 - 2.0, additions of Hydros and Equipment Complete.

• March 26, 2022 - 3.0, changes to how Hydro test complete is determined

To execute the MS Excel utility, follow the below steps.

Step 1 - Download the template MS Excel workbook from here.

Step 2 - Download the following data from the Wall Chart you would like to plot.

1. Activities data as an MS Excel Workbook.

   • This can be found at Administrator Tile->Select Turnaround Card-> Select Wall Chart-> Equipment in Scope tab-> Download Activities Excel workbook in upper right corner.

2. Work Requests as an MS Excel Workbook.

   • This can be found at Inspector Tile-> Select Wall Chart-> Download Work Requests Excel workbook in upper right corner.

3. Equipment data as an MS Excel Workbook.

   • This can be found at Administrator Tile-> Select Equipment Card-> Download Equipment Excel workbook in upper right corner.

     NOTE: For the Equipment download, you will delete all Equipment from the MS Excel workbook that is not related to the Wall Chart.

Step 3 - In this step you will copy and paste the download data into the “Aggregates by Equipment Category” template. The “Aggregates by Equipment Category” download template workbook already has sheets in it, you will not need to add new sheets, but use the existing sheets. Follow the below steps to copy and paste the downloaded MS Excel sheets (from Step 2) into the template.

1. Paste the downloaded Activities data into the Activities sheet.

   • Ensure you paste the data starting in cell A1 on the sheet.

2. Paste the downloaded Work Requests data into the Work Request sheet.

   • Ensure you paste the data starting in cell A1 on the sheet.

3. Paste the downloaded Equipment data into the Equipment sheet.

4. Ensure you paste the data starting in cell A1 on the sheet.

   NOTE: If the Equipment data has not changed from the last time you produced the report, there is no need for refresh the data to the “Aggregates by Equipment Category” MS Excel template.

Step 4 - Now that you have the data pasted into the template “Aggregates by Equipment Category”, you can run the macro to generate the data.

This is performed by tapping the “Execute Process” command button on the “Setup and Create” tab.

Once you receive a message stating the process is complete and how many Equipment records were processed, you’ll notice on the Equipment sheet columns were added with aggregates. This newly added columns of data will be used to generate the pivot chart and associated graph on the “Output” Sheet.

NOTE: When you tap “Execute Process”, the prior data in the columns are cleared before it is repopulated.

Step 5 - The final step is to confirm the Pivot Table and associated Graph is correct. To perform this step, follow the below steps.

1. Select the “Output” sheet.

2. Place your cursor on any cell within the Pivot table. When you perform this step, the “PivotTable Analyze” ribbon should appear in MS Excel.

3. Tap the “Refresh” icon to refresh the pivot table to the latest data.

4. You are complete, the data has been plotted within the Pivot Table and associated Graph.

   NOTE: The MS Excel template has a predefined range for the Pivot Table based on sample data. You may beed to increase, or decrease, the Pivot Table row range to ensure it analyzes all of your data.

This MS Excel utility will generated a project “S” curve based on predicted vs actual activity completion.

Versions:

• August 04, 2022 - 1.0, Initial Release

To execute the MS Excel utility, follow the below steps.

Step 1 - Download the template MS Excel workbook from here.

Step 2 - Download the Activities from the Wallchart Administrative tile and save the MS Excel file.

Step 3 - Copy and paste the Activities downloaded into the MS Excel Activities sheet, starting with the A1 cell. Note: If there are legacy activities from an older project, please ensure they are removed.

Step 4 - On the “Setup and Create” sheet, set the Start and End Date for the project. This will create the “Y” axis of the Pivot char and “X” axis of the chart.

Step 5 (Optional) - There may be activities, such as report writing, defined in the data you want to ignore. You can define these activities to be ignored by filling in this information on “Setup and Create”.

Step 6 - On the “Setup and Create” tap the “Execute Process” button to execute the process. The VBA code will run and create the data starting in Column “T” on Activities sheet.

Step 7 - To generate the graph, a Pivot table is used. In this step, you will need to ensure the Pivot table source rows match the data generated in Step 5.

Step 8 - The final step is to refresh the Pivot table to reflect the data generated. At this stage, the graph should reflect the data.