Justifying Speadsheet Calculation Against Modelling Software

[palta79]

Hi All,

I am trying to justify the use of spreadsheet calculation instead of hydraulic model(Info Works or similar) for hydraulic analysis of a small exisiting catchment of sewer diameter 150mm. This query is in relation to a project in the United Kingdom.

The project involved hydraulic assessment of existing sewer network to understand the cause of internal flooding of a residential property, confirming the flood volume for a 30 year return period, which is a design standard as per the client and finding solution-most likely to be upsize or offline storage tank.

There was no hydraulic sewer model available or provided by the client for the cacthment. Due to the size of the catchment and the diameter of the sewer-150Ø, a good level of flow verification was not anticipated. As a result it was decided that a spreadsheet calculation would suffice to confirm the carrying capacity of the existing sewer without the need for the higher costs of generating a computer model.

A Flow survey was carried out for a period of 6 weeks. Two rain gauges were also installed to estimate the rainfall within the catchment during the survey.

I prepared a spreadsheet based on Rational Formula(for run off calculation) and Manning Equation(for pipe carrying capacity).

Using the flow survey data, i.e. litres per second,'Qobserved', for a particular rainfall intensity,'I', observed during the survey, I tried to play with the contributing area,A, and the co-efficient of run-off, C, to match the flow observed during the survey against the flow predicted by the spreadsheet,using Rational formula. I selected three rainfall events, short, long and medium duration. Once a good level of verification was achieved for all the three events which ensured that the model will predict reasonable run off data for the various storm duration, then I used design rainfall intensity, for a storm return period of 30 year to predict the flow expected in the system. Comparing the Qactual versus Qfull, I identified the undercapacity sewers and found solution accodringly. Qactual is the verified flow based on the rational formula for a 30 year return period and Qfull is based on manning equation.

I followed the above mentioned steps using a spreadsheet model without the need of generating a hydraulic model.

I am going to apprear for my professional review and I am expecting some difficult questions to be raised since my method may not be considered as traditional way of undertaking flood investigation.


I would like your view in this regard. Please ask me some difficult question so that I can prepare myself for the worst case scenario.

Regards

 

[francesca]

I'm not sure that your method is not "traditional." Software applications like SWMM which would usually be used for this nowadays haven't been accessible for very long. You seem to have come up with a few scenarios, but my primary question relates to Tc and how many alternatives you considered regarding times of concentration. In a storm sewer network it's possible that a worst-case condition in a particular culvert does not relate to the basin-wide storm event (lower intensity), but rather to a higher-intensity, local storm event with higher intensity over a lesser contributing area. Did you have any observed storm events (intensity, duration, flow)?

 

[cvg]

while Manning's equation is fine for estimating the conveyance in a pipe or channel at normal depth, you generally also need to use either a step-backwater method to calculate the hydraulic grade line for sub-critical flow or perhaps weir or orifice equation for the inlets on sewers which have super-critical flow. Very few sewers are able to run at mannings theoretical Qfull capacity. This generally adds significant complexity to your spreadsheet model.

 

[beej67]

I would only rely on the procedure you enumerated if you are demonstrably sure that there are no backwater effects in your system. But if you can be sure there's no backwater, then the procedure you indicated isn't too bad, as long as the calibrated basin characteristics you generated from your flow metering are similar to the basin characteristics you get when you do a typical delineation.

For what it's worth, some areas of the US require an additional factor of safety on the Rational Method once you get equal to or beyond the 25 year event. 25yr = x1.1, 50 = x1.2, 100 = x1.25 This factor of safety isn't universally applied, but it's supposed to compensate for the fact that infiltration and other losses make up less of a ratio of total runoff for bigger storms after they reach their capacity. I wouldn't worry about such a factor for highly impervious watersheds, but for mostly pervious ones it might mean something.



Hydrology, Drainage Analysis, Flood Studies, and Complex Stormwater Litigation for Atlanta and the South East -

http://www.campbellcivil.com

 

[stanier]

What did engineers do before computers? There are many working sewer systems out there that relied upon hand calculations. Whether you use a modellling software package, spreadsheets or an abbacus the same criteria needs to be used as described above. Get that wrong and and calculation will be flawed.

“The beautiful thing about learning is that no one can take it away from you.”
---B.B. King

http://waterhammer.hopout.com.au/