Building Construction Bill Format in Excel for Communications & Media Made Easy

Building construction bill format in excel for Communications & Media

okay so you first thing you do when you open up Excel you're going to get a couple different options in here there's a get starting with formulas make your first pivot table take a tour if you're new with Excel they may be something cool to explore there's a lot of good good tutorials and help online there's a ton of different templates so you may find yourself using this for other classes or tasks budgeting your own personal finances whatever you think I'm a huge fan of Excel so I clicked the just blank workbook and that gives me a excel page so if you've never used Excel before up here's the ribbon with all the buttons so it looks similar to Word PowerPoint I'm assuming you've used some Microsoft products before we'll get into some of these functions and then the data itself this chart itself is on a XY axis the columns are labeled with letters and the rows are labeled with numbers and each cell has a cell address this is cell b2 right this is cell g8 so that allows us to link cells together when we're creating formulas so we're gonna make a estimate template that has different tabs for each division so down here at the bottom down here at the bottom left of the page you can see where is this sheet one I'm just gonna double click in there and I'm gonna call this 1000 Division 1 the reason I call it 1000 instead of 1 is there's a couple subsets within Division 1 and you'll see that when we get into specs and CSI after ok so I'm gonna start I'm gonna leave myself a just a roll around the edge you can start right at the beginning but I find this way here sometimes helps you when you're formatting your printing or things like that so we need a couple of different columns we'll need a 1/4 section so we know what CSI division we're in description quantity and I'm just typing you know just like you would type in word or anything else waste factor quantity total unit say material dollars per unit so material unit cost material total material dollar slash unit labor total subcontractor total equipment cost total cost right so you'll notice that a lot of these descriptions here are a little longer than the cells are wide so if you hover between the two columns so if I hover on that line between C and D you'll see my cursor changes from this what looks like an addition symbol to a vertical line with two arrows coming out of each side and if I double click it Auto fits that cell width to the length of my text so I'm going to do that all the way across double-clicking okay so this is section 1001 1000 which is general requirements general requirements should you spell something wrong in Excel it does not highlight it in red like word does but if you push the f7 button that is the key short key for spell check so I can say yes I want to continue and you can see how I had quantity with y instead of a u so I could demonstrate the function of the spell check to you and you it looks like comes up just like Microsoft Word and you can you can change that material it that's an abbreviation so I'm just going to tell it to ignore that and then it says you're you're good to go make sure you spell check your estimates it looks really unprofessional when you have things kind of misspelled everywhere in your and your estimate so I'm just gonna skip an arbitrary amount of lines here maybe go to line 15 now I want this to I'm trying to make the spreadsheet as efficient as possible so I could type one thousand again here right for formatting purposes but if I push the equal symbol and then select the cell b3 you can see it links the text for me so that when I go to make other tabs I don't have to change that in two places I change it once in it it updates automatically I'm gonna call this cell section totals section totals okay and we'll go from here so right now it looks like a bunch of text on a page it's really kind of boring so we're going to I'll show you how to do some of the math in here first and then I'll show you how to spice this up with some formatting to make it look more presentable a little easier to follow highlighting cells and colouring cells for different reasons so we'll put in some items here I know we're in we're in general requirement so we'll just we'll make some whatever temporary temporary he could be an item what else for a general requirement item here we could put in temporary partitions right just to give us a couple line items in here to work with so if I go over here we'll put in some numbers and then we'll see if we can figure out the formulas temporary partitions let's say we have five hundred square feet of these five hundred square feet units are really important just like every other class so they go on the unit column five hundred square feet temporary he we could say I don't know a hundred days so we have our quantity our are temporary partitions maybe we have some temporary temporary concrete parking I'll use concrete only because it's common to have a waste factor with with concrete today I know we're in general conditions but we'll or general requirements but we'll put concrete in here and I don't know a hundred because that'll be an easy number for you guys to envision cubic yards so when I put a waste factoring yes there's no really formulas working at because we haven't put them in we want to be able to take our quantity that we take off and have a waste percentage add it to it to get our quantity totals anyone have an idea how that formula might work right so let's I'm gonna first I'm gonna highlight that column I'll right click format cells percentage and I don't really want any decimal places so I'll hit the down arrow I'll make that zero decimal places click okay so we'll say it's we're gonna have a 5% concrete waste factor so you said it was you know kind of five percent of that number so if I say okay it is five percent of this number right here well that's five cubic yards right we want a quantity total and on our waste factor total so that's that's the waste factor so if I go back into that formula what we want is we want our original total plus and I'm gonna put the waste factor in parentheses just because just because Excel is very picky with order of operations so now when you go into here that that formula that I put in in f7 is our quantity of concrete which is d7 over here d7 plus our waste factor times that quantity so now we're adding 5% to the total so we should have a hundred and five percent of 100 which should be a hundred and five is that correct so now when you change this waste percentage you can change it to ten in it and it'll automatically calculate right that's a really easy way to do it once you have one of these formulas in if you hover if you highlight the cell with the formula all right so I highlighted that cell I'm going to hover over the bottom right hand corner of that cell and you can see the cursor changes again from like this white addition symbol to this black solid plus and you can drag that so I'm gonna drag that up here and I'm gonna drag this down you know all the way here so you can see items that we don't have a total in there there's zero and items that so temporary Heat 100 we don't have a waste factor in there they're their own number right because this is 0 so you're adding 0 to the 100% that that's already there if that makes sense so now if you were to go into these and you double click in any of these formulas it highlights the cells that are involved in the formula and then in the cell itself the text you can see d12 is blue e 12 is orange you can see the colors on those rectangles match that formula question so far okay so units are the unit you know unit of measure so now let's let's put some costs in here so let's say our concrete we can get the material for $100 per cubic yard and we'll just say that placing it also costs $100 per cubic yard all right and just for sake of argument here we need 500 dollars in equipment rental and maybe we subcontract something out we have a a CI tester come over and do the concrete tests and they're gonna bill us 250 bucks so now we have some numbers in here right as far as Costco this whole section over here is currency so just to make it easier just like we added the the percentage over here reformat the cells to be a percent I'm gonna format this whole section of our estimate to be currency so I'm gonna highlight all the cell's I'm gonna right click format cells and I'm going to go to currency symbol would be the dollar you're gonna round that down so it's all whole dollars and you can see in here there's way more currencies than I really knew existed but we're just gonna use the dollar so you can see now I have $100 $100 $200 $250 $500 so now it looks like currency it's easier to follow does that make sense okay and of course across this bottom here I could have done this before these bottom cells are where we're going to total some stuff so these also can be currency so again I'm going to right click format cells currency change that to zero decimal places and click OK you can change the negative how negative dollars look to be read which is okay if you're always gonna print in color but we won't always print in color you can change it to be parentheses that's kind of an accounting notation but I have the negative symbol in here just we're not gonna really have negative numbers in a in an estimate to be to begin with