The Lotus 1‐2‐3 spreadsheet package has been used to develop a stormwater drainage design method which is based on the Florida Department of Transportation's (FDOT) hand tabulation form (FDOT 1987). By using the FDOT procedure, the spreadsheet method allows engineers already familiar with the manual calculation procedure to more easily adapt to the new microcomputer environment. The spreadsheet performs the necessary calculations quickly and easily while not hindering the user with tedious input procedures and confusing algorithms typical of many computer codes. The spreadsheet method also employs a heuristic cost estimation approach which allows the user to find a least cost design. This method has been used to solve a problem previously solved with two dynamic programming algorithms. Since the spreadsheet method does not use a sophisticated optimization algorithm, many simplifying assumptions made in the dynamic programming algorithms are not necessary. The results show that the spreadsheet method was a...

Better Than “Optimal” Method for Designing Drainage Systems

An optimization procedure has been developed for branching storm and sanitary sewer systems with a pre-determined layout for determining the minimum total cost. The model was developed within Microsoft Excel using simulated annealing as the optimization procedure. The total cost of the storm sewer system that was obtained with this optimal design procedure was compared to the total cost of the system as obtained from the conventional straight slope design procedure. Applying the simulated annealing optimizer to the design of the branching storm sewer network resulted in a cost savings of over $77,100 or about 7 % (a reduction from $1,117,700 to $1,040,600). These significant savings were realized by simply going an extra step and implementing an optimization technique during the design phase. Use of Excel should enhance the availability and the usage of such an optimization model for the design of storm and sanitary sewer systems by consulting engineers and various agencies.

Sewer System Design Using Simulated Annealing in Excel

The design of urban stormwater systems and sanitary sewer systems consists of solving two problems: generating a layout of the system and the pipe design which includes the crown elevations, slopes and commercial pipe sizes. A heuristic model for determining the optimal (minimum cost) layout and pipe design of a storm sewer network is presented. The hierarchical procedure combines a sewer layout model formulated as a mixed-integer nonlinear programming (MINLP) problem which is solved using the General Algebraic Modeling System (GAMS) and a simulated annealing optimization procedure for the pipe design of a generated layout was developed in Excel. The GAMS and simulated annealing models are interfaced through linkage of Excel and GAMS. The pipe design model is based upon the simulated annealing method to optimize the crown elevations and diameter of pipe segments in a storm sewer network using layouts generated using GAMS. A sample scenario demonstrates that using these methods may allow for significant costs saving while simultaneously reducing the time typically required to design and compare multiple storm sewer networks.

Heuristic Optimization Model for the Optimal Layout and Pipe Design of Sewer Systems

A Microsoft-Excel spreadsheet was developed using Visual Basic for Applications (VBA) functions to calculate fundamental wave properties using linear wave theory, simple wave transformations, tidal elevations, tsunami travel time, wind wave growth parameters, design wave heights using Raleigh's probability distribution, breakwater dimensions, and longshore sediment transport rates. This spreadsheet was used in teaching an undergraduate coastal engineering course. The students were found to be motivated to study the subject matter with the help of VBA functions and develop their own applications in coastal engineering. This spreadsheet may be helpful to the practicing engineers as well for calculations relevant to some important wave parameters that are used in the design of coastal structures. © 2017 Wiley Periodicals, Inc. Comput Appl Eng Educ 25:304–310, 2017; View this article online at wileyonlinelibrary.com/journal/cae; DOI 10.1002/cae.21798

Teaching fundamental concepts of coastal engineering using excel spreadsheet

. ! \" # $% &' ()' *+, , &. / 0 1 2 3 4 5# *, 6 7 8 ) $% . . *, &$ : ; ; < 4 5# = . > ! < \" # & ? @ %?' &$ ?; A & ? @ % ?( B $% % ?B 0 1 2 C DB ?! 56B . 2 = E . F ?G. H?DE B < 0 I J ? 4() , A $! > 6B < \" # &B? 2 *, , K $% ? % &! % &L MB *) * , ? / (G * *, ! ?(, ! %?' . $ , / # & $ 6 ! A ? L N# B A ?; ?B *, ! . 6 $ ! , 3% % K ?! 6 ?B & 0 ?D O &B 36 P( , Q 7 O *, RB &L/ S , 6 $ 1T)B %?36 # :, 6B $! 6 B U 8 % 6( V )B . ?3 4( ; P(B & # & $ 6 W ! $ , +,?; (!U' ( O % 1 F B & F ? ; $! B % 7 ?8 B % K 7 L/ & B % K 7

/pdf/optimal-design-of-sewer-networks-using-genetic-algorithm-212fvvo57w.pdf

Optimal Design of Sewer Networks Using Genetic Algorithm

The ability to accurately route flows through storm drain networks relies not only on the skill of the engineer, but also on the resources at his disposal. The purpose of this paper is to stress the advantage of using a shell system, in this case LOTUS 123, to perform storm drain designs. Pipe segments are conceptualized as storage elements, distributed or concentrated. The proposed analytical method uses analytical, algebraic relationships derived from linear diffusive wave theory and provides reasonably accurate designs at a very modest computational effort. This procedure is applicable to desktop calculations, but the utilization of the LOTUS environment provides the greater ease and efficiency necessary for routine use.

Antonis D. Koussis

Papers

LOTUS Spreadsheet Design for Storm Drain Networks