Category:PQS
Jump to navigation
Jump to search
Introduction to the Project Quantity Spreadsheet
New Sharepoint site The following link contains more information about Project Quantity Spreadsheet. If prompted, select "Request Access". Project Quantity Spreadsheet
- The Project Quantity Spreadsheet (PQS) is a tool designed by MDOT to standardize quantity calculations. It was developed to improve designer efficiency and reduce risk and errors throughout the design process. The PQS has features to streamline project packaging, automate quantity generation, and link quantities with Microstation. Each time the PQS opens it verifies that the pay items are current and up to date. If the pay items are not up to date it will automatically update its pay item list.
- The PQS is a spreadsheet with a combination of in-cell formulas and VBA macros. The spreadsheet macros work out of the MDOT ribbon at the top. The ribbon is dynamic and will change depending on what tab is selected. There are also default tabs that come with the PQS including:
- Info
- The Info tab gives basic information about the PQS including the version and what each update has included.
- Category
- The Category tab is where the project categories will be defined. All road items (non bridge) will be an odd category while all bridge items will be an even category.
- Plan Sheets
- The Plan Sheets tab is where the DGN's for the project are defined. Each defined DGN will have an associated Breakdown ID that can be linked with the pay item. This allows the user to associate the pay item with the defined DGN name and update the Breakdown ID in one location rather than updating it for every pay item.
- Table | Drain
- The Table | Drain is where the designer defines the characteristics of the drainage structures that can be linked with a pay item on the Project Qnty tab and linked with Microstation.
- Table | Driveway
- The Table | Driveway is where the designer defines the characteristics of the driveway that can be linked with a pay item on the Project Qnty tab and linked with Microstation.
- Project Qnty
- The Project Qnty tab is where the defined categories and the pay items are connected. The Project Qnty tab can be used to define a pay item just enough to create an XML or it can be used to attribute pay items in such a way that they can be easily linked with Microstation.
- Pricing
- The Pricing Tab is used to define each pay items price once and then push it back to the Project Qnty tab. This tab ignores the items with LSUM for units. The LSUM items will need to be priced in the Project Qnty tab.
- Summary | Qnty
- The Summary | Qnty tab gives a clean view of the project total quantities with the ability to sort by breakdown ID.
- Summary | Est
- The Summary | Est tab gives a clean view of the project total quantities and cost with the ability to sort by breakdown ID.
Software Requirements
- The PQS is compatible with 32-bit and 64-bit versions of Microsoft Excel.
Options
General Option
- The General Option is the most basic functionality of the PQS. The functionality of this option allows users to enter each project item and item quantity into the spreadsheet in a user-friendly format and create an XML file for import into Preconstruction. It has a similar functionality to both Preconstruction and the Stand Alone Project Estimator's Worksheet (SAPW) software. A user can also link quantities with Microstation and leverage the quantity calculation features using this option.
- This option is often used to verify XML files from SAPW, MERL, or an equivalent XML.
Bridge Option
- The Bridge Option has all the features of the General Option, but with the ability to use the automated quantity tools. This option has a feature to generate 8 to 15 commonly used rehabilitation quantities based on the general deck rehabilitation. A new calculation tab is created where they can add additional items and hand calculations.
- The calculation tab can be used as long as the following rules are maintained:
- The calculation tab name cannot be changed once created
- Column B must only have the pay item name generated by the PQS using the Add Item button at the top
- The word Total can only be used once per item next to the final quantity
- The totaled quantity must be one cell to the right of the word total
- Keep the word total between columns C and Z
- As long as the above rules are followed this feature is a powerful tool to add efficiencies in the projects development.
Road Option
- The Road Option has all the features of the General Option, but with the ability to link Drainage and Driveway tables. This options allows a designer to define the characteristics of a driveway or drainage structure to be linked with the quantities on the Project Qnty tab. Then the user is able to generate a linking table that can be used with the cells provided in the MDOT workspace.
- This options allows the designer to reduce risk and errors by removing the manual quantity updates and link other table types.
Signal Option
- The Signal Option has all the features of the General Option, but with the ability to create linking tables that are organized by a number other than the pay item number.
Best Practices
- Always update quantities in the PQS and then create a new XML for Preconstruction.
- One PQS per discipline working on the project.
- Keep PQS files under 8MB when using the 32-bit Microsoft Excel.
- Work out of ProjectWise.
- Communicate with project team on using the PQS.
- Use PQS Link feature for Large Projects or for combining PQS files greater than 8MB.
- Use the PQS Link feature for project roll-up at a submittal.
- Read PQS validation output for troubleshooting.
PQS Process
Help Resource
For any issues, concerns or assistance with the PQS please contact us at our resource email MDOT Engineering Support Training.
For additional help resources please see Support Services Help
Training
General Features
- Opening the PQS: Video
- This video walks through how to open the PQS.
- Select the Spec Book Year: Video
- This video walks through how to select the Spec Book Year. This imports item codes from either the 2012 or 2020 spec book item code lists.
- PQS Ribbon and How to Use it: Video
- This video walks through the general portions of the PQS ribbon.
- Defining a Category: Video
- This video walks through how to define a new category.
- Adding Non-Linking Pay Item: Video
- This video walks through how to add Pay Items to the PQS that will not be linked with Microstation. Typically this is only used with the General Option.
- Using the Pricing Tab: Video
- This video walks through how to use the pricing tab.
- Importing Pricing: Video
- This video walks through how to Import pricing from an XML generated from the PQS, SAPW, MERL, or Preconstruction.
- Updating Existing 2012 Preconstruction Projects to the 2020 Spec Book Video
- This video shows how to update existing preconstuction projects from the 2012 spec book to the 2020 book. This should only be done on projects that do not contain breakdown ID's (Scoping or log projects).
Combining PQS Files
- Combining Sheets: Video
- This video walks through how to use the Combine Sheets feature of the PQS. This feature only works on PQS files with the same option selected.
- PQS Link: Video
- This video walks through how to use the PQS Link feature to combine an entire folder of PQS files.
Upgrading PQS Versions
- Upgrading the PQS: Video
- This video walks through how to use the Combine Sheets feature and upgrade to a newer version of the PQS.
Advanced Features
- Defining a Plan Sheet (DGN): Video
- This video walks through how to define a plan sheet to use as a breakdown ID.
- Adding Road Linking Pay Item: Video
- This video walks through how to add Pay Items to the PQS that will not be linked with Microstation. Typically this only used with the General Option.
- Bridge Automated Quantity Generation: Video
- This video walks through how to use the automated bridge quantity calculations tool in the Bridge Option.
- Create Even Category Quantity Linking Table: Video
- This video walks through how to create a quantity linking table with even categories that can be linked with Microstation.
- Create Odd Category Quantity Linking Table: Video
- This video walks through how to create a quantity linking table with odd categories that can be linked with Microstation.
- Create Drainage Linking Table: Video
- This video walks through how to create a drainage linking table that can be linked with Microstation.
- Create Driveway Linking Table: Video
- This video walks through how to create a driveway linking table that can be linked with Microstation.
- Linking PQS to Microstation: Video
- This video walks through how to link the tables created in the PQS to Microstation.
Basics Training
- PQS Linking Training: ZIP
- This training shows how to link project quantity spreadsheets with Microstation.
- Project Quantity Spreadsheet Guidance: PDF
- This guidance document describes how to use the PQS.
- Example PQS: XLSX
- This example spreadsheet demonstrates use of the PQS.
Workflows
- Modify Breakdown ID: PDF
- This workflow shows how to override the default settings for Breakdown IDs.
- Pay Item Updating: PDF
- This workflow shows how to manually update pay items and how to turn on the automatic pay item updating.
- PQS Link: PDF
- This workflow shows link project quantity spreadsheets into a combined master spreadsheet.
- Upgrading PQS versions: PDF
- This workflow shows how update the PQS to a newer version for an in-progress project.
- Sorting: PDF
- This workflow shows how to navigate the PQS, and how to sort and filter.
PQS Download
- This link is the current version of the PQS
- Note that the default spec book is 2020. Check with your Project Manager to verify if your project will use the 2012 or 2020 spec book. To use the 2012 spec book, select 2012 from the dropdown list, save, close, and reopen. Video
This category currently contains no pages or media.