If the description  below does not work for you, you can always download a working version of the model here and then when you see the contents click on Download File 

>>>

Start a new spreadsheet.
Fill in the text as shown:

Commentary: Increase is the increase in water vapour as the wind blows over sea/land. Rain is the amount of water that falls. Rain is estimated and then the model is run to see if that is what happens. Ground Water is water that is held by the land, until it flows into the river. River is the amount of water in the river. Temp is the global temperature. Takeup is the amount of water vapour the air takes up per degree centigrade per unit time (day?). River Capacity is the amount of water the river can take away in a day.

Fill in values as shown:

Put the following formulae in the named cells:
B3 =(F$3-13.5)*G$3
C3 =IF(A3>4000;1000;0)
E3 =IF(D3>H$3;H$3;D3)
and
A4 =A3+B3-C3
D4 =C4+D3-E3
Then copy and paste B3 into B4, C3 into C4 and E3 into E4.

Commentary: B3 is the increase in water vapour and is the product of the rate of takeup multiplied by the temperature difference from the baseline global temperature. C3 is the rain which has fallen. It is assumed that it rains if the water vapour rises to 4000. E3 is the amount of water in the river. If that trying to flow away is less than capacity, then that value is what flows, if not less then the river flows at capacity. A4 is the vapour in the air, calculated as equal to yesterday’s amount plus the increase from absorption minus the decrease from it having rained. D4 is the water in the ground, calculated as equal to yesterday’s amount plus rainfall minus what has flowed away down river.

Your spreadsheet should now look like this:

Now copy the row A4 to E4 down – click and hold on A4 and drag across to E4 and let go; click on the small black square on the bottom right hand corner of E4 and drag down to cover E24. Click somewhere outside the marked area.

Select (click and drag down) C3 to C24 then click Format > Conditional formatting and for Condition 1 after Cell value is / equal to / type 0 Click on New Style > Background and choose a yellow. Click OK.

Click into the tick box for Condition 2 and moving right after Cell value is / equal to / type 1000 Click on New Style > Background and choose a blue. Click OK.

Click on OK and then click anywhere ele and your C column should be mostly yellow with 4 blue cells.

Select (click and drag down) D3 to D24 then click Format > Conditional formatting and for Condition 1 after Cell value is use the down arrow to change “equal to” into “greater than or equal to” and fill in the number 1200.

Using New Style choose a background of red.

Noting that:
Yellow shows no rain
Blue shows rain is falling
Red shows that the river has overflowed its banks

We can now start to use the model.

So we are starting with a temperature of 14 degrees. There are 4 days in our three-week timescale in which it rains, but the river copes.

Change 14 in F3 into 14.1 (don’t forget to press ENTER after), we find 5 days on which it rains, and the river still copes.

Change 14.1 into 14.2 and see what happens. The times between rainfall reduce, the river cannot cope and overflows its banks on four days.

Change 14.2 into 14.3 and see the effect. Widespread flooding.