**Optimization Assessment Spreadsheet (OAS) Instructions**

# Updated: January 16, 2004

**Overview**

Surface water treatment plants are considered optimized when they can
consistently meet the optimization performance goals in Table 1. The OAS
is designed to assist plant staff in collecting and using turbidity data
to determine where they stand with respect to the optimization goals and
to identify possible factors that limit the performance of their plant.

These instructions only explain the features of the spreadsheet and the
elements of the reports. They do not provide information for
interpreting possible performance limiting factors at specific plants.

The OAS consists of several different worksheets displayed as tabs at
the bottom of the OAS workbook. Each tab presents options for data entry
and reports generated by the spreadsheet after data entry.

+-----------------------------------------------------------------------+
| **Table 1. Optimized Performance Goals**                              |
+-----------------------------------------------------------------------+
| **Individual Sedimentation Basin Performance Goals**                  |
+-----------------------------------------------------------------------+
| -   Settled water turbidity less than 1.0 NTU 95 percent of the time  |
|     when raw water turbidity is less than or equal to 10 NTU          |
+-----------------------------------------------------------------------+
| -   Settled water turbidity less than 2.0 NTU 95 percent of the time  |
|     when raw water turbidity is greater than 10 NTU                   |
+-----------------------------------------------------------------------+
| **Individual Filter Performance Goals**                               |
+-----------------------------------------------------------------------+
| -   Filtered water turbidity less than 0.10 NTU 95 percent of the     |
|     time based on the maximum filter effluent turbidity for each day, |
|     excluding the 15-minute period after bringing the filter online   |
|     (for plants without filter-to-waste capability)                   |
+-----------------------------------------------------------------------+
| -   Maximum filtered water measurement of 0.30 NTU                    |
+-----------------------------------------------------------------------+
| **Combined Filter Performance Goal**                                  |
+-----------------------------------------------------------------------+
| -   Combined filter effluent turbidity of less than 0.10 NTU, 95      |
|     percent of the time                                               |
+-----------------------------------------------------------------------+

**Data Required for OAS**

The OAS uses the plant's turbidity performance data and works with a
year's worth of data at a time. The OAS can be used to assess a year of
data or the plant's progress towards achieving optimized performance can
be tracked by entering plant data daily throughout the year. The
recommended way to populate the spreadsheet requires entering maximum
daily values for the following parameters:

-   Raw water turbidity

-   Settled water turbidity of each sedimentation basin (up to 4 basins)

-   Filtered water turbidity of each filter (up to 12 filters)

-   Combined filter effluent

A maximum value for the day for each of these parameters is entered into
the spreadsheet. For example, if the plant recorded a sedimentation
basin effluent every 4 hours during the day, they would take the maximum
value from the 6 readings and enter that value into the spreadsheet.

> Table 2 presents monitoring guidelines for these process streams.

+-----------------------------------+-----------------------------------+
| ## Table 2. Monitoring Guidelines |                                   |
+-----------------------------------+-----------------------------------+
| **[Process Stream]{.underline}**  | **[Monitoring                     |
|                                   | Guidelines]{.underline}**         |
+-----------------------------------+-----------------------------------+
| > Raw Water                       | > Daily raw water turbidity       |
+-----------------------------------+-----------------------------------+
| > Individual Sedimentation Basins | > Settled water turbidity at      |
|                                   | > 4-hour intervals from each      |
|                                   | > sedimentation basin             |
+-----------------------------------+-----------------------------------+
| > Individual Filter Effluents     | > On-line (continuous) turbidity  |
|                                   | > from each filter                |
+-----------------------------------+-----------------------------------+
| > Combined Filter Effluent        | > Combined filter effluent at     |
|                                   | > 4-hour time increments          |
+-----------------------------------+-----------------------------------+

**OAS Data Entry Requirements**

The OAS can handle a maximum of 366 daily data points for raw water,
four different sedimentation basins, 12 filters, and the combined filter
effluent. Those plants with more treatment processes require the
creation of another OAS file to track the performance of these
additional basins. Alternately, the plant can choose to only include
those basins with the worst performance. It is suggested that a separate
OAS be created for each year instead of trying to keep a running year of
data on one spreadsheet. Data entry is handled in two separate ways with
two different worksheets provided, accessed through by tabs at the
bottom of the workbook. **[It is important that the proper worksheet be
used for data entry.]{.underline}**

##### Data Entry Paste Worksheet

> **[The "DataEntryPaste" worksheet is only used to populate the
> spreadsheet with electronic data.]{.underline}** Figure 1 shows the
> "DataEntryPaste" worksheet for a plant with one sedimentation basin
> and five filters. This data has been electronically transferred to the
> OAS. Once all the data is copied into the worksheet, clicking on the
> green "TRANSFER" button activates a macro that converts the data to a
> standard format and creates a database. This database serves as the
> basis for the various reports in the OAS. The red "CLEAR" button
> clears the data entry area prior to electronically transferring a new
> data set into the worksheet.
>
> **Data Entry Values Worksheet**
>
> **[The "DataEntryValues" worksheet is only used for data entry when
> plant data is entered by hand.]{.underline}** This worksheet also
> displays the database created when data is entered into the
> "DataEntryPaste" worksheet (Figure 1), and after activation of the
> "TRANSFER" button. Figure 2 shows the "DataEntryValues" worksheet when
> generated. This becomes the basis for the other worksheets, which show
> different plant performance summary reports.
>
> At the top of the "DataEntryValues" worksheet, the plant's name and
> Public Water System (PWS) identification number are entered, along
> with information on the performance goals against which the plant
> would like their data assessed. This includes the regulatory
> requirements. This plant chose 2.0 NTU for the sedimentation basins,
> 0.1 NTU for the filters, and a regulatory requirement of 0.3 NTU. Use
> of the optimized performance goals in Table 1 is recommended, but the
> plant has the option to enter other values for the performance goals.
>
> After entering the value for the different performance goals, the OAS
> highlights, in yellow, those values that exceed the selected goals.
> Figure 2 shows that, for the month of data shown, the sedimentation
> basin did not achieve the performance goal of 2.0 NTU on 1 /17/2002
> and that filters 1,4 and 5 did not meet the 0.1 NTU filtered water
> goal.
>
> Once the performance goals are entered on the "DataEntryValues"
> worksheet, activating the green "UPDATE" button creates a series of
> worksheets summarizing the data in different ways. Activating the red
> "CLEAR" button removes all of the data from the spreadsheet so that it
> can be used for another data set. **It is important to note that every
> time that new data is entered in the OAS, the "UPDATE" button must be
> activated to generate updated summary reports.**

**OAS Summary Reports**

After activating the "UPDATE" button, the macros in the spreadsheet
create a series of reports. A series of tabs across the bottom of the
workbook identify the different worksheets that contain the different
reports. The OAS generates the following reports:

> **Summary Worksheet**
>
> The "Summary" worksheet, shown in Figure 3, presents the "Treatment
> Barrier Performance Summary", which consists of four parts.
>
> The upper left section contains the "Turbidity Profile" with trend
> plots of the log of the turbidity values for raw, max sed, max
> filtered, and combined. The max sed and max filtered represent the
> maximum value of all of the sedimentation basins and filters for that
> day. If, on a particular day, filter 1 had the highest maximum, that
> would be the max filtered and plotted on this graph. The log scale of
> the turbidity values allows presentation of all the treatment process
> performance data on the same graph for determining if variations in
> raw water turbidity pass through the different treatment processes.
>
> The right side of the worksheet provides the next two parts where two
> trend graphs are provided; "Maximum Daily Settled Water Turbidity" and
> "Maximum Daily Filtered Water Turbidity." These show how the plant's
> max sed, max filtered, and combined turbidities compare against the
> selected performance goals.
>
> Finally, at the lower left of the worksheet is a data summary table
> that provides some statistics on the data. The table presents the
> maximum, minimum, and average for all of the data along with the
> 95^th^ percentile value. RSQ provides a correlation between the raw,
> settled, filtered, and combined turbidities. The settled data is
> correlated with the raw, while the filtered and combined are
> correlated with the settled. A high RSQ may indicate that the
> turbidity spikes are passing through the treatment processes. Based on
> experience with this parameter, a coefficient above about 0.25
> indicates that turbidity pass-through may be occurring in a process.
> The last two columns of the table present the percentage of time that
> the data met the selected performance goals. Note that the OAS
> calculates the 95^th^ percentile values using daily maximum values and
> not all the 4-hour discrete readings required by the regulations.
> Because of this, the 95^th^ percentile values in the OAS do not equate
> to the 95^th^ percentiles reported to the state for determination of
> regulatory compliance.
>
> **Optimization Trend Worksheet**
>
> Figure 4 shows the "Optimization Trend" worksheet that contains the
> "Optimization Trend Report," which consists of three sections.
>
> A table across the top of the worksheet contains a summary of the unit
> treatment process performance data by month. This worksheet shows the
> 95 percentile values calculated for the individual sedimentation and
> filtration processes and the percentage of monthly values meeting
> specific performance goals. Calculation of the percentile for
> sedimentation uses the data for all the individual sedimentation
> basins, while the calculation of the filtered water percentile uses
> the combined filtered water data. Charts located on the lower part of
> the report also plot these data.
>
> For each month, the worksheet highlights in red the sedimentation
> basin and filter with the highest turbidity value. Since the example
> plant has only one sedimentation basin, all of the monthly values are
> red. For the month of June, however, filter 4 at this plant had the
> highest turbidity of all the filters (0.21 NTU). A closer inspection
> of the data for all of the filters shows that the range of values for
> all five filters was essentially the same. Looking at filter 4, one
> can see that it had the highest turbidity for five of the 12 months,
> with three months above 0.2 NTU. In addition, one can see, at the
> bottom of the table, that Filter 4 met the goal of 0.10 NTU only 83%
> of the time, compared to 93.7%, 89.3%, 92.9%, and 87.7% for Filters 1,
> 2, 3, and 5, respectively. Filter 4 also had the highest 95^th^
> percentile over the entire year (0.17 NTU) of all of the 5 filters. To
> optimize this plant, the plant staff may try to determine if there are
> reasons for this filter consistently having the highest turbidity.
>
> The "Optimization Trend Report" contains two trend graphs across the
> bottom, with the "Settled Water Optimization Trend" on the left and
> the "Filtered Water Optimization Trend" on the right. Each of these
> graphs trend the same two sets of data, one related to the
> sedimentation basins and the other to the filters.
>
> The most prevalent feature of these graphs is the various colored
> areas that are layered on top of each other. For each month, all of
> the data for the respective treatment process are sorted and placed
> into four categories: For the sedimentation basins the categories are
> \>3 NTU, \<3 NTU, \<2 NTU and \<1 NTU. For the filters the categories
> are \>0.3 NTU, \<0.3 NTU, \<0.2 NTU and \<0.1 NTU. The percentage of
> time the data for that month is in each of the four categories is then
> plotted using the vertical axis on the left. Each category is plotted
> as a separate area on the chart, so that the 0.1 NTU data (and then
> 0.2 NTU data, etc.) is on top of the other categories. Looking at the
> "settled water optimization trends" graph in January 2002, the settled
> water was \<1 NTU 61% of time, \<2 NTU 84% of time and \<3 NTU 100% of
> the time. Since none of the data was \>3 NTU (the plant met \< 3 NTUI
> 100% of the time in January), there is no white area showing in
> January. In April, the plant met \<3 NTU only 96.67% of the time;
> therefore, there is a small white area showing in April.
>
> Though these trend graphs appear confusing at first, their main
> purpose is to allow the plant to quickly see how it performs
> throughout the year with respect to the optimization goals. There is a
> tendency to try and read more into them than is necessary. In looking
> at these graphs, it is important to notice how much of the graph is
> covered with the layer representing the highest level of performance.
> In the "Settled Water Optimization Trends" graph the plant was almost
> completely optimized in November, but had less than optimum
> performance in August. It is also important to look at the trend in
> the different layers. Between September and November, the performance
> of the sedimentation basins greatly improved. Between, July and
> September, however, there were problems with the sedimentation basin
> performance. The plant staff can use this information to assess
> changes in the plant during these time periods to determine what
> results in the best performance.
>
> These graphs also have a solid line that plots the 95% value each
> month, shown on the right vertical axis, for the sedimentation basins
> and filters. The intent of the trend line is to allow the plant to
> observe whether or not the performance is improving based on the slope
> of the line. If the line is sloping downward, then performance is
> improving. If it is sloping upward, then the changes in the plant are
> taking the performance in the wrong direction.

**Other Summary Worksheets**

The OAS also contains several other worksheets that provide graphical
presentation of the performance data. Most of these are trend graphs of
the performance of the individual treatment units. Up to four individual
processes are shown on a single worksheet. Figures 5 and 6 show the
"SedSum" and "FilterSum (1-4)" worksheets.

The remaining worksheets present single versions of some of the graphs
on the "Summary" and "Optimization Trend" worksheets.

**Long Term Trends Worksheet**

The OAS only allows the analysis of one year of data at a time.
Optimization of a treatment plant, however, occurs over several years
and looking at trends between the different years can be beneficial.
There is a separate long-term trends spreadsheet that will allow
development of the settled and filtered water optimization trend charts
for a three-year period. The last tab on the OAS is the "LT-Trend"
worksheet, which generates a table of data (shown in Figure 7) that can
be copied into the long-term trends spreadsheet. Figure 8 shows the
output of the long-term trends spreadsheet.

### ![](media/image1.png){width="8.591666666666667in" height="6.44375in"}Figure 1 

**Figure 2**

![](media/image2.png){width="8.891666666666667in" height="6.66875in"}

**Figure 3**

![](media/image3.png){width="9.0in" height="6.43125in"}

![](media/image4.png){width="8.999212598425197in"
height="6.096456692913386in"}

**Figure 4**

![](media/image5.png){width="8.7in"
height="6.5784722222222225in"}**Figure 5**

![](media/image6.png){width="8.0in"
height="5.959722222222222in"}**Figure 6**

![](media/image7.png){width="9.1in"
height="3.5034722222222223in"}F**igure 7**

![](media/image8.png){width="8.8in" height="6.3in"}**Figure 8**