Spread Sheet Application for Engineering

Problems
1.1. Velocity measurement in Rivers
Exercise 1

Velocity, m/s
Verticals Distance Depth 0.6d 0.2d 0.8d Vavg qi,mid-section qi,mean-section
RB 4 0.00 0.000
1 5 0.31 0.193
2 6 0.40 0.219
3 7 0.51 0.238
4 8 0.85 0.243
5 9 1.23 0.260 0.211
6 10 1.58 0.265 0.216
7 11 1.69 0.278 0.225
8 12 1.71 0.295 0.253
9 13 1.87 0.317 0.257
10 14 1.84 0.287 0.278
11 15 1.71 0.305 0.252
12 16 1.65 0.287 0.238
13 17 1.50 0.278 0.238
14 18 1.36 0.265 0.217
15 19 1.19 0.257 0.193
16 20 1.17 0.235 0.188
17 21 0.92 0.216
18 22 0.81 0.188
19 23 0.70 0.184
20 24 0.63 0.167
21 25 0.55 0.150
22 26 0.48 0.125
LB 26.5 0.00 0.000
Total Q =
River cross-section

0 5 10 15 20 25 30
0.00

0.50

1.00

1.50

2.00

1|Page
Exercise 2
C 15.352695
A 1.4613171
Qc = c(h+a)^n N 2.6951459

actual Computed Qa-Qc Qd^2

Qc
Obser.No h (m) Qa (m^3/s) (m^3/s) Qd Qdsq
1 1.55 300 299.5734 0.4266 0.1820
2 1.44 287 270.9856 16.0144 256.4620
3 1.26 235 228.0226 6.9774 48.6844
4 1.05 193 183.6444 9.3556 87.5276
5 0.73 125 127.1847 -2.1847 4.7730
6 0.69 113 121.0240 -8.0240 64.3849
7 0.7 124 122.5462 1.4538 2.1136
8 1.7 340 341.5089 -1.5089 2.2766
9 0.96 169 166.4408 2.5592 6.5493
10 0.94 168 162.7615 5.2385 27.4424
11 1.35 240 248.9213 -8.9213 79.5901
12 1.17 202 208.2633 -6.2633 39.2284
13 1.79 387 368.3488 18.6512 347.8687
14 3.09 930 911.9271 18.0729 326.6303
15 3.87 1374 1396.7211 -22.7211 516.2473
16 2.33 540 557.3249 -17.3249 300.1505
17 3.49 1152 1144.3487 7.6513 58.5424
18 3.93 1452 1439.4914 12.5086 156.4654
19 2.03 440 446.2929 -6.2929 39.6007
20 1.61 306 315.9335 -9.9335 98.6742
21 2.13 469 481.5867 -12.5867 158.4253
22 1.37 246 253.7229 -7.7229 59.6425
23 1.05 189 183.6444 5.3556 28.6827
24 0.91 163 157.3390 5.6610 32.0470
25 0.79 139 136.7895 2.2105 4.8862
26 0.68 120 119.5138 0.4862 0.2364
27 0.61 104 109.2736 -5.2736 27.8110
28 0.53 94.6 98.2679 -3.6679 13.4535
Total 2788.5785
Procedure
 Assume an arbitrary value of c , a
,n
 Compute Qc = c(h+a)n
 Find the error
 Square the errors and sum up
together
 Open “Tool menu”
 Select “Solver” option
Cell target – error sum
Status – minimizing error
Adjusted parameters – c,a,n,
2|Page
 Stage discharge curve

1600
1400 Measured discharge
Discharge (m^3/s)

1200 Computed discharge

1000
800
600
400
200
0
0 0.5 1 1.5 2 2.5 3 3.5 4 4.5
stage (m)

1.2. Optimization Of Engineering Problems

Problem1: (Maximization)
Maximize the benefit earned from sell of water by withdrawing from reservoir of maximum
capacity of 80 Mm3 and initial storage of 20 Mm3. The price of water during summer and winter
are 0.05 and 0.20 $ per m3 respectively. The inflow during summer and winter are 100 and 35
Mm3 respectively.

Objective function
Z = 0.05 Qs + 0.2Qw (Maximization)
Constraint
Qs + Qw >= 75
Qs + Qw =< 155
Qs >= 40
Qs =< 120
Qs , Qw >= 0

3|Page
Problem2: (Maximization)
A farmer owns a 100 ha farm and plans to plant at most three crops. The seeds for crops A, B, C
costs Birr 40, 20, 30 per ha respectively. The farmer has a maximum of Birr 3200 to spend on
seed. Crops A, B, C require 1, 2 & 1 working days per ha respectively and there are a maximum
of 160 working days available. If the farmer can make a profit of Birr 100 per ha on crop A, Birr
300 per ha on crop B, and Birr 200 per ha on crop C, how many hectares of each crop should be
planned to maximize the profit?

Solution:
X1 = Number of hectares of crop A
X2 = Number of hectares of crop B
X3 = Number of hectares of crop C
P = Total profit
Objective function:
Max P = 100X1 + 300X2 +200X3
Constraints:
X1 + X2 + X3 =< 100
40X1 + 20X2 + 30X3 =< 3200
X1 + 2X2 + X3 =< 160

Non negativity constraint

X1 , X2 , X3 =< 0

1.3. Reservoir Routing

The flood whose hydrograph is prescribed by ordinates at 6 – hours intervals as enters a reservoir
of area A = 106 m2, having a vertical sides is tabulated below.

T (hr) 0 6 12 18 24 30 36 42 48 54 60
Inflow(m3/s) 0 10 20 40 50 40 30 20 10 5 0

The outflow discharge over the spill way, assuming spillway as broad crested weir, is given by
Q = 1.7 *B *H3/2 where Q – outflow in m3/s
B – width of the spillway in m = 30m

Assume that the water level in the reservoir at time t = 0 is just at the crest of the spillway for
which Qo = 0.

4|Page
By completing the table below, compute the outflow hydrograph.

Time Inflow Head, Cell

(hrs) (m^3/s) (I1 +I2)/2 Outflow(Q) S1-0.5Q1 S2-0.5Q2 H(m) target
0 0 5 0 0 5 0
6 10 15 1.43486 3.56514 18.56514 0.0925
12 20 20 8.68434 9.8808 39.8808 0.3072
18 40 40 23.89928 15.98152 60.98152 0.6033
24
30
36
42
48
54
60

Goal seek Cell target

Tool
To value
By changing

1.4. Runoff computation & hydrograph development

Determine the direct runoff hydrograph, the Ф ▬ index and the excess rainfall
hyetograph for the observed stream flow data given below. The watershed area is
18.23 km^2 and the base flow is 11.35 m^3/sec.

Time Rainfall(mm) Intensity excess P streamflow,m^3/s DRH

1 8:30 5.76 0
2 9:00 3.8 7 0
3 9:30 6.6 8 0
4 10:00 33.8 23.5 12.15
5 10:30 55.9 66 54.65
6 11:00 52.8 161.6 150.25
7 11:30 5.1 270.42 259.07
8 12:00 2.3 312.8 301.45
9 12:30 233.6 222.25
10 1:00 122.5 111.15
11 1:30 63.7 52.35
12 2:00 51.12 39.77
13 2:30 34.9 23.55
14 3:00 20.22 8.87
15 3:30 11.18 0
16 4:00 10.04 0
17 4:30 8.6 0

5|Page
 Hydrograph of stream flow

Time
1 7 13
0 350

50 300

100 250
Rainfall depth

150 200

Flow
200 150

250 100

300 50

350 0
0 5 10 15 20
Time

1.5. Regression analysis

Linear regression (y:x)

y  b0  b1 * x

b0 
 y x 2
  x  xy
N x 2
 ( x ) 2

N *  xy   x  y
b1 
N  x 2  ( x) 2

( y ) 2
b0  y  b1  xy 
R2  N
( y ) 2

y 2

N

S* 
y 2
 b0  y  b1  xy
N 2

6|Page
Multiple regressions (y: x1, x2, x3,…)

y  b0  b1 x1  b2 x 2
 y  N *b  b  x  b  x
0 1 1 2 2

x y  b x b x b x x
2
1 0 1 1 1 2 1 2

x y  b x b x x b  x
2
2 0 2 1 1 2 2 2

Test of significance
 y  2
estimate y
(k  1)
F Where k is the number of constraints
 y  y 
2

N  k 
estimate

Exercise
Data for regression analysis
Catch
No Area, m^2 Rfall (in) S per 1000 Qbar, (ft^3/s)
1 28 95.3 1639 2595
2 328 84.2 2706 10741
3 11 66.5 1121 544
4 151 82.2 2242 11176
5 528 45.1 1811 15562
6 6 50.7 2193 269
7 45 36.7 944 1632
8 85 26.4 276 699
9 81 29.7 357 249
10 485 31.4 640 5690
11 187 39.1 677 4275
12 36 23.7 231 178
13 27 24.3 266 244
14 75 25.5 315 447
15 90 25.8 372 597
16 86 26.3 361 478
17 41 26.5 330 166
18 631 24.7 297 2764
19 1170 24.2 197 3689
20 565 25.6 226 3229
21 44 24 328 99
22 180 24.4 171 389
23 117 25 210 793
24 30 25.6 227 223
25 8 25.2 255 180
26 400 25.6 280 1298
27 140 30.9 507 495
28 3810 28.9 338 11832
29 137 28.3 225 658

7|Page
 30 153 34.2 469 1263
31 8 33.8 915 226
32 257 32.1 289 3128
33 617 33.9 452 6269
34 102 27.9 207 1185
35 125 28.4 437 1007
36 859 26.9 339 4708
37 1670 37.2 949 15687
38 782 46.4 1508 10841
39 3 96.4 1706 442
40 1560 40.9 1155 19475
41 28 76.9 1849 1747
42 342 32.9 906 1673
43 138 39.1 1144 5169
44 732 49.9 1456 16545
45 495 56.1 1597 17458
46 64 65.5 1511 4269
47 4 99.7 1943 787
48 44 58 1578 924
49 401 56.9 1777 9252
50 8 51.5 844 420
51 61 29.7 246 573
52 160 34.4 492 1967
53 58 40.7 1207 1931
54 235 30.4 181 2634
55 78 30.4 147 763
56 262 44 1018 6340
57 216 49.8 767 10655

8|Page