Pantry statewide spreadsheet changes

Last updated: 2024-02-23

Refer to this page to see a list of the changes made to the Construction documentation used to administer a construction contract.

File Name

Description of change(s)
QMP-performance-tracking.xlsx Replaced the outdated version of the QMP Nonperformance and QMP Nonconformance spreadshet (QmpNpNcTrkg.xlsx) with the QMP-performance-tracking.xlsx spreadsheet.
Mobil.xlsx An updated version of the Progress Payments Under Mobilization Bid Item spreadsheet has been posted.
File Name

Description of change(s)
CF.xlsx A new Cubic Foot (CF) spreadsheet has been created and is posted on the Statewide Spreadsheets page.
hma-pwl-production-v3.1.1.xlsm Change log - Changelog for hma-pwl-production-v3.1.1.docx
hma-pwl-production-v3.1.xlsm Change log - Changelog for hma-pwl-production-v3.1.docx
File Name

Description of change(s)
TrafficControl.xlsx Renamed TraffCont.xlsx to TrafficControl.xslx. Replaced former spreadsheet with a new version that will auto populate the first date entered on the sheet and also the items on each sheet from the total sheet.
Updated the description for the MGal.xlsx spreadsheet Updated from Mega Gallon (MGAL) to 1000 Gallon Unit.
Pantry Statewide Spreadsheet Contacts The list of Pantry Statewide Spreadsheet contacts has been updated to reflect recent changes.
Hammer(ModifiedGates).xlsx An updated version of the Hammer(ModifiedGates).xlsx spreadsheet has been posted.
hma-density-field-ws-v2.1.5.xlsm Critical: Fixed a static reference in average pcf determination table.
hma-density-field-ws-v2.1.4.xlsm

Change log - Changelog for hma-density-field-ws-v2.1.4.docx
hma-pwl-test-strip-v2.1.3.xlsm Change log - Changelog for hma-pwl-test-strip-v2.1.3.docx
hma-pwl-production-v3.0.4-CORE ONLY.xlsm Change log - Changelog for hma-pwl-production-v3.0.4 Core Only.docx
hma-pwl-production-v3.0.4.xlsm Change log - Changelog for hma-pwl-production-v3.0.4.docx
Optimized_PPC Spreadsheet_Version4.5.xlsx This is temporary fix as the official DT form associated with optimized concrete mixture design requires some revisions. The spreadsheet will be using for concrete mixture designs that use optimized gradations.
File Name

Description of change(s)
Sum.xlsx An updated version of the Sum spreadsheet has been posted to correct an issue with protected cells.
hma-density-field-ws-v2.1.3.xlsm Corrected an error that would not allow project staff to select the layer.
hma-pwl-production-v3.0.2.xlsm

All Sheets

  • All pay calculations are now formatted using accounting format. Negative values will still appear in red.

Nuc Density Pay Factors

  • Mean and PWLD are now shown to the 0.0001 precision.

AC%

  • Added bold text to formatting for columns BK and BL when the orange diagonal line flag is applied for better readability.

Volumetric Pay Factors

  • Fixed a bug that incorrectly classified air voids as unacceptable for values which when rounded to the tenths place would be acceptable, but when rounded to the thousandths (for pay determination) were unacceptable.
  • Froze the upper pane to maintain column names while scrolling.

Total Pay Adjustment Summary

  • Fixed a bug that caused the Total Combined Incentive and Total Combined Disincentive to not include longitudinal joint density incentives and disincentives in the calculations. This fix is informational in nature and did NOT affect the pay determination for longitudinal joints.

Data Summary

  • Added a single line for project summary data to simplify copy and pasting into a database.
  • Corrected significant figures displayed for all data types.
hma-pwl-production-v3.0.1.xlsm

Nuc Density Pay Factors

  • Fixed a bug where Contractor or DOT Test Result was dependent on F&t analysis which wasn’t available until the first 3 lots were completed. The Contractor or DOT test result would check explicitly that the checks for variance (column AH) and means (column AI) said yes. Since 3 lots were not completed yet, these checks were blank. Because blank cells do not equal yes, this caused the analysis to select QV data until 3 lots were fully completed, and F&t analysis results were shown. This also made it not possible to override the dataset used for pay for these lots by entering QC or QV in the Dataset Used for Pay column associated with these lots like is possible for lots 3 on.
  • Fixed a bug that when dispute cores were used, regardless of the F&t results for the lot, the Contractor or DOT test result would only show QV data. While this did not affect pay, it affected the overall project average density that is analyzed in Data Summary tab.

Core Density Analysis

  • Fixed a bug that when unacceptable material pay adjustment was blank, total pay adjustment would not calculate.
  • Fixed a bug with conditional formatting only working for the first 3 lots.

Total Pay Adjustment Summary

  • Added notifications for when the project ends with:
    • A volumetric tonnage that is an increment of 750 tons,
    • A nuc density/core density lot length that is an increment of 1,500 feet,
    • and if longitudinal joint length is an increment of 500 feet.
hma-density-field-ws-v2.1.2.xlsm Fixed entering wet/dry counts as text so they no longer perform the division operation for the 2nd and 3rd readings.

hma-pwl-production-v3.0.xlsm
  • Gmm/Gmb: when means/variances don’t compare, but the pay factor is greater than 102%, the text for columns “Do the Variances Compare?” and “Do the Means Compare?” for the given row will turn red. This is meant to be used to draw additional attention to the fact that there is a non-compare situation occurring.
  • Air voids are rounded to 0.001 precision, the highest accurate precision that can be determined from Gmm and Gmb.
  • Nuc Density and LJ Density F&t: Now evaluate a rolling window of 3 lots instead of 5. F&t results are shown after the first 2 lots for informational purposes only for early identification of potential issues.
  • AC%: Grey bars below each lot (after the first 3 lots) will now provide a detailed warning message for the following occurrences:
    • If QC/QV/BTS results (columns G, H, I) are non-conforming. Additionally, applicable values are flagged with red text. Non-conforming flags can be combined with orange diagonal line flags (red text with diagonal orange lines in the cell) to represent multiple occurrences related to the value, see the detailed warning message in the box for details.
    • If QC/QV/BTS results (columns G, H, I) are unacceptable. Additionally, applicable values are flagged with a red box with bold, black text. Unacceptable flags cannot be combined with orange diagonal line flags, however, any additional applicable warnings for the cell will be provided in the detailed warning message in the box.
    • If QC/QV/BTS results (columns G, H, I) are +/- 0.4 or greater from their calculated values (columns BK, BL). Additionally, applicable values are flagged with orange diagonal lines in the cell. o If QC/QV/BTS (columns G, H, I) values are not within +/- 0.4 of each other. Additionally, applicable values are flagged with orange diagonal lines in the cell.
    • If two consecutive QC tests are outside of the action limits, the detail box will turn red for the applicable lot and a warning message to shut down production will be presented.
    • All of the above warnings/flags also apply to removed and replaced (R&R) material with appropriate warning messages.
  • Nuc Density F&t: Added dispute resolution cores features per STP-460-055.
    • Core densities added to the new dispute resolution cores column will override nuclear density data for pay.
      • F&t analysis is only between nuclear gauges, and dispute cores will NOT impact the F&t analysis.
    • If the pay factor stays the same or decreases, the contractor is assessed a $2,000 credit which is added to bid item 804.5015 – regional lab testing.
    • The grey bar after first 3 lots, then after each lot now works as a notification system for whether a lot is eligible for dispute resolution.
  • Nuc Density Pay Factors: Added dispute resolution core features per STP-460-055.
  • Total Pay Adjustment Summary: Reorganized PWL summaries.
hma-pwl-test-strip-v2.1.2.xlsm
  • Removed conditional formatting from Nuclear Density Gauge Adjusted Difference table and Absolute value table in “Gauge-Core Variability” sheet to prevent confusion about when to remove cores from analysis and when to remove gauges from a project.
  • Updated instructions in “Gauge-Core Variability” sheet to be clearer as to which cells should be checked for whether a core should be removed from analysis or whether a gauge should be removed from analysis.
  • Changed verbiage for Gauge Recommendation on sheet “Test Strip Summary” to make clearer than any gauge that passes can be used. Changed from “Recommended QX” to “Primary QX” and “Secondary QX”.
  • Updated conditional formatting for Nuclear Density Gauge Correlation in sheet “Test Strip Summary” to highlight the entire row for gauges based on which should be selected as primary or if a gauge is suspect.
  • Correct various typos/formatting.
  • Overhaul “Data Summary” formatting and presentation for clarity.
MultipleItemSheet.xlsx New spreadsheet that mirrors the layout of the Miscellaneous Quantities sheet and can be used for multiple items of the Each and LF type. This sheet can be useful when tracking items such as storm sewer, light poles and pavement marking.
SlabSpanTenthPoints.xlsm A new spreadsheet developed for use by field staff to assist in setting grades on slab span bridges.

 

File Name

Description of change(s)
ForceAccountCost.xlsx The formulas within this spreadsheet have been corrected.
ElectricalWire.xlsx Currently there are too many rows in the spreadsheet and it is shrinking the width of the columns to make it fit. A change was made to eliminate the bottom 4 rows.
Mobil.xlsx

A calculation error on the Mobil.xlsx spreadsheet has been corrected.
All Statewide Spreadsheets Reformatted using tables and added a description to each spreadsheet to help staff using the AWPKB search feature to locate spreadsheets.

hma-density-field-ws-v2.1.1.xlsm

 NEW SPREADSHEET - calculation error

hma-pwl-production-v2.1.1.xlsm

 NEW SPREADSHEET - calculation error
hma-density-field-ws-v2.1.xlsm NEW SPREADSHEET for documenting HMA density. Minor revisions that included adding check boxes for project details. Replaces HMADensityFieldWorksheet1min.xlsm.
HMALongJointDensityV2.0.xlsm This spreadsheet has been deleted since it has been combined with the new hma-pwl-production-v2.1.xlsm

spreadsheet.
hma-pwl-production-v2.1.xlsm NEW SPREADSHEET for documenting HMA PWL information (density and volumetrics). Longitudinal joint density is now part of this spreadsheet. Updated 4/14/2020. Replaces HMAPWLProductionV2.0.xlsm.

hma-pwl-test-strip-v2.1.2.xlsm

 NEW SPREADSHEET for documenting HMA PWL test strip information. Updated 4/14/2020. Replaces HMAPWLTestStripV2.0.xlsm.
TackConv.xlsx Updated spreadsheet posted - fixed column D tabulation error within the spreadsheet.
TicketsLbsToTons.xlsx Updated spreadsheet posted - added border to designate printing area.
TicketsTon.xlsx Updated spreadsheet posted - added border to designate printing area.

 

File Name

Description of change(s)
1YearPlantWarr.xlsx

Revised spreadsheet to better match the requirements in the CMM and Standard Specification
2YearPlantWarr.xlsx

Revised spreadsheet to better match the requirements in the CMM and Standard Specification. Included columns for non-conforming plants.
Acre.xlsx Corrected general formatting, unlocked remarks cells and locked acre formulas
AEWEllipRnd.xlsx Corrected general formatting, clarified unit of measurement after A-M, unlocked cells H,I,J for Wingwall 2
AreaByCoord.xlsx Updated hyperlinks to the correct cells for location, entered, checked, filename, page no.
AsphaltPavingDiary.xlsx NEW SPREADSHEET for regions to track asphalt paving operations on their projects
BarsSize4thru11.xlsx Revised formulas and weight per foot format, added weight table for material testing intervals, formatted title block.
BasePatch.xlsx Entered the 5 items and description into the spreadsheet. Resorted base patching items so they are in item number order.
Revised the spreadsheet so that is auto-populates the item #, description and unit of measure when entered. - 05/30/2019
ConcDwy.xlsx Corrected general formatting of title block
ConcPipeEllip.xlxs Eliminated this spreadsheet and replaced with CulvertPipe.xlsm
ConcPipeRnd.xlxs Eliminated this spreadsheet and replaced with CulvertPipe.xlsm
CulvertPipe.xlsm NEW SPREADSHEET. Replaced spreadsheets ConcPipeEllip.xlsx and ConcPipeRnd.xlsx
CY.xlsx Revised sheet to allow widening of cells to display numbers correctly. Corrected general formatting in title block
EA.xlsx

Corrected general formatting in title block.
Locked additional cells - 05/30/2019
Electrical.xlsx Eliminated this spreadsheet and replaced with ElectricalLinealFoot.xlsx.
ElectricalLinealFoot.xlsx Added new items to VLOOKUP function, corrected general formatting in title block. Changed name from Electrical to ElectricalLinealFoot.xlsx.
ElectricalLoopDetectors.xlsx Changed name from LoopDetectors to ElectricalLoopDetectors and corrected general formatting.
ElectricalWire.xlsx Corrected general formatting in title block. Changed name from Wire to ElectricalWire
EndAreaCY.xlsx Revised station entry to a station number instead of arrowing over. Corrected general formatting in title block.
ForceAccountCost.xlsx NEW SPREADSHEET for regions to track force account costs on their projects.
ForceAccountWkly.xlsx NEW SPREADSHEET for regions to track force account work operations on their projects.
FuelCostAdj.xlsx Updated items that qualify. Repaired cells that weren't quantifying Gallons.
GirderTenthPoints.xlsx Revised language on the sheet to match what is shown on the structure plans. 05/30/2019
HMADensityFieldWorksheet1min.xlsm NEW SPREADSHEET for documenting HMA density (1 minute tests.) Minor revisions that included locking certain cells.
HMALongJointDensityV2.0.xlsm NEW SPREADSHEET for documenting HMA density for longitudinal joints
HMAPWLProductionV2.0.xlsm NEW SPREADSHEET for documenting HMA PWL information (density and volumetrics).  Updated 5/21/2019.
HMAPWLTestStripV2.0.xlsm NEW SPREADSHEET for documenting HMA PWL test strip information. Updated 5/21/2019.
LF.xlsx Corrected general formatting in title block and remarks column and unlocked bottom remarks cells. Corrected computation error in cell E10.
LoopDetectors.xlsx Eliminated this spreadsheet and replaced with ElectricalLoopDetectors.xlsx.
MGal.xlsx Corrected general formatting and unlocked cells near bottom of page.
QmpNpNcTrkg.xlsx NEW SPREADSHEET for Regions to Track Nonconforming and Nonperforming QMP Materials.
Signs.xlsx Added sign removal items, removing sign supports and other sign-related items. Revised document title to be "Signs.xlsx". Revised to correct formula error on 5/21/2019.
SignPosts.xlsx Eliminated this spreadsheet and replaced with Signs.xlsx.
SqFt.xlsx Corrected general formatting and unlocked remarks cells near bottom of page.
Protected the sheet - 05/30/2019
SqYdAvgWidth.xlsx Revised station entry to a station number instead of arrowing over. Corrected general formatting in title block.
TicketsLbsToTons.xlsx Corrected general formatting and unlocked location cell.
TicketsTon.xlsx Revised location cell to make larger and wrap text.