Collaborate on Building Construction Bill Format in Excel for Procurement with Ease Using airSlate SignNow

Building construction bill format in excel for Procurement

hi everyone I welcome you all to my YouTube channel so in the previous videos related to construction management as you can find the playlist in my channel we have studied several topics related to tendering related to contracts related to billing it site related to planning and whatnot so you can find that playlist and you can just scroll the videos and you can look the video that you want so in this particular video we'll be discussing one very important part or rather I would say very important Clause of most of the tenders which is called as the escalation Clause so what is escalation how is it calculated what this Clause is all about how this Clause can help a contractor make some good profit also this all we'll study theoretically also as well as we will see one Excel sheet wherein we will be seeing the complete calculations using one very practical example of a hospital project that was that was almost for 1.5 to 2 years and we'll be calculating the escalation on several materials as well as on labor I will also be attaching that Excel sheet in the description below you can check out that Excel sheet also so watch the video till the end because that Excel sheet will only be uh comprehendable only if you will understand the theory behind it because I'll be explaining the in and out of escalation Clause okay so without any further delay let's get started see what escalation is first of all escalation see let's take an example of one Hospital building this Hospital building was of a contract value of 1,000 CR rupees RCV means revised contract value so the project was bided in the month of January 2020 let's say the project started after some 2 3 months and it started in March 2020 now the project got completed in March 2023 now what happens is when you are quoting for that particular job then the prices of the Commodities let's say cement okay or steel or the wages of the labor that you are considering skilled labor unskilled labor semiskilled labor then other materials like bricks or maybe oil fuel then maybe your uh structural steel okay all these things you will be quoting your rates in January 2020 but the project will get completed after maybe 3 years 3.5 years or even more so will the rates that you quoted during this stage will remain same in March 2023 obviously the rates will change most probably due to inflation these rates will increase but there are also some chances that the rates might decrease so what escalation does for you is see either there are two cases either the client can tell you that there will be no escalation clause in that particular case you will take the rates somewhere in between the project project starting and ending that means you will just extrapolate the rates and you will not quote the exact Market rates that means while quoting for the project you will not take the rates based on January 2020 but instead you will extrapolate the rates somewhere around March 2022 or maybe December 2021 and on those rates you will quote your tender why to just prevent yourself from the risk of increasing of the prices but what happens Contra clients usually they put some escalation Clause wherein they will tell that no you don't worry whatever will be the increase in the price of the major Commodities or even minor Commodities also I will take care whatever will be the increase you will be reimbursed whatever will be the decrease I will recover from the bills okay so this is what escalation is all about and this is the only meaning of escalation escalation in dictionary if you say we say now escalate the matter escalate means highlight the matter or increase the matter okay so this is escalation now see there are basically two Clauses as of now if you take the cpwd Clauses then there is one clause which is called as 10c another Clause is nothing but your 10 CC earlier 10ca Clause was also used to be there but what happened is cpwd merged these two Clauses into one clause and which is now only 10 CC clause and there is nothing called as 10ca Clause as of now 10 CC what is it I'll just discuss but let's let me tell you about 10ca and 10 CC 10ca was to compensate or to reimburse you for the increase in price of cement and steel but 10 CC was for all other materials apart from cement and steel like your structural steel your bricks your bitamin your oil your fuel your labor and everything else but now the formulas are more or less same but now they have merched and everything will be coming under 10 CC only okay so now let's first discuss about 10 C CLA what is DC if in any case the existing government changes or there is some fresh law brought by the present government or the new government then and in that particular case whatever will be the increase or decrease in the price of the Commodities or the wages of the laborer those increase or decrease will be compensated as per the tensy Clause whatever suppose let's say currently the wages are let's say 3 00 rupees per day let's say for unskilled labor us for unskilled labor but apart from the inflation suppose the government imposes one law that instead of 300 rupees you will have to pay 350 rupees per day to every unskilled labor and this is a mandatory law that is embosed by the government at that particular point of time so in that particular case this 50 rupes difference calculated from the starting of the fresh law till the end of the project will be reimbursed to the contractor by the client so this is what is meant by 10cc the other case can be if due to any law the increase in the price or decrease in the price of cement or maybe steel is also there then that particular thing will also be taken care by the 10c claw but this is only due to the in change of laws like any fresh laws coming into picture or any statuary law is coming into picture but not due to the change in sales tax or the value added tax if the sales tax changes GST changes any tax changes then it will not compensate you it will not reimburse you okay it is only for the fresh law or the statutary law coming into pit now let's discuss about our main Clauses which are 10 C 10 which is 10 CC earlier it was 10 CA also but now it's only 10 CC okay now see what is 10 CC it is also called as price adjustment for works let me tell you one very important thing that if the price is increase then the client will reimburse you but if the prices decrease then the client will recover the decreased rates also from your every Bill okay now see it is nothing but your compensation that's why I'm using the term compensation not reimbursement for increase or decrease in prices of material or labor wages now this is purely due to inflation or deflation that means due to market the earlier law 10c was not based on Market if anything happens due to Chang changes in the market scenario then it will be taken care of by 10 CC Clause but if due to any fresh law coming into picture any changes are there then it will be taken care by the 10c Clause okay so one more thing is that this will only be applicable during the stipulated period of contract what does it mean that only during the period of the contract it will be applicable that means if the project was supposed to start on March 2020 and end in March 2023 then if you go beyond March 2023 the client will not pay you for April 2023 even if your project gets delayed but if the project get delayed because of the client's fault and the client gives you an eot extension of time then in that case the period to which the client has agreed to extend your project the escalation will be applicable to eot also but only in the case when the client agrees to give you grant you the exension of time otherwise it is only acceptable from the starting period to the ending period written in the contract document okay I hope you understood it now how to calculate the escalation I will just tell you two formulas one formula is for every material like cement or your steel or your petrol your oil fuel any fuel your lubricant practically also we will see using the Excel sheet then your structural steel then your bitumin your bricks and the other formula will be for labor okay so these two variations we will be seeing so I'm rubbing everything I'm just writing the formula now and I'll be explaining you the formula every every detail of the formula so just pay your attention 0.85 into R into X ID 100 into m i - M divided by m so this is the formula this is multiply okay and this is X so I'll write dot maybe instead of multiply I'll write dot so that so that you don't confuse between X and multiplication now see what is this R let me just tell you the notations here R is nothing but your work done or maybe your invoice but this is excluding payment on Market rates what is it I'll explain just hold on okay what is your x x is nothing but your percentage of material in Project I'll explain everything don't worry then Mi is nothing but your wholesale price index WPI for current month and you extract it from a website of Ministry of industry and commerce okay for the material for the respective material what is m not again it is WPI but this is for base month what is base month I'll tell you this is also extracted from the ministry of industry and commerce this websites and all I'll be telling you in the Excel sheet also I'll be attaching the link of these websites in the description okay now see let's say you have been told that one major component is of cement in your project and the amount of cement that is being the cost of the cement in your project is approximately 20% now what happens is during the escalation formula only they will tell you the breakup that cement is 20% steel is 30% labor is 40% then your bricks is maybe 40 50 70 80 90 uh 5% then your other material small small material let's say your fuel or uh any other thing is 5% so the total breakup they will give you covering all the major commod commities and minor Commodities will come in other materials okay so based on this percentage this is your X this will be given in the contract itself how the client calculated this based on the cost only like this much component is of cement in the whole project they have arrived to this percentage so for cement if you are calculating the escalation you have to calculate take 20% x x value as 20 for every month okay it will remain constant for cement if you're calculating escalation for steel it it will remain 30 similarly for labor if you calculating it will remain 40 this formula is not for labor actually formula for labor is also same but I'll show it in some other way okay bricks if you are taking it will be five what is r r is your work done basically invoice whatever whatever invoice you will do suppose for first month let's say the project started in March no I took March 2020 so let's say you only did excavation and excavation that you did is of 10 cres let's say so this 10 only excavation you did nothing else you did so this 10 CR is your Cost Plus your profit so this 10 CR will be your value of R this will be R for first month now suppose in second month April 2020 you did some excavation and you also did some back filling and PCC which will be let's say 15 CR then your R will be 15 CR for April 2020 similarly for every month whatever invoice you will do that will be the value of r for that particular month because this escalation will be calculated on a monthly basis now why is it multiplied with 0.85 now see this 10 cres will be inclusive of your profit also so the client assumes that 15% is your profit margin in every bill that you are submitting in 10 cres he's assuming that 15% is your profit so he's telling that I will only reimburse you the escalation will only be paid on 85 of 10 cres that means 8.5 cres is assuming that 1.5 cres is your profit in this so he's not paying you on the profit why why should he pay on the profit he should only pay on the cost escalation is only related to your cost not at all related to profit what you spend is only will be reimbursable no so that is why this 85 is coming he's excluding The Profit now why 15% profit so it's just an average for some projects the profit might be 5% for some projects the profit might be 25% also so an aggregate number or factor of 85 is taken here now these three things are clear to you 085 R and X now what is MI and what is M see Mi is your wholesale price index see for every material what happens the ministry of industry and commerce they release indices for every month for every particular commodity like for cement you can check the indices in the month of March 2020 March 2021 these indices are nothing but some some index which are based on the market prices of that particular material which can be used to compare the prices from any month to any other month okay so every month you have the IND index suppose let's say for March 2020 the index is 100 then April 2020 the index is 101 that means the price of that particular material has increased by some particular amount that is why there is an increase in the index also so this Mi is nothing but if you're calculating the escalation for suppose let's say uh April 2020 let's say then April 2020 you have to go to the website of this ministry of industry and commerce which is I'll give you that particular website also which is published by the economic adviser to the government of India go to that particular website check for cement which type of cement it will be specified in the contract document or specifically that it's a slack cement or it's an OPC cement or it's a PPC cement of that particular kind of cement just check the index for March 2020 or April 2020 whatever month you are referring to okay so mi will be coming from there now what is m not see Mi is for current month for whatever month you are calculating the escalation it will be for that particular month but M will be for your base month now what is the base month base month is the month in which you have submitted your tender that particular month is called as the base month so M will be the index for that particular commodity let's say cement slack cement if specified in the contract for the base month in which you have submitted your contract so I hope it's understood now so this is how you calculate the escalation practically we will be seeing through the Excel sheet also now coming on to the for formula for labor nothing it's also similar but see notice one thing if Mi is greater than M then definitely the prices have increased but if Mi is lesser than M that means the prices of the commodity has decreased so in that particular case the escalation will come out as negative so the client will be recovering that particular amount instead of paying to you so now coming to the formula for labor so the formula for labor is also similar so instead of x we can say it's y okay then Mi and M not can be replaced with l i - L / l so R is same Y is percentage of Labor okay then your Lai will change see is nothing but your Consumer Price Index there it was wholesale price index here it's Consumer Price Index and it's it is published by the Labor Bureau government of India for the respective cities okay that was this uh wholesale price index was released for the particular material but this is uh published for particular cities okay and this is published by ministry of labor and employment again this is for the current month Li and L KN is for base month okay so nothing different just same thing but now let's say if you want to calculate let's say the project started in March 2020 it ended in March 2023 so you want to see the uh uh index for March 2020 just go to the website and check the index for March 2020 for the laborers it will be specific to that particular area where your project is being executed suppose your project is going in Mumbai so for Mumbai you check if it's going in Delhi you check it for Deli if it's going in Patna you check it for Patna like that only okay so similar to this you have to go to other month also for second month maybe you can check for April so for April 2020 you can go to the website for that particular month you can check the index and you will get the LI but now from where lot will come from the same website lot will come but it will be corresponding to your base month now what is your base month your base month is nothing but the month in which your project was quoted so you quoted the project in January 2020 in the starting of the video I discussed that the month of quotation is January 2020 so for that particular City where your project is going on go to the website check corresponding to January 2020 you will get your Ln Li is there L not is there Y is there from the contract R is there whatever work you're executing you can put in the formula you can get the escalation for your labor so this is how you calculate the escalation for the labor now we can move to the the Excel sheet for checking it practically okay so now see we'll be understanding the concept of escalation using one example so this is one project which is nothing but construction of as Hospital in Mumbai so the project is in Mumbai so the indices of the labor that we will be taking will be corresponding to Mumbai in case Mumbai indices are not available we will refer to some other maybe uh District or some other city which is nearby to Mumbai okay now see name of the agency is mentioned ABC Construction private limited and this is your cumulative price adjustment Bill see as per the contract the base indices are taken for December 2020 because the tender was opened or maybe let's say the tender was quoted in December 2020 so this is your base month cement is your OPC this is given in the contract steel is your mild steel which is long products other materials is nothing but your other Mater materials labor is corresponding to Mumbai you will have to take which is in Maharashtra bricks is your plain bricks okay now is also given for cement it is shown 15% steel is shown 20% other materials 20% labor 30% and breaks is 15% which is total of 100% okay now see what we have mentioned is the project started in July 2021 and the project ends in March 2023 so we have to calculate the escalation from July 21 to March 2023 see we have our array Bills first array bill was in the month of July 2021 which was of 10 cres second ra bill was in August 2021 which was of 12 crores third ra bill was in September 2021 which was of 14 crores similarly ra Bill four was for 2 months con combining two months we separated one bill which was for 13.5 crores and 19.23 cres now see in the formula of escalation you can see here we have 0.85 multiplied by r r was nothing but your work done excluding the cost which is paid by the client maybe or whatever is whatever work you're executing at as per the market prices so see as you can see this 10 CR is similar as 10 CR as per your invoice whatever you did similarly this 12 CR is also similar because every you are doing as per your quoted rates but here you can see your invoice is 13.5 crores but your work done is uh it's calculated by deducting some 0.45 crores so why is it so why they have deducted because the 0.45 crores this much is the amount which the maybe client would have paid in this particular month for the uh or something or what whatever is there it has been done as per the market rates not as per the quoted rates if you are executing work as for the market rates then definitely there is no escalation because a client may be paying you this particular amount so you will have to deduct this okay similarly in one more month you can see it's 30 CRS okay 30 cres is the invoice but some amount has been deducted which is nothing but your uh 2.34 cres and blindly take the invoice instead you have to take the actual work which has been done based on quoted rates as you quoted in December 2020 okay so the total invoice is 352 crores whereas the actual work done on which the escalation is applicable is for cement 24.7 is for July 2021 similarly if you see the base indices also then cement base index for December 2020 is 11.1 from where I got these indices so see there is one sheet in which I have mentioned the websites so this is the website for WPI which is for your cement steel and bricks and other materials and this is for your website for your CPI which is nothing but your labor how to calculate how to get these IND diis I have mentioned the way also like homepage you have to open this website let us first go to the website for WPI okay so this is the website which I have already opened it will look the homepage will look something like this so what you have to do is you have to go to this wholesale price index you have to go to this WPI download data then you have to go to this W download data for 2011 to2 series after you will click on this then you will be directed to this particular page where you will get the monthly index files and yearly index files okay so annual average of monthly index we need so we will click on this after you will click on this then one file will be downloaded which will be something like this okay so now this file is the file which will give you all the wpis for every item you can see even non- construction item also like Cals Petty wheat cement and whatnot hundreds of it items thousands of items are mentioned in this particular sheet so we have to find the item corresponding to cement which type of cement we have to find we have to find the OPC cement first okay so let us go to OPC cement so contrl F we'll type ordinary find next this is Portland cement which I have already highlighted in this sheet for your understanding so we have to get the index for cement for December 2020 which is your base index so just find December 2020 month so this is your December 2020 month and this is your OPC so your index is 118.1 okay so I have mentioned 118.1 similarly for cement you will get the index for July 2021 say ler March 2023 that from this same sheet only now going to steal you have to search your long products in this particular sheet let us put r f and let's write long product so this is again I have highlighted already so you can go to the December 20120 and you can find the base index which is nothing but 11 18.2 which I have mentioned here 11 18.2 similarly for other materials for December 2020 other materials will be on the top the very first row will be of other materials okay so this is the other materials which is nothing but your all Commodities so here you can see the index as 12 5.4 similarly for bricks also you can find corresponding to plain bricks it will be 87.6 the same you have to repeat the exercise for all these months July 21 August 21 September 21 till your March 2023 and these are the indices that I have mentioned here okay in all these cells now calculating the escalation so I have already put the formula in all the cells so what you have to do is you simply have to put these values what is your r r is this value what is your PC PC will be this particular percentage 15% for cement then what is your CI CI is nothing but your index which is mentioned here 24.7 what is your c not c not is 118.1 divided by again so this is the formula which I have already inserted as you can see here so you simply have to put enter and it will calculate the escalation value for cement for the month of July 2021 you can stretch the formula in the downward sales and it will give you the complete escalation or price adjustment values for cement the same exercise you can do for steel other materials as well as for bricks okay but now moving on to labor how did I get the uh indicis for labor so nothing you simply have to go to this particular website office official website of Labor Bureau ministry of labor and employment government of India when you will go to the the website the homepage will look something like this so what you have to do you have to go to the dashboard okay after you go to the dashboard you have to go to this CPI for industrial workers then you have to go to this all India Center index and then it will open a page something like this just have some patience the network is not that good here so yeah it's now open you have to select the base year base year will be nothing but automatically it will select for you 2016 then from which uh date to which date we want see as you can see I want the indicis from from July 2021 see this you can simply get you have to only put December 2020 I'm showing you for this July 202 March 2023 okay so July 2021 to March 2023 March 2023 okay you have to select the state your state is nothing but your Maharashtra because the project is going in Mumbai you have to select the center we already have Mumbai so we can directly get for Mumbai you have to check which type of report we want group index you can submit and after you submit all your indices for labor will be displayed here in such a format okay you can see for every month it's there whatever you demanded in three pages you can download the PDF copy when you download it will look something like this so from this file you can check all the indices like for let's say January 2022 it's 11 19.2 January 22 it's 11 19.2 so January 2022 11 19.2 here it is okay so similarly you can mention all the indices and again you will put the same formula and it will give you the escalation on labor so this is how we calculated the escalation you can calculate the total escalation by adding the escalation for all the Commodities and it will give you the summation of all the escalation values similarly adding all the months escalation will give you the total escalation for for the complete project duration which is nothing but your 41.6 cres so this is how you calculate you can also check that what is the uh percentage of escalation that you are getting first of all let's convert this in cres so it is 41.6 cor you can just calculate the percentage by dividing this with your work done value so it's almost 12% it's coming okay can stretch it it will come 11.92% so I I hope the complete concept of escalation is clear to you all if you want then I can share this particular PDF file uh Excel file with you and it will also give you the websites and the way to download this or you can screenshot this also so just let me know in the comments if you want this Excel sheet I'll attach this or maybe I'll share with you through your email if you share your email so thank you everyone all the very best also like the video and check out other videos on my channel which are related to construction management similar videos goodbye