Effortlessly Manage Your Bill Book in Excel for Building Services

Bill book in excel for Building services

in this video i'll show you how to prepare cost estimation rate breakdown analysis in excel starting right now [Music] hello and welcome to cost engineering professional i am ahmad adel and here i help you develop the required skills and enhance your knowledge to elevate your cost engineering career so if this is something that you want this channel is for you okay with this out of the way let's jump to the screen and see how can we do the cost estimation rate breakdown analysis format [Music] so as you can see here i have a boq that is for some substructure works and some super structure works and i want to estimate the cost so that standard or the normal boq you will find that the rate column is just one column where you can put the rate or whatever and you get the amount but this is not what i want i want to split this rate into four categories so what i am going to do i am going to add 10 columns here 1 2 3 4 5 6 7 8 9 10. so i added 10 columns here and i'll tell you why in a moment so let's just adjust them like that and let's have this let's have one more column just to separate our schedule from the boq itself so here i want to make the rate breakdown analysis and what i want to do i want to start with materials so here i'll put materials and here i'll put equipment and here i can put the manpower and these are the cost elements that we have and here i can put subcontractor so for each of these four cost elements i have two columns and then the last one will be the total and i'll explain what is that in a moment so this is the first thing second thing i want to write here rate then amount so for each of these cost elements i have rate and amount even for the total as well so this is what i have done so far i'll remove this bolt like that then let's have these ghost elements with all borders and now let's go down like that okay and okay starting from the materials here up to this sill i can just go here and go to more borders and what i want i want from left and right i want solid lines so i'll just select the solid line here and i'll make this a solid line and this is a solid line and below also solid line and i need a dotted line here in the middle and i'll click ok nothing happened i don't know why or actually it came here there is a dotted line and here there is a solid line and here there is a solid line so let's just confirm this dotted line in the middle so again i i selected the full range and i'll go here to more borders and i'll make sure that i have a little line in the middle okay so there is a dotted line in the middle now and all i have to do is to just copy this range from the from this cell up to down i'll copy that and i'll paste it here and here and here and here as well so what i have now i have something that looks like this so i'll just zoom out a little bit so this is what we have we have the materials equipment manpower subcontractor and total and we have rate and amount for each of them so i'll start with the material whatever rate i'll put here this rate will be for the material and whatever amount will come here will be the rate of the material multiplied by the quantity of the material so i'll say the amount is equal to the quantity but don't forget the dollar sign for the column so dollar e five multiplied by this rate which is the material rate here okay so if i copy this formula and paste it here what happens i am multiplying the rate of the equipment by the quantity you see the column quantity is fixed because of the dollar sign next to the e column okay so again i'll copy this and i'll put it here and here and here then i'll press enter so what happens now i'm multiplying the rate of the manpower by the quantity and multiplying the rate of the subcontractor by the quantity and actually the total rate will be equal to the material rate plus equipment rate plus manpower rate plus subcontractor rate and enter and the total amount is equal to the total rate multiplied by quantity so i have this line now okay and i'll copy this line and i'll paste it everywhere else done so now i have this format all what you need now is to start estimating the cost and to do this let's have some total here so again more borders and i'll put a solid line on top so i have a solid line here on top and click ok and the same formula that i have here which is the submission of the column i also want to know the total amount for each of these cost elements i want to know how much is the total materials required for the project how much is the total equipment required how much is the total manpower and how much is the total subcontractor and of course here the total amount here if you will use if you will put this cost rate the same cost rate total cost rate here if you will put it here then the amount you get in the boq is similar to the amount the total amount that you get here but usually what happens we estimate the cost on this side and then we put our ceiling rate here which means this rate plus the margins that we need this is something that we can talk about later but this is how we can do or calculate this format now the next part is if you are estimating the cost you will have another tabs here you will have the material tab and you will have your labor productivity and activity cost tab and all of these things so you will start linking these cells to your other tabs where you have estimated the costs of the manpower and if you have some material sheet and some subcontractor sheets some equipment sheet and so on i have done this in details in our cost estimation course but now just to run the formulas let me put some numbers here to make it quick and to show you how the formulas work in that case so when we are talking about concrete casting i have only the materials of the concrete plus pump plus ice or plus temperature control so i'll just put here 250 and the equipment maybe i have vibrator or something so i'll just add five dirhams per cubic meter and for the manpower actually i have shown in one of the previous videos how to calculate the cost for the manpower for each activity in the project now this activity is the concrete casting so if you haven't watched that video i'll leave the link in the description you can go and watch but this is the manpower cost for the concrete casting i'll put here 15. as i told you you can link these cells to your other sheets that's fine and for the subcontractor here i don't have anything so this is how i estimated the cost of the concrete works and actually when i am talking about concrete element the same exact line i can take it because this is for the substructure i can take the same and go here to the superstructure and i can paste the same because everything is similar the concrete casting is similar the equipment similar the material is same material or maybe here let's make it a little bit more expensive let's put it 260 if the concrete required for the super structure is more expensive but usually it is actually less expensive anyway you will just have here the material rate equipment rate manpower rate and subcontractor rate so now i have finished the estimation for my concrete casting for the super structure and for the substructure as well and actually i'm starting to get some amounts here so i'm getting that i need 18 000 for the supply of concrete here as you can see and i am getting here 375 for the equipment vibrator or whatever actually this is a small project or the quantities are very small so that's why the numbers are small i need 1 100 is the cost of manpower that i need to cast the concrete and zero subcontractor because i i didn't price any subcontractors here and this is the total cost so far which is this plus this plus this plus the subcontractor which is zero so the submission of these three is the 19000 here so let's continue our estimation now let's go to the steel reinforcement for example so for the materials here the quantity is in kg so this is the material cost let's have here per kg let's put something like 3.5 or something this you will get it from the material rate depending on the price per ton you will divide that by 1000 to get this rate then for the equipment i might need some hand tools and so on so i just can add let's say um let's have it like 0.5 or something i don't want to make it very expensive okay because this is in each kilogram i'm assuming 0.5 actually 0.5 seems to be a little bit high for me and then after that is still fixing how much was the rate per kilogram we can have 0.4 and 0.4 for all of them so i am getting a total cost for the steel here about 4.15 per kilogram which is a little bit logic and as we said we can copy this because it's a steel reinforcement and i'll press the steel reinforcement of the super structure as well then after that let's go to the formwork and for the materials for the formwork the materials here will include both the wood that you need the plywood and the box sections and everything plus the scaffolding so of course there is a detailed breakdown for this number but let me have it here as 18 let's say i'll just have 18 here and then for the equipment for the formwork again some hand tools and so on so let's just have two dirhams or something for the hand tools and the manpower which is the manpower for the form work so one second let me calculate it so 12 dirhams here and the rate the total rate that i am getting as a cost here is 32 for the formwork so i can copy that and i can price the form work here as well okay so now i started to get some numbers as you can see here i am getting 55 000 almost for the materials i am getting like 3 300 for the equipment and 10 000 for the manpower that are carpenters still fixers and labors required to do the casting and everything and i have assumed that i'll do everything in house so there is no any subcontractor involved that's why the subcontractor here is zero and i am getting a total cost of 67 000 to execute the concrete works of this project so this is how you do this sheet this sheet is actually very useful because as you can see now if i want the boq to be equal to the cost so i'll just make equal to and i can take this rate and have it here but of course or i can say let's add for example 15 so 1.15 into the total cost rate so i'll just copy this and apply here [Music] okay so i got my voq now which is 77 000 and i can mark this all and hide it and i'll print this as a pdf and submit my quotation so this will be your priced boq you have done the cost estimation separate then you have added your margins which i assume 15 here and you can print this file as a pdf that's it so that's it for this part thank you so much for watching this video and i hope you enjoyed it and if you did please support costa engineering professional by subscribing turning on notification like share comment do everything guys because i love you so much and i'll see you in the next video bye bye [Music]