Collaborate on Material Bill Format for Public Relations with Ease Using airSlate SignNow

Material bill format for Public Relations

good morning I've got 10 o'clock so we're gonna go ahead and get things started with this morning's webinar this is the LPA p bom webinar project Bill of Materials overview and we have chase wells from the ODOT division of construction who will be your presenter this morning i'm victoria beal with the ohio l tap center and we're really happy to be able to host this webinar for you a few housekeeping items we are attempting to record the webinar if we are successful we will hopefully be able to send you out a link to it later today or tomorrow and we also have muted everybody as they came in to the webinar room in order to cut down on background noise for the presenter we do ask if you have questions during the webinar itself that you please put those in the chat pod which should be located in the bottom left-hand side of your screen if you have questions put them in there and I will do my best to read them off to chase during the presentation at the very end of the webinar if we have time and we're able we will go ahead and unmute the phone lines and take audio questions there also will be provided contact information as a follow-up so you can pose questions to chase as well once the webinar is completed so I think that's all I had chase are you ready I think so can you hear me sorry yeah yep all right good morning everyone I like Victoria Biel said my name is chase wells I work in the division of construction management office of alternative project delivery here in their Dawson office some of my job duties included construction partnering statewide prepaid coordination implementing design-build projects and other alternative contracting methods coordinating value engineering change proposals greater than 50,000 statewide assisting the department and econstruction initiatives and act as the construction layout or liaison for the office of local programs which is a position that provides me personally with a lot of satisfaction so today we are going to from general material management strategies along with the purpose and overview of the project builder materials which will include a mock run-through toward the end of this presentation the goal of this presentation is to provide you as the OPA or construction consultant engineer end-user with a comfort a familiarity and level of expectation when it comes to tracking materials on a federally aided construction project and I'll I'll provide you all with with more information again at the end I like Victoria said we're being recorded I know sometimes that will deter comments or questions during the presentation so I want you all to feel very comfortable to reach out to me any time either via email or my phone number with any questions and I'd be glad to help you so this morning's agenda I will include the highlight some highlights between FHWA and IO the stewardship and oversight agreement some CFR regulations Code of Federal Regulations the various material acceptance methods how to go about finding job mix formulas and their approvals quote comparison between a node outlet project bill material first a local lift project bill material as I mentioned a lot of demonstration of the people and then of course we'll open it up for questions at the end so to get where we're going we must first know where we've been and to understand how we got here in the first place I'm sure for some of you you didn't expect us to get that philosophical at 10:04 here in the morning but this is where we're at and I'd like to give you kind of a brief history lesson on Onoda and our construction practices I think some of you passed so daughters might appreciate this moving forward I know some of you are on the call so for those that used to work with the department I'm sure you remember the days of CMS the construction management system that we use to manage our construction projects for me it was 2010 when I was hired full time I was fresh out of college and I was definitely eager to learn the construction program that would track the daily work report Testaments change orders materials etc for me I was expecting some intuitive sleep program much like something Microsoft Office or AutoCAD had to offer but instead I was introduced to this pixelated outdated doll space program goodness EMF here's a screenshot on the presentation that that was the Logan back in the day this program relied on the F keys at the top of your keyboard I don't know if you guys use those very much I know I didn't in college saw what it used to when I was hired full time at ODOT but I was a little surprised to be working with with a system like this but it's here where the project build materials aka AP bomb got its name but before I go any further I'm going to ask you to ask yourself what your initial thoughts were when you first heard of the term people I'm going to guess it was probably inappropriate maybe something you would expect to see on the side of a highway but no I'd like to clear that up a little bit and kind of give you an understanding of how that came to be so actually had everything to do with this screen right here you see the little red box up in the upper left in order to become proficient with CMS you must first had to learn the code and there were hundreds of shortcuts one being just this PP om the people and that's how it got to be called when we were out there on the construction projects I think some of you pass through donors you want to appreciate this maybe this is a trip down memory lane we also had shortcuts such as PB o ok P book if you remember that which stood for pay book DM 80 d-mat delivered materials DD rmk daily diary remarks liq ID liquidated damages SPO M bill materials like I said there were hundreds of shortcuts if you knew them you could type those up in the upper left-hand corner and gets it it's where you were going so CMS was developed in 1993 and it existed for almost two decades through 2011 which is why no transition over to ash no site manager I think personally it was a pretty remarkable run for a system that was built entirely internal what I would ask you to do is pay close attention if you see my cursor here on the screen or the headers such as conversion factor inspected quantity required quantity if you've dealt with any project little material in the past I think these terms and column headers are going to look somewhat familiar and we'll get into that even a little bit later so as an OPA you are recipient of federal funds which you may or may not know of are the agreements and obligations that take place beforehand between FHWA and no dot that allow you to be recipients FHWA and what the slide is trying to depict is a relationship that state deities have with their respective divisions and because of this relationship exists the LPA program this agreement states the state deity is designated to administer FHWA s-- federal funded programs furthermore the state ERT may permit local public agencies LPAs to carry out the state deities assumed responsibilities on local administer projects but the state deity is responsible and accountable for LP and compliance with all applicable federal laws and requirements think of it this way authority is shifted down a level because of the role size of local entities federal highway simply doesn't have the resources to manage just in Ohio for example the 200 or maybe even more active LPA projects we got going on at one time so because of the federal highways has to rely on the state deity and our resources in order to ensure compliance with with those locals so this is situation and a relationship where if the LPA fails we as their not fail in the eyes of FHWA so it's something that we as er don't take very seriously therefore we see it as beneficial to provide you the LPA with tools to succeed thus this webinar on the project build materials so in order to receive federal funds on their gauntlet projects one thing ODOT must prove to FHWA is that we have sufficient mechanisms in place to test sample track certify material this is governed by the Code of Federal Regulations 6:37 adopts office of material management OMM goes through an accreditation process every two years with both ashley resource which is once known as a MRL and CCR l ASTM cement and concrete reference laboratory OMM has maintained this credit asian since 1995 this plays a large role and why adults materials management process is so fast and expansive to keep things simple FHWA wants to ensure quality products are used in every facet of construction project and it's the department's responsibility to meet those expectations thus peridot needs to ensure quality products are used on local led projects and tracked ingly knowing full well there's a premium when said materials this is the standard and expectation and accepting federal funds so what is the purpose of the people as stated in the LP and construction manual procedures before payment is made to a contractor the OPA has to make sure sufficient measurements calculations material search etc are made to ensure closed deformity to the plans and specifications what the project Billa material does is ensure that materials accepted on the project are always greater to or equal the materials required such as part of the the tracking process so the question is and how does one accept materials if you're not familiar with their adults process I'll be the first to admit it's not intuitive there are a lot of different ways and means to to the track subtract material and one of the goals of our people on that we provide is to at least call out what acceptance method you as a local should look for to to accept these materials and these revolve around qualified product lists approved lists te 24s certs QC QA mechanisms certified test data small quantity and so forth we'll get into these definitions a little bit later what we also do is talk about the primary and component materials I think the best example that I can give here is conduit think of it this way your primary material for conduit is going to be either your reinforced concrete pipe your aluminum pipe if your corrugated polyethylene etc that's your that's your host that's your primary material what's your component material and they still material still has to be approved or your joint fillers for example or the stand for the bedding in back so that's something that the the P bone is able to do another thing our conversion factors and I think the best example I can give here it would be a work item such as curb the material coming to the project is going to come in cubic yards of concrete but you don't pay for curb and cubic yards of concrete you pay for curb by linear feet so there has to be that that conversion factor of cubic yard to to feed a curb it sounds pretty simple but when you start looking at type 2 curves like to a type 3 curves they all have different cross sections which means there's going to be a different amount of concrete that's going to be required to ensure that that material acceptance is greater than the material required so you know what we try to do with our project build materials is provide those conversion factors to get you from how does the material comes to the project to to what you're paying so let's jump into the the acceptance methods the first one to list is the qualified products list what the qpl does is identify materials manufacturers whose materials are approved these when they're not projects without further ado mutation or testing so these are materials that have been historically accepted based on Norman's history or the producing industry has a history of furnishing quality materials on the grand scheme of things it's a small dollar amount and the department's overall material purchases and then plus may be OMM laboratory does not possess the equipment our capabilities to routinely test for acceptance of these materials so I know that probably doesn't provide you a whole lot of information but what is important is where this qpl list can be found if you if you're going to or dolts website and you go to the division of construction management home page you'll see off to the left OMM the office of materials management homepage you click on that link and this is what you're going to see luckily when you're trying to find these different acceptance methods they're right there on the homepage though immense so if you're looking for qpl or qualified products list you'll see it right there in the in the left column once you click on that link you're going to see all these different types of materials that could be accepted as part of the qpo for example let's go with bridge paint you click on that link and it's going to pull up this this list that's it's maintained I think - this one it looks like it's organic zinc paint but it's going to tell you not only the brand name but the producer supplier so I'm sure you we've all dealt with what bridge paint before so this is this is what you'd be looking at and you'd want to make sure that the material on site falls under one of these listings and I'd like to say to one of the best management practices as I went out and I've done these technical process reviews with the districts in LPA is is actually printing office QP maybe highlighting the material that was used for the project it's sticking it in a reference folder for those that may not know this qpl list is active so OA minim is constantly adding and deleting the different materials and producers suppliers off this list so you know you might start a project and something might be active but if you get toward the end you might not be able to find it just because it's been added or deleted and so forth so just a good practice to go ahead and print it off highlights material used on the projects to get a reference folder and and you're done so along with qpl you also have approved list the approved list is an internet-based list created to provide our contractors and field personnel easy access to information needed to construct or manage the project proof of these files exist as a manufacturer stamp or an imprint on the material maybe some label information not seen in the past where that label has been torn off and stuck in the reference folder for validation maybe they have a letter of performance from the manufacturer or some other documentation showing the manufacturer great brands you know any of this efficient positive identification for the material that can be referenced and threw in a folder or also just the supplier markings so that's the type of things we're looking for approved list when looking for this on the website it's much the same as the qpo it's actually right under click on the approved list you're going to be presented with this website let's say for example we want to look at geotextile fabric so we're looking at maybe some of the fabric that's going to placed on horseback though or some dump Barak you click that link and you're going to be presented with this another listing much like the qpl that will give you the different producer suppliers their material carried which really isn't important on LPAs that's kind of fun oh god identifiers but you'll see the fabric types and the brand names and you just want to make sure that the fabric on your construction project falls under one of these these listings next t24 kind of everyone chamber I think for me the best way to describe it t24 is that ODOT certifies a producer supplier and their bulk inventory so each time that a producer supplier shifts their inventory or material to a project site ODOT has a system known as virtual warehouse it tracks Andy ducts this quantity from their approved inventory so think of it this way once your inventory is depleted that producer supplier and their material has to become recertified as they're not let projects we contract this electronically what project they produce our suppliers have sent their material based on established project reference items the systems talk to each other between our our site manager and virtual warehouse it's not exactly the case for locals or dodds it's not how the database that has the exact numbering of reference work items for locals and the t 24s are sent generic LP a project names like their county rail section or it's simply the city county name but it's something difficult to know exactly what item of work the material is associated with so I'm sure as you've seen t24 thrive on the project that maybe you found errors on on the local led projects that's more common because really the producer supplier is they're getting their information from the prime and if the prime submits an error it's going to reflect on the T 24 and it's not uncommon to see a T 24 and a local that project have an error it's simply one of those things where you get with the prime the prime gets back with the producer supplier and they they present you another piece of paper but this is why it's important when local at projects to collect that paper is because it may be the only source documentation for the material so might be a little more information than what you wanted but what at the bottom of this slide here is a kind of a quick example what the T 24 it looks like what might be more important is this next slide I'll be honest I haven't heard this over a year now but it used to be something I would hear a lot and I think that's just because the locals I've got smarter and been calling out the both the prime and the suppliers but we used to hear the suppliers say we can't create 24s without an air dot project number why they were saying that was they wouldn't produce 8024 because they could almost house more inventory they wouldn't have to take inventory out and say it was going to an LPA project but that's untrue as a local that project you should still be looking 40 twenty fours and hopefully you can see this but when it produced your supplier log them into our virtual warehouse system there's an area right here that they can click for local projects and then there's a unique identifier that's kind of up to the user to what he wants to put in that box but when they submit that that will deduct the material from their inventory which is important because they have to get recertified like I said so ensure that they ste 24s are definitely coming to your project like I said with the qpl in the approved list the the project billet materials will tell you what to look out for whether material should be accepted as a t 24 and there are others certified test data CTD as we like to call them what falls under this category are your H piles maybe your your sip reinforced piles slip erosion protection all that stuff but what it is it's not a it's not a certificate of compliance that just the data is what the data is and so it's up to the local or whoever may be a consultant project engineer to verify that the test data meets meet specifications we also have small quantity I'm sure most of you familiar with that there dot CMS calls it out as 106 or three here it's where the project engineer may accept small quantities materials for temporary application that's the most important part temporary that are not intended for permanent incorporation into the work so before accepting materials a small quantity the project engineer will evaluate one does it meet one in 603 that then excuse me does not exceed 10% of total quantity required for the specification construction item and it's not a critical structural or highway safety item the example I like to give here is maybe some temporary concrete pavement for maintenance of traffic it's one of those things it's only going to be in for a short amount of time so it's still probably not a bad idea to make sure that the durability factor is going to be there you might want to test the maybe the air swamp in yield but it's not something where you need to be shipping off and getting 28 day breaks to make sure that the concrete's holding up and meeting those requirements so small quantities and I make your life a little bit easier and then last job control sample taken to pay for work really what this is is for example your concrete test you do your air slump yield point of delivery but then you're also making cylinders shipping a mall to a certified test laboratory getting your 28 day breaks back that's part of this material so you'll see on the project build materials it'll be G Cpl job control you'll know what to do once you come across that next I'd like to take a little bit of time and talk about JAMF salt mix formulas and how to ensure that your asphalt a concrete mix is approved from certified producer supplier what I've got here is a ticket that was used on an actual ODOT project you want to make sure that each concrete ticket or asphalt take it as it comes to your project has a JMS number on it this one it's c300 102 1a so that's good the JMS located on it next you want to make sure that that JMS and that producer supplier is approved so you go to the Division of construction management home page you'll look forward this EMRs portal click on that link which we'll be looking for is contractors reports so you go up there click and look for active Portland cement concrete JMS your concrete JMS click on that link you type in that JM f-number again or find it from the drop down menu like I said it was c300 102 1a click Submit and hopefully you get something like this and it'll give you the producer supplier name material cone material name and make sure everything's approved and ready to go print that all stick in your reference file you're good to go the same process is typically there for 4s hold one thing I want to make mention here as I see this commonly it's just a general conversion factor with asphalt 2 ton per cubic yard it's a pretty safe number but more often times if it's closer to something like one point nine to nine and just want you all to be aware that that plays a role when you're calculating quantity at the end of the project and you got your tickets so next we're gonna really start getting into project build materials here we're getting close that was kind of the brief history and tracking mechanisms that we use to get to our materials what I want to show you here is essentially how the sausage is made what's important for you as the end user is to understand that ODOT it's not asking you to do anything more than our own project engineers aren't doing already and for that I'm going to spend a little bit of time Lee's next few slides this screenshot is from adult site manager SQL database if you recall site managers they're adults construction management program that's where we track all our daily work reports change order system until these materials etc this is world components of the project mill Dilla materials can be queried on what's important about the slide is a visual marriage really between ODOT item master if you're familiar with that that's our unique 9 digit codes corresponding to the CMS specifications for example 6 1 4 e 1 0 0 0 0 that's your 9 digit code that is a specific work item identifier that arrow dot uses 6 1 4 I'm sure you're familiar with that's maintenance traffic item we don't mandate this on LPA projects but it's something that we may may do in the future it's not uncommon to see plans just have the three-digit spec called out four and the plans and not break it down any further I will say it for the project little materials to run you will need that 9 digit code but to stay on task here what this is it's that marriage between INAF item master and adult standard bill of materials standard bill of materials its database that houses all materials which could be incorporated into a project we're not calling out the brand or manufacturer specific but this is more along the lines of your three or four stone your 57s your sand your conduit types fabrics concrete classes all that stuff Oh dots item master containing seven thousand four hundred and fifty eight different items of works and it's important to note that it's updated pretty regularly I think the last update occurred June 7th so let's take a quick screenshot I just took Milan I should have took of one further down but this is just the very first page of err dogs item master you can see off here on the left that's your nine digit code it is specific to an exact item of work here like I said this is really how the sausage is made it's not that important but what I want to show you is why we're pulling this information and really what it does in the ends so in order to create a project build materials we go into the database we pull the item codes the short descriptions the units make sure that the spec here is 2016 that's what our LPA project should be using as the 2016 CMS and we run the data and this is what we get it's a whole lot of overwhelming information but what's important here is to kind of look at the column names and when I take that information we dump it into our project Bill of Materials spreadsheet this is what we get a little cleaner version and like I said this was all the salsas just made but this is why it's important this slide is what a no dot project engineer sees and has to run every two weeks when they generate an estimate from the contractor this tells a project engineer worries short all materials it'll highlight in red you can see this column here average shortage what you also see is you'll see the item codes you'll see the item descriptions you'll see primary component materials material name assign material quantity and so forth let me jump back to this previous slide which is what you'll see on our ODOT our I'm sorry our local legend project color materials it's the same information ODOT item care descriptions your units primary component materials spec reference which this is a bad example because it has none but trust me there are spec references the conversion factors and so forth what I'm trying to think here is that I know the spreadsheet is not what you're used to if you're not used to running federal rate projects but we're not asking you as local to do anything more than what our project engineers are doing granted they have site manager and the system is able to generate this information for them but with a little bit of work and we'll get into it and I mean little you know you can you can create this project little materials that you can use for your project so let's get into it project build materials set up what you'll need is very little you need your item codes mind you you'll need your nine digit code not just the three digit code in order for the system to work material carriers are assigned to all the different specific items of work so you have to have those unique identifiers which are the nine digit codes so that your big quantities and five minutes of time so I will leave the PowerPoint here and let's jump into the spreadsheet just so everybody knows I will post a copy of the PowerPoint along with hopefully a link to the recording later today so thanks chase Thank You Victoria you are using the spreadsheet now on the presentation I sure AM and we have many questions yet but I encourage anyone who has questions to please enter a note in the chat pod I might put them to sleep so when you go you can go to the LPA construction home page and you can click on the 2016 or don't see MS Project bill of materials and this is what you're going to see very first tab is instructions we'll skip that right now simply because we're we're already going through it what you'll need are your item cards you'll put them and call them in your reference number this I'll be honest it's not really that important it's not crucial to the success of building a project build materials you'll see when I run through the example one to start out with reference one and then your big quantities that's really all the information that you're going to need but you're also going to see this column B you don't do anything with it you'll see here in a second there is a a function in there because I'm hoping that you'll be able to copy and paste your item codes over and you won't have to just manually enter them one by one if you're able to your designer can give you the item codes and you can copy and paste that's great what does column D does is trim the item code and what I mean by that is sometimes you'll carry it over after copying and pasting and there'll be some invisible spaces afterwards which will prevent the system from running so all this does is it looks at this column a terms it up a little bit and make sure that you're good to go one thing I will point out is when you're dealing with the item codes it has that e that stands for English at one point o dot was trying to decide whether we were going to go metric or English it's been a while ago but when you see that nine digit code say example six one for e 10,000 that East stands for English when you input that into Excel sometimes it recognizes it at a scientific notation so what I encourage you to do is simply just make this column a text C and then when you copy it over you should be able to to get your item code it's a in the format which which can be legible so I'll I'll quit blabbering here and run through the people on what here is what we have here is an example project I'm going to take the item codes simply copy them and paste you'll see here the trim function all it does if there were any invisible spaces here column B is is trimming them to where there's exactly nine digits now we need to copy over our reference items and lastly are big quantities Jay says you're working on the spreadsheet or you're just highlighting the whole column and doing a ctrl C to copy and then move into that other sheet and doing it ctrl V to paste that's all I'm doing control C control V or you can right-click if you want and copy that way it's really up to you you're going to get the same result so here I'm just going in that cell I'm pressing ctrl V pasting that information and that's it I mean that's that took me less than a minute to do granted I had the information like I said work with your designers ask them it's really not much more work on their part to add those extension codes but once you get to that point you'll go over here to the second tab J quick question that came in participants would like to know where is the spreadsheet located so Victoria are you seeing the my internet browser I am but why don't you yeah you're gonna take them to the external that's why yeah okay Oh doubt the division of construction so this drop-down construction management and also on the left here you will see LP and construction admin this is a page that I maintain but you go down here to materials you can see I just updated this a few weeks ago it's the project build material set up when you click on that link you click on that it's going to open up the spreadsheet for you that will look like I said exactly like this your font on that spreadsheet that's showing right now is a little small is there a chance you could make it a little larger once you get to this point what you will do is click this this button here and all it does is run a macro so it will take hopefully a lot of a lot of work out of your hands as as the local what I want to point out though for those that want to know it's not important that you know but if you if you do what that macro is doing is there's an if statement over here you see this why it's looking to match this item code with what you have already inputted back here and so if the standard Bill of Materials item code matches one of your item codes here it's going to put a y there when I run that macro all it is is going to filter on the Y's and basically cut your data down the immensely so what's and when I say immensely this is what I mean we have if you look at the countdown here we have 26,000 578 lines of data that is a lot a lot of data but when we go here and we run back to project little materials like the macro button here on them we'll go back you don't mind you don't have to do this but if you want to know how the sausage just made this this is where we're coming from what it did was went back here and found all the Y's and filtered on it and that's 26,000 lines of data literally went down to 198 so I know it probably still seems ever whelming but we're dealing with a lot less data and that's exactly what's here so you'll never have to go to that port tab if you don't want to but that's just kind of like I said that was helping salsa so what you're looking at here to the exact project Bill of Materials it took the standard bill and shrunk it down to your project specific items well this still I'm sure looks overwhelming but one of the nice things is that you can filter you can filter on any any particular work item so what I'd like to just to make things simple something everybody's familiar with is what's they asphalt so we we listed her shortlisted down to reference 33 we're dealing with surface course you will see right off the bat here primary component and substitute the primary materials obviously going to be your asphalt the component material that's going to you know supports primary is the hull applied joint adhesive but you also have this substitute perhaps maybe one of the options for the contractor would be to go with a performance graded asphalt as opposed to the regular primary so it's up to you to the sake of this presentation let's say we're not going to use the performance graded asphalt go ahead and delete it out of there we don't need it it's just taking up room so now we're looking at something a lot more manageable we're looking at one specific item we're looking at the primary and the component so the material obviously is going to arrive as a project in tonnes if you want to know the spec reference you know simply go to CMS 441 do you want to know the material unit conversion factor you look at that it defaults to 2 now granted this the site managers data it's not that's just where the the information was extracted from as you saw in previous slides I made the quantity you know very round numbers to deal with our bid quantity was a thousand cubic yards you has the input you know this is all information you didn't have to input any of this stuff the only stuff in order to make this this p ball work is in and column in what column M is is the accepted material quantity you've done on the project so really what you're doing here in this example is you're counting your number of tons you're doing a take-off on your ticket quantity let's say you've accepted 1980 tons of material and you have paid for what Cisco plan quantity you have paid for 1000 cubic yards of material so that's then the difference between these two columns column M is the material as it arrives to the project column in is what you're paying for those units but then and the nice thing about the spreadsheet is it actually tells you how much how much material you should have accepted in our example use 1980 that's what you accepted how many tons it's telling you you should have accepted 2,000 tons if you're going to pay 4000 cubic yards it also tells you over here how many tons you're short so you're 20 tons short of paying with the claim quantity pretty straightforward but we're not done yet there's a reason I picked asphalt and that's because of the conversion factor that goes back to looking up that JAMF and finding that exact that unit conversion often it's not - it's 1.9 and some change or maybe it's over - who knows but let's say we go in here which here more you can override that that is not an issue we put in 1.9 3 and when you know it we're actually 50 tons to the good even though we only accepted 1990 tons of material so that's one example I think just for the sake of redundancy let's go with another pretty straightforward example but it's something that's very common on projects excuse me reference 46 we're looking at thermoplastic edge line 644 again we have our possible primary materials it could be thermal Palace thermoplastic granular white ing to 740 point o4 spec or which is your material acceptance deck also you'd be looking at 644 then your comparative materials gonna be your glass beads obviously but then you have other options maybe it's yellow and so forth but let's just keep things simple we don't need all these primaries you only need one your head and believe them out of there if you want or you can leave them it's really up to you I'm a little OCD I like to delete this stuff and so now we're dealing with something a lot more manageable we're looking only at specific reference item 46 thermoplastic edge line six-inch we're dealing with primary component material let's look at thermoplastic for example we've got a spec reference 748 or four we have our material unit conversion factor three thousand five hundred and ten pounds 2-mile our material acceptance is cert which also stands for certified material or te twenty four material so we would be expecting as a local to accept this material on a te 24 so as the stripers come to the project you know first thing you should be asking is hey let me see your t 24 throughout the paint and the glass beads so let's look we got a bid item quantity thirteen point seven four mile let's say what's the time you pay plan we put in plan quantity it's going to tell us that we should accept forty eight thousand two hundred and twenty seven point four pounds so let's go ahead i'm working backwards here okay but what that saying is if you're going to pay the plan quantity you should accept forty eight thousand two hundred and twenty eight pounds and it's going to tell you that your point six pounds to the good excuse me so just remember column M that's your materials as they come to the project those units called in that's your paid units your work item units and just for the sake of it let's look at the glass beads again we're going to pay for their eighteen point seven four mile that's telling us that we should accept 4352 so let's go wherever here four thousand three hundred and was put in fifty three it's telling us there that we have accepted point one seven pounds more than we needed to and that is really the gist of the project Bill of Materials it's just always having that current snapshot in the project of ensuring that the materials you've accepted on the project or greater than or equal to the materials that are required and Ultimates necessary you can just keep the one big project build a material spreadsheet or what some locals have done is simply just print out this exact reference item and stick in the reference folder chase we've got a question that's come into the chat pod from David fell and he said when two or more items on the bid tab have the same item code but different reference numbers only one will show up when the macro is run is there any way to have the rest show up or do they have to be entered manually oh to it I was going to I'm going to get to that that next so let's just jump into that we'll get back to the back to the spreadsheet here all right so one of the things I want there some of the things I want to make where to the listeners today are the imperfections of of the spreadsheet in the very first one that I have is yes the inability to repeat item codes this has been one issue since the way I built this was it was a V lookup function so it's it's looking for that first item card as you mentioned or as the submitter of the question and it only returns that first first initial hit it doesn't return all the subsequent hits I don't I don't play it off like I'm in Excel with by any means and it's gonna take somebody smarter than me I did have a spreadsheet that I was trying to develop it resolved that exact issue and it was close but it would take literally close to two minutes to run the macro and sometimes it would crash sometimes it wouldn't and that's when I was hardwired into my computer so if you were running it on Wi-Fi who knows what would happen so it's an important tool yes the way things stand now is that if you have repeating item codes you would have to go in on that second tab and place them in manually so something to be aware of maybe maybe somebody within greater Excel foundation could help me on that but that's what it is for right now the next imperfection is the scientific notation I kind of made mention of that earlier make sure when you're entering in your item codes in the beginning that Excel doesn't convert them to scientific notation and that's simply corrected by making sure that the cell looks up text and no other form of numbering what this project material does what does not do is it does not have local data in it the system is based off site managers database so I can only pull Oh dot let information and so if you have local preferences say for example your water line or maybe some ITF stuff that's not house or no dog system you'll have to go in there manually put that that information in its the static item master like I said the office of estimating for Oh dot they're constantly adding into leading item codes there's nine digit unique identifiers and so it takes you know it takes a little bit to build a spreadsheet behind the scenes and so I don't update it every time estimating updates the the item master but that's something don't be alarmed if if you had an item master that is a reasonable in there you know you have you have the right to add and delete as you feel necessary and then you also see some absent during correct conversion factors just like the two tons per cubic yard been dealing with asphalt it's just what the system defaults to it's up to the user to kind of know better and and to go in there and move those conversion factors around so think of the the spreadsheet is dynamic use it that best fits you to help you manage your project it's not perfect but it will get you it will get you there it's a lot better than what used to be there I will say that when I got involved with the LPA is about five years ago Jason we've got one other questions come in and this is from a consultant who does not have access to site manager they want to know does the P Bob function populate the same if you're not on site manager say that again it doesn't does the P bomb function populate the same if you are not on site manager pretty much yeah so you're going to see all that same data I mean I'm the data that site manager hits I mean that's exactly what I'm pulling so that's exactly what I wrote out project engineers if you see a conversion factor that's wrong that's what our not project engineers are going to see in site manager I mean this is as close as probably possibly that you could get to getting the same information that you would see on the no doubt led projects it's it's we're hitting the same database on the spreadsheets created from from site manager so what I will say is when I wanted to curb this position as a liaison about five years ago was there was a project build materials link if you remember it those had been doing LPA work for a while it was not user-friendly whatsoever it was a small dialog box and what you had to do was take each item code individually put it in manually one by one comma space item care comma space item occurred and if you had a hundred items you had to do that for all 100 and you had to get it exactly right and if he didn't it wouldn't populate and if you did all it did was give you a spreadsheet it would maybe give you a conversion factor it might give you a spec reference but it wasn't dynamic whatsoever it wasn't it was in PDF form so of course it wasn't dynamic what this spreadsheet does is do you have a change order on the project you can increase your decrease quantity to reflect what's actually going on on your project the old PDF that we had you know wouldn't do that but it's one of those things where as I mentioned earlier if the local fails or not fails and it's in our best interest to give you all the tools to help you become successful that way we all succeed and that's that's kind of the purpose of this this webinar and then while we're sitting here today and so I took that upon myself because I knew how important materials was the most project engineer back in district 10 and I wanted to make it easier obviously for the locals it is it perfect like I said no it's not perfect but it's a lot better than what we had and it's going to get you close enough to pass an audit so in summary the airdog provided projectile material spreadsheet it's a project tool i don't mandate it i don't mandate that you use the spreadsheet on any federal and it but what I do ask is that you have some way of tracking your material collecting tickets what it'd be a small tickets and the concrete tickets and then just summing them up that's that doesn't fly that's we're better than that our expectation is higher than that federal highways expectation is higher than that and so when I say that you need to have a material tracking system I'm not talking about just collecting tickets I'm actually talking about making sure that the material you've accepted on the project is approved that you got it from a qualified products list that you got it from an approved list and that you can prove that at any point in the project because you can and will eventually get hit with an audit I go out and I do my TP RS but I will say I don't treat them as hobbits I treat them as kind of a learning session if you will because I don't want to see any local fail I know the local wants to fail and you know it's I see it as an honor really to help help you all out so what I have down here at the bottom too is a little thing in quotes called Apia it's starting to become more and more popular it's an inter tech product site manager that ODOT uses as an info tech product it's endorsed by a shto so this is happier it's a web-based construction management system that is catered for locals Oh granted it's a little bit of a cost upfront as far as licensing you're looking at anywhere between fifteen hundred bucks to two thousand bucks for a license but they have been working closely with the highways specifically and have adopted our project Billa materials so the spreadsheet that we just went over is actually in this obvious system they're trying to get out in front of their competitors obviously and they found that that was a good way to outmatch their competitors was to sit down with their dot and work and understand our project little material our tracking our acceptance methods so that is a tool that's out there and I would encourage you as locals if you don't know about it to do some research or contact I would love to help you out I know if I was running a city or county project that has federal money I'd be using a BIA no doubt about it the time-saving like I said its webpage you can you can do it from your smartphone the first place I saw was Montgomery County and maderas I'm doing a technical process review and he's sitting there while I'm doing the TP are approving daily work reports and change orders from this friend from the day before pretty remarkable stuff hey chase what comment come into the chat pod it's Lisa Sigmund she's that I agree with you you did a great job explaining materials I'm going to be building my own pea bomb for Pickaway County project very soon using your program it's a DB I take it to mean that's design-build and I also wanted to add into that you know while Chase has talked quite a bit about a BIA that we definitely aren't endorsing any one specific product there are a lot of products out there and we do encourage you to definitely look at all the options that are available you know we just know of this one because they you know have tried to incorporate Ohio specific information into their program and hopefully others will take a look at doing that as well because we always encourage open competition yeah thanks Jason very true there are other your other comp competition agents are companies out there a couple that that come to mind or a builder I know there's some consultants out there that like to use the Builder as a construction management tool there's also our key vision I know Butler County Youth Authority vision I've seen their products I've set their demos it's very well accepted to I think almost all counties in the state of Minnesota use our t visions so there are tools out there and that can help you manage your construction projects you know change orders estimates bids all that stuff it's become a little more electronic but as Victoria said I we just mentioned that because they took the initiative to learn it all specific way of tracking and accepting materials and to get back the Lisa's question design bill that's a perfect question you know when you do a design-build project you have buildable units and you have a lot of lump-sum items that may not have quantities associated with the items have worked till much later on down the project and so as an owner you still have to make sure that you have material and the amount of material accepted to approve the total item of work going on and so as part of the LPA design builds going to service we actually passed that onus off to the contractor as they're developing their plans and they're released for construction drawings they have to provide you local with a project bill of materials that way you can do what we just track and certify that materials and quantities are up to par with what's going on in the project so just because there's a bunch of lots of some items out there doesn't really the contractor or the design-build team from from not having to worry about material quantities and stuff so great question Lisa I would have forgot about that if you wouldn't have mentioned so Jase one last question I know we're right at 11 o'clock but Sam wants to know how is all the aggregate on the project tracked to be placed into the component on work items in the P BOM it is very difficult to assume a specific conversion factor into the items yeah so the aggregate that's as far as a conversion factor that's just the default setting you have the right to change that as need be based on the engineering judgment it's a question about as far as accepting the aggregate whether it's the sand or stone or whatnot I understand that correct it said how is the aggregate on the project track to be placed into the component on work items in the P BOM it is very difficult to assume a specific conversion factor into the items sure that's true yeah you're really gonna have to use your engineering judgement sands for example if you're trying to do betting on conduit or even some porous backfill maybe behind a retaining wall it's it's very hard to come up with that conversion factor because you're you're dealing with something that's not standard no there's not just a set template for it so yeah it's there's a number out there but chances are it's not the right number feel free to modify it as need be like I said just use your engineering judgment be able to back up whatever number that you use the biggest thing is probably just ensuring that indeed the aggregate the sand the stone it's coming from a an approved supplier we run into that issue I ran into that issue as a project engineer district then I thought a you know a certain producer supplier had their material proved I was mistaken and we ended up having to rip and replace so to speak so it's important and I'd say that's the most important thing moving forward is just understanding that it needs to come from an approved source thanks Jason we're definitely a couple minutes over 11:00 so I'm gonna go you know just is there anything else you wanted to add real quick well I just what I've got here on the screen is my name my email my phone like I said I know it's kind of hard maybe you don't want to ask questions on a session that's being recorded feel free to call me or email me anytime I'd love to help like I said I think it as an honor and I want to see you all succeed so thank you chase and I did put your email and phone number in the chat pod too we really appreciate your time this morning doing the webinar for us and we're hopeful we can get you to do some more in the future too so alright everyone if the webinar recording was successful you'll get a link to that but at a minimum you'll get a link to the PowerPoint presentation from today and we appreciate everybody's time and joining us thank you thank you