Task:

Determine the optimum $($best$)$ pipe and tank combination $($Figure $1)$ to supply drinking water

to a community. The optimum combination is the lowest combined pipe and storage cost that Step $4$: Calculate new Velocity, $v_2=\sqrt[\phantom{2}]{\frac{2**g**D**h_{\text{f}\text{m}\text{a}\text{x}}}{**l}}$

Step $5$:

If $,v_1{v}_2,$ Then $v_1=v_2$; Repeat Steps $2$ to $4$

If $,v_1=v_2$ Then $Q=v_2^{**}$ Area of pipe, $A(m^2).$ End

To calculate Tank volume $(m^3)$ :

Tank volume at $T_2=$ Tank volume at $T_1+$ Pipe supply during $T_1-$ Volume

demanded during $T_1$

ii$.$ Create the worksheet named Optimization and compute the following for each

pipe Diameter $(D)$ :

Pipe unit cost $($Rand$/$m$)-$ Transfer from the Data worksheet

Pipe diameter, $D(m),-$ Transfer from the Data worksheet

Pipe supply, $Q(\frac{m^3}{s}),-$ Transfer from the Balancing storage calculations

worksheet

Pipe headloss, $h_f(m),=\frac{**l**v^2}{2**g**D}$

Pipe cost $($Rand$),=$ Pipe length $*$ Pipe unit cost Rand $\frac{?}{m}$

Balancing storage $(m^3),=$ absolute value of the Highest cumulative surplus

absolute value of the Highest cumulative deficit

Extra storage $(m^3)=$ Emergency storage $+$ Fire storage $+$ Bottom storage $\frac{1}{2}$

Total storage $(m^3),=$ Balancing storage $+$ Extra Storage $,[\frac{1}{2}]$

Storage cost $($Rand$)=$ Total storage $*$ Storage unit cost $[12]$

Total cost $($Rand$)=$ Storage cost $+$ Pipe cost $[12]$

iii. Highlight the row with the optimum pipe and storage combination. If the optimum

cannot be determined, the code must display the following message: "the

maximum headloss constraint $(h_{\text{f}\text{m}\text{a}\text{x}})$ was violated and no optimum pipe and tank

combination can be calculated".

Efficiency of VBA code

Structure and use of comments

VBA CODE SHOULD BE DESIGNED BY YOU AND ENTERED INTO A VBE MODULE$($S$).$

SHOULD YOU USE ACTIVEX 'FORMS' CONTROLS, THE CONTROL MUST FUNCTION

BASED ON THE VBA CODE YOU DEVELOPED AND INPUT INTO THE' FORMS' MODULE.

does not violate anv constraints.

Figure $1.$ Irunk ma$$ns $($p$$pe$)$ to intermealate tank to consumers

a$)$ Project Part A:

Total $=5$ marks$]$

Submit the algorithm $($hand$-$sketched or computer generated$)$ for Project Part B on

one A$4$ or A$3$ page.

On the page, provide a legend where each variable in the algorithm is described.

b$)$ Project Part B:

$[$Total$=15$ marks$]$

Submit one $(1)$ Excel$$ Macro$-$Enabled Workbook that contains the provided Data

worksheet with the given input data. Do not rearrange the data.

Place one Optimise button on the Data worksheet. This button is to call the VBA code

that will:

i$.$ Create the worksheet named Balancing storage calculations and compute Pipe

supply, $Q(\frac{m^3}{s})$ for each Diameter $(D)$ and Tank volume $(m^3)$ at each hour. The

below steps$/$formulae are to be employed:

To calculate Pipe supply, $Q(\frac{m^3}{s})$ :

Step $1$: Assume Velocity, $v_1(\frac{m}{s})=1$ and Velocity, $v_2(\frac{m}{s})=1$

Step $2$: Calculate Reynolds Number, $Re=\frac{v_1^{**}D}{?}$ v

Step $3$: Calculate pipe headloss coefficient Lambda, $$ using the

Swamee$-$Jain formula $=(\frac{1}{(-2**lo{g}_{10}(\frac{5\mathrm{.}74}{R{e}^{0\mathrm{.}9}}+\frac{K}{3\mathrm{.}7**D}))}{)}^2$