1
THE APPLICATION OF SPREADSHEETS TO THE ANALYSIS AND
OPTIMISATION OF SYSTEMS AND PROCESSES IN THE TEACHING
OF HYDRAULIC AND THERMAL ENGINEERING
A. Rivas, T. Gómez-Acebo, J. C. Ramos
TECNUN (University of Navarra)
Paseo de Manuel Lardizábal, 13. 20018 San Sebastián (Spain)
Tel.: 943 21 98 77, Fax: 943 31 14 42
e-mail: [email protected]
ABSTRACT
This paper shows the capability of current spreadsheets to define, analyse and
optimise models of systems and processes. Specifically, the Microsoft
spreadsheet, Excel®, is used with its built-in solver, to analyse and to
optimise systems and processes of medium complexity whose mathematical
models are expressed by means of non-linear systems of equations.
Two hydraulic and thermal engineering-based application examples are
presented, respectively: the analysis and optimisation of vapour power
cycles, and the analysis and design of piping-networks. The mathematical
models of these examples have been implemented in Excel® and have been
solved with the solver. For the power cycles, the thermodynamic properties
of water have been calculated by means of the add-in TPX®
(Thermodynamic Properties for Excel). Performance and optimum designs
are presented in cases studies according to the optimisation criteria of
maximum efficiency for the power cycle and minimum cost for the piping-
networks.
2
KEYWORDS: systems, processes, analysis, optimisation, mathematical
modelling, spreadsheets, thermodynamic cycles, piping-networks.
1. Introduction
In almost all fields of Engineering there are substantial numbers of problems that can
be mathematically expressed as a system of non-linear and, sometimes, non-analytic algebraic
equations. These problems are encountered not only in the analysis of the system but also in
the search for the system’s optimal design. In the latter case there are usually a great number
of parameters, constraints and interrelations which affect the variables of the problem,
preventing solution by means of trial-and-error methods. Two examples of engineering
problems which involve the analysis and optimisation of systems and processes are found in
thermodynamic cycles and pipe networks.
Various methods have been used, to date, to analyse and optimise systems and
processes of this type that have been mathematically modelled as a set of non-linear
equations. One of the first responses to the problem of analysing and optimising
thermodynamic cycles is that of Valente and Pina [1], who present a method based on the
formulation of the balance of mass and energy laws to perform computer-aided analysis and
design of thermodynamic cycles for steam power plants. Optimisation is performed to find the
best cycle according to a user-selected group of criteria and constraints.
Ibrahim et al. [2] utilise the method of Lagrange multipliers for the optimisation of the
power output of Carnot and closed Brayton cycles. The optimum heat power cycle, which will
provide the upper limit of power obtained from any thermodynamic cycle for specified
boundary conditions and heat exchanger conductances, is considered. The optimum heat
power cycle is identified by optimising the sum of the power output from a sequence of
Carnot cycles.
3
The theory of minimum energy cycles is applied by Moraitis and Akritidis [3] to obtain
a numerical solution of a model which describes the optimal operation of a heat pump for
drying applications.
Erbay and Yavuz [4] use the maximum power density technique to optimise a Stirling
heat engine operating in a closed regenerative thermodynamic cycle. Maximised, both power
and power density are obtained for different values of the main parameters.
Lu and Goswami [5] develop an optimisation program, based on the Generalized
Reduced Gradient algorithm, to optimise a combined power/refrigeration thermodynamic
cycle and to analyse its thermal performance over a range of ambient temperatures.
More recently, Lu et al. [6] present a practical method to optimise centralised Heating,
Ventilation and Air-Conditioning (HVAC) systems. The mathematical models for heat
exchangers and energy consuming units are defined considering cooling loads and energy
consumption. To model duct and pipe networks, an Adaptive Neuro-Fuzzy Inference system
(ANFIS) is employed. The system is solved by a modified genetic algorithm with a mix-
integer nonlinear constraint optimisation.
For the problem of pipe-network analysis and optimisation the number of approaches
and methods presented in the bibliography is huge. One of the first studies is that of Boulos
and Altman [7], who develop an explicit algorithm for nonlinear constrained pipe network
optimisation using the incremental Newton-Raphson method. Optimal design parameters are
calculated to meet the pressure and flow constraints of one application example.
Mohtar et al. [8] develop a model that optimises pipe networks based on economic
parameters using the life-cycle costing technique and the results of finite element analysis.
One of the first applications of the genetic algorithm technique being used for the
optimisation of pipe-networks was made by Walters and Lohbeck [9] and Simpson et al. [10].
Later, Dandy et al. [11] apply an improved genetic algorithm which uses variable power
4
scaling of the fitness function and introduces an adjacency or creeping mutation operator. The
method is applied to the New York City tunnels’ problem and the results are compared with
other traditional methods. Al-Khomairi and Imam [12] uses a technique called gene therapy
to improve the performance of Genetic Algorithms when applied to the problem of
optimisation of pipe networks. Finally, Ostfeld and Salomons [13] describe the methodology
and application of a genetic algorithm scheme for optimising the operation of a water
distribution system under unsteady water quality conditions. The objective is to minimise the
total cost of pumping and treating the water within the requirements of certain supply
conditions and constraints.
Following with other types of approaches, Basha and Kassab [14] use a perturbation
method to transform the set of nonlinear equations into a series of linear equations that can be
solved easily using matrix methods. For simple examples they obtain an acceptable result
with the third-order perturbation equation but for complex examples a fifth-order perturbation
is necessary to improve the accuracy of the results.
Ahn and Loganathan [15] applies a global search method called Stochastic Probing to
find the minimum system design cost of a water distribution system subjected to hydraulic
and operational constraints.
Sung et al. [16] present a hybrid network model that uses a minimum cost Spanning
Tree Network algorithm to determine the optimum path and a Constrained Derivative method
to select an optimum pipe diameter.
Other methods applied to the optimisation problem of pipe network systems are the
Back Analysis techniques [17], the Hardy-Cross algorithm implemented in Mathcad [18] and
the enumeration theory (consisting of the initial feasible solution procedure, the decreasing
upper bound procedure, and the system's lower bound procedure) [19].
5
Gorikanec and Krope [20] use a numeric Simplex optimisation method to obtain the
optimal design of tree path pipe networks. The nonlinear objective function is the
minimisation of the costs, and a system of hydraulic restriction inequalities is added to the
mathematical model, that is then solved implicitly.
Bhave and Gupta [21] apply a modified fuzzy linear programming model for
minimum cost design of water distribution networks including loop-head loss constraints.
Finally, Gosselin and Bejan [22] employ the Lagrange multipliers method to the
optimisation of tree fluid networks based on the minimisation of pumping power requirement.
In this type of study the pipe network volume is constrained and the solution is the tree
architecture of the network.
Nowadays, due to the rapid increase in computational power, spreadsheets are
becoming an alternative computing tool to the previously mentioned methods. The
spreadsheets are usually accessible by any engineer having a PC, are of easy handling and are
capable of performing complex calculations because they include advanced mathematical
functions (add-ins). This calculation power is increased because most spreadsheets include a
solver, an additional module to resolve complex systems with non-linear equations and
problems of optimisation. Specifically, Microsoft Excel®, the spreadsheet used in this paper,
includes a solver developed by Frontline System® [23], implementing the GRG2 method to
solve problems of non-linear restricted mathematical programming [24].
The drawbacks of the spreadsheets increase with the size of the problem to be solved
because the calculation speed slows down and because the difficulty of defining the
mathematical model rises. But when solving problems of a medium size, they represent a fast
and powerful alternative to other calculation platforms and a preliminary means of finding
ways to solve problems of higher size.
6
In this sense, there are several examples of spreadsheet applications being used in
different engineering problems involving analysis and optimisation of systems and processes.
Paradoxically, however, no applications to thermodynamic cycles nor pipe-network systems,
as presented in this paper, have been found.
One of the first applications of spreadsheets in engineering calculations was reported
by Rosen and Adams [25] who carried out a review of the use of the electronic spreadsheets
in chemical engineering calculations and process design.
Silva [26] presents the application of spreadsheets as an interactive tool for modelling
and simulation in the teaching and learning of electrical circuits.
Advanced machine design problems involving optimisation concepts can be analysed
using spreadsheets [27]. In this article, linear and non-linear examples were modelled on
current versions of Microsoft Excel®, QuattroPro® and Lotus®. A comparison of data
entering and the presentation of solutions are discussed.
Gupta [28] presents the application of the solver embedded in Microsoft Excel® for
quantitative interpretation of counter-current imbibition experiments on reservoir core
samples. The tests involve minimisation of a user-defined global objective function with
respect to various model parameters.
The use of spreadsheets in an undergraduate course in process engineering is analysed
in [29]. The use of the Excel® solver as a tool to solve optimisation problems with iteration
cycles in the calculation of the variables involved and as an introductory approach for the
basic aspects of process simulators is presented.
Thiriez [30] shows how Operational Research education may be improved through the
use of spreadsheets in the case of Linear Programming - where the solver is used, and in the
case of Simulation - where it is seen how Stochastic Simulation may be facilitated through the
use of an add-in.
7
Ferreira et al. in [31] and in [32] compare the cost of learning to handle complex
optimisation tools at the undergraduate level with the ease of the Microsoft Excel®
spreadsheet solver when used for the optimisation of several chemical engineering systems,
including a classic solvent extraction/pollution prevention with heat integration process.
Schumack [33] describes how the solver tool in Microsoft Excel® can be used to solve
nonlinear systems of equations resulting from Finite Differences discretisation of the
governing equations for Fluid Dynamics and Heat Transfer. In addition the spreadsheet model
is valuable for giving students an intuitive feel for discretisation before they go on to write
code.
The last mentionable case is the use of a spreadsheet program introduced as a platform
for teaching engineering students to solve thermal radiation problems, particularly problems
relating to multi-surface enclosures [34].
In this paper two examples of the use of Microsoft Excel® with its solver and the add-
in TPX® (Thermodynamic Properties for Excel) [35] are presented. The two application
examples refer to the analysis and optimal design of power cycles and pipe-networks,
respectively.
2. Application to the analysis and the optimisation of thermodynamic cycles
2.1 Mathematical model
A thermodynamic cycle can be described as a set of devices interconnected through a
number of nodes. The fluid flows in/out a device from/to another device of the cycle. The
equipment of the cycle interacts with its environment by exchanging energy in the form of
heat or work. Analysis of the cycle permits the thermodynamic properties of the fluid in every
node, the mass flow rates between the devices, and the power in the form of heat or work
8
interchanged with the environment, to be determined. Knowing these values it is possible to
calculate certain characteristic parameters of the cycle, such as the efficiency.
For simplicity, the analysis is carried out according to the following assumptions:
• The cycle is under conditions of steady state.
• The variations of mechanical energy of the fluid are neglected.
• The devices are classified into work or heat equipment. The work equipment is
assumed to be adiabatic (i.e., no heat is exchanged with the environment) and in
the heat devices there is no work exchange with the environment.
• In the connections between the different devices and in the nodes the fluid does not
experience any thermodynamic process. This assumption implies head and heat
losses in the pipe system are neglected.
The thermodynamic properties in a node i are not independent. So, if the fluid is a pure
substance, two properties, Χi, are independent and the rest, Υi, are related to the previous
through the state relations, Fi, which are assumed known:
( )iii XFY = (1)
The nodes’ independent properties, X, consist of a group, U, whose values are fixed by
the operational conditions and which are related to the problem’s degrees of freedom, and a
group, V, whose values are obtained from the equations governing the cycle behaviour, and
which are related to the problem’s unknowns:
• The continuity equation applied to every device is expressed as:
0=− ∑∑ →→k
Ikj
jI mm && (2)
where jIm →& and Ikm →& are the mass flow rates flowing from the device I to the
node j and from the node k to the device I, respectively. From these continuity
9
equations, it is possible to express the different mass flow rates of the whole cycle
as a function of one of them, fixed by the operational conditions. Usually this mass
flow rate is set to the value of unity and the rest, m& , are determined relative to it.
• The conservation of energy equation applied to every device is then:
IIkk
Iki
jjI WQhmhm &&&& −=− ∑∑ →→ ·· (3)
where hj is the enthalpy per unit mass in the node j connected to the device I. The
enthalpy can be an independent thermodynamic property, and its value is obtained
after solving the mathematical model (V), an independent property whose value is
fixed by the working conditions (U) or a dependent property (Y). IQ& and IW& are,
respectively, the heat and work power exchanged by the device I with the
environment. The values of these terms can be previously known or be obtained by
solving the mathematical model.
In the previous equations the outlet mass flow rates and the outlet work power are
considered positive and the outlet heat is considered negative.
The previous equations can be written in a compact form:
( ) 0WmXM = ,, & (4)
where m& and W are the unknown mass flow rates and heat of work power,
respectively.
• The process equations. These equations relate the thermodynamic properties of
two nodes of the cycle:
( ) 0XXΠ =jii , (5)
Xi and Xj being the thermodynamic properties in nodes i and j. These equations
can be expressed in a compact form:
10
( ) 0XΠ = (6)
An example of these last equations could be the equation for a pump (represented
in Figure 1) with an efficiency ηB. The thermodynamic properties in its nodes are
the pressure and temperature at the inlet and the pressure and enthalpy at the outlet.
The relation between these properties is:
( ) ( )[ ] ( ){ }iiiiiijB
iiij T,PhT,Ps,PhT,Phh −⋅η
+= 1 (7)
where h, P, T, s and ηB are respectively enthalpy, pressure, temperature, entropy
and the performance of the pump.
2.2 Thermodynamic cycle Analysis Problem solution using Microsoft Excel®
Once working conditions have been established by specifying the values of U-
thermodynamic properties and the efficiencies of the devices, analysis of the cycle consists of
obtaining the remaining unknowns, i.e., the V-thermodynamic properties, mass flow rates,
m& , and work power and heat, W.
V-thermodynamic properties can be obtained from equation (6). This equation can
require the use of the state relationships between thermodynamic properties. There are several
commercial applications developed as an add-in to Microsoft Excel® that allow the
determination of the thermodynamic properties of fluids of technical interest. In this work the
application TPX® has been chosen.
Once the thermodynamic properties are obtained, equation (4) can be used to calculate
the unknown mass flow rates and power.
All the relationships included in the mathematical model of the cycle have been
written in Microsoft Excel® and the unknown quantities have been solved following the
previously described scheme with the help of TPX® and its solver.
11
2.3 Thermodynamic cycle Optimisation Problem solution using Microsoft Excel®
The optimisation of a cycle implies the determination of several parameters, δ,
including some thermodynamic variables, which establish a working condition that can be
considered as optimum according to a previously defined criterion. In this paper the
maximum energy efficiency, η(δ, X, m& , W), has been chosen as criteria for an optimum
operation condition. It is possible to adopt other criteria, such as the maximum exergy
efficiency or the minimum cost, but they require more information and a more complex
model.
Moreover, the solution must fulfil several imposed constraints. These can be expressed
as mathematical relationships with the following form:
( ) 0WmXδR ≤ ,,, & (8)
The optimisation of the thermodynamic cycle is expressed mathematically as:
( ) ( ) ( ){ }0WmXδR0WmXδMWmXδUWmδ
≤= ,,,,,,,;,,,η,,,
max &&&&
(9)
where M represents equations (4) and (6).
2.4 Example of Analysis Problem solution of a thermodynamic cycle.
The resolution of a vapour power cycle is presented as an example. The schematic
diagram is shown in Figure 2. It is a regenerative Rankine cycle with three closed feedwater
heaters. The working conditions are:
• The boiler produces vapour at 12 MPa and 450 ºC.
• The pressures of the turbine extractions are 5, 0.8 and 0.2 MPa.
• The condenser pressure is 10 kPa.
• The turbine and the pump are adiabatic with an isentropic efficiency of 85%.
12
• The condensed liquids in the condenser and in the closed feedwater heaters are
saturated liquids.
• In the regenerators the temperature difference (∆T) is 5 ºC.
Table 1 shows the independent thermodynamic variables fixed by the working
conditions, U, and the unknown thermodynamic variables, V, with the relationship (6) to
solve them as a function of the former.
The unknown mass flow rates are the fractions extracted from the turbine:
{ }γβα=Tm& (12)
and the unknown power:
{ }TURCONCAL WQQ &&&=W (13)
Once the thermodynamic properties are obtained, m& and W are calculated from
equation (4). The results are: α=0.2277, β=0.0582, γ=0.1224, CALQ& =2079.4 kJ/kg, CONQ& =-
1319.3 kJ/kg and TURW& =-760.1 kJ/kg. The cycle efficiency is 36.6%.
2.5 Thermodynamic cycle Optimisation Problem solution example
The cycle described in the previous section (Figure 2) is optimised to obtain the
maximum energy efficiency. The optimisation parameters are: the steam pressure and
temperature at the boiler exit (P1 and T1), the pressures of the turbine extractions (P2, P3 and
P4), the turbine exit pressure (P5) and the feedwater heaters temperature difference (∆T):
{ }TPPPPTPT ∆= 543211δ (14)
The thermodynamic properties with an imposed value are:
{ }1513116 xxxxT =U (15)
13
The adopted design constraints are:
• The maximum allowed pressure and temperatures of the steam produced by the
boiler: 16 MPa and 550 ºC, respectively, (P1 ≤ 16 MPa and T1 ≤ 550 ºC).
• The minimum value for the condenser outlet water temperature: 35 ºC (T6 ≤ 35
ºC).
• The temperature difference in the closed regenerators: 5 ºC (∆T ≤ 5 ºC).
The optimisation results are shown in Table 2.
As expected, pressure and temperature at the boiler and condenser exits take the
allowed extreme values.
Following the same procedure it is easy to analyse the effect on the optimum
efficiency of the different working conditions. For example, these conditions might be the
level of sub-cooling liquid after the condenser, and the turbine and pump isentropic
efficiencies. Other types of thermodynamic cycle can be analysed and optimised too,
providing the state equation of the working fluid is known.
3. Application to the analysis and the optimisation of pipe-networks
A pipe-network is a group of interconnected elements (pipes, valves, tanks and other
auxiliary elements) whose mission is to transport certain amount of fluid under certain
conditions from the production or storage points to the use points.
The working conditions of a pipe-network are the boundary conditions and the state of
the elements. The former usually refer to the elevation of the storage tanks and to the demand,
and the latter to the pumps’ speeds and the valves’ opening level. The pipe-network operation
can be stationary, if the working conditions do not vary with time, or transient, if they do. In a
strict sense, a network is always a dynamic system and its behaviour is never totally
stationary. Nevertheless, in some cases the working conditions vary so slowly, or during such
14
a long period of time, that they can be considered constant. The network can then be analysed
as under steady-state conditions. On the other hand, in other cases the variations in the
working conditions are so fast and important that the pipe-network must be analysed as a
dynamic system.
In the present work the pipe-network is studied under steady-state working conditions
and its fluid flow is assumed incompressible. With this initial hypothesis and from the
mathematical modelling point of view, the network is considered as a group of junctions
connected by lines. A line is a set of elements joining two nodes where fluid flows at the same
rate. The hydraulic variables representing pipe-network behaviour are: the piezometric heads
(the sum of pressure head and elevation) in the nodes, H, the volumetric flow rates injected
into or extracted from the network through the nodes, Q, and the volumetric flow rates
flowing through the lines, q. The mathematical model providing the mentioned hydraulic
variables is formed by a system of non-linear algebraic equations that relate the hydraulic
variables and the network elements characteristics, such as head losses coefficients or energy
added to or retired from the fluid, to the working conditions, the boundary conditions and the
state of the elements.
3.1 Mathematical model
The equations of the pipe-network mathematical model are obtained by applying the
energy conservation equation (Bernouilli) between the nodes and the continuity equation to
each node [36]:
( ) ( )linesnetwork ∈∀
−=−
ijqHqhHH ijijijijji (16)
where Hi and Hj are the piezometric head in the extreme nodes of the line, hij are the energy
losses of the fluid when going through the line, Hij is the energy given to (Hij>0) or extracted
15
from (Hij<0) the fluid in the line and qij is the volumetric flow rate going through the line. For
example, for the case of a line with one pipe, the expression of equation (16) would be:
ijijijji qqRHH ··=− (17)
ilki
Qqq il
lik
ik
node toconnected and nodesnetwork
0
∀∈∀
=+− ∑∑ (18)
where qik is the flow rate of the line ik that begins at node i, qli is the flow rate of the line li
that ends at node i and Qi is the flow rate injected into (Qi<0) or extracted from the network
(Qi>0) through the node i.
To complete the mathematical model the boundary conditions and the state of the
elements must be added.
The boundary conditions are imposed at the nodes and can be of two types. The first
establishes the piezometric head of the node (representing a tank connected to that junction),
with the the flow rate, Qi , as the unknown variable at the node. The other type of boundary
condition imposes a known flow rate at the node (representing a demand) with the
piezometric head, Hi, as the unknown.
The states of the elements (pumps, valves, turbines) have an influence on the hydraulic
performance of the pipe-network by modifying the characteristic equation of the line where it
is mounted. For example, assuming that the line ij is formed by one pipe and one valve, the
opening level of the valve, ζ, determines its loss coefficient, Rv, and the characteristic
equation of the line is thus:
( )linesnetwork
2
∈∀
⋅ζ+⋅⋅=−
ij
qRqqRHH ijVijijijji (19)
16
3.2 Analysis Problem of pipe-networks using Microsoft Excel®
Once the mathematical model has been established, the first type of problem that can
be considered is the analysis problem, which consists of solving the hydraulic variables of the
network for a set of known working and boundary conditions.
The mathematical model of a pipe-network is given by a system of non-linear
algebraic equations:
( ) 0δQqHM = ,,, (20)
where M is a vector of the equations; H, q and Q are the vectors of the network hydraulic
unknowns, and δ, the vector of the design parameters.
In the Analysis problem, δ is fixed and the solution is obtained by solving the
mathematical model:
( ) 0QqHM = ,,* (21)
This problem can be also mathematically expressed in terms of H, q and Q by
minimising the objective function η defined as:
( ) *T*,,η MMQqH ⋅= (22)
η is the magnitude of the residue of the mathematical model equations. Among the
solutions minimising the objective function there is only one that also provides a solution to
equation (21). Therefore, this equation must be added as a constraint. The minimisation
problem is then mathematically expressed in the following form:
( ) ( ){ }0QqHMMMQqHQqH
=⋅= ,,;,,η,,
min **T* (23)
Expression (23) can be read: “find values of H, q and Q which minimise η and are
constrained to ( ) 0QqHM = ,,* ”.
17
3.3 Optimal Design Problem of pipe-networks using Microsoft Excel®
The optimal design problem of a water distribution network consists of searching the
values of certain design parameters (usually the pipe diameters and the pumping heads) in
such a way that, for known working conditions, the network has a fixed performance relating
its hydraulic variables.
The hydraulic variables must satisfy the mathematical model, equation (20), and,
moreover, the required hydraulic performance. The constraints to be taken into account by the
design parameters can be mathematically expressed as:
( ) 0δQqHR ≤ ,,, (24)
With equations (20) and (24) the solution to the Optimal Design problem is not
determined because there are several solutions. It is necessary to add a criterion satisfied by
one of the solutions to assure which is the optimum.
This design criterion can be that the required solution was the optimum from an
economical point of view, and its total cost, the sum of the construction and operating costs,
CT, was minimum. Mathematically, this is expressed as:
( ) ( ) ( ){ }0δQqHR0δQqHMδQqHδQqH
≤= ; ,,,,,,;,,,C,,,
min T (25)
3.4 Example of a pipe-network Analysis Problem
As an example of this problem the pipe-network of Figure 3 will be solved. The
network has 13 nodes and 16 pipes and the fluid is water. The pipes and demand data are
given in Table 3. All pipes are of steel with a roughness of 0.3 mm.
The unknown variables vectors are:
{ }1110987654321 HHHHHHHHHHHT =H
18
}qqqqqqqqqqqqqqqq{T
1710198810101111991367
78852665544332212
−−−−−−−−
−−−−−−−−=q (26)
{ }1312 QQT =Q
As the network is only formed by pipes, the equation for all the lines is similar to
equation (17), where Rij is given by:
ijij gD
LfR
=
····852π
(27)
where f is Darcy’s friction factor and L and D are the pipe length and diameter. Darcy’s
friction factor has been calculated with the PSAK expression [37].
This Analysis Problem was written on the spreadsheet Microsoft Excel® and was
solved with its solver. The results obtained are in Table 4.
The negative volumetric flow rates indicate that the water is flowing in the opposite
direction to that represented in Figure 3.
3.5 Example of a pipe-network Optimal Design Problem
The example for this type of problem is the pumping network represented in Figure 4.
The network has 7 nodes and 8 lines and works for 6000 hours of the year. The pump
has an efficiency of 75% and the cost of the kW·h is 0.18 €. The remaining data are presented
in Table 5.
The pipes are of commercial steel with 0.3 mm of roughness. The cost per linear metre
of these type of pipes can be related to the diameter by means of the following expression:
( ) aL D·ADc = (28)
19
where cL is the cost in €/m and D is the pipe diameter in metres. The values of the coefficients
A and a in this example are 1800 and 1.5, respectively.
The hydraulic variables of the problem are:
{ }765432 HHHHHHT =H
{ }6756473625342312 qqqqqqqqT =q (29)
{ }1QT =Q
The characteristic equations for all the lines are similar to equation (17), except for the
line between nodes 1 and 2, whose equation is:
BHqqRHH −=− 12121221 ·· (30)
where HB is the head provided by the pump in this line.
The design variables are the pumping head and the pipe diameters:
{ }TB
T H Dδ = with { }6756473625342312 DDDDDDDDT =D (31)
In the desired hydraulic performance of the network, the pressure in the consumption
nodes cannot be lower or higher than certain values, which, in this case, are 30 and 45 meters
of water column, respectively. This condition is expressed mathematically as:
m.w.c. 30m.w.c. 54 =
γ
≥
γ
≥
γ
=min
i
max
ppp (32)
where γ is the specific weight of water and pi is the pressure at the consumption node i. This
can also be expressed as:
minmax HHH ≥≥ (33)
The objective function is the pipe-network total cost, formed by the addition of the
construction cost and the operation cost:
20
ECT CCC += (34)
In the construction cost the main component is the cost associated with the pipes. This
can be mathematically expressed as:
( ) ∑α=ij
ijaijC L·D·A·C D (35)
where CC is the construction cost in €/year and α = 0.1 the amortization coefficient, obtained
from the interest e and the lifetime of the installation, T:
( )( ) 11
1·−+
+= T
T
eeeα (36)
The operation cost is associated with the pump energy consumption. The electric
energy consumed by the pump during one year is:
hB
BB n·q·HE ⋅
ηγ
= 12 (37)
where HB, ηB y nh are the head, the efficiency and the number of working hours of the pump,
respectively.
The electric energy cost is:
( ) hB
BEBEBE n
ηγ·q·H
PE·Pq,HC ⋅⋅== 12 (38)
where PE is the price of the electric energy.
The values of the pipe diameters would be limited to the maximum and minimum of
the commercial series used. This is expressed as:
maxmin DDD ≤≤ (39)
The pipe-network optimum design problem is mathematically expressed as:
21
( ) ( ){ }maxminminmaxT ,,,;,C,,,
min DDDHHH0δQqHMqδδQqH
≤≤≥≥= ; ; (40)
This problem with its variables, objective function and restrictions has been written in
Microsoft Excel® and has been solved with the help of the solver incorporated into this
spreadsheet. The results are shown in Table 6.
The optimum pumping head is 201.96 m.w.c. and the total cost of the installation is
3,037,824.37 €. From these results it can be deduced that the optimal design based on an
economic criterion gives a ramified network, assigning the minimum diameter to the
redundant lines.
4. Conclusions
In the present work the spreadsheet Microsoft Excel® has been used in the definition,
analysis and optimisation of systems and processes whose performance can be described by
means of a mathematical model expressed as a system of non-linear algebraic equations.
Two application examples in the fields of Hydraulic and Thermal Engineering have
been presented: the analysis and optimisation of thermodynamic cycles, and the analysis and
optimum design of pipe-networks. These two problems have been formulated in Microsoft
Excel®. The incorporated solver and the add-in TPX® have been used in their resolution.
The results relating the size of the mathematical models and the speed of resolution are
acceptable. The calculation time has never exceeded one minute using a PC with an Intel
Pentium® processor at 1.7 GHz.
The methodology presented in this paper can also be applied in other fields of
Engineering to resolve problems whose mathematical formulation are similar to that
described here and can be extended to problems formulated by means of systems of
differential equations.
22
Acknowledgements
The authors wish to acknowledge the Antonio Aranzábal Foundation for its economic
support.
References
[1] F. Valente and H. Pina, Computer aided analysis and optimisation of steam power
cycles, Advances in Engineering Software 7(3) (1985), 121-125.
[2] O. M. Ibrahim, S. A. Klein, and J. W. Mitchell, Optimum heat power cycles for
specified boundary-conditions, Journal of Engineering for Gas Turbines and Power-
Transactions of the ASME 113(4) (1991), 514-521.
[3] C. S. Moraitis and C. B. Akritidis, Optimization of the operation of a drying heat pump
using superheated steam, Drying Technology 15(2) (1997), 635-649.
[4] L. B. Erbay and H. Yavuz, Analysis of the Stirling heat engine at maximum power
conditions, Energy 22(7) (1997), 645-650.
[5] S. G. Lu and D. Y. Goswami, Optimization of a novel combined power/refrigeration
thermodynamic cycle, Journal of Solar Engineering-Transactions of the ASME 125(2)
(2003), 212-217.
[6] L. Lu, W. Cai, L. Xie, S. Li and Y. C. Soh, HVAC system optimization—in-building
section, Energy and Buildings 37 (2005),11–22.
[7] P. Boulos and T. Altman, A graph-theoretic approach to explicit nonlinear pipe network
optimisation, Applied Mathematical Modelling 15(9) (1991), 459-466.
[8] R. H. Mohtar, V. F. Bralts and W. H. Shayya, A finite element model for the analysis
and optimisation of pipe networks, Transactions of the ASAE 34(2) (1991), 393-401.
[9] G. A. Walters and T. Lohbeck, Optimal layout of tree networks using genetic
algorithms, Engineering Optimization 22(1) (1993), 27-48.
23
[10] A. R. Simpson, G. C. Dandy and L. J. Murphy, Genetic Algorithms compared to other
techniques for pipe optimization, Journal of Water Resources Planning and
Management-ASCE 120(4) (1994), 423-443.
[11] G. C. Dandy, A. R. Simpson and L. J. Murphy, An improved genetic algorithm for pipe
network optimization, Water Resources Research 32(2) (1996), 449-4583.
[12] A. Al-Khomairi and M. H. Imam, Gene therapy for improving the performance of
genetic algorithm in pipe network optimisation, Proceedings of the 5th International
Conference on Computational Structures Technology/2nd International Conference on
Engineering Computational Technology, Leuven (Belgium) 2000, 105-110.
[13] A. Ostfeld and E. Salomons, Optimal operation of multiquality water distribution
systems: Unsteady conditions, Engineering Optimization 36(3) (2004), 337-359.
[14] H. A. Basha and B. G. Kassab, Analysis of water distribution systems using a
perturbation method, Appl. Math. Modelling 20 (1996), 290-297.
[15] T. Ahn and G. V. Loganathan, Pipe network optimisation, Proceedings of the Special
Session of ASCE 25th Annual Conference on Water Resources Planning and
Management / 1998 Annual Conference on Environmental Engineering, Chicago (USA)
1998, 431-436.
[16] W. Sung, D. Huh, J. Lee and O. Kwon, Optimization of pipeline networks with a hybrid
MCST-CD networking model, SPE Production & Facilities 13(3) (1998), 213-319.
[17] M. Kanoh, T. Kuroki and N. Nakamura, Estimation of diameters of pipe networks using
least squares of residuals, Computational Mechanics, Vols. 1 and 2, Proceedings - New
Frontiers for the New Millennium, 1st Asian-Pacific Congress on Computational
Mechanics, Sydney (Australia) 2001, 1245-1250.
[18] B. K. Hodge and R. P. Taylor, Piping-System solutions using Mathcad, Computer
Applications in Engineering Education 10(2) (2202), 59-78.
24
[19] B. L. Lin, R. S. Wu, S. L. Liaw and R. T. Chen, The improvement of enumeration
algorithm for the optimization of water distribution networks, Advances in Hydraulics
and Water Engineering, Vols. 1 and 2, Proceedings of the 13th Congress of the
Asia/Pacific Division of the International-Association-of-Hydraulic-Engineering-and-
Research, Singapore 2002, 619-624.
[20] D. Goricanec and J. Krope, Dimensioning and design of tree path transport pipe
networks, Power and Energy Systems, Proceedings of the 7th IASTED International
Multi-Conference on Power and Energy Systems, Palm Springs (USA) 2002, 327-331.
[21] P. R. Bhave and R. Gupta, Optimal design of water distribution networks for fuzzy
demands, Civil Engineering and Environmental Systems 21(4) (2004), 229-245.
[22] L. Gosselin and A. Bejan, Tree networks for minimal pumping power, International
Journal of Thermal Sciences 44 (2005), 53–63.
[23] A tutorial on spreadsheet optimisation, Frontline System Inc (1999).
[24] L. S. Lasdon, A. D. Warren, A. Jain and M. Rather, Design and testing of a generalized
reduced code for nonlinear programming, ACT Transactions on Mathematical Software
4(1) (1978), 34-50.
[25] E. M. Rosen and R. N. Adams, A review of spreadsheet usage in chemical engineering
calculations, Computers & Chemical Engineering 11(6) (1987), 723-736.
[26] A. A. Silva, Energy in electrical circuits: a computer assisted approach, Computers
Educ. 24(1) (1995), 51-57.
[27] Z. L. KahnJetter and P. A. Sasser, Using spreadsheets for studying machine design
problems involving optimisation, Computer Applications in Engineering Education 5(3)
(1997), 199-211.
[28] A. Gupta, Application of PC spreadsheet software for non-linear parameter
optimisation, Journal of Canadian Petroleum Technology 37(3) (1998), 51-54.
25
[29] O. A. Iglesias, C. N. Paniagua, Use of spreadsheets in optimization problems involving
iterations, Computer Applications in Engineering Education 7(4) (1999), 227-234.
[30] H. Thiriez, Improved OR education through the use of spreadsheet models, European
Journal of Operational Research 135(3) (2001), 461-476.
[31] E. C. Ferreira and R. Salcedo, Can spreadsheet solvers solve demanding optimization
problems?, Computer Applications in Engineering Education 9(1) (2001), 49-56.
[32] E. C. Ferreira, R. Lima and R. Salcedo, Spreadsheets in chemical engineering education
- A tool in process design and process integration, International Journal of Engineering
Education 20(6) (2004), 928-938.
[33] M. Schumack, Use of a spreadsheet package to demonstrate fundamentals of
computational fluid dynamics and heat transfer, International Journal of Engineering
Education 20(6) (2004), 974-983.
[34] P. J. Jordan, A spreadsheet-based method for thermal radiation calculations,
International Journal of Engineering Education 20(6) (2004), 991-998.
[35] D. G. Goodwin, TPX: Thermodynamic Properties for Excel,
http://adam.caltech.edu/software/tpx, (1998).
[36] R. W. Jeppson, Analysis of Flow in Pipe Networks, 3rd ed., Ann Arbor Science,
Collingwood, 1977.
[37] P. K. Swamee and A. K. Jain, Explicit Equations For Pipe-Flow Problems, ASCE
Journal of Hydraulic Division 102(5) (1976), 657-664.
26
Pj,hj Pi,Ti
j i
Figure 1. Pump device.
1 1
α β γ1-α-β-γ
2 3 4
5
678910
11 12 13 14 15 16
1
α α+β α+β+γα+βα
CALQ&
CONQ&
TURW&
Figure 2. Schematic diagram of the regenerative Rankine cycle.
27
123 (m)
112 (m)
[1][2]
[3] [4]
[5]
[6] [7]
[8]
[9]
[10]
[11]
[12]
[13]
(1)
(2)
(3)(4)
(5)
(6)
(7)(8)
(9)
(10)
(11)
(12)
(13)
(14)
(15)
(16)Q6 Q7 Q1
Q10
Q11
Q4
Figure 3. Example of pipe-network Analysis Problem.
28
0 (m)
[1]
50 (l/s) 20 (l/s) 30 (l/s)
30 (l/s)(1)
(2) (3)
(4) (5) (6)
(7)
a
[2] [3] [4]
[7][6][5]
(8)
40 (l/s)
20 (l/s)
Figure 4. Example of Optimum Design Problem.
Table 1. Cycle thermodynamic properties.
Node U V Π
1 P1=12000 kPa T1=450 ºC
2 P2=5000 kPa h2 h2=h1-εS·(h1-h2S) h2S=h(P2, s1)
3 P3=800 kPa h3 h3=h1-εS·(h1-h3S) h3S=h(P3, s1)
4 P4=200 kPa h4 h4=h1-εS·(h1-h4S) h4S=h(P4, s1)
5 P5=10 kPa h5 h5=h1-εS·(h1-h5S) h5S=h(P5, s1)
6 x6=0 P6 P6=P5
7 P7, h7 P7=P1 h7=h6+(h7S-h6)/ηB h7S=h(P7, s6)
8 P8, T8 P8=P7 T8=T15-∆T
9 P9, T9 P9=P8 T9=T13-∆T
10 P10, T10 P10=P9 T10=T11-∆T
11 x11=0 P11 P11=P2
12 P12, h12 P12=P3 h12=h11
13 x13=0 P13 P13=P3
14 P14, h14 P14=P4 h14=h13
15 x15=0 P15 16 P16, h16
29
Table 2. Cycle optimisation results.
η 40.98 % P1 16000 kPa T1 550 ºC P2 1989 kPa P3 428 kPa P4 67.2 kPa P5 5.6 kPa
δ
∆T 5 ºC α 0.140 β 0.095 m& γ 0.068
CALQ& 2547.3 kJ/kg
CONQ& -1503.5 kJ/kg W
TURW& -1044 kJ/kg
Table 3. Pipe-network data.
Line Diameter (mm) Length (m) Node Demand (l/s)
1 250 200 1 30 2 150 400 4 20 3 200 300 6 100 4 300 190 7 30 5 80 210 10 10 6 200 300 11 10 7 150 160 8 300 200 9 200 180
10 250 140 11 200 360 12 150 200 13 150 340 14 80 180 15 150 180 16 200 345
30
Table 4. Analysis problem results.
Line Flow rate (m3/s) Node Head (m.w.c)
1 0.1415 1 96.962 2 0.0376 2 115.878 3 0.0376 3 100.999 4 0.0176 4 98.497 5 0.0026 5 98.451 6 -0.1039 6 97.335 7 0.0150 7 97.284 8 0.0357 8 97.471 9 -0.0065 9 111.125
10 0.0585 10 98.507 11 0.0483 11 106.219 12 0.0383 12 123 13 0.0105 13 112 14 -0.0102 15 -0.0178 16 0.0122
Table 5. Optimal design problem data.
Line Diameter (mm)
Length (m)
Roughness (mm)
Node Elevation (m)
Demand (l/s)
1 ? 2000 0.3 1 0 ? 2 ? 1500 0.3 2 100 40 3 ? 2000 0.3 3 85 30 4 ? 1200 0.3 4 90 20 5 ? 2500 0.3 5 100 50 6 ? 3000 0.3 6 92 20 7 ? 1500 0.3 7 78 30 8 ? 1500 0.3
31
Table 6. Optimal design problem results.
Line Diameter (mm) Flow rate (m3/s) Node Head (m.w.c.) Pressure (m.w.c.)
1 0.292 0.1900 1 0.000 0.000 2 0.248 0.0725 2 145.000 45.000 3 0.207 0.0318 3 130.000 45.000 4 0.251 0.0775 4 120.000 30.000 5 0.150 0.0107 5 132.178 32.178 6 0.150 0.0118 6 122.000 30.000 7 0.185 0.0275 7 108.550 30.550 8 0.150 0.0182