Every civil engineer and estimator has faced this situation. After spending hours in front of the drawing board working out a rural water scheme or town water supply network, there is only one thing left to do – count the fittings.
You may have worked out the length of the pipeline, quantity of trenches, and storage capacity of the tank.
But every water supply scheme needs fittings like
- tees,
- reduders,
- valves,
- unions,
- flanges,
- air release valves,
- etc.
The good news is that all of these fittings are usually easy to count. Unless, of course, the pipeline is several kilometers long, meanders up and down slopes, has multiple branches, or uses different sizes of pipes throughout its length.
It only takes one wrongly estimated GI bend or HDPE coupling to turn a simple bill of quantities exercise into a costly site inspection.
That is why a well-organized water supply all components fitting calculation Excel sheet is so important. It serves to bind the design, material, and cost estimation together to facilitate seamless construction.
The Problems With Manual Estimation
Manual calculations are fraught with problems, even if the design is correct.
For example, I was once involved in a gravity-fed community water supply project. We had done extensive calculations to ensure that the pressure at the outlet would meet the design criteria. The hydraulic calculations checked out, and the profile was satisfactory. But the first shock came when the civil works started.
The site supervisor called us in great distress. He informed us that we did not have enough flange adaptors and tee fittings for the breakout tank. It turned out that we had missed several fittings on multiple drawings. Not only that, the fittings on the transmission main and breakout tank’s branch were hard to find on the drawings.
A water supply scheme is never a straight line from source to destination. It meanders along contours and slopes and may involve several sizes of pipes. There may be multiple branch lines for water storage, each with their chambers and valves.
There are simply too many opportunities to miss a fitting during manual calculations. In turn, this leads to construction delays, additional expenditures, and even site inspections to determine the missing components.
A properly designed fitting calculation sheet in excel will solve all these problems.
Key Parts of a Fitting Calculation Sheet
A fitting calculation sheet should be designed in such a way that it serves both the estimating and purchasing purposes. In other words, it should be possible to come up with the complete material list directly from the design sheet. A fitting calculation sheet usually contains the following sections:
1. Component List
This section should ideally contain all the fittings used in the water supply scheme. The list should be grouped according to material and application. Examples of fitting categories are:
- Galvanized iron
- Ductile iron
- HDPE
- PVC
- Gate valve
- Air release valve
- Scour valve
- Flange adaptor
- Union and coupler
- Bolts, washers, nuts, and gaskets
The list can be expanded to include other fittings such as bends, tees, reducers, and many more. Make sure that the component list covers fittings in the transmission line, distribution network, and service connections.
2. Pipe Size and Fitting Size
Different fittings are suitable for different pipe sizes. This information should be provided in the calculation sheet. This way, it will be easier to come up with the bills of quantities for the fittings. The fittings size should be related to the sizes of the pipes. The excel sheet should be designed in such a way that it becomes impossible to select a 80mm flange for a 100mm pipe.
3. Quantity Rules
Some fittings have standard quantities depending on the application. For example:
- One tee is needed for every junction
- One bend is needed at every change of direction
- Two flange sets are needed at a valve
- One washer is needed for every flanged joint
- Extra one set of small fittings is added as a contingency
The calculation sheet should have quantity rules to help with the calculations. It should also have a contingency note for small items.
4. Weight and Price
The calculation sheet should ideally have space for entering the item’s unit weight and price. This way, it will be possible to calculate the total weight and cost of all fittings as shown below.
With this information, it becomes easy to come up with the total material cost and weight.
When the fitting calculation sheet is connected to the design, it becomes possible to update quantity automatically when the design changes.
How to Build a Reliable Excel Workbook
A reliable excel workbook need not have many fancy functions. A simple well-organized sheet is always easier to maintain and update.
Keep Inputs Separated
The calculation sheet should be connected to a inputs sheet. This way, it will be possible to update multiple calculations at once. Some typical inputs are;
- Pipe length, material, and size
- Tanks and chambers
- Number of branch lines and valves
- Design pressure and contingency
The calculation sheet should always reference these values using excel connections. That way, it will be possible to make large-scale adjustments without having to update every cell in the sheet.
Use a Master Fitting Table
Every good calculation sheet comes with a master fitting table. The table should contain all the fittings needed for the project together with their material, size, pressure, unit, weight, and rate.
Formulas such as XLOOKUP and VLOOKUP can be used to pull this information into the calculations sheet. This approach is particularly useful if the fitting rate or weight changes.
Add Error Checks
The calculation sheet should have error checks to help detect design errors. Some of the error checks that should be included are:
- Fitting size not matching with pipe size
- Valve without a pressure class
- Tee without the branch diameter
- Flanged joints without gaskets or bolts
- Missing fitting quantity
- Wrong material fittings for specific fittings type
Conditional formatting can be used to highlight these errors on the calculation sheet. Follow Engineering Standards
A proper calculation sheet goes beyond just quantity calculations. It should also enforce engineering standards. For example, the master table should have the relevant code for HDPE, GI, and ductile iron fittings as well as valves.
The right code may be selected using data validation. This way, it will be possible to avoid manual entry of values that do not conform to engineering standards.
Turning Accurate Estimates Into Site Results
With the help of a well-designed calculation sheet, every water supply project estimator can provide the project team with an accurate material list for the project. In addition to saving the cost of extra fittings, the calculation sheet can also be used for a wide range of purposes including:
- Budgeting
- Tendering
- Material comparison
- Material inspection
- Store management
- Variation processing
- Progress payment
Excel is a fantastic tool for civil engineers. It has been used extensively in the construction industry to create reliable calculation sheets. An excel calculation sheet helps the estimator to move from design to construction smoothly. Even better, it ensures that the little things such as fittings are not forgotten or overlooked.
Fittings are crucial for every water supply scheme. Without them, it is impossible to construct even a simple pipeline. The right fitting calculation sheet should contain a comprehensive list of all fittings required for a water supply project. Although some fittings such as valves are easy to count, others such as couplings and bolts need accurate calculation.
FAQs
1. Which fittings are most often missed in water supply estimates?
Small fittings and accessories are often missed in fitting calculations. Such fittings include washers, bolts, nuts, and flange adaptors. Engineers are tempted to ignore the small items such as gaskets, valves, and couplings. At the same time, it is these fittings that cause installation delays and leakages if they are not available at the job site.
2. How should an Excel sheet handle HDPE, GI, and other pipe materials?
HDPE and GI pipes have different fittings. HDPE pipes fittings include compression, electrofusion, and butt fusion fittings. On the other hand, GI pipes fittings include threaded and flanged fittings. To avoid mixing the two, it is best to make separate calculations for HDPE and GI.
3. Should the sheet include a fitting contingency allowance?
A contingency allowance should always be provided for fittings. This is mainly due to additional fittings that are required due to site-specific fitting wastage, damaged fittings, and changes in routing. The contingency percentage should be between 3% and 5% for fittings.
4. Can Excel assign pressure classes automatically?
Yes. Excel can be used to automatically determine the pressure class for valves. This can be done using lookup tables, IFS function, and XLlOOKUP function. For example, a XLlOOKUP table can be used to select between PN6, PN10, PN16, and PN20 based on the working pressure, pipe size, and system.
Unzip Pw: digitaldeni