Excel as an Educational Platform for Design Analyses of Fluid-Thermal Systems

Mohamed M. El-Awad, Mohammed S. Al-Saidi

Engineering Department, University of Technology and Applied Sciences, Suhar, Oman.

DOI: 10.4236/wjet.2022.102025                

Abstract

Equipped with its Solver and-in and VBA, Microsoft Excel makes an ideal educational platform for design analyses of fluid-thermal systems. This paper illustrates this capability by considering a common type of these systems; which is the double-pipe heat exchanger. While Solver is used for the optimisation analysis, VBA is used for the development of a user-defined function (UDF) that determines the optimum standard-pipe size for the system.

Keywords

Fluid-Thermal Systems, Design Optimisation, Excel, Solver, VBA

1. Introduction

Engineering curricula aim to help future engineers acquire the basic mathematical and scientific analytical tools and apply these tools to the design of relevant engineering systems. However, design assignments involve open-ended problems that require economic and other considerations, searching for data, and selection from various options. The iterative nature of the design process also makes it both laborious and error-prone. Therefore, many good students fail to achieve the learning objects of the design-based assignments despite of their adequate knowledge of the basic analytical tools [1]. Computer-oriented approaches can be helpful in this respect for two reasons: 1) they eliminate the tedium and error-prone nature of hand calculations and 2) they improve the learning process by integrating the students’ knowledge of the basic analytical tools with their computer-oriented skills [2].

Although numerous applications are now available for industrial design and can be used to improve the relevant engineering curricula, these applications are usually costly to acquire, need time to use, and do not allow the development of “white-box” models [2]. By comparison, general-purpose software such as Microsoft Excel perfectly suits the educational requirements. The simplicity and wide availability of Excel, together with its powerful tools for data analyses and visualisation, encouraged its use in various engineering courses [3] [4] [5] [6] [7]. With respect to fluid-thermal analyses, the lack of built-in functions for fluid properties also motivated the development of a number of relevant add-ins for Excel [8] [9] [10]. Together with the Solver add-in and VBA (Visual Basic for Applications), these add-ins enable Excel to be used as an effective educational platform for a wide range of fluid-thermal analyses [11] [12] [13].

The use of Excel as an educational platform for fluid-thermal analyses has been discussed by a number of publications in the past few years, but most of these publications dealt with the application of basic numerical methods rather than addressing design issues [12] [13]. Design analyses of fluid-thermal systems involve many interdependent steps, such as selecting a suitable pipe diameter, a suitable pump size, or a suitable insulation thickness. Excel offers the students the convenience of investigating the effects of various design parameters (sensitivity analyses); which gives them confidence and improves their problem-solving skills. However, utilising the full capabilities of the platform requires the awareness and skilful use of the spreadsheet, not only by the students but also by their instructors. The aim of this paper is to illustrate the use of Excel, Solver, and VBA as an educational platform for design analyses of fluid-thermal systems by considering a common type of these systems, which is the double-pipe heat exchanger.

2. The Design Case

The design case used to illustrate the usefulness of the Excel-based platform for optimisation analyses of fluid-thermal systems is based on Example 11-11 in [14]. The case is that of a dairy plant in which the hot water needed for milk pasteurization is supplied by a natural gas furnace. The hot water cannot be returned to the furnace and re-circulated because it is contaminated during the process. Therefore, it is discharged to an open floor drain at 80˚C. The energy engineer suggests the installation of a double-pipe heat exchanger that utilizes the energy of the drained hot water for preheating the incoming cold water. The total length of the heat-exchanger is to be divided into a suitable number of 6-m hairpins as shown in Figure 1. A bunch of 6-m long, schedule-40 commercial-steel pipes of various sizes is available from previous projects for making the heat-exchanger. The dimensions of the pipes are shown in Table 1. The largest steel pipe available, which can be used as the shell pipe of the heat-exchanger, is a 1½-nominal pipe (inner diameter = 4.09 cm).

The plant operates 24 h a day and 365 days a year (8760 h/year). It is required to determine the diameter of the inner pipe and the length of the heat exchanger that maximise the saving in energy cost, i.e., natural gas and electricity based on the following data:

Figure 1. Schematic of the double-pipe heat-exchanger with three hairpins.

Table 1. Standard pipe dimensions^a.

^aBased on the data provided by [15].

­ The hot water flows at a rate of 1.2 kg/s.

­ The cold water flows at the rate of 1.5 kg/s at an average temperature of 15˚C throughout the year.

­ Cost of electricity (c_elect) = 0.12 $/kW-h.

­ Cost of natural gas (c_NG) = 0.4 $/Therm.

Allow for component losses by using K = 10 for every hairpin on both the inner and outer sides and take the furnace efficiency (η_F) as 80% and the pump efficiency (η_P) as 75%.

3. The Analytical Model

The objective of the optimisation is to maximise the saving in energy cost (S) given by:

$S=C_{NG}-C_{elect}$, (1)

where C_NG and C_elect are the annual costs of natural gas and electricity, respectively. These costs are given by:

$C_{NG}=\frac{3600c_{NG}\stackrel{\dot{}}{Q}\tau }{10548{\eta }_F}$, (2)

$C_{elect}=\frac{c_{elect}{\stackrel{\dot{}}{W}}_p\tau }{{\eta }_P}$, (3)

where, $\stackrel{\dot{}}{Q}$ is the rate of heat-transfer in the heat-exchanger, ${\stackrel{\dot{}}{W}}_p$ is the required pump power for circulating the two water streams through the heat-exchanger, and τ is the number of operation hours per year. The pump power ( ${\stackrel{\dot{}}{W}}_p$ ) is determined from:

${\stackrel{\dot{}}{W}}_p={\gamma }_h{\stackrel{\dot{}}{V}}_h{h}_{f,h}+{\gamma }_c{\stackrel{\dot{}}{V}}_c{h}_{f,c}$ (4)

where, γ and $\stackrel{\dot{}}{V}$ are values of the specific weight and volume flow rate of water in the hot and cold sides of the tube, respectively, and the suffices h and c refer to the hot and cold water streams, respectively, The frictional head losses h_f,h and h_f,c on the two sides of the tube are determined from the following equation:

$h_f=\left(f\frac{L}{D_h}+{\displaystyle \sum K}\right)\frac{V^2}{2g},\left[\text{m}\right]$ (5)

where f and V are values of the friction coefficient and water velocity at the hot and cold sides of the tube, respectively, and D_h stands for the relevant hydraulic diameter which for the inner tube is equal to its inside diameter (D) and for the outer shell is equal to (D_o − D_i). The friction factors f_h and f_c at both sides of the tube can be determined from the respective values of the Reynolds number (Re), roughness height (ε), and the respective diameter by using the following Swamee-Jain formula:

$f=0.25/{\left[{\log }_{10}\left(\frac{\epsilon }{3.7D}+\frac{5.74}{{\mathrm{Re}}^{0.9}}\right)\right]}^2,\mathrm{Re}>4000$ (6)

The rate of heat-transfer $\stackrel{\dot{}}{Q}$ in Equation (2) can be determined by using either the log-mean temperature difference (LMTD) method or the effectiveness-NTU method [14]. The LMTD method, which is easier to apply, requires the values for the exit temperatures of the two water streams to be specified. To apply the LMTD in the present analysis the hot water is assumed to exit with a temperature difference (ΔT = 20˚C) higher the cold water inlet temperature and the exit temperature of the cold water are then obtained from energy balance. The effect of the value of ΔT on the outcome of the analysis can easily be investigated by using Excel. Accordingly, the exit temperature of the hot water (T_h,out) and that of the cold water (T_c,out) are calculated as follows:

$T_{h,out}=T_{c,in}+\Delta T$, (7)

$\stackrel{\dot{}}{Q}=C_h\left(T_{h,in}-T_{h,out}\right)$, (8)

$T_{c,out}=T_{c,in}+\stackrel{\dot{}}{Q}/C_c$, (9)

where C_h ( $={\stackrel{\dot{}}{m}}_c{C}_{pc}$ ) and C_c ( $={\stackrel{\dot{}}{m}}_h{C}_{ph}$ ) are the heat capacity rates of the hot and cold water streams, respectively. The log-mean temperature difference (ΔT_lm) is then calculated from:

$\Delta T_{lm}=\frac{\Delta T_1-\Delta T_2}{\ln \left(\Delta T_1/\Delta T_2\right)}$, (10)

where ΔT₁ and ΔT₂ are the temperature differences at the two ends of the heat-exchanger. The required surface area of the heat-exchanger (A) can then be determined from:

$A=\frac{\stackrel{\dot{}}{Q}}{U\Delta T_{lm}}$, (11)

where U is the overall heat-transfer coefficient of the heat-exchanger which is yet to be determined. Neglecting the thermal resistance through the inner pipe, U is given by:

$\frac{1}{U}=\frac{1}{h_i}+\frac{1}{h_o}$. (12)

The heat-transfer coefficients h_i and h_o can be determined from the respective Nusselt number (Nu) according to:

$h=\frac{kNu}{D_h}$, (13)

where k is the thermal conductivity of the respective water stream. The Nusselt number itself can be obtained from the Dittus-Boelter equation:

$N_u=0.023{\mathrm{Re}}^{0.8}{\mathrm{Pr}}^n$, (14)

where n is equal to 0.3 for the hot water (cooling) and equal to 0.4 for the cold water (heating). For more information about the analytical model for the design of double-pipe heat exchangers, the reader can refer to [16].

4. Development of the Excel Model

Figure 2 shows the front sheet of the Excel workbook developed for this analysis that stores the basic information of the system including the flow rates and temperatures of the cold and hot water stream as well as their thermo-physical properties. For simplicity, properties of the two water streams are taken at their inlet temperatures of 15˚C and 80˚C, respectively [16]. The front sheet also shows the properties of the steel pipes, the different costs involved in the economic analysis, and the efficiencies of the pump and the furnace.

Figure 3 shows the back sheet of the workbook that performs the analysis. Note that the inner diameter of the larger pipe (D_large) in sheet 2 is kept

Figure 2. The front sheet of Excel for the heat-exchanger optimisation.

Figure 3. The back sheet of Excel for the heat-exchanger optimisation.

constant at 4.09 cm and the temperature difference (ΔT) is specified as 20˚C. The back sheet starts the analysis with an inner pipe diameter (D_small = 1.58 cm). It first calculates the overall heat-transfer coefficient of the heat-exchanger (U) by applying Equation (12) and then determines the exit temperatures of the hot and cold streams (Th_out and Tc_out) and the rate of heat-transfer (Q). The sheet then calculates the different cost involved and the net annual saving (Saving). Figure 3 shows that at the initially assumed diameter of 0.0158 m, the total length of the heat-exchanger (Length) is 38.698 m and net saving is $29405.44. Solver can now be used to determine the pipe diameter that maximises the annualised energy cost given by Equation (1).

5. Finding the Optimum Pipe Diameter with Solver

Solver, which is found on the Data ribbon of Excel, finds the optimal value of a target cell by changing values in the cells used to calculate it. Figure 4 shows Solver’s set-up for finding the maximum value (Max) of the annual energy saving (Saving) by changing the value of the inner-pipe diameter (D_small). Solver allows the user to impose constraints on the solution and Figure 4 shows two constraints that require the tube diameter to be more than 1.58 cm but less than the inner diameter of the shell pipe. Three solution options are offered by Solver which are the GRG Nonlinear method (the default method), the Evolutionary method, and the Simplex LP method.

Figure 5 shows the solution found by using the Evolutionary method (the GRG Nonlinear method which is used by default failed to reach a solution). According to Solver’s solution shown in Figure 5, the optimum size for the inner pipe is 2.621 cm that achieves a total annual energy saving of $33072.62. Referring to Table 1, the standard pipe with the nearest internal diameter to the optimum diameter is the 1"-nominal pipe. The optimum total length of the heat-exchanger is 32.64 m, which means that means 3 hairpins are required as shown in Figure 1.

Figure 4. Solver set-up for the heat-exchanger optimisation analysis.

Figure 5. Solver solution for the heat-excahnger optimisation.

6. A VBA Function for Determining the Standard Pipe Diameter

Standard pipes are designated by a nominal diameter and a schedule that determine their inside diameter and thickness. Since the size determined by Solver does not usually match a standard pipe, the VBA function listed in the appendix has been developed for determining the standard-pipe diameter. The function, called “Schedule40”, stores the external and internal diameters and flow areas for schedule-40 pipes in English and SI units [15]. It finds the internal diameter of a standard pipe that is equal to, or just larger than, the optimum diameter determined by Solver.

Figure 5 shows how the VBA function is used in Excel to determine the optimum standard pipe diameter (in cm). The formula written in cell K16 is as follows:

= Schedule40 (D_small*100, 3)/100

As shown in Figure 5, the nearest standard size for the pipe of nominal diameter 1". The analysis can now be repeated with the smaller 1¼"-nominal pipe as the shell pipe of the double-pipe heat-exchanger instead of the 1½"-nominal pipe and the resulting energy saving compared with the present value.

Note that the “Schedule40” function listed in the appendix provides the data for pipe sizes up to “nominal-1½” only. To be able to use the function for larger standard pipes, the data for these pipes have to be added in a similar way and functions similar to “Schedule40” are needed for other pipe schedules.

7. Concluding Remarks

Compared to the dedicated industrial applications, the Excel-based educational platform allows the students to develop “white-box” models for their analyses. This enables them to combine their theoretical knowledge with their computer skills and, therefore, improves their learning process. Compared to the use of design tables and charts, Excel enables any required modifications to the system to be investigated faster and more accurately and also makes it much easier to investigate the effect of possible changes to the initial and running costs on the system’s design. These advantages make the Excel-based platform more effective as an educational platform for design analyses of fluid-thermal systems than the traditional design methods as well as the dedicated industrial applications.

Acknowledgements

The authors acknowledge the support and encouragement given by the University of Technology and Applied Sciences (UTAS) throughout the progress of this work.

Appendix

Function Schedule40 (Size, index)

'This function returns the external and internal diameters and flow areas for Schedule

'40 pipes in English and SI units. Size is the initial value of the pipe dimension. Index =

'1 Outside Diameter (in)

'2 Outside Diameter (cm)

'3 Inside Diameter (in)

'4 Inside Diameter (cm)

'5 Flow Area (ft²)

'6 Flow Area (cm²)

A = Array (0.405, 1.029, 0.02242, 0.683, 0.0003947, 0.3664)

B = Array (0.54, 1.372, 0.03033, 0.924, 0.0007227, 0.6706)

C = Array (0.675, 1.714, 0.04108, 1.252, 0.001326, 1.233)

D = Array (0.84, 2.134, 0.05183, 1.58, 0.00211, 1.961)

E = Array (1.05, 2.667, 0.06867, 2.093, 0.003703, 3.441)

F = Array (1.315, 3.34, 0.08742, 2.664, 0.006002, 5.574)

G = Array (1.66, 4.216, 0.115, 3.504, 0.01039, 9.643)

H = Array (1.9, 4.826, 0.1342, 4.09, 0.01414, 13.13)

If (Size ≤ A (index)) Then

Schedule40 = A (index)

Else If Size ≤ B (index) Then

Schedule40 = B (index)

Else If Size ≤ C (index) Then

Schedule40 = C (index)

Else If Size ≤ D (index) Then

Schedule40 = D (index)

Else If Size ≤ E (index) Then

Schedule40 = E (index)

Else If Size ≤ F (index) Then

Schedule40 = F (index)

Else If Size ≤ G (index) Then

Schedule40 = G (index)

Else If Size ≤ H (index) Then

Schedule40 = H (index)

End If

End Function

Conflicts of Interest

The authors declare no conflicts of interest regarding the publication of this paper.

References

[1]	Katz, R. (2015) Integrating Analysis and Design in Mechanical Engineering Education. Procedia CIRP, 36, 23-28. https://doi.org/10.1016/j.procir.2015.01.042
[2]	Niazkar, M. and Afzali, S.H. (2015) Application of Excel Spreadsheet in Engineering Education. First International & Fourth National Conference on Engineering Education, Shiraz, 10-12 November 2015.
[3]	Musimbi, O.M. and Mulanza, J.P. (2018) Using Excel as a Tool to Teach Manufacturing and Heat Transfer. 2018 ASEE Zone IV Conference, Boulder, 25 March 2018, Paper ID: #24161.
[4]	Brkic, D. (2016) Spreadsheet-Based Pipe Networks Analysis for Teaching and Learning Purpose. Spreadsheets in Education, 9, Article No. 4.
[5]	Karimi, A. (2009) Using Excel for the Thermodynamic Analyses of Air-Standard Cycles and Combustion Processes. Proceedings of IMECE 2009, ASME International Mechanical Engineering Congress and Exposition, Orlando, 13-19 November 2009, 181-191. https://doi.org/10.1115/IMECE2009-11722
[6]	Ahmadi-Brooghani, Z. (2006) Using Spreadsheets as a Computational Tool in Teaching Mechanical Engineering. Proceedings of the 10th WSEAS International Conference on Computers, Vouliagmeni, 13-15 July 2006, 305-310.
[7]	Oke, S.A. (2004) Spreadsheet Applications in Engineering Education: A Review. International Journal of Engineering Education, 20, 893-901.
[8]	Huguet, J., Woodbury, K. and Taylor, R. (2008) Development of Excel Add-In Modules for Use in Thermodynamics Curriculum: Steam and Ideal Gas Properties. American Society for Engineering Education, Washington DC, AC 2008-1751.
[9]	Mahan, K., Huguet, J., Woodbury, K. and Taylor, R. (2009) Excel in ME: Extending and Refining Ubiquitous Software Tools. American Society for Engineering Education, Washington DC, AC 2009-2295.
[10]	El-Awad, M.M. (2019) A Multi-Subject Excel Add-In for Fluid Properties and Its Use for Analysing Cascade and Multi-Stage Compression Refrigeration Cycles. The Electronic Journal of Spreadsheets in Education, 12.
[11]	El-Awad, M.M. (2021) Computer-Aided Thermofluid Analyses Using Excel. https://www.academia.edu/40015718/Computer_Aided_Thermofluid_Analyses_using_Excel
[12]	El-Awad, M.M. (2022) Optimisataion Analyses of Fluid-Thermal Systems Using Excel. https://www.academia.edu/36307413/Optimisation_Analyses_of_Fluid_Thermal_Systems_using_Excel
[13]	El-Awad, M.M. and Abuharaz, M. (2014) A Case for Introducing Thermal-Design Optimisation Using Excel Spreadsheet. Proceedings of the 10th International Conference on Heat Transfer, Fluid Mechanics and Thermodynamics (HEFAT 2014), Orlando, 14-16 July 2014.
[14]	Cengel, Y.A. and Ghajar, A.J. (2015) Heat and Mass Transfer: Fundamentals and Applications. 5th Edition, McGraw Hill, London.
[15]	Janna, W.S. (2011) Design of Fluid Thermal Systems. Third Edition, Cengage Learning, Boston.
[16]	CheGuide.com, Design of Double-Pipe Heat-Exchangers. https://cheguide.com/double_pipe.html