Promoting Open-Data Mindset: Resource Check Pivot Table Solution

INNOVATION EXPO 2020 Resource Check Pivot Table A solution to promote the Open-Data Mindset Cameron Muir Transportation Engineer and GIS Specialist, District 1 11/16/2020

Disclaimer This presentation is designed to be a detailed walkthrough of the process to use the Resource Check Pivot Table. A copy of the file template is located on the last slide Resource Check Pivot Table, Cameron Muir, 11/16/2020

Background Problem: Problem: Resources to complete a project are measured by hours of work. Before projects make it to the K phase, the resources for each functional unit are allocated in PRSM. During the K phase, functional units use their allocated resources to complete their contribution to the PID. The Advance Planning Senior is tasked with managing the resources for all the projects in the K phase, and often there are many projects. The Advance Planning Senior relies on the PE s to verify functional units have not overspent their resources for their projects. This resource check consists of comparing the current usage of resources to the original allocated amount. To complete this comparison, the PE needs the current usage, which is provided in PRSM. The current usage is compared with the allocated amount, which is logged in a local spreadsheet. Once the comparison is made, it is sent to the Advance Planning Senior for them to review and make decisions about resource allocation for the remainder of the K phase. This process is completed on a monthly basis. 3 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Solution Best Solution Best Solution The best solution would be one that is able to automatically query and pull resource data from the PRSM database and compare it to the original allocated amount. This comparison would be in the form of a chart, graph, or other visualization. Workaround Workaround Due to the restrictive nature of PRSM, data is required to be manually downloaded. Once the data is downloaded, it can be pasted into a solution that performs the resource check comparison automatically. 4 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Solution The workaround solution consists of an excel pivot table. Data is downloaded from PRSM for a specific project. The data is then pasted into the excel spreadsheet. The data is parsed to fit the data format required by the pivot table. Data is automatically pulled from a original allocation spreadsheet, and the pivot table is updated to complete the resource comparison. The resource check pivot table spreadsheet is then sent each month to the Advance Planning Senior for each project. 5 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 5. Wait for the view to change 1. Open PRSM 2. Go to the Project 3. Expand the Task Tab 4. In the Task Column (not the check box) Click on task K.150 6 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 8. Open the Resource Check Pivot Table Excel file 9. In the K.150 excel file highlight the E and F columns (i.e. Start and Finish columns) and right click to Insert new columns. Two new columns should show up to the right of the Role column. (see image on right) 6. Click on the wee gear near the upper right of the view and export to excel. The excel file will contain resource data for the K.150 task 7. Repeat steps 4-6 for task K.100.05. 7 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 10.Highlight the data in the Role column (not the headings but the data in the white cells) 11.At the top of excel, click on the Data Tab then click on Text to Columns. (see image on right) 8 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 12.When prompted, select Delimited then Next then select Comma and Other. In the Other box put a period . and hit Finish. Hit yes for the warning box. The data from the Role column should be parsed into the three columns (see images to the right). 9 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 13.Copy the data (white cells) from the Resource column to the ETC column, and Paste the data into the PRSM Data worksheet located in the Resource Pivot Check file. Paste the data into cell A2 so as to not overwrite the table headings (see image to the right for an example of successful pasting of data) 14.Repeat steps 9-13 for the K.100.05 excel file and paste data below the K.150 data. 10 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps Note: Steps 15 and 16 are unique to Note: Steps 15 and 16 are unique to District 1, because resources used District 1, because resources used in the Materials Functional Unit are in the Materials Functional Unit are specifically monitored. These steps specifically monitored. These steps can be adapted to the names of the can be adapted to the names of the materials employees in other materials employees in other districts districts 15.In the Resource Column on the PRSM Data worksheet, filter for the Materials folks (currently GUIMARAES, ANDRE V and RAJBANSHI, ABNISH). 16.Enter MAT CONS in the Unit Desc column (see image below) 11 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 17.On the "Resource Check" worksheet, fill in the yellow highlighted cell (K1) with your project 5-digit EA. Be sure to not include the district prefix or phase. E.g. for 01-0H160K, you would enter "0H160 This will populate the "MONDO ALLOCATION" cells with the allocated resource hours for each District and Unit (see image below) 12 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps 18.Click on Dist & Unit (cell A1 ) in the Pivot Table. 19.At the top of excel click on Analyze Tab. Then click the Refresh button on the tab (see image to the right) Once the Pivot Table has refreshed, some values will be shown in red. These values indicate: overspending, resource budget reached, or PRSM discrepancies with original allocation spreadsheet. 13 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Steps Lastly, if any hours were reallocated (i.e. removed or added to your project) in your project, then provide the number of hours in the Adjustments (+/-) column. If hours were added added to your project, then insert the positive number of hours of hours in the row that corresponds to the unit (e.g. TPLN, CONS, PRJD, etc.) that received the hours. If hours were removed removed from your project, then insert the negative number of hours negative number of hours in the row that corresponds to the unit that hours were removed from. positive number 14 Resource Check Pivot Table, Cameron Muir, 11/16/2020

Workaround Solution File Double click the image below to edit the file. You can save a copy for yourself to configure. Double click the image below to edit the file. You can save a copy for yourself to configure. Dist & Unit 1 ADMN MTCE PPM TPLN TROP 3 CONS ENVM ESRV PRJD RWLS SURV MAT CONS 59 PPM SDSN SP&I EXPENSE (blank) LABOR (blank) Grand Total Spent Leftover _PRSM Allocation_ 666.5 707.5 12.0 28.0 14.5 46.5 0.0 80.0 613.5 514.5 26.5 38.5 81.5 250.0 0.0 52.0 27.0 68.0 24.5 77.0 0.0 6.0 23.0 39.0 0.0 8.0 7.0 0.0 4.0 76.0 0.0 0.0 4.0 4.0 0.0 72.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 752.0 1033.5 MONDO Allocation Adjustments (+/-) Adjusted Allocation MONDO Leftover Left Over By District 1374.0 40.0 61.0 80.0 1128.0 65.0 331.5 52.0 95.0 101.5 1336.0 40.0 52.0 80.0 1110.0 54.0 308.0 12.0 88.0 72.0 41.0 62.0 1336.0 40.0 52.0 80.0 1110.0 54.0 308.0 12.0 88.0 72.0 41.0 62.0 669.5 28.0 37.5 80.0 496.5 27.5 226.5 12.0 61.0 47.5 41.0 39.0 8.0 18.0 ADMN= PPM= TPLN= TROP= MTCE= ENVM= RWLS= SURV= MAT CON= CONS= 669.5 226.5 6.0 62.0 8.0 7.0 80.0 0.0 8.0 72.0 0.0 0.0 0.0 0.0 8.0 25.0 8.0 25.0 . 1785.5 Note: Formulas in the yellow bordered cells reference to the Note: Formulas in the yellow bordered cells reference to the original spreadsheet for District 1. You can configure these cells to pull data from your own original spreadsheet for District 1. You can configure these cells to pull data from your own original allocation spreadsheet for your district. Alternatively, you can manually enter the values into the allocation spreadsheet for your district. Alternatively, you can manually enter the values into the cells. cells. original resource allocation resource allocation 15 Resource Check Pivot Table, Cameron Muir, 11/16/2020