Loading...
HomeMy Public PortalAboutExhibit MSD 65 - VertexOne Attribute StudyMSD Unmetered Rate Analysis Project Crystal Tjaden Data Analyst Mobile: 308-631-7056 Email: crystal.tjaden@vertexone.net A VertexOne Solution Overview Exhibit MSD 65 ©VertexOne | Subject to Contract Page | ii Table of Contents 1 Project Summary .......................................................................................................... 3 2 Analysis Overview ......................................................................................................... 4 3 Demographic Data Append .......................................................................................... 5 4 County Parcel Information ........................................................................................... 7 5 Customer Demographic Data Distribution .................................................................. 8 6 Metered Consumption Observations .......................................................................... 10 7 Customer Growth ........................................................................................................ 12 8 Metered Consumption Analysis .................................................................................. 13 9 Prior Study .................................................................................................................. 14 10 Linear Step Down of Usage ...................................................................................... 15 11 Attribution Volume Summary ................................................................................. 16 12 Assumptions ............................................................................................................. 17 ©VertexOne | Subject to Contract Page | 3 1 Project Summary Metropolitan St. Louis Sewer District (MSD) currently bills approximately 77,000 customers without water meters, a wastewater service charge based on estimates of indoor water usage that is estimated based on the following property attributes: number of rooms, baths, water closets and additional showers. The rate associated with each attribute is currently based on a 2015 study conducted by VertexOne. To support an upcoming rate study, MSD commissioned VertexOne to conduct an updated analysis of the volume contribution based on property attributes for unmetered customers. MSD requested the analysis should be done using a similar approach to the previous analysis conducted in 2015, where a comparison was done between metered accounts and unmetered accounts along the parameters of usage, billing, and customer profiles. ©VertexOne | Subject to Contract Page | 4 2 Analysis Overview VertexOne acquired third-party data from both Experian and the Saint Louis County Government. All active non-commercial customers identified in the CIS Essentials billing system were available for analysis. The associated premises information was added when the premises was flagged as one of the following premises types: • Multi-Unit Quarterly Average • Multi-Unit Winter Quarter • Multi-Unit Unmetered • Residential Metered • Residential Unmetered Once the data was joined between the three systems of r ecord – billing system, demographic data and county parcel data, analysis was completed for consumption trends, demographic breakdowns and usage comparisons using a three-year average usage from 2018 to 2020. Figure 1 - Analysis Overview ©VertexOne | Subject to Contract Page | 5 3 Demographic Data Append Customer information from the CIS Essentials billing system was provided to E xperian for matching with customer demographic data. Experian matched on customer name and address and returned a file with the following metrics for matched records. The process resulted in roughly 400k customer and premises combinations available for a nalysis; this was approximately a 79% match rate between CIS Essentials and the demographic data provider. The estimated age from the demographic data provider was only populated on 22% of the records. MICE was used to impute the age based on similar attributes on other accounts. All other records with missing values were scrubbed before the linear transformation model was run. Blanks % Populated Metered Unmetered % Metered % Unmetered Homeowner 84,564 79% 257,886 61,561 81% 19% Income Range 84,564 79% 257,886 61,561 81% 19% Number of Occupants 84,564 79% 257,886 61,561 81% 19% Number of Adults 84,564 79% 257,886 61,561 81% 19% Estimated Age 315,985 22% 66,985 20,941 76% 24% Education 84,564 79% 257,886 61,561 81% 19% Marital Status 84,564 79% 257,886 61,561 81% 19% Occupation 84,564 79% 257,886 61,561 81% 19% Total Rooms 84,564 79% 257,886 61,561 81% 19% Square Footage 84,564 79% 257,886 61,561 81% 19% Bedrooms 84,564 79% 257,886 61,561 81% 19% Bathrooms 84,564 79% 257,886 61,561 81% 19% Table 1 - Demographic Data Match Rates Demographic Data Field Definition Homeowner Homeowner information indicates the likelihood of a consumer owning a home and is received from tax assessor and deed information. For records where exact Homeownership information is not available, homeownership propensity is calculated using a proprietary statistical model which predicts the likelihood of homeownership. Renter status is derived from self- ©VertexOne | Subject to Contract Page | 6 Demographic Data Field Definition reported data. Unit numbers are not used to infer rented status because units may be owner condominium/coop. Probable Renter is calculated using an algorithm based on lack of Homeowner, the Address Type, and Census Percent Renter. Income Range The total estimated income for a living unit incorporating several highly predictive individual and household level variables. Multiple statistical methodologies are used to predict the income estimate for the living unit. Number of Occupants The total number of people on the record, includes count for children, adults. Number of Adults Number of adults on record. Estimated Age The estimated age of person number 1. Education The likelihood of the defined education codes. Marital Status The likelihood of the defined marital status codes. Occupation The occupation association with person number 1. Total Rooms The Total Rooms is determined from Grant/Warranty Deed information recorded or other legal documents filed at the county recorder's office in the county where the property is located. Square Footage The square footage of any buildings associated with the home determined from Grant/Warranty Deed information recorded or other legal documents filed at the county recorder's office in the county where the property is located. Bedrooms The home Bedrooms is determined from Grant/Warranty Deed information recorded or other legal documents filed at the county recorder's office in the county where the property is located. Bathrooms The home Bath is determined from Grant/Warranty Deed information recorded or other legal documents filed at the county recorder's office in the county where the property is located. Table 2 - Demographic Data Dictionary ©VertexOne | Subject to Contract Page | 7 4 County Parcel Information County parcel data was downloaded from the County website and loaded for analysis. The primary file contains the parcel information, namely the address of the property. The file was then linked to the dwelling file to retrieve the number of rooms, bedrooms, and bathrooms. The county parcel information was located at Saint Louis County Open Government (arcgis.com). Parcel data: Saint Louis County Open Government : Parcels : Parcels (arcgis.com) Dwelling data: Saint Louis County Open Government : Real Estate Data Extract CERT19 (arcgis.com ) Source Table Data Field Definition Parcels PROP_ADD Property Address Parcels PROP_ZIP Property Zip Code Dwelling RMTOT Total number of rooms. Dwelling FIXBATH Number of bathrooms. Dwelling FIXHALF Number of half baths. Table 3 - Parcel Data Definitions ©VertexOne | Subject to Contract Page | 8 5 Customer Demographic Data Distribution The following is a breakdown of how the customers are distributed by some of the demographic variables. ©VertexOne | Subject to Contract Page | 9 ©VertexOne | Subject to Contract Page | 10 6 Metered Consumption Observations Consumption for all residential and multi-unit metered premises was obtained from the billing system and analyzed for previous consumption patterns and projected usage patterns. Between 2014 and 2020 consumption declined 10.9% (Figure 2 - Historical ). The projected reduction in consumption between 2021 and 2030 is an additional 16.4% (Figure 3 - Projected ), making the total reduction between 2013 and 2030 – 27.3%. Figure 2 - Historical Consumption Future consumption values were predicted using the Exponential Smoothing (ETS) algorithm. The forecasted (predicted) consumption values are a continuation of the historical values . The forecast was also moved back to 2018 and 2019 to verify that the forecast was in-line with actuals. Figure 3 - Projected Consumption ©VertexOne | Subject to Contract Page | 11 Figure 4 - Consumption by Generation ©VertexOne | Subject to Contract Page | 12 7 Customer Growth Customer growth was analyzed for the study population to determine if there is a correlation between the reduction between the reduction in consumption over the past seven years and the number of active accounts over the past seven years. The number of active accounts has shown a slight growth trend over time, which will result in a .3% increase between 2021 and 2030. Figure 5 - Historical Active Account Counts Again, future active account values were predicted using the Exponential Smoothing (ETS) algorithm. Figure 6 - Projected Active Account Counts ©VertexOne | Subject to Contract Page | 13 8 Metered Consumption Analysis Analysis methods: the file returned from the demographic data provider was split into two classes – metered and non- metered. Usage was obtained for all the metered premises for 2018, 2019 and 2020. Analysis was completed for 2020, all three years and two additional methods. 1. Using parcel data (number of rooms, bathrooms, and water closets), like records were compared to get metered consumption for unmetered premises. For example, unmetered premises with 4 rooms, 1 bed and 1 bath were matched against metered premises with 4 rooms, 1 bed and 1 bath to get the corresponding consump tion. 2. Using demographic data (number of bathrooms, occupants, and household square footage), the same comparison took place as in method 1. For example, unmetered premises with 2 baths, 4 occupants and a home square footage range of 1100-2000 were matched against metered premises with the same metrics to get the corresponding consumption. Three-year Average (2018 through 2020) 2015 Residential and Multi-Unit Premises All Residential and Multi-Unit Premises Using Parcel Data Matched by Rooms, Baths & WC Using Demographic Data Matched by Baths, Occupants, Home Square Footage Average Daily Use .260 0.239 0.280 0.451 Dwelling Units 1.27 1.27 1.31 1.96 CCFs per Year per Dwelling Unit 74.881 68.592 78.168 83.867 CCFs per Month per Dwelling Unit 6.240 5.716 6.514 6.989 Gallons per Month 4667.925 4275.861 4872.826 5228.043 ©VertexOne | Subject to Contract Page | 14 9 Prior Study A data pull from the CIS Essentials billing system was used to sum then average the usage for metered customers. The estimated usage analyses completed for the unmetered customers had two approaches. The first analysis leveraged the current MSD Basis of Rates for the unmetered customers formula, which calculated usage based upon the number of rooms (.6464 CCFs per), water closets (2.4343 CCFs per), and baths/show ers (2.321 CCFs per). The total number of rooms, water closets, and bath/showers were summed for all unmetered customers from the CIS Essentials system and multiplied by their corresponding CCF rate calculation to create a total estimated usage for all unmetered customers. This aggregate usage was then divided amongst the total number of unmetered customers to create a per unmetered customer estimated average usage. The second analysis calculated estimated usage for unmetered accounts using the MICE statist ical method. Whereas most statistical procedures exclude observations with any missing values from the analysis, MICE was used to replace missing usage. Data Used Summary Original Billing Function 1. Metered Data 2. Demographic Data 3. Unmetered Data From the documentation the Mice method was used on the prior study to fill in the missing usage values • 0.6464 Total Rooms + 2.4343 Toilets + 2.0321 Baths o When finding a usage value for the unmetered clients with this formula, the average CCFs is 11.2 Table 4 - Prior Study ©VertexOne | Subject to Contract Page | 15 10 Linear Step Down of Usage Due to the overall drop in consumption across all residential metered customers, as validated between t he prior study average consumption of 6.3 CCFs and the 2020 average consumption of 5.4 CCFs, the linear step down of usage method was used to derive the new billing function. The prior billing formula was used to estimate the premises consumption and step down the values by the percentage drop (14%). The existing billing function was then used to get the consumption value per premises, which was reduced by the drop of 14% to get a new “stepped down premises usage.” A regression on those values was run to get a new billing function: Billing Function = 0.5083TotalRooms + 1.9Toilets + 1.584Baths • This formula gives an average monthly usage of 9.5 CCFs per premise s and 7.9 CCFs per dwelling unit The resulting billing function retains relative weights of rooms, toilets, and baths, with toilets driving the biggest impact . It is expected that unmetered usage would be higher than metered usage due to moral hazard (fixed charge model discourages controls on usage). Additionally, the average usage calculation per dwelling unit (7.9 CCFs) for unmetered customers is higher than matched demographic based calculation (6.98 CCFs) from the metered consumption analysis, which also supports the fact that unmetered usage would be higher. Data Used Summary Billing Function 1. Metered 2020 data 2. Unmetered data Proactive measures were taken to clean and keep the data consistent with other methods/studies. Once the data was cleaned, the following steps were followed to obtain the billing function: 1. Used the 2014 study average consumption per premises (6.3 CCFs) and compared it to the 2020 Metered Data consumption average (5.4 CCFs) 2. Noted approximately a 14% drop from the previous study average consumption to 2020 average consumption 3. The existing billing function (reference Table 4 - Prior Study Billing Function cell) was used to get a usage value per premises 4. Next the calculated usage value was decreased by the 14% to get a new “stepped down premises usage” 5. Once that new “stepped down premises usage” was found, a regression was run for rooms/toilets/baths on that value to get a new billing function that is referenced in the billing function cell to the right • 0.5083 Total Rooms + 1.9000 Toilets + 1.5840 Baths o When finding a usage value for the unmetered clients with this formula, the average CCFs is 9.5 Table 5 - Step Down Summary ©VertexOne | Subject to Contract Page | 16 11 Attribution Volume Summary The following is a historical comparison of the volume attributions. The original values were prior to the VertexOne previous study. The current values are a result of that study and the Linear Transformation are the new values that have been calculated and are recommended for the new basis of rates. Fixture Usage Unit Values (GPD) Fixture Original (pre-2015) Current Linear Transformation Rooms 16 14.5 12.5 Water Closets 60 54.2 46.7 Baths 50 45.2 40 Separate Showers 50 45.2 40 Calculated Rate Basis (CCF) Fixture Original (pre-2015) Current Linear Transformation Rooms 0.6464 0.5896 0.5083 Water Closets 2.4343 2.2038 1.9000 Baths 2.0321 1.8379 1.5840 Separate Showers 2.0321 1.8379 1.5840 AVERAGE CONSUMPTION PER DWELLING UNIT (CCF) 10.1 9.2 7.9 ©VertexOne | Subject to Contract Page | 17 12 Assumptions Demographic Data • HBSFootageCode is in 100s i.e. multiply value by 100 • HomeownerCode: Homeowner = H/7/8/9 Renter = R/T • IncomeRangeCode: Low = A/B/C/D Middle = E/F/G Upper = H/I/J/K/L • BathroomCodes ending in 5 translates to a half bathroom Ex: 25 = 2 showers & 3 toilets • MaritalStatusCode Single = 5S Married = 1M/5M • EducationCode No HS Diploma = 15/55 HS Diploma = 11/51 Some College = 12/52 Bach Degree = 13/53 Grad Degree = 14/54 • EstimatedAgeCode Use MICE to fill in the 257,469 missing values CIS Essentials Data • PremiseMaserType: Multi Family = MUU/MUWA/MUQ Single Family = RESU/RESM • TotalUsage Used TotalUsage of RevMonth 202009 for each account • Dwelling Units Dwelling Units of 2 was assumed a duplex each with their own meter • 748.05 Gallons per CCF • 365 days in a year • 12 months in a year