HomeMy WebLinkAbout10/01/2016 - Report: Water Demand Forecasting - City Council - Audit Committee
Water Demand Forecasting
October 2016
City Internal Auditor’s Office
City of College Station
Water Demand Forecasting
Table of Contents
Introduction ................................................................................................................. 3
Purpose and Objectives ..................................................................................... 4
Scope and Methodology ..................................................................................... 4
Water Demand Forecasting Background ........................................................... 6
Accepted Forecasting Methods Range from Simple to Complex ................................... 6
Several Factors Should be Considered in Selecting a Forecasting Method .................... 7
Water Demand is influenced by a Number of Factors ................................................. 9
Findings and Analysis .................................................................................................. 10
Environmental Effects on Water Demand ........................................................ 10
Weather Explains Much of the Summer Variability in Water Demand ......................... 11
Other Factors May Affect Yearly Demand Predictions ............................................... 12
Some Billing Strategies May Be Diminishing Conservation Impact ............................. 13
Block Rates May Not Be Having Their Expected Impact on Conservation ................... 14
Inflation Must Be Considered to Understand the True Cost of Water ......................... 16
Very-Short- and Short-term Forecasting Evaluation ........................................ 17
Daily Forecasts Are Effective and in Alignment with AWWA Approaches .................... 17
Subjective Forecasting Methodologies Present a Risk ............................................... 19
Short-term Forecasting: There is a Lack of Connection to Water Demand Data .......... 19
Medium-term Forecasting Evaluation .............................................................. 20
Consultant Cost-of-Service Studies do not Adequately Forecast Water Demand ......... 20
Revenue classifications May Not Continue to Be Effective as Forecasting Categories ... 21
Long-term Forecasting Evaluation ................................................................... 22
Some Consultant Long-term Forecasting Methods Are Unclear ................................. 22
In-House Long-term Forecasting Focuses on Peak Day Demand ............................... 24
Reliance on Consultant Forecasts has Risks ............................................................ 25
General Conclusions ........................................................................................ 27
Other Considerations ....................................................................................... 28
Appendices
Appendix A: Water Demand Weather Predictive Model Development
Appendix B: Water Demand Environment Causal Model Development
Appendix C: Very-Short-Term Replica Model Development Appendix D: Developed Forecasting Methodologies – Per Capita Appendix E: Developed Forecasting Methodologies – Sectoral
Appendix F: Management Response
3 Water Demand Forecasting Audit
Introduction
The City of College Station has the capacity to produce approximately 30 million gallons of water to
serve residents, businesses, and other organizations in the community each day. These customers use
the water, returning some part of it back to the City’s water treatment facilities that discharges its
effluent into the area’s rivers, streams, or other environments.
The purpose of water demand forecasting is to make information available to public water suppliers as
they conduct their business. Capital investments associated with public water supply systems are
extremely expensive, costing millions — even hundreds of millions — of dollars. It thus behooves utility
management to make continuing comparisons between current conditions and longer-term forecasts.
Why we conducted this review: An examination of the Water and Wastewater utilities was included in
the fiscal year 2016 audit plan based on direction given by the Audit Committee. We conducted a
preliminary risk assessment in May 2016 of the Water Services Department and found that a review of
the City’s water demand forecasting methods was warranted. This was largely based on two factors.
First, population growth has put enormous strain on the City’s water and wastewater infrastructure.
Over $17 million in expenditures for water capital projects is estimated to be spent in fiscal year 2017 on
infrastructure expansion to increase water capacity as well as rehabilitation projects to maintain current
infrastructure. In addition, wastewater capital projects are estimated to be over $20 million to fund
sewer improvements, wastewater facility expansion, and various line rehabilitation projects. Although
no rate increase is proposed for the Water Fund in fiscal year 2017, a rate increase of 8% was approved
for the Wastewater Fund in the upcoming fiscal year.
Second, the last comprehensive water and sewer utility rate study commissioned by the City occurred in
2003. This study was completed by Black and Veatch, a global engineering and consulting company
specializing in utility infrastructure development and management consulting. Black and Veatch also
conducted a utility rate study in 2002. Prior to that, a study conducted by McCord Engineering was
completed in 1987. All three of these reports contain water demand forecasts that act as a basis for
their cost-of-service study, which functions as a key component for setting utility rates and planning for
future growth and infrastructure needs. However, we found that these reports relied on simple
forecasting techniques. Although the forecasting methods used in these reports may be reasonable in
certain circumstances, many larger utilities use more sophisticated methods that focus on the dynamics
of residential, commercial, industrial, and public customers — issues that ultimately relate to the form
and growth of a community or region.
Water Demand Forecasting Audit 4
Purpose and Objectives
Purpose: The purpose of this review is to evaluate the City’s methods — both past and present — for
determining future water demand. In addition, this report contains forecasting models that were
developed using America Water Works Association accepted methodologies. These approaches are
demonstrated in this report in order to best evaluate how widely accepted models compare to City
water demand forecasting approaches.
Objectives: This report answers the following questions:
How effective are the City’s methodologies for forecasting water demand?
How do the City’s methodologies for forecasting water demand compare with the most widely
accepted approaches promulgated by the American Water Works Association?
How do weather and demographics impact water demand, and can a better understanding of
these impacts be used to better inform policy decisions?
Scope and Methodology
The Office of the City Internal Auditor conducted this review of the City’s water demand forecasting
methods pursuant to Article III Section 30 of the College Station City Charter, which outlines the City
Internal Auditor’s primary duties. This examination was conducted in accordance with government
auditing standards (except for the completion of an external peer review),1 which are promulgated by
the Comptroller General of the United States.
Although we conducted interviews with City staff and other relevant professionals and researched
professional literature and peer reviewed articles; our primary source of criteria in conducting this
review comes from the American Water Works Association.
The American Water Works Association (AWWA, http://www.awwa.org/) is the largest nonprofit,
scientific and educational association dedicated to managing and treating water. The AWWA has
approximately 50,000 members and is a trusted source of industry standards that establishes minimum
requirements for materials, equipment, and practices used in water treatment and supply. These
standards are used by thousands of manufacturers, distributors, and water treatment facilities
worldwide.
The AWWA publishes manuals and journals with peer reviewed scientific research that are widely
accepted in the industry. In developing our criteria for forecasting water demand we relied on the
AWWA’s most recently revised Forecasting Urban Water Demand and Principals of Water Rates, Fees,
1 Government auditing standards require audit organizations to undergo an external peer review every three years.
5 Water Demand Forecasting Audit
and Charges manuals. The Forecasting Urban Water Demand manual was used to construct the water
demand forecasting models that we created for comparative purposes.2
We examined the City’s very-short-, short-, and long-term water demand forecasting approaches as well
as their past use of consultants when these consultants provided water demand forecasts. In evaluating
whether the City is utilizing an optimal approach to water demand forecasting, we (1) compared the
methods utilized by Water Services to methodologies and accepted principles presented in AWWA
materials and (2) evaluated the accuracy of the City’s forecasts by comparing actual to forecasted
amounts.
In this review, we also sought to discern if there were forecasting approaches that could possibly yield
stronger and more accurate forecasts than those currently in use. To accomplish this objective, we
identified accepted AWWA approaches that best lent themselves to the data that was readily available.
Based on our research and data collection efforts, we were able to develop per capita, aggregated time-
series extrapolation, sectoral time-series extrapolation, and multiple regression3 (predictive) models.
We used historical population forecasts and estimates obtained from the City’s comprehensive plan to
develop a per capita water demand forecasting model. We also conducted a comparative analysis using
census data to evaluate the accurateness. Based on this review, we found that the City tends to slightly
under project City population on average by approximately 2%. We determined that this was an
acceptable error rate for our purposes. The daily amount of water pumped to be put into production
towards water consumption was obtained from Water Services. This pumped water data set contained
records from January 1996 to July 2016.
Using this pumped water data set, we identified the peak day water demand for 1996 through 2015. We
also obtained peak day water demand data for the years 1985 through 1995 from City staff. After
combining this data we were able to estimate an aggregate time-series trend line to forecast peak day
water demand growth.
For our sectoral time-series extrapolation model, we obtained monthly billed water consumption from
2008 to 2015 by utility customer from the City’s utility billing information system. Based on when a
meter was read, we created an algorithm to adjust the billed consumption to more accurately account
for the month in which the consumption actually occurred. From the Brazos Central Appraisal District,
we obtained the state property category descriptions for each property in the City. A relation by
property address was created to combine these two data sources.
Finally, we examined the impacts of a number of demographic variables such as price and population, as
well as those that account for conservation efforts and inflation using principles set forth by the AWWA.
We also wanted to know what impacts weather had on water demand in our local area through the use
of multiple regression analysis. To further ensure the reasonableness of our methods, we consulted with
a distinguished ecologist, who is knowledgeable in climate science and an expert in developing multiple
regression models, to examine and critique our analyses.
2 See Appendices D and E.
3 Regressions are statistical models that mathematically relates one variable to another, where the magnitude of one of the variables
(the dependent variable) is determined by the second variable (the independent or explanatory variable). In the case of multiple
regression, more than one explanatory variable is used to predict the magnitude of change in the dependent variable.
Water Demand Forecasting Audit 6
Water Demand Forecasting Background
Accepted Forecasting Methods Range from Simple to Complex
According to the American Water Works Association (AWWA), water utilities across the nation utilize a
wide range of forecasting models. These methods can range from simple informal forecasts, in which
decision makers judge that the future will act just like their recollection of the past, to complex formal
models requiring many variables, large amounts of data, and a significant commitment of resources. The
most common approaches fall within one of the following categories: (1) subjective methods (2) per
capita or other unit-use coefficient approaches, (3) time-series extrapolation, and (4) regression models.
Subjective Methods. Judgment-based forecasting methods vary widely, ranging from the informed
opinion of utility management to highly structured scenario-building methods. Presumably, utilities that
do not use a formal forecasting method rely on the informed opinion of management to make decisions.
For many small utilities that are experiencing slowly changing conditions, this may be sufficient. Larger
utilities and those facing more rapid changes in their service areas would most likely benefit from more
elaborate methods.
Per capita and other unit-use coefficient approaches. In its simplest form a per capita model multiplies
estimated water use per person by the projected population. This method relies on the ability of
analysts to identify reasonable numbers for gallons per capita per day and accuracy of population
forecasts that are typically produced by other agencies.4 Larger urban water systems tend to develop
sectoral demand forecasts on a per customer basis, calculating unit water use coefficients for customer
by categories such as residential, commercial, industrial, and public.
A variant of the unit-use coefficient approach is to calibrate the demand forecast to the land use plan in
the utility service area. Residential, commercial, and industrial land uses are estimated to consume
certain amounts of water per acre per year. It is important to note that the effectiveness of water
demand forecasts based on land use is greatest in those areas with strict land use regulations,
comprehensive land use planning, and a stable industrial structure. The long-range water demand
forecast utilized by City Water Services uses this variant unit-use coefficient approach.5
Time-series extrapolation. Time-series extrapolation encompasses a variety of techniques, including,
simple time trends, exponential smoothing, and autoregressive integrated moving-averages models to
project historical water use trends into the future. These models rely on the assertion that future
changes in water use can be predicted based on historical changes in water use (ignoring all other
possible influences). These methods can provide reasonably accurate forecasts as long as the future is
essentially similar to the past. The strength of extrapolation models is that the only data required are
the historical data on the variable being forecasted. However, all single variable forecasting methods
share a major limitation — they do not account for changes such as population shifts, conservation
programs, or price increases.
4 In the City of College Station, the Department of Planning and Development Services produces population forecasts based on
certificates of occupancy. 5 The most recent long-term water demand forecasting model was conducted by the consulting and engineering firm Freese and
Nichols in 2014.
7 Water Demand Forecasting Audit
Regression Models. The essential feature of these statistical models is the use of a set of driver or
explanatory variables to describe why water use has changed historically and to forecast future values.
The models directly incorporate anticipated changes in driver variables such as customer income levels,
water rates, conservation programs, weather factors, and technology advancements. Because per capita
and unit-use coefficient forecasting methods ignore socioeconomic factors, properly designed
regression models tend to yield more accurate forecasts.
If trends in water prices, personal income, ownership of water using appliances, population, urban
density, and other factors are to be used together in a forecasting model, regression modeling is
appropriate. The challenge arises because data for these driver variables must be readily available and
obtained (or forecasted) first, before water use forecasts can be developed. This makes the entire
forecasting effort far more complex.
Several Factors Should be Considered in Selecting a Forecasting Method
Water utilities should consider the following when selecting and evaluating a water demand forecasting
method: purpose of the forecast, data availability, requirements for accuracy of the forecast, how well
the forecasting model can be explained to stakeholders in the water planning process, and the ease of
updating the model.
Forecasting purpose. The choice of methodology, including the forecast horizon, is directly linked with
the intended purpose for the forecast results. The basic application areas for water demand forecasts
include: (1) sizing system capacity and raw water supply, (2) sizing the staging treatment and distribution
system improvements, (3) water rate setting, revenue forecasting, and budgeting, (4) program tracking
and evaluation, and (5) system operations management and optimization.
Capacity issues and raw water supply usually relate to long-term forecast horizons that range from one
to several decades. Rate setting and sizing and staging treatment and distribution system improvements
in a water system usually involve a medium-term forecast horizon of several years to a decade. In the
short term, a few months to a few years, the forecast focus is on budgeting, program tracking and
evaluation, and revenue forecasting. Finally, managing and optimizing system operations, such as
pumping and maintenance schedules, involve very-short-term forecasts — periods of hours, days, or
weeks. See Table 1 below.
Table 1: Water Demand Forecasting Types and Applications
Forecast Type Forecast Horizon Applications
Long-Term Decades, 10-50 years Sizing system capacity, raw water supply
Medium-Term Years to a decade, 7-10 years Sizing, staging treatment and distribution system
improvements, investments, setting water rates
Short-Term Years, 1-2 years Budgeting, program evaluation, revenue forecasting
Very-Short-Term Hours, days, weeks Optimizing, managing system operations, pumping
Water Demand Forecasting Audit 8
Customer Disaggregation. Forecast accuracy can often be improved, regardless of the choice of method,
by segmenting utility customers into relatively homogeneous groups such as single family residential,
multifamily residential, commercial, industrial, or governmental customers. The choice of segments
depends on the characteristics of the utility service area and may include additional categories such as
high- and low-valued housing areas.
For smaller public water supply systems, relatively simple forecast methods suffice, and not just because
of costs. With smaller numbers of customers, disaggregating water use by categories is more likely to
result in excessive volatility within each category. Simpler forecasting methods, such as the per capita
water demand forecasting approach with no disaggregation, are appropriate in this case. As the water
system grows in size, however, customer water use disaggregates become more predictable. Developing
a sectoral water demand forecast, which focuses on movements of water use by major customer
categories, can result in gains in accuracy and explicability.
Sectoral water demand forecasts also provide better benchmarks for tracking water demand in the near
term. Maintaining a sectoral water demand forecast is good business practice when data availability and
system financial resources allow it and heterogeneous groups of customers make it worthwhile.
Data Availability. The availability of data is often a primary constraint in developing forecasting models.
In general, several years of data are needed to develop medium- to long-term water demand forecasting
models. This requirement for time or historical depth of the data is closely related to the importance
and unpredictability of weather on urban water use. The historical data must be of sufficient length or
time depth to allow unusual weather effects — such as droughts or exceptionally wet, rainy, and cool
periods — to be included or accounted for across the historical record.
At the same time, new forces can emerge in the community, causing changes in water use patterns.
Examples can include “densification” of settlement patterns, or construction of substantially larger
houses with more bathrooms and water using appliances on larger landscaped lots. Conversely, new
constructions, especially of townhouses and condominiums, may be built around natural areas with no
cultivated landscape. Carefully examining community trends helps analysts determine how many years
of data are required and which community patterns are relevant in developing a forecast.
Model Accuracy. The ultimate test of any forecast is how close it came to predicting what actually
happened. This suggests that utilities undertake periodic comparisons between previous forecasts and
realized values for water demand and utility revenues. With water demand forecasts, however, this is
complicated by the importance and inherent unpredictability of transitory weather events. As a result,
water demand models should be closely monitored. This essentially means developing a comprehensive
water demand regression (probably on a monthly or seasonal basis), and studying the performance of
predicted parameters (such as water use rates of residential households) when making allowance for
specific weather conditions.
9 Water Demand Forecasting Audit
Water Demand is influenced by a Number of Factors
A number of factors have a significant impact on water demand, including population, employment,
economic cycles, technology, weather and climate, price, and conservation programs.
Population, Employment, and Technology. Population growth is often the major trend factor in water
use. Business cycle factors affect water use because fluctuations in industrial and commercial
production translate into commensurate changes in water demand. In addition, water consumption will
increase, other things being equal, when family income rises. Technological change can also affect water
use over time. For example, widespread installation of garbage disposals and automatic dishwashers in
homes may increase domestic water use.
Weather and Climate.6 Seasonal weather (such as summer high temperatures) and component water
use are generated primarily by the local climate (humid, subtropical temperatures). Summer peaking
demand is typical. Higher summer demand levels are related to water use for outdoor activities,
including lawn watering and gardening, and to the use of evaporative coolers. Seasonal demand
patterns are important in planning the capacity of water treatment and distribution systems. Short-term
patterns are also critical for scheduling maintenance times for water services infrastructure.
Price. Price effects are important for short-, medium-, and long-term forecasts. Both water use and
utility revenue are directly affected by water rate changes. In the short term of a few months, rate hikes
can cause consumers to change their behavior. These changes can include taking shorter showers or
reducing car washing and lawn watering. In the longer term, if a noticeable rate hike keeps pace with
inflation, consumers may adapt through their selection of water using durable goods, favoring
appliances with lower water use ratings and possibly innovative landscaping designed to cut back on
water use.
Efficiency and Conservation Programs. Water efficiency and conservation programs typically couple an
appeal to civic virtue with information on how to use less water. Crisis programs resulting from drought
or other supply interruptions have generated large, albeit temporary, reductions in water use. Programs
designed to permanently change individual behavior are capable of generating long-term reductions in
water usage. Conservation program effects must be thoughtfully included in the water demand
forecasting model to minimize errors in projected water use and revenue.
6 Weather is the daily or monthly changes in temperature, precipitation, relative humidity, whereas, climate is more general, meaning
year-to-year or region to region.
Water Demand Forecasting Audit 10
Findings and Analysis
Environmental Effects on Water Demand
In College Station, water demand typically peaks during the summer months, and peak summer demand
usually occurs in August. However, what may go unmentioned is the increased variability in summer
month demands. As we can see from Figure 1, the average water demand range (maximum minus
minimum) during summer months (June-August) is almost 5 times larger than the average winter
months (December-February) water demand range.
Figure 1: Water Demand Patterns by Month (2000-2015)
To evaluate this variability, we generated the following three predictive multiple regression models
based on weather and pumped water data: 1) a daily model for each month, 2) a general monthly
model, and 3) a monthly model for each month. For each of these models, we used daily pumped water
and weather data from 2000 through 2015. The summary statistics for variables used in model
development for each of these models are shown in Table 2 below.
Table 2: Variable Summary
Daily Models (N=5,844) Monthly Models (N=192)
Variable Mean SE Max Min Variable Mean SE Max Min
PUMP 11.14 0.05 26.24 3.28 PUMPM 339.03 8.56 708.45 193.93
(millions of gallons) (millions of gallons)
PRECIP (inches) 0.11 0.01 5.28 0.00 PRECIPM (inches) 3.31 0.19 12.89 0.00
TMAX (F) 79.92 0.19 112.00 31.00 TMMAX (F) 79.85 0.93 103.84 54.74
TMIN (F) 59.00 0.19 81.00 17.00 TMMIN (F) 58.93 0.92 78.03 35.58
TAVG (F) 69.46 0.19 93.50 25.50 TMAVG (F) 69.39 0.92 90.94 45.16
LAST2 0.37 0.01 1.00 0.00 LAST2M 0.38 0.01 0.77 0.00
DAYSSINCE 4.88 0.09 56.00 0.00 DAYSMSINCE 13.56 0.54 4.90 53.55
WEEKFREQ 1.69 0.02 7.00 0.00 MONTHFREQ 7.35 0.25 18.00 0.00
WWINDEX 53.41 0.26 93.50 0.00 MWINDEX 53.05 1.04 85.98 22.41
0
100
200
300
400
500
600
700
800
Pu
m
p
e
d
W
a
t
e
r
(m
i
l
l
i
o
n
s
o
g
g
a
l
l
o
n
s
)
MEAN PUMP
MAX PUMP
MIN PUMP
11 Water Demand Forecasting Audit
A full explanation of each regression model, as well as the description for each variable used, can be
seen in Appendix A. Below, in Table 3, are the results for the general model and the monthly models
based on a monthly time-step.
Table 3: Weather Model Results – Monthly Time-Step7
Model Coeff. Variable Partial R2 P-value Model R2 F-value
General -12.856 0.9031 346.88
0.0112 MWINDEX 0.7851 0.0001
0.0194 MONTHFREQ 0.0565 0.0001
0.0073 YEAR 0.0518 0.0001
0.0018 DAYSMSINCE 0.0070 0.0004
-0.0035 PRECIPM 0.0027 0.0247
February 2.0893 0.2591 4.90
0.0039 TMMAX 0.2591 0.0440
March 2.0948 0.6640 27.67
0.0067 MWINDEX 0.6640 0.0001
April 2.4007 0.4824 13.05
0.0067 DAYSMSINCE 0.4824 0.0028
May 2.2151 0.7996 25.93
0.0062 DAYSMSINCE 0.5752 0.0019
0.0041 MWINDEX 0.2244 0.0021
June 0.0100 0.9482 118.96
0.0281 TMMAX 0.9249 0.0000
-0.0071 PRECIPM 0.0233 0.0311
July -0.0672 0.6883 30.91
0.0287 TMMAX 0.6883 0.0001
August -0.1946 0.7047 15.51
0.0306 TMMAX 0.5784 0.0006
-0.0041 DAYSMSINCE 0.1263 0.0347
September -0.0104 0.6019 21.16
0.0328 TMAVG 0.6019 0.0004
October 2.1817 0.6826 30.10
0.0068 MWINDEX 0.6826 0.0001
November 2.5072 0.5066 14.37
-0.0157 PRECIPM 0.5066 0.0020
December 1.9889 0.2540 4.78
0.0072 TMAVG 0.2540 0.0465
Weather Explains Much of the Summer Variability in Water Demand
As we can see from Table 3 above, water demand in different months is affected by different weather
events. Specifically, during the months of June, July, August, and September a single temperature variable
(either TMMAX or TMAVG) explains more than half of the variability in water demand. Moreover, in June 92.5%
of water demand variability can be explained by maximum temperature (TMMAX) alone. This is most likely
7 January is absent from this table, because no significant weather variables could be identified; All monthly models were log10
transformed.
Water Demand Forecasting Audit 12
because it is a transition month from Spring to Summer. As we can see from Figure 2, average summer
month (June – September) temperatures are seemingly significant drivers in yearly water demand
(correlation coefficient of 0.77).
Figure 2: Average Maximum Summer Temperatures and Total Pumped Water
On another note, there is no weather forecasting model for January. This is because none of the
variables we used to measure weather effects in other months were statistically significant in January.
This is particularly useful to know when attempting to understand indoor versus outdoor water usage as
we can reasonably assume that, in general, there is very little outdoor (and thus weather related) water
demand that occurs during January.
To aid in future water demand planning, we have provided the table below summarizing the average
value (between 2000 and 2015) of each weather variable listed in the general model for each month.
Table 4: Monthly Weather Variable Summary
Variable JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
TMMAX 61.72 64.67 72.28 80.06 86.67 92.82 95.03 97.10 91.30 82.10 71.30 63.17
TMMIN 40.78 43.96 51.13 59.26 66.71 73.15 74.70 75.07 69.96 60.26 49.98 42.24
MWINDEX 37.66 38.79 45.09 54.88 58.05 62.83 68.76 70.57 63.08 54.23 44.12 38.56
MONTHFREQ 8.31 8.13 8.38 6.44 7.56 7.38 5.94 5.63 6.56 7.38 8.25 8.31
DAYSMSINCE 11.57 11.00 10.74 10.00 13.31 12.97 16.14 19.05 15.83 15.41 15.35 11.36
PRECIPM 3.05 2.85 3.71 2.05 4.20 3.73 2.70 2.09 3.59 4.87 3.81 3.10
Other Factors May Affect Yearly Demand Predictions
It is important to note that, while weather is an important factor in predicting monthly water demand
(mostly due to seasonality), other environmental factors (such as demographic changes and
conservation efforts) may also impact a yearly water demand model. The results from a yearly causal
demand model, including weather, are presented in Table 5 on the next page.
85
87
89
91
93
95
97
99
101
103
105
3,000
3,500
4,000
4,500
5,000
5,500
T
e
m
p
e
r
a
t
u
r
e
(
F
)
Pu
m
p
e
d
W
a
t
e
r
(m
i
l
l
i
o
n
s
o
f
g
a
l
l
o
n
s
)
Pumped Average Max Temp
13 Water Demand Forecasting Audit
Table 5: Yearly Water Demand Environment Model
Potential Models (1 – 5)
Variable 1 2 3 4 5
TSUMMERMAX 189.73*** 161.50*** 158.38*** 155.50*** 162.98***
POP 0.0269*** 0.0202**0 0.0130000 0.0107000
CONS 137.67000 165.78000 22.23000
PRICE 701.42000 00
INFLATE -1434.06*00
Constant -13656.97000 -13492.12000 -12636.04000 -13247.17000 -8642.72000
Model R2 0.7760000 0.9431000 0.9493000 0.9520000 0.9681000
Model F-Stat 34.64000 74.63000 49.89000 34.68000 53.03000
Model Sig 0.0002000 <0.0000000 <0.0000000 0.0001000 <0.0000000
Note: * indicates significance above the 90% level, ** indicates significance above the 95% level, and *** indicates
significance above the 99% level.
If the coefficients presented above were to be used for predictive purposes, then only Model 2 should be considered
because it provides the best-fit model explaining 94.3 % (R2 = 0.9431, p < 0.0001) of the variation in water pumped per
year (PUMPY) with two significant variables TSUMMERMAX and POP, where PUMPY = -13492.12 + 161.50 (TSUMMERMAX) +
0.0269 (POP). In the other models, although the R2 may be slightly higher, not all variables were significant, and
therefore should not be used as a predictive model.
In the models above, the variables considered included: TSUMMERMAX, an average of the maximum daily
temperature during the months of June through September; POP, an estimated population of the City of
College Station based on certificate of occupancy; CONS, an indicator variable representing years in
which there was an active conservation program (starting in 2010; 1 = active, 0 = inactive); and the PRICE
and INFLATE variables, both measure the lowest residential volumetric water rate; PRICE indicates the
nominal price and INFLATE indicates the real price in 2016 dollars. Full model development is provided
in Appendix B.
Table 5 presents the results of five separate regression models. Unlike previously discussed predictive
weather models, the models presented in Table 5 were prepared for causal analysis. More commonly
used in econometrics, causal analysis through the use of regression modeling attempts to determine
whether a particular independent variable meaningfully affects the dependent variable. In the models
above, the average summer maximum temperatures are very significant and impactful to annual water
demands. However, when the price variables are included, population no longer seems significant.
This is most likely because the price variables and population have a very strong correlation (correlation
coefficient above 0.80 or below -0.80) and thus a higher variance inflation factor. This is presumably due
to time being a strong driver for both variables. Though this effects the significance of these two
variables, it should not have an effect on coefficient estimation.
Some Billing Strategies May Be Diminishing Conservation Impact
While conservation is not significant in our causal model, it may still be having an impact on water
demand. For example, from 2010 to 2015, 874 high efficiency toilet rebates have been issued and 138
rain barrels have been installed. Over the course of these five years, this resulted in estimated savings of
at least 5 million gallons of water. These conservation efforts should not be marginalized. However, to
put this into perspective, Water Services has the capacity to produce 30 million gallons of water each
day.
Water Demand Forecasting Audit 14
This lack of statistical significance of conservation (Table 5) may be partially attributed to fiscal
procedures and strategies. For example, some College Station residents take advantage of the budget
billing system. This system allows customers to pay the same amount of money every month for their
utility bill, no matter what they actually consumed. Then each year, there is a “settle up” month in which
any remaining balance is paid for that year’s utility bills. This, along with an automatic bank draft service,
allows customers to pay their utilities without ever having to look at their bill. These two fiscal
procedures increase the risk of large water leaks going unnoticed and diminish the effects of
conservation-oriented, block rates as well as other conservation-oriented programs.
Block Rates May Not Be Having Their Expected Impact on Conservation
In fiscal year 2008, the City changed its water rate structure to have five tiers of increasing block water
rates for residential customers.8 At each tier water is charged a different rate per thousand gallons.9
According to the AWWA’s Manual M-1 Principals of Water Rates, Fees, and Charges, increasing block
rate structures are generally considered to be conservation-oriented. Also, the manual mentions that
usage block sizes should correspond to the utility’s usage patterns. We examined the percentage of
customers that were in each tier on average throughout the year. The results are presented in Figure 3
below:
Figure 3: Average Monthly Water Usage by Tier
According to the Environmental Protection Agency, the average US family uses about 400 gallons of
water per day. Over the course of a month, this is about 12 thousand gallons. As we can see from Figure
3, the largest tier of College Station residential customers is Tier 1 (0 – 10 thousand gallons per month).
This is a little lower than the EPA’s average usage. Moreover, it appears that this tier of customers has
been growing in the past eight years (see Figure 4 on the next page).
8 Tier 1 ranges from 1-10 thousand gallons, Tier 2 ranges from 11-15 thousand gallons, Tier 3 ranges from 16-20 thousand gallons,
Tier 4 ranges from 21-25 thousand gallons, and Tier 5 includes all thousand gallons consumed at or above 26 thousand gallons. 9 Partial thousand gallons are not charged; for instance a customer can use an extra 999 gallons of water before the next thousand
gallons is charged.
0%
20%
40%
60%
80%
100%
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
Tier 1 Tier 2 Tier 3 Tier 4 Tier 5
15 Water Demand Forecasting Audit
Figure 4: Residential Customer Tiers over Time10
Though this appears to suggest that block rates and conservation are having their intended effect on
water usage, this may not be the case. As you can see from Figure 4 above, there was relatively little
change in water usage from 2008 through 2010. However, when the 2011 drought happened, the higher
water usage categories increased, especially Tier 5. This suggests that movement between water tiers
has actually been driven due to weather patterns and not conservation efforts or rate structure. Due to
the budget billing system and automatic bank draft service, we were unable to draw any conclusions
about the rate structure from this analysis. However, if this wasn’t the case, these patterns might
suggest that the Tier 1 cap is too high to effectively act as a conservation-oriented price signal in this
community.
Figure 5: Average Customer Water Usage and Average Summer Maximum Temperature
As we can see from Figure 5, there appears to be a strong correlation between average customer water
usage and the average maximum summer (June – September) temperatures. In fact, the correlation
between average customer use and average summer max temperature is above 0.9 for both customer
sets. This would imply that, instead of conservation, weather is actually the strongest driving force in
decreasing customer water usage, as we concluded from our multiple regression analysis. This supports
10 Water Tiers 2 through 4 were combined to simplify the figure and because their trend lines were nearly the identical (correlation
coefficient above 0.85).
0.00%
5.00%
10.00%
15.00%
20.00%
25.00%
60.00%
62.00%
64.00%
66.00%
68.00%
70.00%
72.00%
74.00%
76.00%
78.00%
80.00%
82.00%
2008 2009 2010 2011 2012 2013 2014 2015
T
i
e
r
s
2
-
5
Ti
e
r
1
Tier 1
Tier 2-4
Tier 5
6
8
10
12
14
16
18
90
92
94
96
98
100
102
2008 2009 2010 2011 2012 2013 2014 2015
C
u
s
t
o
m
e
r
U
s
a
g
e
(
T
h
o
u
s
a
n
d
G
a
l
l
o
n
s
)
Te
p
e
r
a
t
u
r
e
(
F)
Avg Summer Max Temp
Avg Res Customer Use
Avg All Customer Use
Water Demand Forecasting Audit 16
the need for more thorough and complex analysis when trying to understand water consumption, as
simple trend analysis can occasionally be misleading.
Inflation Must Be Considered to Understand the True Cost of Water
As we can see from Table 5, when price is included in a nominal sense in the yearly water demand
regression model, it is not significant and has a positive coefficient. However, when price is included in
the model as real 2016 dollars (INFLATE), then it is a significant variable (above the 90% level) and has a
negative coefficient (Table 5). To understand this, it is important to note that real water price has been
decreasing for the last twelve years. Essentially, this means that water consumption has taken up less
and less of customer’s purchasing power as time has gone on. This decrease in the real price of water
may be part of the reason conservation effects have been limited — it has become increasingly cheaper
to use more and more water over time. Figure 6 illustrates this further.
Figure 6: Lowest Volumetric Price of Water11
As we can see from Figure 6, the real and nominal price of water have been converging in the past ten
years. This is due to declining US inflation rates. However, this trend may not continue into the future. If
inflation rates increase without a change in water rates, effective price of water will increase. This may
or may not change revenue streams, but an increase in real water prices may increase conservation
incentives. Evidence of this can be seen when comparing Figure 4 and Figure 6. There was an increase in
inflation rates between 2008 and 2009 that can be seen in Figure 6. In Figure 4, we can see a
corresponding increase in the lower usage tiers, as well as a decrease in the highest usage tier.
Insignificant variables are still important. Though weather is the most significant factor in water
demand, this does not mean that other factors should be ignored. According to Forecasting Urban
Water Demand, price and conservation should both be significant demand influencers but, locally, they
are not. This evidences a need for change in rate setting efforts, city-wide conservation efforts, or both.
If water conservation is deemed a worthy objective by the College Station community, additional
support should be given to Water Services in order to generate real change in usage.
11 Volumetric Water rates could not be determined before 2004. Real prices are expressed in constant 2016 dollars. Dashed lines
indicate years with water rate changes.
2.00
2.10
2.20
2.30
2.40
2.50
2.60
2.70
Pr
i
c
e
(
d
o
l
l
a
r
s
)
Nominal
Real
17 Water Demand Forecasting Audit
Very-Short- and Short-term Forecasting Evaluation
Generally, very-short-term forecasting is focused on optimizing water production by forecasting water
demand for the current day. More specifically, this helps the water production operators stabilize
production and increase energy efficiency. Water Services staff have an equation that they use to
forecast daily water demand in this capacity. This equation is shown in the figure below:12
Figure 7: Water Services Daily Water Demand Forecast Formula
Daily Forecasts Are Effective and in Alignment with AWWA Approaches
Water Services provided daily forecast data dating back to 2006. Not only did we find that these
forecasts were accurate but we also found the methods utilized to be in general alignment with
forecasting principles promulgated by the AWWA.
Water Services daily water demand equation makes intuitive sense. Many of the factors the AWWA
advises to consider in forecasting water demand seem to be accounted for in this equation. For instance,
the Previous Days Flow variable accounts for long-term time variant factors like seasonality and
demographic changes. Furthermore, during most of the year, population does not change drastically
from one day to the next. On the days that it does, the equation accounts for this in the City Activity
variables. In addition, we were told that Monday, Wednesday, and Friday are high irrigation days
traditionally. Therefore, it would then make sense that these days have higher “Day of Week”
coefficients.
Most variables significantly affect daily water demand. In order to test if each of the variables included
in the equation above were significantly affecting water demand, our office developed a predictive daily
water demand model using multiple regression. The regression was developed using forward selection
and a set of model comparison criteria to determine if each variable should remain in the model (see
Appendix A, Table A-3).
12 On Tues and Thurs, the Weather Constant coefficients follow this pattern: Full Sunshine = 1.0, Partly Cloudy – 0.99, and Full Clouds
= 0.98 and the Rain Day Interval coefficients follow this pattern: day of = 0.98, 1-8 days = 1.0 and >8 days = 1.01.
Previous
Days Flow
Day of = .98
1-4 days = 1.0
4-12 days = 1.01
>12 days = 1.02
Weather
Constant
City
Activity
Day of
Week
Rain Day
Interval
Full Sunshine = 1.01
Partly Cloudy = 1.0
Full Clouds = .99
Normal = 1.0
Home
Football
Parents
Weekend
Christmas
Weekend
Thanksgiving
Weekend
Monday = 1.05
Tuesday = .98
Wednesday = 1.01
Thursday = .98
Friday = 1.01
Saturday = .97
Sunday = .98
X X X
Football= 1.01
Thanksgiving = .99
Christmas
Weekend = .99
Weekend = 1.01
X
Water Demand Forecasting Audit 18
In this way, we determined that all variables were significant influences on water demand except the
Christmas, Thanksgiving, and Parents Weekend variables. Specifically, this means that average water
demand is not significantly different during Christmas, Thanksgiving, or Parents Weekend than average
water demand during the whole year, other things being equal. However, we excluded the Thursday,
Saturday, and GAMEDAY variables from our predictive model because they were not very explanatory
(explain less than 0.1% of variation). Table 6 provides the output of our final predictive regression
model. A full explanation of variables and model development can be seen in Appendix C. Overall, our
model shows that the amount of water pumped the previous day (PUMPPREV) explains the largest
proportion of the variation (92.5%), while other factors are much less important (all combined
explaining only an additional 2%).
Table 6: Daily Water Demand Model – Replica Output Summary
Variable Partial R2 P-value Variation Inflation Factor Model R2 F-value
PUMPPREV 0.9256 <0.0001 2.25 0.9454 72649.9
MON 0.0038 <0.0001 1.15 316.1
WED 0.0042 <0.0001 1.15 370.6
FRI 0.0061 <0.0001 1.15 586.0
TMAX 0.0030 <0.0001 2.03 300.6
SUN 0.0014 <0.0001 1.15 148.9
DAYSSINCE 0.0013 <0.0001 1.22 143.5
Model R2 = 0.9324; Mean of Squared Error = 1.175; 5844 Observations (days/month x 12 months x 16 years)
Water Services’ daily forecasts are accurate. We compared the accuracy of Water Service’s daily
forecasts to our replica regression model (Table 6) as well as a simple model that forecasts current day
water demand to be the same as the previous day’s water demand. The results can be seen in Table 7:
Table 7: Very-Short-Term (Daily) Forecasting Comparison
Forecast Method Avg. Error Percentage Standard Deviation
Water Services Predictions - 0.22% 7.80%
Previous Day Predictions - 0.08% 8.51%
Daily Water Demand Model – Replica - 0.34% 8.66%
As we can see, Water Services predictions have the second lowest average error percentage and the
lowest standard deviation. This means that, assuming a normal distribution, 68% of Water Services’ daily
demand predictions are within ± 7.80% of the mean error percentage.
It is important to note, however, that the previous day’s water demand accounts for 92.56% percent of
the current day’s water demand. This is supported by the almost nonexistent inaccuracy of the Previous
Day Predictions model (average of -0.08% error). Though this simple forecast is extremely accurate, it
fails to illustrate how and to what extent other variables impact water demand. This supports our
finding that weather is the largest determinant in water demand variability, since the previous day
variable takes into account the effects of seasonal weather factors. Due to this, it is not surprising that
Water Service’s daily forecasts are so accurate.
19 Water Demand Forecasting Audit
Subjective Forecasting Methodologies Present a Risk
Water Services very-short-term forecasting methodology’s lower average error percentage and standard
deviation are most likely due to prediction alterations made by operators due to their experience. We
believe that despite the equation used by Water Services (Figure 6), much of their daily demand
knowledge is subjective. According to staff, throughout the day water production operators monitor the
weather and City radio channels for water demand influencers (rain, heat advisories, water main breaks,
fire flow tests, etc.). Once these events are identified, SCADA13 parameters are reset.
Since we were not provided with the calculations for each of Water Service’s predictions, we attempted
to identify each predictive coefficient used based on our historic data and the equation. Of the 2,922
daily observations we had (2008 through 2015), we were able to correctly identify all coefficients about
44% of the time. This suggests that, while Water Services is using their equation, they often use
professional judgement and experience when predicting daily demand.
According to AWWA literature, subjective forecasting methods can be effective in some circumstances.
More importantly, these methods have served Water Services well in predicting daily water demand.
Currently, understanding the effects of these variables is largely based on years of experience with the
City’s wells and pumps system and demand influencers. This type of experience is invaluable and largely
contributes to the accuracy of Water Services daily predictions. However, these subjective methods
present a risk to continued accuracy if the department experiences employee turnover, and thereby
loses this institutional knowledge, especially as the City’s water demand patterns diversify and change as
the City grows and becomes more urbanized.
Short-term Forecasting: There is a Lack of Connection to Water Demand Data
Generally, short-term forecasting is used for budgeting, revenue forecasting, and program evaluation.
Since demand forecasting is often the first step in setting rates, we evaluated Water Service’s water
revenue forecasting as completed by the City’s Fiscal Services Department.
According to City staff, water revenue projections are mostly based on historic billed water data, taking
into account extreme weather conditions. Actual water usage is then monitored to evaluate the
accuracy of the forecasts. We reviewed the accuracy of these revenue forecasts for the past four years,
focusing in on water customer revenues.14 The results are presented in Table 8.
Table 8: Revenue Forecasting Evaluation (percentage error)
Year Residential Commercial Total
2012 -7.09% -13.00% -9.50%
2013 -4.13% -12.71% -7.68%
2014 10.54% 1.89% 6.84%
2015 3.20% -1.39% 1.25%
Average: 0.63% -6.30% -2.27%
Stnd. Dev. 7.90% 7.69% 7.68%
13 The City’s SCADA system controls various equipment and monitors water transportation, distribution, and treatment.
14 Excluding revenues from commercial/sale of effluent, other operating, investment earnings, and other non-operation categories.
Water Demand Forecasting Audit 20
As we can see over the past four years,15 revenue forecasts have often varied from the actual revenue
received (Table 8). However, these error rates have not significantly affected Water Services as a
department. This may change if conservation efforts take a stronger effect in the future, the water
system grows in size, the City becomes more urbanized, or land uses become less homogeneous.
Additionally, over- or under-estimating revenues increases risk. For example, if revenues are over-
estimated, the department may not have enough money to meet its debt obligations. On the other
hand, if revenues are under-estimated, the department may increase its debt obligations without
necessity. These risks do not seem to have actualized in the past, but Water Services should be aware of
them — especially since information systems between departments are not currently integrated,
resulting in revenue forecasts that do not take advantage of data Water Services utilizes for their long-
term water demand forecasts.
Medium-term Forecasting Evaluation
Medium-term forecasting is used for sizing and staging treatment and distribution system
improvements, deciding when and how to invest, and to set water rates. Over the course of our review,
we found three consultant cost-of-service studies conducted in the last thirty years. One was completed
by McCord Engineering in 1987, and the other two were completed by Black & Veatch, Inc. in 2002 and
2003. Due to lack of data and the antiquity of a 1987 cost-of-service study, we will focus on the Black &
Veatch studies.
Consultant Cost-of-Service Studies do not Adequately Forecast Water Demand
According to the AWWA’s M-1 Principals of Water Rates, Fees, and Charges, a cost-of-service study
often has these typical objectives: (1) Fair and Equitable Cost Recovery, (2) Revenue Stability and
Predictability, (3) Promotion of Conservation, (4) Simplicity in Understanding and Execution, and (5)
Legality and Defendability.
The first step in this process is the Revenue Requirement Analysis, which compares aggregate costs to
utility revenues to determine adequacy of existing rates. In order to determine revenue, a water utility
must forecast water demand into the future as accurately as possible. We examined the water demand
forecasting aspect of each Black & Veatch cost-of-service study (2002 and 2003). The results of our
evaluation are presented below.
The Test period is too short. The consultant studies conducted in both 2002 and 2003 used only the
previous fiscal year as their test period. This is considered common practice when developing a cost-of-
service study, but it may not be the most accurate way to forecast water demand. Only analyzing a
single year’s consumption and customer growth fails to account for any abnormal weather or
demographic events. For example, the 2011 drought caused about a 25% growth in average daily
demand for that year. If this year alone was used as a test period, demand predictions would be
extremely high when weather conditions normalized. On the other hand, land annexation can cause
large, irregular increases in number of water customers, which could skew growth patterns.
15 A four year period was chosen due to changes in categorization of revenue in FY 2011.
21 Water Demand Forecasting Audit
Data and analysis are lacking for a sectoral forecast. Under the consultant’s methodology, the largest
factors in water demand forecasting are customer growth and per capita usage. Both Black & Veatch
reports state that City staff provided them with an estimated customer growth of approximately 3% per
year. Though this may be true, it is not a metric that lends itself to a sectoral forecast. A key advantage
to the sectoral forecast is the ability to separately identify customer growth patterns, thereby creating a
more accurate water demand forecast. Assuming a singular growth rate for all customer classifications
nullifies this benefit.
Moreover, the consultant forecasted water demand (and revenues) in both 2002 and 2003. However,
we found no evidence that the water demands forecasted in 2002 were ever compared to the actuals in
2003 to check for accuracy. Instead, water demand was completely re-forecasted. We were unable to
compare the Black & Veatch forecasts to billed actuals. However, we were able to estimate a 0.99%
error percentage between the 2003 forecast in the 2002 report and the 2003 actual from the 2003
report. This is an acceptable error rate, however, error rates should continue to be evaluated as one
moves further and further into a forecast.
Revenue classifications May Not Continue to Be Effective as Forecasting Categories
Through developing our sectoral forecast (see Appendix E), we discovered that different consumption
patterns exist within revenue customer categories. It is not our intention to suggest that an average
consumption trend should be developed for every customer. However going forward, revenue
categories may not adequately distinguish between these consumption patterns.
We developed a sectoral forecast model based on both the revenue classifications and on state tax
board property types. When the accuracy of these two models were compared through the use of a
backcast, they do not appear to have significantly different error rates. See Table 9 for further
illustration:
Table 9: Sectoral Backcast Comparison
Year
State Property Types Revenue Classifications
Metered Total Metered Total
2008 0.19% -2.32% -1.07% -3.48%
2009 0.88% -1.28% 0.00% -2.09%
2010 2.39% 0.90% 1.88% 0.43%
2011 -16.77% -17.15% -16.90% -17.27%
2012 2.56% 1.19% 2.75% 1.37%
2013 1.92% -1.70% 2.43% -1.24%
2014 8.15% 10.20% 9.02% 11.03%
2015 5.26% 5.46% 6.40% 6.53%
Mean: 0.57% -0.59% 0.56% -0.59%
SD: 7.46% 7.90% 7.78% 8.28%
The similarity in results of these backcasts can be explained by the historic homogeneity of City property
types. Specifically, single family homes make up a little over 60% of all metered water locations and
consume almost half of all metered water in the City.16 Also, single family homes consume water over
time similarly to one another even if at different levels of consumption. Simply, this means that
uniformity in water customers has inadvertently made forecasting water demand easier.
16 See Appendix E, Table E-2
Water Demand Forecasting Audit 22
There is evidence of City diversification. When we examined trends in consumption over time, we
discovered that the percentage of total water consumption driven by single family properties has been
decreasing. Meanwhile, the percentage of total water consumption driven by apartments and
commercial properties has been increasing, especially in recent years. This shift in consumption
coincides with the City’s Comprehensive Plan, amended in 2015, which anticipates higher density
development.
As the City continues to diversify, a more detailed sectoral forecast should be used to predict water
demand. The sectoral forecasting method presented in Appendix E is just one of many ways a more
comprehensive forecast can be performed. However, future sectoral forecasting would require more
extensive data collection. Specifically, these factors should be considered before deciding on a sectoral
forecasting methodology: 1) forecasting categories, 2) water consumption trends, and 3) future water
rate strategies.
Departmental cooperation is necessary for effective data use. Also, it is important to note that any
changes in forecasting method must be supported by both Water Services and Utility Billing. When
selecting forecasting categories, water rate strategies and customer categorization should both be taken
into account, otherwise, data cannot easily be used by both departments. For instance, customers can
be classified as well as possible when forecasting in the long- or medium-term. However, if demand
forecasting categories do not translate to rate strategies, this forecast cannot be easily used in
budgeting, revenue forecasting, or rate setting efforts.
Long-term Forecasting Evaluation
In general, long-term forecasting is used for sizing system capacity and raw water supply. In the City, this
type of forecasting is completed about every five years as part of a Water Master Plan update. We
reviewed the water demand forecasting aspects of the Water Master Plan in both 2010 and 2016. Each
plan was developed by a separate consultant: in 2010 HDR Engineering, Inc. and in 2016 Freese &
Nichols Engineering, Inc. Water Services also periodically forecasts peak day water demand in the long
term. The results of each of these evaluations are presented below:
Some Consultant Long-term Forecasting Methods Are Unclear
HDR and Freese & Nichols both used a variant unit-use approach to calibrate water demand forecasts.
Specifically, land uses assigned to each parcel by Planning and Development Services were used to
assign water usage factors and estimate water build out needs based on the comprehensive plan.
For both methodologies, the actual forecasting approach is fairly simple. First you project the unit of
interest and then multiply times the per unit usage. For example, in a per capita model, the unit of
interest is population. Population is then projected forward and multiplied by the per capita usage to
estimate future water demands.17 However in the variant approach, the more complex part is
developing the unit of interest. In the 2010 Master Plan, this was the gallons per acre, while in 2016, this
was the living unit equivalent (LUE).
17 See Appendix D for an example of a simple per capita forecasting model.
23 Water Demand Forecasting Audit
While gallons per acre is a self-explanatory metric, LUE is more complex to describe. According to the
2016 Water Master Plan, 2014 LUEs were assigned with the following criteria: 1) single family parcels
received 1 LUE, 2) duplex parcels received 2 LUEs, 3) commercial parcels received 6 LUEs per acre, and 4)
apartment parcels received the number of LUEs equivalent to their number of units. A population
density was then estimated based on population data received from City staff.
Unit of interest forecasting methods are unclear. We conducted a thorough review of the 2016 Freese
& Nichols report and examined the analysis conducted by these consultants documented in EXCEL
spreadsheets. We also conducted multiple interviews with City staff who worked closely with Freese &
Nichols in developing their water demand forecast. Though it appears that forecasts were developed
using professional judgment, we were unable to identify any stated criteria for LUE projections.
Moreover, it is not made apparent in the HDR Master Plan how parcel land uses were combined with
billed water demand.
Forecasts are purposefully inflated. When reviewing the 2010 Water Master Plan, we noted that the
consultant reviewed five years of water billing records. However, within the plan, the consultants stated
that they chose only one specific year to base their consumption patterns on due to it being “the driest
and highest water demand year.” This purposeful inflation can then be seen further when we review the
accuracy of the consultant’s demand forecasts.
Table 10: Forecast Accuracy Evaluation (Avg Day Demand)
Year HDR Forecast Freese & Nichols Forecast Actuals Error Percentage
2008 14.25 N/A 11.63 22.53%
2013 15.71 N/A 12.73 23.41%
2014 N/A 13.31 11.56 15.14%
We also reviewed the accuracy of the Freese & Nichols forecast, which showed signs of purposeful
inflation as well. Similarly to the 2010 Water Master Plan, the 2016 Water Master Plan states that
historical data was used as a basis for projecting water demands. Moreover, the actual usage factors
utilized were slightly exaggerated for each consumption class. This inflation is further illustrated in
Figure 9. However, while water demand forecasts used for rate setting and revenue projections should
be as accurate as possible, this may not be true when forecasting demand for system capacity and
supply.
Inflated long-term forecasts may be necessary in some circumstances. Underestimating water demand
can inconvenience continued City development and growth if the water system cannot meet regulatory
standards. For example, the Texas Commission on Environmental Quality may order a public water
supply system to stop operations if it was constructed without prior approval or there is an imminent
health hazard.18 Also, according to the Texas Local Government Code19 a municipality can issue a
moratorium on development if there is a shortage of essential public facilities, such as a water utility, or
a significant need for public facilities. Moreover, budgetary restrictions or time constraints may
necessitate an earlier estimation of needed infrastructure improvements.
18 Taken from the Texas Administrative Code, Part 1, Chapter 290 Subchapter D, Rule §290.40.
19 Title 7, Subtitle A, Chapter 212, Subchapter E
Water Demand Forecasting Audit 24
In-House Long-term Forecasting Focuses on Peak Day Demand
When planning for system capacity, the most important forecasting measure is peak day demand.
Specifically, a water utility must have the system capacity to serve all of its customers on the day each
year that water demand is the highest. The City’s Water Services’ Department conducts this kind of
forecasting in-house, which allows them to more effectively plan and adjust their capital improvements.
For example, in combination with the Water Master Plan, this in-house forecasting has led to the
scheduling of two capacity improvements for 2017 and 2022.
In-house time-series forecasting methods can be relatively accurate. Water Service’s peak day
forecasting methodology assumes that past growth in peak day water usage will be similar in the future.
After reviewing peak day demand data from 1985 – 2015, there is an average yearly increase in peak day
demand of about 0.5 million gallons of water. Figure 8 below illustrates that the linear trend of peak day
demand represents relatively good fit to the actual data.20 This indicates that a time-series methodology
can be a relatively accurate way to forecast peak day demand if a linear trend is used. However, Water
Service’s does not use a linear trend to forecast, they use a percentage growth rate, which overstates
peak day demand growth.
Figure 8: Peak Day Demand Growth (in millions of gallons)
Forecasting method may be conventionalized to aid in understanding. When demand increases a
similar amount each year, the growth rate is actually decreasing. This is illustrated further on the right in
Figure 9 on the next page. As we can see, the range between the growth rate projection and the linear
trend projection almost triples over a fifteen year forecasting horizon. This may have implications on
planned system capacity improvements.
Though growth rate projections seem to over-estimate peak day demands, there may be reasons it is
used. For instance, growth rates are commonly employed when communicating change over time. Using
this type of metric may then make it easier for decision makers and other stakeholders to evaluate
change in demand. Growth rates are also normalized, which may facilitate comparison and evaluation
between other organizations of differing size and capacity.
20 The R-squared value of 0.85 indicates that the model explains 85% the variability of the response data around its mean. For a time
series model, r-squared values between 0.7 and 0.9 are considered to be quite good.
y = 0.4836x + 9.7368
R² = 0.8504
10.0
15.0
20.0
25.0
30.0
Mi
l
l
i
o
n
s
o
f
G
a
l
l
o
n
s
25 Water Demand Forecasting Audit
Figure 9: Peak Day Forecasting Comparisons
Consultant forecasting assumptions may be inconsistent with changes in the water demand
environment. Figure 9 above shows four different peak day demand forecasts, each using a slightly
different methodology. Both consultants use a sectoral, unit-use methodology to project peak day
demands, however, our office and Water Services used two different time-series methodologies.21 As
we can see, the consultant forecasts (on the left side of the figure) are very distinctly inflated. As
mentioned in previous sections, there may be reasonable justifications for this.
However, what is more striking is the divergence of the HDR and Freese & Nichols (FNI) projection lines.
These two forecasts were made with two distinct sets of assumptions that were also separate from the
forecasts made by our office and Water Services. This is perhaps most telling about consultant risks.
Though this may have saved Water Service’s time and staff, the HDR forecast assumes that after
reaching 2016, peak day demands will decrease. Though this may have seemed reasonable at the time,
it has essentially made this forecast ineffective after that point. This may be acceptable to Water
Services, since a new forecast was developed by Freese and Nichols before this point was reached.
However, it illustrates a need for constant adjustment and accuracy evaluation.
In-house forecasting has no connection to consultant forecasts. It is important to regularly evaluate the
accuracy of forecasts so that they can be updated or adjusted if discrepancies are discovered. We found
no evidence of any changes made to either consultant model after they completed their contracts. This
may be due to resource or time limitations. Moreover, after examining an EXCEL spreadsheet provided
by City staff, we found no evidence of any comparison between consultant projections and water
demand actuals.
Reliance on Consultant Forecasts has Risks
There are several reasons that consultants should be hired. For example, an organization may
occasionally lack the resources necessary to complete a certain project. In these cases, consultants can
offer needed expertise and can take on projects that staff cannot complete due to time restraints or
expertise. Currently in the City, the Water Services Department may not have the resources to complete
21 Our office used the linear trend method (demonstrated in Figure 8). Water Services used a percentage growth rate method.
20
24
28
32
36
40
2005 2010 2015 2020 2025 2030
Mi
l
l
i
o
n
s
o
f
G
a
l
l
o
n
s
HDR - Peak Day Production Capactiy
Actual Demand FNI - Peak Day
20
24
28
32
36
40
2005 2010 2015 2020 2025 2030
Mi
l
l
i
o
n
s
o
f
G
a
l
l
o
n
s
Growth Rate Projection Production Capactiy
Linear Trend Projection Actual Demand
Water Demand Forecasting Audit 26
complicated in-house forecasting. However, it is necessary when hiring consultants to understand the
risks associated with forecasts that either under-estimate or over-estimate water demand.
For instance, consultants may lack the institutional knowledge necessary to see all sides of a project.
This may decrease the usefulness of their reports and could inadvertently harm City staff efforts. Also,
when a consultant has completed their contract they move on to their next project, potentially leaving
few people within City staff who completely understand the reports the consultants completed. This risk
is greater when: 1) a consultant’s work must be relied upon for a long period of time and 2) resource or
time constraints may prevent an organization from recalibrating or updating consultant products in a
timely manner.
27 Water Demand Forecasting Audit
General Conclusions
Using the book Forecasting Urban Water Demand, we identified four different forecasting types based
on forecast horizon length. These are used for different purposes within a water utility and should be
update and completed at different intervals. Over the course of our review, we identified all four
forecasting activities, which were being covered through some combination of Water Services, Fiscal
Services, and outside consultants.
City forecasts match their purpose. We found that all four forecasting types matched their purpose. For
instance, the daily forecasting equation is used to help minimize the starting and stopping of pumps and
wells and promote energy efficiency. Moreover, the medium- and long-term forecasts are used as part
of rate studies and capital needs projections, respectively. The short-term forecast completed by Fiscal
Services does not cover water demand, however, it is a revenue forecast.
Generally, forecasts are completed and updated in a timely manner. Very-short-term forecasts are
conducted daily, and short-term forecasts are conducted yearly, which matches the prescribed time
period for updating. On the other hand, there have only been, effectively, two medium-term forecasts
conducted in the last thirty years. This lack of rate analysis may have contributed to City risk in the past
and unintentionally diminished City conservation goals. Furthermore, long-term forecasts should be
compared to actuals on a yearly basis, and updated if drastic changes are identified in water usage or
significant forecasting mistakes are discovered. A summary of these findings can be seen in Table 11
below:
Table 11: City Water Forecasting Summary
Forecast Type Forecast Application Forecast Update Period
Long-Term Projecting Capital Improvement Needs Every 5 Years
Medium-Term Identifying Rate Structure Changes About 15 Years
Short-Term Forecasting Revenues Yearly
Very-Short-Term Optimizing System of Wells and Pumps Daily
Recommendation: More complex methods should be investigated in the future as the City grows and
diversifies. In the past, the forecasting methods utilized by the City have been sufficient. Though each
forecast has associated risks, these have not had significant impact on Water Services operations in the
past. However, as the City grows and diversifies these risks may become more apparent. As this occurs,
the City could benefit from more complex in-house water demand forecasting approaches, as it allows
for more thorough analysis and increases institutional knowledge.
Water Demand Forecasting Audit 28
Other Considerations
1. Climate changes may increase water demand variability in future years. According to the National
Wildlife Federation, the most visible effect of climate change is an intensification of weather extremes.
Simply, this means that hot days will be hotter, rain will be heavier, droughts will be more severe, etc.
When reviewing effects of weather, we noted that average maximum summer temperatures had the
most significant effect on annual water demands. As climate change and weather intensification
continues, this will cause an increase in water demands that the City should prepare for or at least
consider. Including weather variables into forecast models is most effective for medium-term and
longer-term forecasting.
2. Rate Structure may not currently incentivize all of Water Services Goals. While the current revenue
structure has effectively been covering costs (about 32% of rate revenues cover all O&M expenses), it
may not have been encouraging conservation. This is evidenced by a strong correlation between
average customer water usage and average summer maximum temperatures and may be due to the
real price of water decreasing as inflation rates decrease. It is likely that inflation rates will increase in
the future, causing the real price of water to increase. This may strengthen conservation efforts;
however, it should be noted that revenues may decrease as real price increases.
3. Some forecasts should be updated more regularly. In-house (including very-short-, short-, and long-
term) forecasts are performed and updated in a timely manner. This is most likely due to these
forecasts being completed by City staff, making them relatively cheaper to perform. However, medium-
term forecasts are not updated or completed in a timely manner. This is evidenced by the thirteen
years that have passed since the last cost-of-service study. Furthermore, we found evidence that
forecasting accuracy in the medium-term was not evaluated. This is most likely due to lack of resources
and a perception that the current rate structure is adequate for covering costs. Though this may be
true, it does not necessarily indicate that rate structures are effective in accomplishing all department
goals.
4. Dependence on consultant forecasts have some risks. Long-term consultant forecasts are developed in
a timely manner (every five years), however, forecasts should be updated when changes or mistakes
are realized. We found no evidence that long-term forecasts were regularly monitored for accuracy —
unlike very-short- and short-term forecasts where such evidence was apparent. Nonetheless, it may
not be cost-effective to bring back a consultant every time forecasting assumptions or methodology
need to be adjusted. Furthermore, consultants may use forecasting methodologies that cannot be
easily updated, adjusted, or recreated by City staff.
A-1 Appendix A
Appendix A: Water Demand Weather Predictive Model Development
We began creating our weather model by combining daily pumped water data with daily weather data
obtained from the National Oceanic and Atmospheric Administration. We had complete years of data for
2000 through 2015 for these two datasets, leaving us with 5,844 observations. We then computed several
other measures based on these weather observations. Descriptions of each daily weather variable follow:
Daily Variable Descriptions. The variable PUMP is the total amount of water pumped by Water Services in
millions of gallons (excludes water lost to cooling).
Precipitation. The PRECIP variable is the total amount of precipitation that fell each day expressed in inches.
The LAST2 variable is an indicator variable that had a value of 1 if there had been a precipitation event in
the last two days, and a value of 0 if there had not been. The DAYSSINCE variable counts the number of days
since a precipitation event occurred. The WEEKFREQ variable counts the number of days in the last week
with a precipitation event.
Temperature. The TMAX variable is the highest temperature recorded each day. The TMIN variable is the
lowest temperature recorded each day. The TAVG variable is the average of each day’s maximum and
minimum temperature.
Weather Index. The WWINDEX is a modified version of an equation taken from “Determinants of Demand for
Water Used in Texas Communities” by David R. Bell and Ronald C. Griffin of the Department of Agricultural
Economics at Texas A&M University (2005). The Bell and Griffin equation is designed to capture seasonal
changes for monthly weather models. Therefore, the equation was modified to fit a seven day time frame.
As a result, the modified equation takes into account the average temperature of the day and the
frequency of precipitation in the last week. In our monthly models (see Table A-5), we used the Bell and
Griffin equation as published. The modified equation is presented below:
Equation A-1: Weekly Weather Index
𝑊𝑊𝐻𝑀𝐷𝐷𝑊= (
𝑆𝑀𝐴𝑋+𝑆𝑀𝐼𝑀
2 ) ∗ (1 −
𝑊𝐷𝐷𝐾𝐸𝑄𝐸𝑄
7 )
Summary statistics for each variable can be seen in Table A-1. We then generated a correlation matrix
which can be seen in Table A-2 on the next page.
Table A-1: Daily Variable Summary
PUMP PRECIP LAST2 DAYSSINCE WEEKFREQ WWINDEX TMAX TMIN TAVG
Mean 11.14 0.11 0.37 4.88 1.69 53.41 79.92 59.00 69.46
SE 0.05 0.01 0.01 0.09 0.02 0.26 0.19 0.19 0.19
SD 4.17 0.39 0.48 6.78 1.46 20.17 14.87 14.87 14.45
MIN 3.28 0.00 0.00 0.00 0.00 0.00 31.00 17.00 25.50
MAX 26.24 5.28 1.00 56.00 7.00 93.50 112.00 81.00 93.50
Appendix A A-2
Table A-2: Daily Correlation Matrix
PUMP PRECIP LAST2 DAYSSINCE WEEKFREQ WWINDEX TMAX TMIN TAVG
PUMP 1.00
PRECIP -0.10 1.00
LAST2 -0.29 0.13 1.00
DAYSSINCE 0.44 -0.20 -0.45 1.00
WEEKFREQ -0.41 0.07 0.54 -0.50 1.00
WWINDEX 0.71 -0.07 -0.50 0.52 -0.84 1.00
TMAX 0.73 -0.09 -0.25 0.30 -0.27 0.71 1.00
TMIN 0.64 0.03 -0.16 0.19 -0.18 0.65 0.89 1.00
TAVG 0.70 -0.03 -0.21 0.25 -0.23 0.70 0.97 0.97 1.00
We developed three simple linear regressions using the PUMP variable as the response variable and the
TMAX, DAYSSINCE, and WEEKFREQ weather variables as the explanatory variables. We also developed a simple
linear regression using only the month as the explanatory variable. When we examined the residuals of this
regression (Figure A-1), a clear pattern can be seen, implying that a separate weather model should be
developed for each month.
Figure A-1: Monthly Residual Plot
Noting this, we developed a model on a daily time-step for each month. For each of these models, we
added the variable most strongly correlated to the response variable (due to residual plot shapes, the
PUMP variable was transformed by logarithm base 10 and used as the response variable in the models).
Each remaining variable was then correlated with the residuals of the previous regression. The next most
strongly correlated variable was then added to the model. This process continued until the regression with
each added variable did not meet the criteria in Table A-3. At this point, the previous regression model (the
one excluding the insignificant variable) was then considered to be the best-fit predictive model. The
results from these analyses are presented in Table A-4 on the next page.
Table A-3: Parameter Selection Criteria
Statistic Selection Basis
Residual Plots Seemingly Random
R^2 (whole model) Increase from previous model
Mean of Squared Error Decrease from previous model
F-Statistic (whole model) Significance F < 0.01
T-Statistic (all parameters) P-value < 0.05
Variation of Inflation (all parameters) VIF < 10
-10
-5
0
5
10
15
20
0 6 12
Re
s
i
d
u
a
l
s
Month
A-3 Appendix A
Table A-4: Daily weather models for each month (n>450 observations for each analysis)
Month Coeff. Variable Partial R2 P-value Model R2 F-Value
January 0.8289 0.0615 11.8070
0.0018 DAYSSINCE 0.0359 0.0009
-0.0009 TMIN 0.0158 0.0052
0.0011 TMAX 0.0098 0.0005
February 0.8394 0.1060 17.6999
0.0010 TMAX 0.0756 0.0000
-0.0045 WEEKFREQ 0.0215 0.0034
-0.0109 PRECIP 0.0089 0.0458
March 0.9152 0.2562 84.9161
0.0064 DAYSSINCE 0.1771 0.0000
-0.0169 WEEKFREQ 0.0791 0.0000
April 0.7435 0.4684 104.6514
0.0071 DAYSSINCE 0.3545 0.0000
0.0042 TMAX 0.0697 0.0000
-0.0016 TMIN 0.0250 0.0005
-0.0100 WEEKFREQ 0.0192 0.0005
May 0.8518 0.5847 137.9476
0.0027 WWINDEX 0.3543 0.0000
0.0049 DAYSSINCE 0.1150 0.0000
-0.0039 TMIN 0.0575 0.0000
0.0032 TMAX 0.0415 0.0000
-0.0221 LAST2 0.0164 0.0045
June -0.0179 0.7149 237.7402
-0.0256 WEEKFREQ 0.3202 0.0000
0.0032 DAYSSINCE 0.3048 0.0000
0.0124 TMAX 0.0686 0.0000
-0.0185 LAST2 0.0114 0.0199
0.0186 PRECIP 0.0099 0.0304
July -0.1560 0.6576 473.4102
0.0144 TMAX 0.3692 0.0000
-0.0350 WEEKFREQ 0.2884 0.0000
August 0.1781 0.4713 146.1675
0.0110 TMAX 0.3572 0.0000
-0.0199 WEEKFREQ 0.1030 0.0000
-0.0219 LAST2 0.0111 0.0190
September 0.4961 0.4900 152.4266
0.0029 WWINDEX 0.3411 0.0000
0.0065 TMAX 0.1381 0.0000
-0.0017 TMIN 0.0108 0.0230
October 0.7492 0.5250 135.6569
0.0030 WWINDEX 0.3842 0.0000
0.0021 DAYSSINCE 0.0722 0.0000
-0.0025 TMIN 0.0506 0.0000
0.0034 TMAX 0.0180 0.0000
November 0.8043 0.2699 58.6435
0.0021 WWINDEX 0.2416 0.0000
-0.0012 TMIN 0.0152 0.0069
0.0017 TMAX 0.0131 0.0004
December 0.7613 0.0967 26.3919
0.0018 TAVG 0.0548 0.0000
0.0041 DAYSSINCE 0.0419 0.0000
Appendix A A-4
As we can see from Table A-4, weather appears to be a more significant factor in water demand as we
move towards the summer months, peaking in June with a model R2 value of 0.7149, which means that this
model explains 71.5% of the variation in water pumped. This is also reflected in the weather models we
developed on a monthly time-step, where June had the most explanatory model with a model R2 value of
0.9482, the results of which can be seen in Table A-5. January is absent from Table A-5 because no
significant weather variables could be identified.
Table A-5: Weather Model Results – Monthly Time-Step
Model Coeff. Variable Partial R2 P-value Model R2 F-value
General -12.856 0.9031 346.88
0.0112 MWINDEX 0.7851 0.0001
0.0194 MONTHFREQ 0.0565 0.0001
0.0073 YEAR 0.0518 0.0001
0.0018 DAYSMSINCE 0.0070 0.0004
-0.0035 PRECIPM 0.0027 0.0247
February 2.0893 0.2591 4.900
0.0039 TMMAX 0.2591 0.0440
March 2.0948 0.6640 27.67
0.0067 MWINDEX 0.6640 0.0001
April 2.4007 0.4824 13.05
0.0067 DAYSMSINCE 0.4824 0.0028
May 2.2151 0.7996 25.93
0.0062 DAYSMSINCE 0.5752 0.0019
0.0041 MWINDEX 0.2244 0.0021
June 0.0100 0.9482 118.96
0.0281 TMMAX 0.9249 0.0000
-0.0071 PRECIPM 0.0233 0.0311
July -0.0672 0.6883 30.91
0.0287 TMMAX 0.6883 0.0001
August -0.1946 0.7047 15.51
0.0306 TMMAX 0.5784 0.0006
-0.0041 DAYSMSINCE 0.1263 0.0347
September -0.0104 0.6019 21.16
0.0328 TMAVG 0.6019 0.0004
October 2.1817 0.6826 30.10
0.0068 MWINDEX 0.6826 0.0001
November 2.5072 0.5066 14.37
-0.0157 PRECIPM 0.5066 0.0020
December 1.9889 0.2540 4.78
0.0072 TMAVG 0.2540 0.0465
*In these models, PUMPM variable was LOG10 transformed, except for the General model.
Each model presented in Table A-5 was developed using the same statistical methodology as the daily
model. It is important to note that each daily model per month had between 452 and 496 observations for
each month (days/month x 16 years); meanwhile, each monthly model per month had 16 observations (16
years, 2000-2015) while the General model had 192 observations (16 years x 12 months). This would
partially explain the discrepancy between the month based predictor variables and the day based predictor
A-5 Appendix A
variables, as a large number of observations can sometimes cause a model to be “over fit” to a particular
set of data. Noting this, we felt that developing the models in both ways would allow us to better identify
the most significant variables. Below is a description of each monthly variable used to develop the models
in Table A-5:
Monthly Variable Descriptions. The PUMPM variable is the total amount of water pumped by Water
Services each month in millions of gallons (excludes water lost to cooling).
Precipitation. The PRECIPM is the total amount of precipitation that fell each month expressed in inches.
The LASTM2 variable is the average of the LAST2 variable for each month; the LAST2 variable is an indicator
variable that had a value of 1 if there had been a precipitation event in the last two days, and a value of 0 if
there had not been. The DAYSMSINCE variable was calculated for each day by identifying the highest DAYSSINCE
value in the last thirty days for each day and then averaging over each month. The MONTHFREQ variable
counted the number of days a precipitation event occurred each month.
Temperature. The TMMAX variable is the maximum temperature of each day averaged for each month. The
TMMIN variable is the minimum temperature of each day averaged for each month. The TMAVG variable is the
average of the TMMAX and TMMIN variables for each month.
Weather Index. The MWINDEX is a monthly version of the WWINDEX and is taken from “Determinants of
Demand for Water Used in Texas Communities” by David R. Bell and Ronald C. Griffin of the Department of
Agricultural Economics at Texas A&M University (2005). The equation below was used to calculate this
variable:
Equation A-2: Monthly Weather Index
𝑀𝑊 𝐼𝑛𝑑𝑑𝑥 = (𝑆𝑀𝑀𝐴𝑋+𝑆𝑀𝑀𝐼𝑀
2 ) ∗ (1 −
𝑀𝑀𝑀𝑆𝐻𝐸𝑄𝐸𝑄
𝐷𝐴𝑊𝑆)
Table A-6 is a summary of the monthly variables described above and a correlation matrix of each variable
is presented in Table A-7.
Table A-6: Monthly Variable Summary
PUMPM PRECIPM LASTM2 DAYSMSINCE MONTHFREQ MWINDEX TMMAX TMMIN TMAVG
Mean 339.03 3.31 0.38 13.56 7.35 53.05 79.85 58.93 69.39
SE 8.56 0.19 0.01 0.54 0.25 1.04 0.93 0.92 0.92
SD 118.65 2.63 0.16 7.45 3.50 14.47 12.88 12.79 12.76
MIN 193.93 0.00 0.00 4.90 0.00 22.41 54.74 35.58 45.16
MAX 708.45 12.89 0.77 53.55 18.00 85.98 103.84 78.03 90.94
Table A-7: Monthly Correlation Matrix
PUMPM PRECIPM LASTM2 DAYSMSINCE MONTHFREQ MWINDEX TMMAX TMMIN TMAVG
PUMPM 1.00
PRECIPM -0.27 1.00
LASTM2 -0.57 0.52 1.00
DAYSMSINCE 0.50 -0.23 -0.51 1.00
MONTHFREQ -0.51 0.60 0.95 -0.43 1.00
MWINDEX 0.86 -0.38 -0.77 0.46 -0.76 1.00
TMMAX 0.84 -0.11 -0.41 0.35 -0.36 0.87 1.00
TMMIN 0.78 0.00 -0.29 0.25 -0.23 0.80 0.98 1.00
TMAVG 0.82 -0.05 -0.35 0.30 -0.29 0.84 0.99 0.99 1.00
B-1 Appendix B
Appendix B: Water Demand Environment Causal Model Development
We compiled daily pumped data into monthly and then yearly time-steps (Table B-1) based on data from
2004 through 2015 (this time period was chosen due to the lack of complete water rate data before 2004).
Summary statics for this data set can be seen below. A correlation matrix of the data set was also
generated and can be seen at the end of Appendix B (Table B-4 and Table B-5).
Table B-1: Yearly Weather Model (Obs = 12)
Variable Average Stnd Err Stnd Dev Min Max
PUMPY 4235.97 150.03 519.71 3268.14 5320.09
PRECIPY 39.04 3.36 11.63 19.90 58.31
DAYSYSINCE 13.77 0.92 3.18 9.15 19.10
YEARFREQ 86.33 5.42 18.78 54.00 117.00
TYMAX 80.12 0.46 1.59 77.73 83.42
TYMIN 59.33 0.26 0.91 57.77 61.17
TYAVG 69.73 0.32 1.12 67.75 71.72
TSUMMERMAX 94.31 0.70 2.41 90.98 99.71
YWINDEX 53.68 1.24 4.28 47.30 62.15
POP 92963.50 2397.62 8305.58 80214.00 106465.00
POPΔ 2.60 0.29 1.02 1.34 4.50
STUDENT 31889.85 669.71 2319.94 29302.91 36945.79
STUDENTΔ 2.78 0.89 3.07 -0.68 10.40
UNEMP 4.64 0.26 0.89 3.30 6.30
CONS 0.42 0.15 0.51 0.00 1.00
BARREL 11.08 5.25 18.20 0.00 63.00
TOILET 71.00 41.24 142.85 0.00 485.00
SAVINGS 445.49 283.00 980.36 0.00 3493.35
PRICE 2.20 0.02 0.08 2.03 2.26
INFLATE 2.44 0.03 0.10 2.29 2.59
The variables were then divided into two sets. One only included weather variables (Weather Set) and the
other included demographic, economic, and conservation variables (Environment Set). We began with the
weather set by graphing each explanatory variable with the response variable. Most notable was the
polynomial shape to the TSUMMERMAX. Due to this, a TSUMMERMAX2 term was computed and graphed.
Each pair of weather variables were then graphed. For the PRECIPY variable, there were obvious trends in
each graph (scatter plot) except for the TYMAX and TYMIN variables. However, not all of these trends were
highly linear. Correlation coefficients indicate a strong relationship between the PRECIPY variable and the
YEARFREQ and the YWINDEX variables. Scatter plots reinforce these coefficients and indicate that a model
should not include all three of the variables. This is expected, as the total precipitation should be affected
by the number of times it rains, and the monthly weather index includes the number of times it rains (as
frequency) into the computation.
The DAYSYSINCE variable appears to be linear and most strongly correlated with the TSUMMERMAX variable.
However, notably, there appears to be a cubic shape to the scatter plot of the DAYSYSINCE and YEARFREQ
variables as well as the YWINDEX variable. These plots are shown on the next page:
Appendix B B-2
Figure B-1: DAYSYSINCE Scatter Plot
As we can see, there are very strong third degree polynomial relationships between these variables (Figure
B-1). Although none of these are our variable of interest, it is still important to note relationships like these
when selecting variables for inclusion in the regression model, especially since it is not a predictive model.
When graphed with other variables, the YEARFREQ variable is not visually correlated with any temperature
variables. However, the YWINDEX scatter plot is almost a straight line (Figure B-2), which is indicative of how
the YWINDEX is calculated. It is also evidence of the impact of type of weather event on the YWINDEX. As we
can see from Figure B-2, the frequency of precipitation events seems to have a much stronger impact (i.e.,
tighter fit to the linear line) in the value of the YWINDEX than the average temperature.
Figure B-2: YWINDEX Variable Make Up
When the TYMAX variable is graphed with other weather variables, they support the strong correlations
between TYMAX and TSUMMERMAX, TYAVG, and YWINDEX identified in the correlation matrix. Interestingly, a fourth
degree polynomial trend line fits each of these scatter plots best. Though this may be a slight over fit to the
data, it could also be indicative of the effect average maximum daily temperature has on the temperature
during the rest of the day. For example, the duration of high temperatures most likely matters to water
demand. The TYAVG and TYMAX graph may be indicative of some sort of duration term, since the TYAVG variable
0
10
20
30
40
50
60
70
0
20
40
60
80
100
120
140
0 5 10 15 20 25
M
o
n
t
h
l
y
W
e
a
t
h
e
r
I
n
d
e
x
Fr
e
q
u
e
n
c
y
o
f
P
r
e
c
i
p
i
t
a
t
i
o
n
(
D
a
y
s
)
Average Maximum Number of Days Since Precipitation
YEAR FREQ
MW INDEX
Poly. (YEAR FREQ)
Poly. (MW INDEX)
67.50
68.00
68.50
69.00
69.50
70.00
70.50
71.00
71.50
72.00
50
60
70
80
90
100
110
120
40 45 50 55 60 65
A
v
g
T
e
m
p
e
r
a
t
u
r
e
(
F
)
YE
A
R
FR
E
Q
(D
a
y
s
)
MWINDEX
YEAR FREQ
T YAVG
Linear (YEAR FREQ)
Linear (T YAVG)
B-3 Appendix B
is not truly the average daily temperature, but it is instead an interaction term between the maximum daily
temperature and minimum daily temperature variables. This is an important factor to note as we move
forward with variable selection. This interaction is easier to see in the figure below (Figure B-3), which
shows the almost inverse relationship between the maximum and minimum temperatures even as they
both trend upwards.
Figure B-3: Temperature Interaction Term (TYAVG) Make Up
Finally, there is also a fairly strong correlation between the YWINDEX variable and the TSUMMERMAX variable
when the scatter plot is graphed. This relationship is expected due to temperatures being a factor included
in the YWINDEX. Also, both variables are strongly correlated with the PUMPY variable, so this may be inflating
the correlation between these two.
The Environment Set of variables include demographic, economic, and conservation variables. We plotted
each variable with the response variable.
The demographic variables, POP and STUDENT have a positive trend, this is what we would intuitively
expect from population growth variables. For economic variables, there is a positive trend for all except the
INFLATE variable. This is most likely due to declining inflation rates since 2004 with few increases in
nominal price. Intuitively, we would not expect the UNEMP and PRICE variables to have positive trends,
however, this is most likely due to the rapid growth rates in College Station during this time period. Also, all
conservation variables appear to have a positive trend, which may be due to population increases over
time as well.
Next we look at the scatter plots with the POP variable. As we would expect, there is a strong positive,
linear correlation between the POP and STUDENT variables. Other than that, the scatter plots with other
variables tend to look like a time-series plot, indicating that population has a strong time factor driving it.
This may disrupt, or cause false collinearity with other variables with strong time factors. The STUDENT
variable is similar though less extreme. This may indicate a need to include a different type of variable, such
as the growth rates of both the overall population and the student population when developing predictive
model. By using growth rates, there will be no undue correlation between population growth and other
variables with large time factors.
57.50
58.00
58.50
59.00
59.50
60.00
60.50
61.00
61.50
76
77
78
79
80
81
82
83
84
67 68 69 70 71 72
T
Y
M
I
N
(
F
)
T YM
A
X
(F)
TYAVG (F)
T YMAX
T YMIN
Poly. (T YMAX)
Poly. (T YMIN)
Appendix B B-4
Most other graphs result in no visually striking correlation. There is a positive correlation between the
TOILET and SAVINGS variables, this is most likely due to the number of toilets (and rain barrels) having a
direct influence on the number of additional estimated saved gallons. This may indicate that the TOILET and
BARREL variables should not be included in a model with the SAVINGS variable. Also, the CONS and INFLATE
variables are correlated, but this is most likely due to time factors.
As one might image, there are relationships within the conservation variable set. It is important to note
that the CONS variable is an indicator variable (which acts like a switch), signaling which years the
conservation programs were in effect. It may not be necessary to include this variable if the other
conservation variables are included in our model. Also as mentioned previously the BARREL and TOILET
variable directly influence the SAVINGS variable.
Finally, when graphed as a scatter plot, there is no real visual relationship between the PRICE and INFLATE
variables. However, it is interesting to note how they have changed over time. As we can see, real price
(INFLATE) has been decreasing over the past twelve years, even as rates have increased. Though nominal
price (PRICE) is currently as close as ever to real price, this is due to low inflation.
Figure B-4: Lowest Volumetric Price of Water (dashed lines indicate year rates changed)
Now that we have a better understanding of how each variable interacts with the others within each data
set, we began selecting variables. To do this, we examined each set of variables (weather, demographic,
economic, and conservation) and determined which variables have the strongest f-stat performance and p-
value based on a simple linear regression. From each set of variables the following were chosen: TSUMMERMAX,
POP, CONS, and PRICE. However, we also ran a regression replacing the PRICE variable with the INFLATE
variable. The CONS variable was chosen over the SAVINGS variable due to SAVINGS being an inadequate
measure. The results of the regressions are shown in Table B-2 on the next page.
2.00
2.10
2.20
2.30
2.40
2.50
2.60
2.70
2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Pr
i
c
e
PRICE
INFLATE
B-5 Appendix B
Table B-2: Yearly Water Demand Regression Results
Potential Models (1 - 5)
Variable 1 2 3 4 5
TSUMMERMAX 189.73*** 161.50*** 158.38*** 155.50*** 162.98***
POP 0.0269*** 0.0202**0 0.0130000 0.0107000
CONS 137.67000 165.78000 22.23000
PRICE 701.42000 00
INFLATE -1434.06*00
Constant -13656.97000 -13492.12000 -12636.04000 -13247.17000 -8642.72000
Model R2 0.7760000 0.9431000 0.9493000 0.9520000 0.9681000
Model F-Stat 34.64000 74.63000 49.89000 34.68000 53.03000
Model Sig 0.0002000 <0.0000000 <0.0000000 0.0001000 <0.0000000
Note: * indicates significance above the 90% level, ** indicates significance above the 95% level, and *** indicates
significance above the 99% level.
If the coefficients presented above were to be used for predictive purposes, then only Model 2 should be considered
because it provides the best-fit model explaining 94.3 % (R2 = 0.9431, p < 0.0001) of the variation in water pumped per
year (PUMPY) with two significant variables TSUMMERMAX and POP, where PUMPY = -13492.12 + 161.50 (TSUMMERMAX) +
0.0269 (POP). In the other models, although the R2 may be slightly higher, not all variables were significant, and
therefore should not be used as a predictive model.
Variable Descriptions. The PUMPY variable is the total amount of water pumped by Water Services each
year expressed in millions of gallons (excludes water lost to cooling).
Precipitation. The PRECIPY variable is the total amount of precipitation that fell during each year expressed
in inches. The DAYSYSINCE variable is maximum number of days without rain in the last thirty days averaged
over each year. The YEARFREQ variable is the total number of days each year with a precipitation event
above 0.01 inches.
Temperature. The TYMAX variable is the daily maximum temperature averaged over each year. The TYMIN
variable is the daily minimum temperature averaged over each year. The TYAVG variable is the daily average
of the minimum and maximum temperature averaged over each year. The TSUMMERMAX variable is the daily
maximum temperatures in June through September averaged for each year.
Weather Index. The YWINDEX was an equation take from “Determinants of demand for Water Used in Texas
Communities” by David R. Bell and Ronald C. Griffin of the Department of Agricultural Economics at Texas
A&M University (2005). Equation B-1 is the equation presented in their paper, where CI is calculated for
each month and then averaged over each year to equal the YWINDEX.
Equation B-1: YWINDEX Equation
Demographics. The POP variable is the estimated College Station population for each year based on
certificates of occupancy. The POPΔ variable is the growth rate in estimated College Station population
expressed in percentage. The STUDENT variable is the weighted average of Texas A&M enrollment over
each calendar year. The STUDENTΔ variable is the growth rate in the weighted average of Texas A&M
enrollment expressed in percentage.
Appendix B B-6
Conservation. The CONS variable is an indicator variable, expressed as a 1 in years with an active water
rebate program (2010 – 2015) and a 0 in years without an active water rebate program (2004 – 2009). The
BARREL variable is the number of additional rain barrels rebated over the course of a year. The TOILET
variable is the number of additional high efficiency toilets rebated over the course of a year. The SAVINGS
variable is the estimated amount of additional water saved due to additional rebated rain barrels and
toilets expressed in millions of gallons. Assumptions for estimated savings calculations are summarized
below:
Table B-3: Estimated Rebate Savings Assumptions
High Efficiency Toilets
Replaced Toilet Age Uses per Day Saved Flush Volume
1950 - 1980 5.1 3.72
1980 - 1994 5.1 2.22
Rain Barrels
Gallons of Rain per Year 41,040
Rainfall collected per Barrel 5.00%
Annual Savings per Barrel 2,052
Economy. The UNEMP variable is the average unemployment rate taken from the US Bureau of Labor
Statistics for College Station. The PRICE variable is the nominal price of water at the lowest volumetric
residential water rate. When a rate change occurred (usually starting in October), a weighted average of
water price was calculated for that year. The INFLATE variable is the PRICE variable, expressed in real
(inflation adjusted) 2016 dollars.
Table B-4: Environment Set Correlations
PUMPY POP POPΔ STUDENT STUDENTΔ CONS BARREL TOILET SAVINGS UNEMP PRICE INFLATE
POP 0.66 1.00
POPΔ -0.21 0.02 1.00
STUDENT 0.50 0.95 0.15 1.00
STUDENTΔ -0.43 0.01 0.29 0.22 1.00
CONS 0.65 0.81 -0.21 0.78 0.06 1.00
BARREL 0.35 0.48 -0.51 0.34 -0.19 0.66 1.00
TOILET 0.76 0.31 -0.36 0.18 -0.21 0.52 0.15 1.00
SAVINGS 0.74 0.30 -0.29 0.20 -0.18 0.53 0.17 0.94 1.00
UNEMP 0.44 0.04 -0.71 -0.17 -0.37 0.00 0.27 0.54 0.45 1.00
PRICE 0.65 0.87 0.04 0.71 -0.24 0.63 0.46 0.37 0.35 0.17 1.00
INFLATE -0.62 -0.84 0.07 -0.84 0.03 -0.81 -0.50 -0.23 -0.22 0.10 -0.65 1.00
Table B-5: Weather Set Correlations
PUMPY PRECIPY DAYSYSINCE YEARFREQ TYMAX TYMIN TYAVG TSUMMERMAX MWINDEX
PUMPY 1.00
PRECIPY -0.52 1.00
DAYSYSINCE 0.57 -0.66 1.00
YEARFREQ -0.73 0.84 -0.82 1.00
TYMAX 0.66 -0.52 0.69 -0.72 1.00
TYMIN 0.05 0.22 0.07 0.12 0.57 1.00
TYAVG 0.49 -0.28 0.52 -0.47 0.94 0.81 1.00
TSUMMERMAX 0.88 -0.66 0.75 -0.79 0.81 0.18 0.65 1.00
MWINDEX 0.76 -0.80 0.84 -0.98 0.85 0.08 0.64 0.86 1.00
Highlighted cells indicate evidence of multicollinearity; across set correlations were calculated but not shown because no evidence of
multicollinearity was found.
C-1 Appendix C
Appendix C: Very-Short-Term Replica Model Development
After discussing very-short-term (day-to-day) water demand forecasting with Water Services staff, we
received three equations (combined into Figure 4 in the report on page 17) used to forecast daily water
demand. We also received a spreadsheet containing their final daily demand predictions, which we used to
compare their accuracy to other predictive models. It is important to note that we did not receive how
these final predications were calculated. These equations use the following variables to forecast the next
day’s water demands: (1) Previous Days Flow, (2) Weather Constant (cloud cover), (3) City Activity (normal,
Home Football, Parent’s Weekend, Christmas Weekend, Thanksgiving Weekend), (4) Day of Week, and (5)
Rain Day Interval.
Each of these variables is included in the three simple equations that Water Services uses. The equations
are broken out by day (one for Tuesdays, one for Thursdays, and one for the other days). According to City
staff, Tuesday and Thursday have their own equation because they are traditionally low irrigation days.
Variable Definition. We then attempted to recreate, refine, and combine Water Service’s three equations
using regression modeling. Using daily pumped water and weather data from 2000 through 2015, we
recreated some of the variables utilized in the daily water demand equations utilized by Water Services.
The Previous Days Flow variable was recreated using the previous day’s pumped water. The Weather
Constant could not easily be recreated since we could not obtain cloud cover data for any extended period
of time. However, according to the International Satellite Cloud Climatology Project, the main effects that
cloud cover has on climate is the temperature of the earth. Due to this, we believed it best to use a
temperature variable (TMAX, TMIN, or TAVG) in place of this variable. The TMAX variable was ultimately chosen,
because it had the strongest correlation with the amount of water pumped each day.
The next variable is City Activity. In Water Services’ equations there are four events that they have specified
as significantly changing water demand: 1) Home Football, 2) Parents Weekend, 3) Christmas Weekend,
and 4) Thanksgiving Weekend. In our regression model, these variables were represented by binary
indicator variables. Essentially, they act as switches. When the variable has a value of 1, it’s “on” and
contributes to the model, but when it has a value of 0, it’s “off” and contributes nothing to the model.
These variables are explained further below.
The next variable is then Day of Week. We examined the effects of this variable in two different ways: (1) as
a discrete variable with values between 1 and 7 and (2) by including Day of Week Fixed Effects (FEDOW) in
the model. As a discrete variable, each number is equal to a different day. For example, 1 would indicate
the observation occurred on a Sunday, 2 would indicate the observation occurred on a Monday and so on.
On the other hand, Day of Week Fixed effects act similarly to the “Event” variables and estimates the
average effects of, for example, a Monday on water demand.
When we added the discrete day of week variable (DOW), we examined the residual plot. As we can see,
the plot had a slight pattern to it (see Figure C-1). We also calculated if there was a significant difference
between the mean water pumped on each individual day versus the mean of all days. This analysis can be
seen in Table C-1.
Appendix C C-2
Based on the results that are described in Figure C-1 as well as the analysis summarized in Table C-1, were
led to include the day of week fixed effects (FEDOW) in our final model versus the discrete DOW variable.
Figure C-1: Day of Week (DOW) Residual Plot
Table C-1: Day of Week Difference between Means
SUN MON TUE WED THR FRI SAT ALL
AVERAGEPUMP 10.98 11.62 10.71 11.27 10.90 11.45 11.03 11.14
AVERAGEPREV PUMP 11.03 10.98 11.62 10.71 11.27 10.91 11.44 11.14
Proportion 1.00 1.06 0.92 1.05 0.97 1.05 0.96 1.00
Stnd Dev 4.10 4.42 3.84 4.26 4.01 4.31 4.26 4.17
Obs 835 835 835 835 835 834 835 5844
t-value 1.04 -3.09 2.81 -0.84 1.56 -2.00 0.71
Significant NO YES YES NO NO YES NO
This set of day-of-week (FEDOW) variables allows us to account for the differences in activity level in the City
as it affects water demand between, for example, a Monday and a Wednesday. This otherwise would be
unaccounted for, since the previous pumped water variable (PREV PUMP) can only account for one day
prior to current day use. In other words, Day of Week Fixed Effects accounts for the standard activity level
of the City on any given day of the week; for example, more water is pumped on Mondays and Fridays, on
average, than other days of the week (Table C-2).
Table C-2: Variable Summary
Variable Mean SE Min Max
PUMP 11.14 0.05 3.28 26.24
PREV PUMP 11.14 0.05 3.28 26.24
TMAX 79.92 0.19 31.00 112.00
CHRIST 0.01 0.00 0.00 1.00
THANKS 0.01 0.00 0.00 1.00
GAMEDAY 0.02 0.00 0.00 1.00
RINGDAY 0.01 0.00 0.00 1.00
DOW 4.00 0.03 1.00 7.00
DAYSSINCE 4.88 0.09 0.00 56.00
-8
-6
-4
-2
0
2
4
6
0 1 2 3 4 5 6 7 8
Re
s
i
d
u
a
l
s
DOW
C-3 Appendix C
Finally, the last variable was Rain Day Interval. In our replica model, we included this as a DAYSSINCE variable.
This variable is a running discrete variable that counts the number of days since the last precipitation event
(greater than 0.01 inches). When developing the model, we used forward selection, meaning we added one
variable at a time, checking for individual and model significance (p-values and F-statistic), as well as
individual variable contribution to the model (partial R2).
Replica Model Development. To begin the replica model development, we first added the PREV PUMP
variable into our regression variable. As expected (due to high correlation between water pumped in the
current day (PUMP) and water pumped in the previous day (PREV PUMP)), this simple regression model has
a very high R2 (0.93), a significant F-statistic, and a high t-statistic (both significances are above the 99%
level). Also, when we plotted the residuals with the predicted values, they appeared to be randomly
scattered, indicating no need to transform the response variable (PUMP) in this model.
We then added several of the Day of Week Fixed Effects variables (MON, WED, and FRI). Together, these
three variables explain about 1.41% of variation in pumped water. Next, the TMAX (maximum temperature)
variable was added due to a lack of cloud cover data. The partial R2 of this variable was 0.0030, indicating
that only 0.3% of the variation can be explained by changes in the maximum daily temperature.
We then added another Day of Week Fixed Effect variable (SUN). This variable explained only about 0.14%
of variation in pumped water. Next, the DAYSSINCE variable was added. When this variable was added, the R2
value increased slightly. Also, the partial R2 value of this variable was 0.0013. This indicates that the
DAYSSINCE variable explains 0.13% of the variation in water demand (PUMP variable) when the PREV PUMP
variable was already included. When viewing the residual plot of this model, there was no indication of the
need to transform the response variable. Also, all t-statistics and the F-statistic are significant above the
99% level. We continued to add the other variables in sequence including only those that were significant
and followed our criteria for model inclusion (Table A-3). When this was done, we found that all variables
were significant except the variables indicating Parents Weekend, Christmas, and Thanksgiving. This can be
seen in Table C-3 below:
Table C-3: Daily Water Demand Model – Replica Output Summary
Variable Coefficients Partial R2 P-value Variation
Inflation Factor
Model R2 F-value
Intercept -1.52432 0.9468
PUMPPREV 0.89974 0.9256 <0.0001 2.28
72649.9
MON 1.47476 0.0038 <0.0001 1.72 316.1
WED 1.37265 0.0042 <0.0001 1.73 370.6
FRI 1.34778 0.0061 <0.0001 1.77 586.0
TMAX 0.02086 0.0030 <0.0001 2.03 300.6
SUN 0.77143 0.0014 <0.0001 1.76 148.9
DAYSSINCE 0.02446 0.0013 <0.0001 1.22 143.5
THR 0.49706 0.0004 <0.0001 1.72 40.6
SAT 0.45131 0.0009 <0.0001 1.76 100.9
GAMEDAY 0.17770 0.0001 0.0002 1.08 9.6
Model R2 = 0.9468; Mean of Squared Error = 0.9256; 5844 Observations (days/month x 12 months x 16 years)
Appendix C C-4
As we can see from Table C-3 on the last page, all of these variables are significant above the 99% level
(except GAMEDAY). However, many of the variables explain very little of the variation in pumped water.
This is mostly because they are only intended to signify certain events (such as home football games) that
over the course of a year are not very impactful.
It is good that Water Services has recognized that on these days, water consumption is significantly
different than on regular days. However, it may not be necessary to include these less explanatory variables
in a predictive model. Due to this, we have excluded any variables that explain less than 0.1% of variability
in pumped water. Table C-4 below is the final predictive model used to evaluate accuracy in Table 7 of the
report:
Table C-4: Daily Water Demand Model – Replica Output Summary
Variable Coefficients Partial R2 P-value Variation
Inflation Factor
Model R2 F-value
Intercept -1.21229 0.9454
PUMPPREV 0.89909 0.9256 <0.0001 2.25
72649.9
MON 1.15107 0.0038 <0.0001 1.15 316.1
WED 1.04898 0.0042 <0.0001 1.15 370.6
FRI 1.04554 0.0061 <0.0001 1.15 486.0
TMAX 0.02108 0.0030 <0.0001 2.03 300.6
SUN 0.46811 0.0014 <0.0001 1.15 148.9
DAYSSINCE 0.02486 0.0013 <0.0001 1.22 143.5
Model R2 = 0.9454; Mean of Squared Error = 0.9492; 5844 Observations (days/month x 12 months x 16 years)
D-1 Appendix D
Appendix D: Developed Forecasting Methodologies – Per Capita
This forecasting method is based solely on historic pumped water data and population projections. It is the
easiest method to use and requires the least amount of time and effort. Like the other methods that will be
discussed here, it forecasts daily average water demand. Below is an example simple forecast:
Table D-1: Simple Water Demand Forecast*
2015 population ........................................................................... 106,465
2015 average water production, mgd .......................................... 12.2504
2015 per capita water use, gpcd ................................................ 115.0651
2015 year peak-to-average day ratio ............................................. 2.1088
Year Population Forecast Water Demand Forecasts
Average Day (mgd) Peak Day (mgd)
2020 113,665 13.08 27.58
2025 124,219 14.29 30.14
2030 134,772 15.51 32.70
*Where mgd stands for millions of gallons per day and gpcd stands for gallons per capita per day
In the above example, 2015 daily pumped water data (mgd, millions of gallons per day) was averaged to
produce the “2015 average water production, mgd.” The December 2015 population estimate purported
by the City of College Station’s Planning and Development Services Department was used as the “2015
population.” The average water production was then divided by the 2015 population to obtain the “2015
per capita water use, gpcd.” The “2015 year peak-to-average day ratio” was calculated by dividing the
highest water production day in 2015 (25.8336 mgd) by the average daily water production in 2015.
The Population Forecast numbers were taken from the City of College Station Comprehensive Plan as
adopted in May 2009 (page 1-13). These population forecasts were then multiplied by the calculated per
capita water use to obtain the Average Day (mgd) Water Demand Forecast. This was then multiplied by the
year peak-to-average day ratio to obtain the Peak Day Water Demand Forecast.
General Conclusions:
The simple forecasting method is the easiest to use. This forecasting method requires only two sources of
data, which are readily available to Water Services. This method also takes into account daily water loss
and forecasts peak day demands, which is important when planning for infrastructure improvements.
Simple forecasts have limited uses. Though the simple forecast is useful for long term infrastructure
planning, it is not useful as a revenue projection tool. The simple forecast only provides a general daily
usage for the entire customer population, which cannot be easily translated into a revenue stream. Also, it
relies on population projections, which are prone to inaccuracy. Due to the presence of Texas A&M
University, College Station’s population is more transient and variable than many other cities. This
variability may require a more detailed approach to forecasting.
E-1 Appendix E
Appendix E: Developed Forecasting Methodologies - Sectoral
This forecasting methodology is a disaggregated, variant unit-use water demand forecast. We had
complete billed water data from 2008 through 2015 for individual water location IDs. We then connected
each location ID to its 2016 property type, assigned using the Brazos Central Appraisal Districts database. A
full list of property type codes can be seen in Table E-4 at the end of this appendix.
Once we had identified the property type for each metered location, we created an average consumption
pattern and an aggregate consumption pattern for each property type group. We then correlated these
patterns together to generate an average correlation for each pair of property types.
For example, the Commercial (F1) and Industrial (F2) properties’ consumption patterns for one year and
resulting correlations are shown in Table E-1. Only one year of each consumption pattern is shown in this
table, however, each pattern was generated for the period between 01/2008 and 11/2015.
Table E-1: Example Correlation Calculations
Month
Commercial (F1) Industrial (F2)
Average
Pattern
Aggregate
Pattern
Average
Pattern
Aggregate
Pattern
01/2008 28.45 42607.65 37.36 410.96
02/2008 27.56 41410.69 35.95 415.66
03/2008 29.35 45308.90 47.96 541.46
04/2008 36.83 58574.00 80.43 884.77
05/2008 46.09 72599.11 107.46 1182.05
06/2008 61.57 94987.35 98.15 1079.68
07/2008 64.33 100143.32 138.20 1580.72
08/2008 52.27 83620.41 93.24 1118.92
09/2008 45.34 73807.43 126.80 1462.39
10/2008 37.25 59938.63 109.68 1156.53
11/2008 36.86 54435.22 90.67 885.02
12/2008 34.02 53605.65 99.27 893.47
Average Consumption Pattern Correlation 0.88
Aggregate Consumption Pattern Correlation 0.90
Average Correlation 0.89
Based on these correlations, the water use categories were identified as shown in Table E-2. A brief
description of our recommended sectoral forecasting methodology follows; the full calculations can be
seen at the end of this appendix.
Appendix E E-2
Table E-2: Water Use Categories
Property Type Category 2015 Metered Location 2013 2014 2015 Avg.
No Common Space (NCS)
Single Family 15,099 60.91% 61.12% 61.05% 61.03%
Patio Home 449 1.68% 1.73% 1.82% 1.74%
Manufactured Home 10 0.04% 0.04% 0.44% 0.04%
NCS Totals: 15,558 62.63 62.89% 62.91% 62.81%
Commercial & Industrial (C/I)
Commercial 1,376 5.57% 5.59% 5.56% 5.57%
Industrial 9 0.04% 0.04% 0.04% 0.04%
C/I Totals: 1,385 5.61% 5.63% 5.60% 5.61%
Shared Common Space – Not Resident Maintained (CSN)
Apartments 2,379 9.73% 9.69% 9.63% 9.68%
Condominium 434 1.68% 1.67% 1.75% 1.70%
CSN Totals: 2,813 11.41% 11.36% 11.38% 11.38%
Shared Common Space – Resident Maintained (CSM)
Duplex 2,496 10.44% 10.25% 10.09% 10.26%
Townhome 787 2.92% 2.98% 3.18% 3.03%
Homeplex 426 1.73% 1.72% 1.72% 1.72%
Triplex 42 0.18% 0.17% 0.17% 0.17%
Fraternity/Sorority 4 0.02% 0.02% 0.02% 0.02%
CSM Totals: 3,755 15.29% 15.14% 15.18% 15.2%
All Other Categories 1,223 5.09% 5.00% 4.94% 5.01%
To begin water usage rates were calculated for each water use category by dividing the annual water
consumption by the number of locations for each category. These were then averaged over an eight year
period and forecasted forward. Below is a graph of categorical location growth from 2008 through 2015.
Figure E-1: Categorical Location Growth
The metered location growth of the NCS, C/I, CSN, and CSM categories are all fairly linear, so linear trend
lines were generated to forecast this growth (Figure E-1). A second degree polynomial trend line was used
for the “Other” category, since it provided the best fit to the data, but did not predict unreasonable growth.
12,000
12,500
13,000
13,500
14,000
14,500
15,000
15,500
16,000
1,000
1,500
2,000
2,500
3,000
3,500
4,000
2008 2009 2010 2011 2012 2013 2014 2015
N
C
S
P
u
m
p
e
d
W
a
t
e
r
(
M
G
)
No
t
N
C
S
Pu
m
p
e
d
W
a
t
e
r
(
M
G
)
C/I
CSM
CSN
Other
NCS
E-3 Appendix E
The forecasted metered location amounts were then multiplied by the calculated average water usage
rates to obtain the average daily water demand for each category. These are then added together to equal
the “metered usage.”
Water loss was forecasted using Water Services’ 2014 Water Conservation Plan. In this plan, Water Services
states their water loss goal to be 8 gallons per capita per day. Noting this, the water loss is then calculated
by multiplying the forecasted population (taken from the City’s 2009 Comprehensive Plan) amount by 8
and dividing by one million. The average daily metered usage plus the average daily water loss then equals
the total system demand.
Table E-3 Sectoral Water Demand Forecast
Year Population
Forecast
Water Demand Forecasts (mgd)
NCS C/I CSN CSM Other Metered
Usage
Water
Loss
Total System
Demand
2020 113,665 6.05 2.78 0.70 2.82 0.83 13.17 0.91 14.08
2025 124,219 6.57 2.97 0.74 3.07 0.87 14.23 0.99 15.22
2030 134,772 7.08 3.16 0.78 3.33 0.94 15.29 1.08 16.37
Table E-4: State Property Types – Full
State Code
(BCAD)
Description
State Code
(BCAD)
Description
A1 Residential-Single Family D1 OS-Land Qualified For Open Space
A2 Residential-Manufactured Home D2 OS-Farm & Ranch Improvements
A3 Residential-Duplex E1 Rural-Single Family
A4 Residential-Triplex E4 Rural-Land
A5 Residential-Fourplex EA2 Rural-Manufactured Home
A6 Residential-Condominium EB1 Rural-Apartments (5+)
A7 Residential-Townhome EB2 Rural-Duplex
A8 Residential-Patio Home EB3 Rural-Triplex
A9 Residential-Homeplex EB4 Rural-Fourplex
B1 Rental-Apartments F1 Commercial
B2 Rental-Duplex F2 Industrial
B3 Rental-Triplex F3 Commercial-Improvement Only
B4 Rental-Fourplex F4 Industrial-Improvement Only
B10 Rental-Fraternity/Sorority House M1 Personal Property Manufactured Home
C1 Vacant-Residential Lot
C2 Vacant-Commercial Lot
C3 Vacant-Rural or Recreational Lot
Appendix E E-4
Table E-5: Property Type Correlations – Expanded
A1 A2 A3 A5 A6 A7 A8 A9 B1 B2 B3 B4 B10 F1 F2
A1 1.00
A2 0.94 1.00 Color Key A3 0.58 0.53 1.00 Color Correlation A5 0.08 0.07 0.12 1.00 Very Strong 0.90-0.99 A6 0.76 0.69 0.53 0.11 1.00 Strong 0.80-0.89
A7 0.86 0.82 0.52 0.06 0.75 1.00 Semi Moderate 0.70-0.79
A8 0.95 0.92 0.53 0.07 0.78 0.84 1.00 Moderate 0.60-0.69
A9 0.85 0.77 0.52 0.15 0.76 0.77 0.86 1.00
B1 0.53 0.47 0.23 0.04 0.78 0.54 0.57 0.57 1.00
B2 0.75 0.68 0.54 0.14 0.67 0.66 0.73 0.80 0.49 1.00
B3 0.77 0.74 0.52 0.06 0.68 0.69 0.81 0.70 0.47 0.77 1.00 B4 0.33 0.29 0.39 0.01 0.43 0.40 0.32 0.35 0.26 0.48 0.49 1.00 B10 0.77 0.71 0.54 0.10 0.76 0.66 0.82 0.77 0.55 0.80 0.74 0.41 1.00
F1 0.93 0.91 0.57 0.07 0.81 0.87 0.96 0.82 0.59 0.68 0.75 0.31 0.78 1.00
F2 0.89 0.86 0.52 0.07 0.74 0.80 0.87 0.76 0.56 0.58 0.68 0.26 0.69 0.89 1.00
Table E-6: Historic Annual Water Usage by Category
DATE ANNUAL USAGE (millions of gallons)
Year NCS C/I CSM CSN Other
2008 1,828.4 797.2 237.2 786.8 263.9
2009 1,799.5 802.3 236.5 827.3 283.5
2010 1,799.8 816.3 225.0 844.3 277.1
2011 2,337.2 1,044.8 255.5 939.6 386.8
2012 1,894.6 893.8 227.6 875.3 220.7
2013 1,900.8 913.6 227.7 884.0 273.5
2014 1,730.4 899.0 218.0 893.3 286.0
2015 1,783.3 998.0 220.9 898.3 308.1
E-5 Appendix E
Table E-7: Sectoral Forecast Calculations – Expanded (millions of gallons)
Year NCS C/I CSM CSN Other NCS C/I CSM CSN Other NCS C/I CSM CSN Other Metered
Usage
Water
Loss
Total
System
Demand
2008 370.7 1,752.3 188.2 885.8 601.3 13,477 1,243 3,444 2,427 1,199 5.0 2.2 0.6 2.1 0.7 10.69 1.00 11.69
2009 357.0 1,743.1 187.7 910.3 647.2 13,808 1,261 3,451 2,490 1,200 4.9 2.2 0.6 2.3 0.8 10.82 0.97 11.79
2010 348.3 1,754.1 176.6 920.5 634.3 14,159 1,275 3,491 2,513 1,197 4.9 2.2 0.6 2.3 0.8 10.86 0.89 11.75
2011 444.3 2,203.5 197.7 1,015.9 879.4 14,411 1,299 3,541 2,534 1,205 6.4 2.9 0.7 2.6 1.1 13.60 0.98 14.58
2012 353.5 1,843.0 172.7 898.0 500.0 14,642 1,325 3,601 2,663 1,206 5.2 2.4 0.6 2.4 0.6 11.23 0.92 12.15
2013 349.0 1,873.6 171.4 892.4 617.7 14,922 1,336 3,640 2,714 1,213 5.2 2.5 0.6 2.4 0.7 11.51 1.23 12.73
2014 310.6 1,803.0 162.5 889.4 645.4 15,266 1,366 3,676 2,752 1,214 4.7 2.5 0.6 2.4 0.8 11.03 0.53 11.56
2015 314.0 1,974.1 161.1 874.9 690.2 15,558 1,385 3,755 2,813 1,223 4.9 2.7 0.6 2.5 0.8 11.53 0.76 12.29
2016 355.9 1,868.4 177.2 910.9 651.9 15,836 1,404 3,779 2,867 1,230 5.6 2.6 0.7 2.6 0.8 12.34 0.84 13.18
2017 355.9 1,868.4 177.2 910.9 651.9 16,126 1,424 3,824 2,924 1,238 5.7 2.7 0.7 2.7 0.8 12.55 0.86 13.41
2018 355.9 1,868.4 177.2 910.9 651.9 16,416 1,445 3,870 2,980 1,248 5.8 2.7 0.7 2.7 0.8 12.76 0.88 13.63
2019 355.9 1,868.4 177.2 910.9 651.9 16,706 1,466 3,915 3,037 1,258 5.9 2.7 0.7 2.8 0.8 12.96 0.89 13.86
2020 355.9 1,868.4 177.2 910.9 651.9 16,997 1,486 3,960 3,093 1,269 6.1 2.8 0.7 2.9 0.8 13.17 0.91 14.08
2021 355.9 1,868.4 177.2 910.9 651.9 17,287 1,507 4,006 3,150 1,282 6.2 2.8 0.7 2.9 0.8 13.38 0.93 14.31
2022 355.9 1,868.4 177.2 910.9 651.9 17,577 1,527 4,051 3,206 1,295 6.3 2.9 0.7 2.9 0.8 13.59 0.94 14.54
2023 355.9 1,868.4 177.2 910.9 651.9 17,867 1,548 4,096 3,263 1,309 6.4 2.9 0.7 3.0 0.9 13.80 0.96 14.76
2024 355.9 1,868.4 177.2 910.9 651.9 18,157 1,568 4,142 3,319 1,324 6.5 2.9 0.7 3.0 0.9 14.01 0.98 14.99
2025 355.9 1,868.4 177.2 910.9 651.9 18,448 1,589 4,187 3,376 1,341 6.6 3.0 0.7 3.1 0.9 14.23 0.99 15.22
2026 355.9 1,868.4 177.2 910.9 651.9 18,738 1,610 4,232 3,432 1,358 6.7 3.0 0.8 3.1 0.9 14.44 1.01 15.45
2027 355.9 1,868.4 177.2 910.9 651.9 19,028 1,630 4,278 3,489 1,376 6.8 3.0 0.8 3.2 0.9 14.65 1.03 15.68
2028 355.9 1,868.4 177.2 910.9 651.9 19,318 1,651 4,323 3,545 1,395 6.9 3.1 0.8 3.2 0.9 14.87 1.04 15.91
2029 355.9 1,868.4 177.2 910.9 651.9 19,608 1,671 4,368 3,602 1,416 7.0 3.1 0.8 3.3 0.9 15.08 1.06 16.14
2030 355.9 1,868.4 177.2 910.9 651.9 19,899 1,692 4,414 3,658 1,437 7.1 3.2 0.8 3.3 0.9 15.29 1.08 16.37
F-1 Appendix F
Appendix F: Management Response
The following is the Water Services Department’s response to the recommendations made
in the City Internal Auditor’s Office Water Demand Forecasting Audit. The audit
recommendation is followed by a response describing how the recommendation will be addressed by the Water Services Department. 1. Audit Recommendation: More complex methods should be investigated in the future
as the City grows and diversifies. In the past, the forecasting methods utilized by the
City have been sufficient. Though each forecast has associated risks, these have not
had significant impact on Water Services operations in the past. However, as the City
grows and diversifies these risks may become more apparent. As this occurs, the City
could benefit from more complex in-house water demand forecasting approaches, as it
allows for more thorough analysis and increases institutional knowledge.
Management Response
Management concurs with this recommendation and will begin the transition to in-house
water demand forecasting. We are in the process of hiring a new position, an Engineer-in-Training to work under our Utility Engineer. With this added manpower, we can begin to dedicate the time to train and implement more sophisticated and accurate water demand forecasting methods.