@Risk Excel Spreadsheet Add-in

There’s an excellent software program called @Risk, an add-in to Microsoft Excel, that is used for risk analysis or decision-making in the presence of uncertainty, which, really, applies to all of our decision-making. I’m going to go step-by-step through an example of where I can either accept a point estimate or I can evaluate the variability of key factors in an equation that estimates the theoretical carbonaceous oxygen consumption in a biological reactor. The equation of interest to us in this example is shown below.

Oxygen consumption estimate


Q denotes the influent flow rate to the biological reactor in units of million gallons per day

BODinf denotes the influent BOD5 concentration to the reactor

BODeff denotes the effluent BOD5 concentration leaving the reactor

8.34 is the unit conversion factor representing pounds per gallon

0.68 is the conversion factor for converting BOD5 to BODL for municipal wastewater (If all of the BOD5 were converted to the end products of CO2 and H2O the total oxygen demand would be computed by converting the BOD5 to BODL.)

1.42 represents the fact that one mole of bacterial cells is equal to 1.42 times the concentration of cells

WASlb denotes the pounds of waste activated sludge removed from the reactor each day calculated as shown in the equation below.

WAS pounds

We will first determine our point estimate of the oxygen consumption in a biological reactor using the following variable assignments:

Q = 2,500 gallons per minute (gpm) = 3.6 million gallons per day( mgd)

BODinf  = 250 mg/L

BODeff  = 0 mg/L, We have selected an effluent BOD concentration of zero in order to be “conservative” in our estimate of oxygen consumption.

QWAS = 15 gpm = 0.0216 mgd

RASTSS = 8,000 mg/L (This is the total suspended solids concentration of the return activated sludge which is also the concentration of the waste activated sludge.)

The result of our calculation for the point estimate of oxygen consumption in a biological reactor given the “fixed” conditions just stated is shown below in the series of three equations.

Oxygen consumption point estimate

Therefore, we can state, but not with certainty, that the oxygen consumption is 8,992 O2 lb/day. I make a point that there is no certainty because several of the variables can change significantly throughout the day. For example, the flow rate can range from 2,000 to 3,000 gpm though we fixed this value at 2,500 gpm. The influent BOD can range from 200 to 600 mg/L or more. The sludge wasting rate itself may change but typically this value is set each day so we’ll consider this variable to be fixed in this example. And the concentration of the return sludge leaving the clarifiers, from which the waste sludge is pulled, is constantly changing throughout the day in response to many, many changes in the bioreactor and the clarifiers including flow rate, temperature, pH, BOD (or COD) load, nutrient conditions, biomass, etc.

So the issue is, how confident can we be in a point estimate when so many of the variables used to calculate the point estimate are themselves highly variable? And knowing how much variability we have in a dynamic wastewater treatment system, what can we possibly do about it? Enter @Risk which allows us, based on our knowledge of the system and the changeable nature of the variables involved, to quantitatively assign a range of “assumptions” or “probabilities” to each variable. In the table below all of the variables we defined above are listed with those highlighted in yellow representing the variables that are changing randomly throughout the day.

For this example let’s say that we know the flow rate, Q, is normally distributed with a standard deviation of 250 gpm. With @Risk we can assign a normal distribution to the flow rate and then we can model the expected oxygen consumption showing a range of values based on changes in the flow rate. We can assign another probability distribution to capture the variability in the influent BOD concentration and yet another probability distribution to capture the variability in the RAS concentration. Letting each of these key inputs vary based on an assigned probability distribution, we can generate a range of likely oxygen consumption values (the output we are looking for) and assign a probability to this range.

Here is what this modeling looks like. The first graph below is the normal probability distribution assigned to the influent flow rate. You can see from the graph our assumption is that the influent flow averages 2,500 gpm with a standard deviation of 250 gpm as previously stated. Therefore, in any given 24-hour period we can be 90% confident that the flow will range between 2,088 and 2,909 gpm.

@Risk normal distribution

In the next graph below we have assigned another normal distribution to the influent BOD concentration using our knowledge (based on laboratory data) of the wastewater plant to assign a mean value of 250 mg/L BOD with a standard deviation of 25 mg/L. From the graph we can be 90% confident that during any 24-hour period the influent BOD is likely to range between 208.7 to 291 mg/L.

Normal distribution of influent BOD

For our last variable, the return activated sludge concentration, which is also the concentration of the waste activated sludge, we’ve selected a Gamma distribution as shown below. We didn’t actually select this distribution. Rather, we let @Risk find the best distribution fit based on several months of laboratory data. As we can see below, at any given time during the day we are 90% confident that the RAS concentration is going to be between 8,283 and 11,780 mg/L.

Gamma distribution of return sludge concentration

The graph below represents the results of our modeling effort using @Risk. Instead of a point estimate that has no known certainty of ever actually representing the true oxygen demand in the biological reactor we now have a range of values and a confidence level. From this range we can state that we are 90% confident the oxygen demand throughout the day is going to range from 6,028 to 11,363 pounds. This range takes into account the uncertainty, and the assumptions we’ve made about that uncertainty through the selection of the probability distributions, the three key variables of influent flow, influent BOD, and the RAS concentration. All three of these key variables are changing randomly throughout the day and this randomness is captured in the range of oxygen consumption values shown in the graph below.

Now here’s where this graph really helps us with process control. The biological reactor has a maximum oxygen input capacity of 10,000 pounds per day. With the point estimate above that determined an oxygen consumption rate of 8,892 pounds per day we were (or should have been) falsely confident that we had all the oxygen we needed. But that doesn’t match reality because we have had repeated episodes of almost zero dissolved oxygen in the biological reactor. With our new estimate we can see that we are likely to have an oxygen generation shortfall at some point in any 24-hour period. But just how likely are we to have that oxygen shortfall?

Oxygen consumption results

Here’s what we can now do with the probability model. We can adjust the range to show the percentage of time we are likely to need an oxygen input rate that is greater than 10,000 pounds per day, our maximum generation quantity with the existing aeration equipment. As we can see from the figure below our chance of not having enough oxygen in any 24-hour period is 14.2%. This is a valuable piece of information and it’s telling us we should be thinking about a way to increase the total oxygen input capability to at least 12,000 pounds per day. But is that going to be enough?

Oxygen consumption

We will now bring this exercise to a nice conclusion by answering the question: “If we increase the oxygen generation capability to 12,000 pounds per day, will that be enough?” If we move the right boundary line out to “infinity” and move the left boundary line to 12,000 we can see that the likelihood of exceeding 12,000 pounds of oxygen consumption on any given day, given the conditions we stated when we started this analysis, is only 2.1%. That is, if we expand the system to generate 12,000 pounds of oxygen per day we have a 2.1% risk of not having sufficient oxygen input on any given day. Personally, I’m comfortable with that level of risk.

Nonetheless, we can do a cost-benefit analysis to determine how much it will cost to increase the oxygen generation capacity from 10,000 to 12,000 pounds per day compared to increasing it to the next available increment of 14,000 pounds per day. With an economic comparison in hand, combined with our detailed risk analysis, we will be able to make a very reasoned decision on a key capital expenditure to increase oxygen generation capacity.

Oxygen consumption

To see an example of how @Risk can be used to easily fit a distribution to a set of data, click here.

To learn more about @Risk visit www.palisade.com.

Home   Treatment   Formulas   OUR   Unit Processes   Modeling   @Risk   GPS-X   Microsoft Math   Hydraulics   Library   About