Incorporation Technical Analysis

Incorporation Technical Analysis
of fiscal projections

Links to files are at the bottom of this web page. Questions by email should go to alliance@edhca.net.

Objectives:

Evaluate fiscal viability of incorporation at lower levels of growth than were covered in the 2005 CFA (Comprehensive Fiscal Analysis). Levels to report are 0% reduced growth (nominal case), 25% reduced growth, 50% reduced growth, 75% reduced growth, and 100% reduced growth (zero growth). Nominal growth is the rate projected by the CFA based on actual growth rate in recent years. The CFA used a 2-step model, splitting its 9-year analysis into an early phase with residential growth at 900 housing units per year and a later phase at 450 units per year. The overall average for the nominal case was 675 housing units per year.
Confirm accuracy of the algorithm for extrapolating CFA data where possible by applying logic to using parts to generate results that should correspond precisely to CFA results published in tables E-1 and E-3.
Constrain the domain of analysis to historic VLF in-lieu revenue levels, in recognition of enactment of AB 1602. CFA sensitivity analyses for what was then "current VLF level" are no longer applicable.
Update Road Fund revenue projections to account for doubling of gasoline (fuel) sales tax revenues since the CFA's 2004 data.
Expose all computer scripts used to generate results to public open-source access.

The final point, open source access, is intended primarily to permit individuals and organizations to inspect and validate the computer logic involved. A secondary possibility is that is may be possible for others to extend this analysis with the benefit of more recent data. At this time the best available data on El Dorado Hills is in two reports: The 2005 Incorporation CFA and the Annual Report of the El Dorado Hills Fire Department ("El Dorado Hills County Water District").

Data summary:

The nominal growth rate recognized by the CFA in terms of residential growth was 675 housing units per year. Over the 9-year analysis period this was split into an initial phase at 900 units per year and a final phase at 450 units per year.
Data directly sensitive to 25% reduced growth is taken from the CFA as primary data
Data directly sensitive to 50%, 75%, and 100% growth is derived by linear extrapolation from the CFA data for 0% reduced (nominal) growth and 25% reduced growth.
Projection of actual fuel sales tax revenues is updated to be near actual revenues but still below them. The most readable source for recent data is in a graphic published on 3/25/08 by the San Francisco Chronicle. The ultimate source of this data is the California Board of Equalization. BOE web site contents include most of this data embeded in several different reports but the data was awkward to locate. Like the CFA, the updated projections used here use the most recently recorded data and to not anticipate future changes due to gasoline prices.

SQL logic summary:

Initialization creates one table for each level of the 5 levels of growth and a delta table. Columns include a numeric row code, a row label (text), and one numeric column for each year covered by the CFA (2007 - 2015). These tables correspond closely to the format used in CFA tables A-1 and E-1 through E-3. Additional rows based on CFA Table B-1 are inserted to provide separate data for Road Fund revenue sources: Property taxes and fuel sales taxes.
Tables for 0% and 25% reduced growth are populated by inserting primary data from the CFA. Derived data such as subtotals and surplus/deficit results are calculated from the primary data. The delta table is populated with data for rows whose values depend on growth by subtraction, to determine the delta (change) in primary data values between 0% and 25% reduced growth. The delta table provides a computationally simple way to extrapolate between equally spaced increments of reduced growth.
The table for 50% reduced growth is populated by propagating primary data from the 25% reduced growth table; growth-invariant data is unmodified. Growth-variant data is copied without change, growth variant data reflects the row-specific delta: The value for 50% growth is the value for 25% growth minus the row-specific delta value. Derived data is then computed from the primary data.
The same procedure is used to produce full data for 75% reduced growth (derived from the 50% reduced growth data) and for 100% reduced growth (derived from 75% reduced growth data).
Results are logged by selecting all data in each of these tables.

The SQL script was set up for execution with an Oracle database, using Oracle's sqlplus client program. Minor changes would be needed to run it on a non-Oracle system.

Perl script notes:

The Perl script reads the log created by the SQL script and formats results into multiple log files and one html file. It skips content resulting from generation of table contents and produces reports based on the portion of the log showing 'select *' results from all tables. The multiple log files it generates are primaryl the verbatim results shown in the SQL log file.

The html file for the main concise fiscal summary is generated by reading a template html file and substituting values corresponding to different columns and rows into an html table.

To use the Perl script requires only a command line invocation with a single argument to provide a basename used as the initial substring for all filenames. On Windows the command line invocation for the "gas updated" data set is "perl ip_filter ip_gas_updated". The table at the bottom of this page lists the corresponding filenames which the Perl script reads and writes.

Comments within the Perl script provide additional notes.

Validation notes:

The most direct validation possible is to run the "ip_cfa" data set and compare results with those directly reported in the CFA for the cases of 0% reduced growth (Table E-1) and 25% reduced growth (Table E-3). In doing this almost all derived data values are identical. A small scattering show a difference of $1 in individual values. These probably represent differences betwen the SQL program and the CFA in handling round-off to integer values. These $1 differences are not significant in the context of bottom line results on the order of millions or tens of millions of dollars.

There is no direct source of comparison for results at 50% to 100% reduced growth, the only validation step is to confirm that results are consistent with linear extrapolation from the originally given CFA data. This presumes that the numbers in question are linear with respect to (for example) population, housing units or square feet of retail space. This assumption is generally the best available without analyzing fine detail but cannot be expected to be precise.

Links to files: Yellow background for sources, no color for files generated by 2-step run
All files in this list are released to the public domain. The Citizens Alliance explicitly disclaims the implicit copyright included in U.S. copyright law for web materials.

Component	For data with update to fuel tax revenue	For strictly CFA data
SQL script: Contains all quantitative logic	ip_gas_updated.sql	ip_cfa.sql
Results of running SQL script	ip_gas_updated.log	ip_cfa.log
Perl script to read SQL log, format output files	ip_filter
HTML template for generation of overall fiscal summary	ip_gas_updated_fiscal_summary_template.html	ip_cfa_fiscal_summary_template.html
Overall fiscal summary in HTML	ip_gas_updated_fiscal_summary.html	ip_cfa_fiscal_summary.html
Detail results for all growth levels	ip_gas_updated_1.log	ip_cfa_1.log
Detail results for 0% reduced growth (nominal)	ip_gas_updated_1_0.log	ip_cfa_1_0.log
Detail results for 25% reduced growth	ip_gas_updated_1_25.log	ip_cfa_1_25.log
Detail results for 50% reduced growth	ip_gas_updated_1_50.log	ip_cfa_1_50.log
Detail results for 75% reduced growth	ip_gas_updated_1_75.log	ip_cfa_1_75.log
Detail results for 100% reduced growth (zero growth)	ip_gas_updated_1_100.log	ip_cfa_1_100.log