SQL> -- Updated incorporation fiscal projections. SQL> -- SQL> -- Project fiscal results of El Dorado Hills incorporation SQL> -- if it had occurred in 2006 as a result of a Yes vote on 2005 Measure P. SQL> -- Both versions of this script account for enactment of AB 1602, SQL> -- which restored historic VLF revenue to cities incorporated SQL> -- between 2004 and 2007. SQL> -- SQL> -- This version, ip_gas_updated, factors in doubling SQL> -- of fuel tax revenues for the Road Fund between the 2004 data SQL> -- used in the CFA and generation of these results in 2008. SQL> -- SQL> -- An alternate version, ip_cfa, uses strictly CFA data, SQL> -- use it to validate sql and Perl logic by comparing SQL> -- results directly with CFA tables E-1, E-3, and B-1. SQL> -- SQL> set serveroutput on SQL> set linesize 160 SQL> set pagesize 100 SQL> set numformat $99,999,999 SQL> column "Item" justify center SQL> column "2007" justify center SQL> column "2008" justify center SQL> column "2009" justify center SQL> column "2010" justify center SQL> column "2011" justify center SQL> column "2012" justify center SQL> column "2013" justify center SQL> column "2014" justify center SQL> column "2015" justify center SQL> SQL> drop table a1; Table dropped. SQL> drop table e1; Table dropped. SQL> drop table e2; Table dropped. SQL> drop table e3; Table dropped. SQL> -- e1 is minus0 SQL> -- e3 is minus25 SQL> drop table minus50; Table dropped. SQL> drop table minus75; Table dropped. SQL> drop table minus100; Table dropped. SQL> drop table delta25; Table dropped. SQL> SQL> create table a1 ( 2 rowcode number, 3 "Item" varchar2(36), 4 "2007" number, 5 "2008" number, 6 "2009" number, 7 "2010" number, 8 "2011" number, 9 "2012" number, 10 "2013" number, 11 "2014" number, 12 "2015" number); Table created. SQL> SQL> create table e1 as select * from a1; Table created. SQL> create or replace synonym minus0 for e1; Synonym created. SQL> SQL> create table e2 as select * from a1; Table created. SQL> SQL> create table e3 as select * from a1; Table created. SQL> create or replace synonym minus25 for e3; Synonym created. SQL> SQL> create table minus50 as select * from a1; Table created. SQL> SQL> create table minus75 as select * from a1; Table created. SQL> SQL> create table minus100 as select * from a1; Table created. SQL> SQL> create table delta25 as select * from a1; Table created. SQL> SQL> SQL> SQL> insert into minus0 values 2 ( 0, 'GENERAL FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> insert into minus0 values 2 ( 1, ' Total General Fund Revenues', 3 14864353, 17335665, 18631512, 19908476, 20881445, 4 21434854, 21926657, 22064878, 22759987 ); 1 row created. SQL> insert into minus0 values 2 ( 2, ' Total General Fund Expenditures', 3 6713588, 14551958, 15387757, 15611834, 15977259, 4 16418556, 15946352, 16294745, 16600742 ); 1 row created. SQL> insert into minus0 values 2 ( 3, ' General Fund Operating Surplus', 3 8150765, 2783707, 3243754, 4296642, 4904186, 4 5016298, 5980305, 5770132, 6159245 ); 1 row created. SQL> SQL> -- Memo: SQL> -- Remember the sign is already incorporated in the data SQL> -- for the revenue neutrality payments, they're negative. SQL> -- They need to be ADDED to the operating surplus/deficit SQL> SQL> insert into minus0 values 2 ( 4, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> insert into minus0 values 2 ( 5, ' Revenue Neutrality + Fire payments', 3 -525307, -525307, -525307, -525307, -525307, 4 -525307, -525307, -525307, -525307 ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 5.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> -- Iterate to derive annual General Fund balances. SQL> -- Initial 'seed' amount is $1,247,106. Start with each year's SQL> -- net results (surplus/deficit) in each column, then accumulate SQL> -- previous balances from earlier years. SQL> -- SQL> insert into minus0 2 ( select 6.5, 'General Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus0 5 where rowcode = 6 ); 1 row created. SQL> update minus0 set "2007" = "2007" + 1247106 where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2008" = "2007" + "2008" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2009" = "2008" + "2009" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2010" = "2009" + "2010" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2011" = "2010" + "2011" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2012" = "2011" + "2012" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2013" = "2012" + "2013" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2014" = "2013" + "2014" where rowcode = 6.5; 1 row updated. SQL> update minus0 set "2015" = "2014" + "2015" where rowcode = 6.5; 1 row updated. SQL> SQL> insert into minus0 values 2 ( 7, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 8, 'ROAD MAINTENANCE FUND', 3 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> -- Original CFA gas tax data, not forecasting increase in retail gas prices SQL> -- Why do revenues decrease in 2014 & 2015? SQL> -- SQL> -- insert into minus0 values SQL> -- ( 9, ' Gas sales tax', SQL> -- 1178548, 1178548, 1178548, 1178548, 1178548, SQL> -- 1178548, 1178548, 999239, 1025173, ); SQL> -- SQL> -- CFA data should be corrected for retail gas prices increase since 2004, SQL> -- and consequent increase in fuel sales tax revenues. SQL> -- State Board of Equalization projects 2008 revenues to be slightly SQL> -- over double those from 2004, when the CFA data was finalized. SQL> -- Both the CFA and this projection assume 0 growth in fuel tax revenues SQL> -- in future years. This is extremely conservative and probably SQL> -- is unrealistically low for projections to 2015. SQL> -- SQL> -- Values for 2004 retail fuel tax collections: SQL> -- SQL> -- SQL> -- Values for fuel tax revenue corrected to correspond to actual data for SQL> -- years 2007 and using the Department of Equalization's SQL> -- projection for 2008 for all following years. SQL> -- Correction factors are the ratio of state-reported fuel sales tax SQL> -- receipts to 2004 fuel sales tax receipts. Base numbers are the SQL> -- CFA projections for the city's share of this revenue. SQL> SQL> insert into minus0 values 2 ( 9, ' Gas sales tax', 3 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), 4 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), 5 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); 1 row created. SQL> ------Use uncompensated CFA values SQL> ---- SQL> ----insert into minus0 values SQL> ----( 9, ' Gas sales tax', SQL> ---- 1178548, 1178548, 1178548, SQL> ---- 1178548, 1178548, 1178548, SQL> ---- 1178548, 999239, 1025173 ); SQL> SQL> insert into minus0 values 2 ( 10, ' Road Fund property tax', 3 1517125, 1700410, 1884235, 2080962, 2265819, 4 2367083, 2481453, 2583419, 2685795 ); 1 row created. SQL> SQL> insert into minus0 2 ( select 11, ' Total Road Fund Revenues', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 9 and b.rowcode = 10 ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 12, ' Total Road Fund expenses', 3 1486400, 1588732, 1714836, 1817168, 1876713, 4 1960029, 2019575, 2079120, 2138666 ); 1 row created. SQL> SQL> insert into minus0 2 ( select 13, ' Total Road Fund Operating Surplus', 3 a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", 4 a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", 5 a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 11 and b.rowcode = 12 ); 1 row created. SQL> SQL> -- Memo: SQL> -- Remember the sign is already incorporated in the data SQL> -- for the revenue neutrality payments, they're negative. SQL> -- They need to be ADDED to the operating surplus/deficit SQL> SQL> insert into minus0 values 2 ( 14, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 15, ' Revenue Neutrality payments', 3 -751300, -751300, -751300, -751300, -751300, 4 -751300, -751300, -751300, -751300 ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 15.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 2 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 13 and b.rowcode = 15 ); 1 row created. SQL> SQL> SQL> -- Iterate to derive annual Road Fund balances. SQL> -- Initial 'seed' amount is $166,606. Start with each year's SQL> -- net results (surplus/deficit) in each column, then accumulate SQL> -- previous balances from earlier years. SQL> -- SQL> insert into minus0 2 ( select 16.5, 'Road Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus0 5 where rowcode = 16 ); 1 row created. SQL> update minus0 set "2007" = "2007" + 166606 where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2008" = "2007" + "2008" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2009" = "2008" + "2009" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2010" = "2009" + "2010" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2011" = "2010" + "2011" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2012" = "2011" + "2012" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2013" = "2012" + "2013" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2014" = "2013" + "2014" where rowcode = 16.5; 1 row updated. SQL> update minus0 set "2015" = "2014" + "2015" where rowcode = 16.5; 1 row updated. SQL> SQL> SQL> insert into minus0 values 2 ( 17, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 18, 'OTHER RESTRICTED FUND', 3 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 19, ' Revenue; payments = revenue', 3 829756, 4743357, 5498666, 5209944, 4415583, 4 2065252, 4670891, 4776530, 4882169 ); 1 row created. SQL> SQL> SQL> insert into minus0 values 2 ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 2 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 6 and b.rowcode = 16 ); 1 row created. SQL> SQL> insert into minus0 values 2 ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus0 2 ( select 23, 'ALL FUNDS BALANCE', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus0 a, minus0 b 7 where a.rowcode = 6.5 and b.rowcode = 16.5 ); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> insert into minus25 values 2 ( 0, 'GENERAL FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 1, ' Total General Fund Revenues', 3 13978841, 16126410, 17113461, 17966793, 18777319, 4 19222097, 19624291, 19442255, 19976612 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 2, ' Total General Fund Expenditures', 3 6557674, 13975724, 14600718, 14456507, 14890897, 4 15228336, 14736310, 15013333, 15264235 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 3, ' General Fund Operating Surplus', 3 7421167, 2150686, 2512743, 3510286, 3886421, 4 3993762, 4887981, 4428921, 4712377 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 4, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 5, ' Revenue Neutrality + Fire payments', 3 -525307, -525307, -525307, -525307, -525307, 4 -525307, -525307, -525307, -525307 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 5.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> -- Iterate to derive annual General Fund balance. SQL> -- Initial 'seed' amount is $1,247,106. Start with each year's SQL> -- net results (surplus/deficit) in each column, then accumulate SQL> -- previous balances from earlier years. SQL> -- SQL> insert into minus25 2 ( select 6.5, 'General Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus25 5 where rowcode = 6 ); 1 row created. SQL> update minus25 set "2007" = "2007" + 1247106 where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2008" = "2007" + "2008" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2009" = "2008" + "2009" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2010" = "2009" + "2010" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2011" = "2010" + "2011" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2012" = "2011" + "2012" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2013" = "2012" + "2013" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2014" = "2013" + "2014" where rowcode = 6.5; 1 row updated. SQL> update minus25 set "2015" = "2014" + "2015" where rowcode = 6.5; 1 row updated. SQL> SQL> insert into minus25 values 2 ( 7, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 8, 'ROAD MAINTENANCE FUND', 3 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> SQL> insert into minus25 values 2 ( 9, ' Gas sales tax', 3 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), 4 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), 5 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); 1 row created. SQL> --Use uncompensated CFA values SQL> SQL> ----insert into minus25 values SQL> ----( 9, ' Gas sales tax', SQL> ---- 1178548, 1178548, 1178548, SQL> ---- 1178548, 1178548, 1178548, SQL> ---- 1178548, 999239, 1025173 ); SQL> SQL> -- 25%-reduced property tax values are derived from baseline in Table B-1 SQL> -- and total Road Fund revenues reported in Table E-3. Derivation is SQL> -- based on total_revenue = property_tax_revenue + gas_sales_tax_revenue, SQL> -- with gas sales tax revenue not dependent on growth, property tax SQL> -- revenue variant depending on growth. (Derivation done manually, SQL> -- results inserted below.) SQL> SQL> insert into minus25 values 2 ( 10, ' Road Fund property tax', 3 1381265, 1521288, 1661739, 1811892, 1953166, 4 2031779, 2120226, 2140696, 2212609 ); 1 row created. SQL> SQL> insert into minus25 2 ( select 11, ' Total Road Fund Revenues', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 9 and b.rowcode = 10 ); 1 row created. SQL> SQL> -- CFA shows that decreased growth decreases Road Fund expenses. SQL> SQL> insert into minus25 values 2 ( 12, ' Total Road Fund expenses', 3 1403708, 1480457, 1575035, 1651784, 1696443, 4 1758930, 1803589, 1848248, 1892907 ); 1 row created. SQL> SQL> insert into minus25 2 ( select 13, ' Total Road Fund Operating Surplus', 3 a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", 4 a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", 5 a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 11 and b.rowcode = 12 ); 1 row created. SQL> -- Memo: SQL> -- Remember the sign is already incorporated in the data SQL> -- for the revenue neutrality payments, they're negative. SQL> -- They need to be ADDED to the operating surplus/deficit SQL> SQL> insert into minus25 values 2 ( 14, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 15, ' Revenue Neutrality payments', 3 -751300, -751300, -751300, -751300, -751300, 4 -751300, -751300, -751300, -751300 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 15.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 2 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 13 and b.rowcode = 15 ); 1 row created. SQL> SQL> SQL> -- Iterate to derive annual Road Fund balances. SQL> -- Initial 'seed' amount is $166,537. Start with each year's SQL> -- net results (surplus/deficit) in each column, then accumulate SQL> -- previous balances from earlier years. SQL> -- SQL> -- There's a minor issue to resolve in the "seed" number for higher SQL> -- percentages of reduced growth. At 0% reduction it's $166,606, at 25% SQL> -- it's $166,537. This is the difference between the initial FY 2007 SQL> -- road fund balance and the net revenue for FY 2007. The equicalent SQL> -- seed funding for the General fund is identical for 0% and 25% reduced SQL> -- growth. It's not clear whether the Road Fund variance in this amount SQL> -- is a function of growth reduction or whether it may be an algorithmic SQL> -- artifact such as quantization error in deriving this amout. In SQL> -- any case the difference is small, $69. Remaining projections for SQL> -- 50%, 75%, and 100% reduced growth will subtract an additional $69 SQL> -- for each 25% reduction in growth. SQL> -- SQL> insert into minus25 2 ( select 16.5, 'Road Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus25 5 where rowcode = 16 ); 1 row created. SQL> update minus25 set "2007" = "2007" + 166537 where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2008" = "2007" + "2008" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2009" = "2008" + "2009" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2010" = "2009" + "2010" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2011" = "2010" + "2011" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2012" = "2011" + "2012" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2013" = "2012" + "2013" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2014" = "2013" + "2014" where rowcode = 16.5; 1 row updated. SQL> update minus25 set "2015" = "2014" + "2015" where rowcode = 16.5; 1 row updated. SQL> SQL> SQL> insert into minus25 values 2 ( 17, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 18, 'OTHER RESTRICTED FUND', 3 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 19, ' Revenue; payments = revenue', 3 755283, 4046440, 4612965, 4396423, 3800696, 4 4287948, 3992221, 4071494, 4150767 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 2 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 6 and b.rowcode = 16 ); 1 row created. SQL> SQL> insert into minus25 values 2 ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus25 2 ( select 23, 'ALL FUNDS BALANCE', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus25 a, minus25 b 7 where a.rowcode = 6.5 and b.rowcode = 16.5 ); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> insert into delta25 2 (select a.rowcode, a."Item", 3 a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", 4 a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", 5 a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" 6 from minus0 a, minus25 b 7 where a.rowcode = b.rowcode ); 28 rows created. SQL> SQL> delete delta25 where rowcode = 6; 1 row deleted. SQL> delete delta25 where rowcode = 6.5; 1 row deleted. SQL> delete delta25 where rowcode = 11; 1 row deleted. SQL> delete delta25 where rowcode = 13; 1 row deleted. SQL> delete delta25 where rowcode = 16; 1 row deleted. SQL> delete delta25 where rowcode = 16.5; 1 row deleted. SQL> delete delta25 where rowcode = 21; 1 row deleted. SQL> delete delta25 where rowcode = 23; 1 row deleted. SQL> SQL> select * from delta25; ROWCODE Item 2007 2008 2009 2010 2011 2012 2013 2014 ------------ ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 2015 ------------ $0 GENERAL FUND $1 Total General Fund Revenues $885,512 $1,209,255 $1,518,051 $1,941,683 $2,104,126 $2,212,757 $2,302,366 $2,622,623 $2,783,375 $2 Total General Fund Expenditures $155,914 $576,234 $787,039 $1,155,327 $1,086,362 $1,190,220 $1,210,042 $1,281,412 $1,336,507 $3 General Fund Operating Surplus $729,598 $633,021 $731,011 $786,356 $1,017,765 $1,022,536 $1,092,324 $1,341,211 $1,446,868 $4 $5 Revenue Neutrality + Fire payments $0 $0 $0 $0 $0 $0 $0 $0 $0 $6 $7 $8 ROAD MAINTENANCE FUND $9 Gas sales tax $0 $0 $0 $0 $0 $0 $0 $0 $0 $10 Road Fund property tax $135,860 $179,122 $222,496 $269,070 $312,653 $335,304 $361,227 $442,723 $473,186 $12 Total Road Fund expenses $82,692 $108,275 $139,801 $165,384 $180,270 $201,099 $215,986 $230,872 $245,759 $14 $15 Revenue Neutrality payments $0 $0 $0 $0 $0 $0 $0 $0 $0 $16 $17 $18 OTHER RESTRICTED FUND $19 Revenue; payments = revenue $74,473 $696,917 $885,701 $813,521 $614,887 -$2,222,696 $678,670 $705,036 $731,402 $20 $22 20 rows selected. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> SQL> -- Generate data for 50% reduced growth by applying delta between SQL> -- 0% and 25% reduced growth to 25% reduced growth data. SQL> -- Recompute dependent data such as subtotals that depend on SQL> -- data subject to growth deltas. SQL> SQL> insert into minus50 2 (select a.rowcode, a."Item", 3 a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", 4 a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", 5 a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" 6 from minus25 a, delta25 b 7 where a.rowcode = b.rowcode ); 20 rows created. SQL> SQL> insert into minus50 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> insert into minus50 2 ( select 6.5, 'General Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus50 5 where rowcode = 6 ); 1 row created. SQL> update minus50 set "2007" = "2007" + 1247106 where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2008" = "2007" + "2008" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2009" = "2008" + "2009" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2010" = "2009" + "2010" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2011" = "2010" + "2011" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2012" = "2011" + "2012" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2013" = "2012" + "2013" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2014" = "2013" + "2014" where rowcode = 6.5; 1 row updated. SQL> update minus50 set "2015" = "2014" + "2015" where rowcode = 6.5; 1 row updated. SQL> SQL> insert into minus50 2 ( select 11, 'Total Road Fund Revenues', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 9 and b.rowcode = 10 ); 1 row created. SQL> SQL> insert into minus50 2 ( select 13, ' Total Road Fund Operating Surplus', 3 a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", 4 a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", 5 a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 11 and b.rowcode = 12 ); 1 row created. SQL> SQL> insert into minus50 2 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 13 and b.rowcode = 15 ); 1 row created. SQL> SQL> insert into minus50 2 ( select 16.5, 'Road Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus50 5 where rowcode = 16 ); 1 row created. SQL> update minus50 set "2007" = "2007" + 166468 where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2008" = "2007" + "2008" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2009" = "2008" + "2009" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2010" = "2009" + "2010" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2011" = "2010" + "2011" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2012" = "2011" + "2012" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2013" = "2012" + "2013" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2014" = "2013" + "2014" where rowcode = 16.5; 1 row updated. SQL> update minus50 set "2015" = "2014" + "2015" where rowcode = 16.5; 1 row updated. SQL> SQL> insert into minus50 values 2 ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus50 2 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 6 and b.rowcode = 16 ); 1 row created. SQL> SQL> insert into minus50 values 2 ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus50 2 ( select 23, 'ALL FUNDS BALANCE', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 6.5 and b.rowcode = 16.5 ); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> -- Derive data for 75% reduced growth, same technique as for deriving 50% SQL> SQL> insert into minus75 2 (select a.rowcode, a."Item", 3 a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", 4 a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", 5 a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" 6 from minus50 a, delta25 b 7 where a.rowcode = b.rowcode 8 ); 22 rows created. SQL> SQL> insert into minus75 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> insert into minus50 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus50 a, minus50 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> insert into minus75 2 ( select 6.5, 'General Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus75 5 where rowcode = 6 ); 1 row created. SQL> update minus75 set "2007" = "2007" + 1247106 where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2008" = "2007" + "2008" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2009" = "2008" + "2009" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2010" = "2009" + "2010" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2011" = "2010" + "2011" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2012" = "2011" + "2012" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2013" = "2012" + "2013" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2014" = "2013" + "2014" where rowcode = 6.5; 1 row updated. SQL> update minus75 set "2015" = "2014" + "2015" where rowcode = 6.5; 1 row updated. SQL> SQL> insert into minus75 2 ( select 11, 'Total Road Fund Revenues', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 9 and b.rowcode = 10 ); 1 row created. SQL> SQL> insert into minus75 2 ( select 13, ' Total Road Fund Operating Surplus', 3 a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", 4 a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", 5 a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 11 and b.rowcode = 12 ); 1 row created. SQL> SQL> insert into minus75 2 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 13 and b.rowcode = 15 ); 1 row created. SQL> SQL> insert into minus75 2 ( select 16.5, 'Road Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus75 5 where rowcode = 16 ); 1 row created. SQL> update minus75 set "2007" = "2007" + 166399 where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2008" = "2007" + "2008" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2009" = "2008" + "2009" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2010" = "2009" + "2010" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2011" = "2010" + "2011" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2012" = "2011" + "2012" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2013" = "2012" + "2013" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2014" = "2013" + "2014" where rowcode = 16.5; 1 row updated. SQL> update minus75 set "2015" = "2014" + "2015" where rowcode = 16.5; 1 row updated. SQL> SQL> insert into minus75 values 2 ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus75 2 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 6 and b.rowcode = 16 ); 1 row created. SQL> SQL> insert into minus75 values 2 ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus75 2 ( select 23, 'ALL FUNDS BALANCE', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus75 a, minus75 b 7 where a.rowcode = 6.5 and b.rowcode = 16.5 ); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> -- Derive data for 100% reduced growth, same technique as for SQL> -- deriving 50% and 75% SQL> SQL> insert into minus100 2 (select a.rowcode, a."Item", 3 a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", 4 a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", 5 a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" 6 from minus75 a, delta25 b 7 where a.rowcode = b.rowcode ); 24 rows created. SQL> SQL> insert into minus100 2 ( select 6, 'GF surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 3 and b.rowcode = 5 ); 1 row created. SQL> SQL> insert into minus100 2 ( select 6.5, 'General Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus100 5 where rowcode = 6 ); 1 row created. SQL> update minus100 set "2007" = "2007" + 1247106 where rowcode = 6.5; 1 row updated. SQL> update minus100 set "2008" = "2007" + "2008" where rowcode = 6.5; 1 row updated. SQL> update minus100 set "2009" = "2008" + "2009" where rowcode = 6.5; 1 row updated. SQL> update minus100 set "2010" = "2009" + "2010" where rowcode = 6.5; 1 row updated. SQL> update minus100 set "2011" = "2010" + "2011" where rowcode = 6.5; 1 row updated. SQL> update minus100 set "2012" = "2011" + "2012" where rowcode = 6.5; 1 row updated. SQL> SQL> insert into minus100 2 ( select 11, 'Total Road Fund Revenues', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 9 and b.rowcode = 10 ); 1 row created. SQL> SQL> insert into minus100 2 ( select 13, ' Total Road Fund Operating Surplus', 3 a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", 4 a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", 5 a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 11 and b.rowcode = 12 ); 1 row created. SQL> SQL> insert into minus100 2 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 13 and b.rowcode = 15 ); 1 row created. SQL> SQL> insert into minus100 2 ( select 16.5, 'Road Fund Balance', 3 "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" 4 from minus100 5 where rowcode = 16 ); 1 row created. SQL> update minus100 set "2007" = "2007" + 166330 where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2008" = "2007" + "2008" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2009" = "2008" + "2009" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2010" = "2009" + "2010" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2011" = "2010" + "2011" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2012" = "2011" + "2012" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2013" = "2012" + "2013" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2014" = "2013" + "2014" where rowcode = 16.5; 1 row updated. SQL> update minus100 set "2015" = "2014" + "2015" where rowcode = 16.5; 1 row updated. SQL> SQL> insert into minus100 values 2 ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus100 2 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 6 and b.rowcode = 16 ); 1 row created. SQL> SQL> insert into minus100 values 2 ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); 1 row created. SQL> SQL> insert into minus100 2 ( select 23, 'ALL FUNDS BALANCE', 3 a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", 4 a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", 5 a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" 6 from minus100 a, minus100 b 7 where a.rowcode = 6.5 and b.rowcode = 16.5 ); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- Report. SQL> SQL> set echo off =========================== Baseline Growth =========================== PL/SQL procedure successfully completed. Item 2007 2008 2009 2010 2011 2012 2013 2014 2015 ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ GENERAL FUND Total General Fund Revenues $14,864,353 $17,335,665 $18,631,512 $19,908,476 $20,881,445 $21,434,854 $21,926,657 $22,064,878 $22,759,987 Total General Fund Expenditures $6,713,588 $14,551,958 $15,387,757 $15,611,834 $15,977,259 $16,418,556 $15,946,352 $16,294,745 $16,600,742 General Fund Operating Surplus $8,150,765 $2,783,707 $3,243,754 $4,296,642 $4,904,186 $5,016,298 $5,980,305 $5,770,132 $6,159,245 Revenue Neutrality + Fire payments -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 GF surplus/deficit after RN pmts $7,625,458 $2,258,400 $2,718,447 $3,771,335 $4,378,879 $4,490,991 $5,454,998 $5,244,825 $5,633,938 General Fund Balance $8,872,564 $11,130,964 $13,849,411 $17,620,746 $21,999,625 $26,490,616 $31,945,614 $37,190,439 $42,824,377 ROAD MAINTENANCE FUND Gas sales tax $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $1,958,508 $2,009,339 Road Fund property tax $1,517,125 $1,700,410 $1,884,235 $2,080,962 $2,265,819 $2,367,083 $2,481,453 $2,583,419 $2,685,795 Total Road Fund Revenues $3,827,079 $4,010,364 $4,194,189 $4,390,916 $4,575,773 $4,677,037 $4,791,407 $4,541,927 $4,695,134 Total Road Fund expenses $1,486,400 $1,588,732 $1,714,836 $1,817,168 $1,876,713 $1,960,029 $2,019,575 $2,079,120 $2,138,666 Total Road Fund Operating Surplus $2,340,679 $2,421,632 $2,479,353 $2,573,748 $2,699,060 $2,717,008 $2,771,832 $2,462,807 $2,556,468 Revenue Neutrality payments -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 Rd Fnd surplus/deficit after RN pmts $1,589,379 $1,670,332 $1,728,053 $1,822,448 $1,947,760 $1,965,708 $2,020,532 $1,711,507 $1,805,168 Road Fund Balance $1,755,985 $3,426,317 $5,154,370 $6,976,818 $8,924,578 $10,890,286 $12,910,819 $14,622,326 $16,427,494 OTHER RESTRICTED FUND Revenue; payments = revenue $829,756 $4,743,357 $5,498,666 $5,209,944 $4,415,583 $2,065,252 $4,670,891 $4,776,530 $4,882,169 ALL FUNDS OPERATING SURPLUS/DEFICIT $9,214,837 $3,928,732 $4,446,500 $5,593,783 $6,326,639 $6,456,699 $7,475,530 $6,956,332 $7,439,106 ALL FUNDS BALANCE $10,628,549 $14,557,281 $19,003,781 $24,597,564 $30,924,203 $37,380,902 $44,856,433 $51,812,765 $59,251,871 28 rows selected. ========================= 25% Reduced Growth ========================= PL/SQL procedure successfully completed. Item 2007 2008 2009 2010 2011 2012 2013 2014 2015 ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ GENERAL FUND Total General Fund Revenues $13,978,841 $16,126,410 $17,113,461 $17,966,793 $18,777,319 $19,222,097 $19,624,291 $19,442,255 $19,976,612 Total General Fund Expenditures $6,557,674 $13,975,724 $14,600,718 $14,456,507 $14,890,897 $15,228,336 $14,736,310 $15,013,333 $15,264,235 General Fund Operating Surplus $7,421,167 $2,150,686 $2,512,743 $3,510,286 $3,886,421 $3,993,762 $4,887,981 $4,428,921 $4,712,377 Revenue Neutrality + Fire payments -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 GF surplus/deficit after RN pmts $6,895,860 $1,625,379 $1,987,436 $2,984,979 $3,361,114 $3,468,455 $4,362,674 $3,903,614 $4,187,070 General Fund Balance $8,142,966 $9,768,345 $11,755,781 $14,740,760 $18,101,874 $21,570,329 $25,933,003 $29,836,617 $34,023,687 ROAD MAINTENANCE FUND Gas sales tax $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $1,958,508 $2,009,339 Road Fund property tax $1,381,265 $1,521,288 $1,661,739 $1,811,892 $1,953,166 $2,031,779 $2,120,226 $2,140,696 $2,212,609 Total Road Fund Revenues $3,691,219 $3,831,242 $3,971,693 $4,121,846 $4,263,120 $4,341,733 $4,430,180 $4,099,204 $4,221,948 Total Road Fund expenses $1,403,708 $1,480,457 $1,575,035 $1,651,784 $1,696,443 $1,758,930 $1,803,589 $1,848,248 $1,892,907 Total Road Fund Operating Surplus $2,287,511 $2,350,785 $2,396,658 $2,470,062 $2,566,677 $2,582,803 $2,626,591 $2,250,956 $2,329,041 Revenue Neutrality payments -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 Rd Fnd surplus/deficit after RN pmts $1,536,211 $1,599,485 $1,645,358 $1,718,762 $1,815,377 $1,831,503 $1,875,291 $1,499,656 $1,577,741 Road Fund Balance $1,702,748 $3,302,233 $4,947,591 $6,666,353 $8,481,730 $10,313,233 $12,188,525 $13,688,181 $15,265,922 OTHER RESTRICTED FUND Revenue; payments = revenue $755,283 $4,046,440 $4,612,965 $4,396,423 $3,800,696 $4,287,948 $3,992,221 $4,071,494 $4,150,767 ALL FUNDS OPERATING SURPLUS/DEFICIT $8,432,071 $3,224,864 $3,632,794 $4,703,741 $5,176,491 $5,299,958 $6,237,965 $5,403,270 $5,764,811 ALL FUNDS BALANCE $9,845,714 $13,070,578 $16,703,372 $21,407,113 $26,583,604 $31,883,562 $38,121,528 $43,524,798 $49,289,609 28 rows selected. ========================= 50% Reduced Growth ========================= PL/SQL procedure successfully completed. Item 2007 2008 2009 2010 2011 2012 2013 2014 2015 ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ GENERAL FUND Total General Fund Revenues $13,093,329 $14,917,155 $15,595,410 $16,025,110 $16,673,193 $17,009,340 $17,321,925 $16,819,632 $17,193,237 Total General Fund Expenditures $6,401,760 $13,399,490 $13,813,679 $13,301,180 $13,804,535 $14,038,116 $13,526,268 $13,731,921 $13,927,728 General Fund Operating Surplus $6,691,569 $1,517,665 $1,781,732 $2,723,930 $2,868,656 $2,971,226 $3,795,657 $3,087,710 $3,265,509 Revenue Neutrality + Fire payments -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 GF surplus/deficit after RN pmts $6,166,262 $992,358 $1,256,425 $2,198,623 $2,343,349 $2,445,919 $3,270,350 $2,562,403 $2,740,202 GF surplus/deficit after RN pmts $6,166,262 $992,358 $1,256,425 $2,198,623 $2,343,349 $2,445,919 $3,270,350 $2,562,403 $2,740,202 General Fund Balance $7,413,368 $8,405,726 $9,662,151 $11,860,774 $14,204,123 $16,650,042 $19,920,392 $22,482,795 $25,222,997 ROAD MAINTENANCE FUND Gas sales tax $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $1,958,508 $2,009,339 Road Fund property tax $1,245,405 $1,342,166 $1,439,243 $1,542,822 $1,640,513 $1,696,475 $1,758,999 $1,697,973 $1,739,423 Total Road Fund Revenues $3,555,359 $3,652,120 $3,749,197 $3,852,776 $3,950,467 $4,006,429 $4,068,953 $3,656,481 $3,748,762 Total Road Fund expenses $1,321,016 $1,372,182 $1,435,234 $1,486,400 $1,516,173 $1,557,831 $1,587,603 $1,617,376 $1,647,148 Total Road Fund Operating Surplus $2,234,343 $2,279,938 $2,313,963 $2,366,376 $2,434,294 $2,448,598 $2,481,350 $2,039,105 $2,101,614 Revenue Neutrality payments -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 Rd Fnd surplus/deficit after RN pmts $1,483,043 $1,528,638 $1,562,663 $1,615,076 $1,682,994 $1,697,298 $1,730,050 $1,287,805 $1,350,314 Road Fund Balance $1,649,511 $3,178,149 $4,740,812 $6,355,888 $8,038,882 $9,736,180 $11,466,231 $12,754,036 $14,104,350 OTHER RESTRICTED FUND Revenue; payments = revenue $680,810 $3,349,523 $3,727,264 $3,582,902 $3,185,809 $6,510,644 $3,313,551 $3,366,458 $3,419,365 ALL FUNDS OPERATING SURPLUS/DEFICIT $7,649,305 $2,520,996 $2,819,088 $3,813,699 $4,026,343 $4,143,217 $5,000,400 $3,850,208 $4,090,516 ALL FUNDS BALANCE $9,062,879 $11,583,875 $14,402,963 $18,216,662 $22,243,005 $26,386,222 $31,386,623 $35,236,831 $39,327,347 31 rows selected. ========================= 75% Reduced Growth ========================= PL/SQL procedure successfully completed. Item 2007 2008 2009 2010 2011 2012 2013 2014 2015 ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ GENERAL FUND Total General Fund Revenues $12,207,817 $13,707,900 $14,077,359 $14,083,427 $14,569,067 $14,796,583 $15,019,559 $14,197,009 $14,409,862 Total General Fund Expenditures $6,245,846 $12,823,256 $13,026,640 $12,145,853 $12,718,173 $12,847,896 $12,316,226 $12,450,509 $12,591,221 General Fund Operating Surplus $5,961,971 $884,644 $1,050,721 $1,937,574 $1,850,891 $1,948,690 $2,703,333 $1,746,499 $1,818,641 Revenue Neutrality + Fire payments -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 GF surplus/deficit after RN pmts $5,436,664 $359,337 $525,414 $1,412,267 $1,325,584 $1,423,383 $2,178,026 $1,221,192 $1,293,334 General Fund Balance $6,683,770 $7,043,107 $7,568,521 $8,980,788 $10,306,372 $11,729,755 $13,907,781 $15,128,973 $16,422,307 ROAD MAINTENANCE FUND Gas sales tax $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $1,958,508 $2,009,339 Road Fund property tax $1,109,545 $1,163,044 $1,216,747 $1,273,752 $1,327,860 $1,361,171 $1,397,772 $1,255,250 $1,266,237 Total Road Fund Revenues $3,419,499 $3,472,998 $3,526,701 $3,583,706 $3,637,814 $3,671,125 $3,707,726 $3,213,758 $3,275,576 Total Road Fund expenses $1,238,324 $1,263,907 $1,295,433 $1,321,016 $1,335,903 $1,356,732 $1,371,617 $1,386,504 $1,401,389 Total Road Fund Operating Surplus $2,181,175 $2,209,091 $2,231,268 $2,262,690 $2,301,911 $2,314,393 $2,336,109 $1,827,254 $1,874,187 Revenue Neutrality payments -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 Rd Fnd surplus/deficit after RN pmts $1,429,875 $1,457,791 $1,479,968 $1,511,390 $1,550,611 $1,563,093 $1,584,809 $1,075,954 $1,122,887 Road Fund Balance $1,596,274 $3,054,065 $4,534,033 $6,045,423 $7,596,034 $9,159,127 $10,743,937 $11,819,891 $12,942,778 OTHER RESTRICTED FUND Revenue; payments = revenue $606,337 $2,652,606 $2,841,563 $2,769,381 $2,570,922 $8,733,340 $2,634,881 $2,661,422 $2,687,963 ALL FUNDS OPERATING SURPLUS/DEFICIT $6,866,539 $1,817,128 $2,005,382 $2,923,657 $2,876,195 $2,986,476 $3,762,835 $2,297,146 $2,416,221 ALL FUNDS BALANCE $8,280,044 $10,097,172 $12,102,554 $15,026,211 $17,902,406 $20,888,882 $24,651,718 $26,948,864 $29,365,085 32 rows selected. ============================= Zero Growth ============================= PL/SQL procedure successfully completed. Item 2007 2008 2009 2010 2011 2012 2013 2014 2015 ------------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ GENERAL FUND Total General Fund Revenues $11,322,305 $12,498,645 $12,559,308 $12,141,744 $12,464,941 $12,583,826 $12,717,193 $11,574,386 $11,626,487 Total General Fund Expenditures $6,089,932 $12,247,022 $12,239,601 $10,990,526 $11,631,811 $11,657,676 $11,106,184 $11,169,097 $11,254,714 General Fund Operating Surplus $5,232,373 $251,623 $319,710 $1,151,218 $833,126 $926,154 $1,611,009 $405,288 $371,773 Revenue Neutrality + Fire payments -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 -$525,307 GF surplus/deficit after RN pmts $4,707,066 -$273,684 -$205,597 $625,911 $307,819 $400,847 $1,085,702 -$120,019 -$153,534 General Fund Balance $5,954,172 $5,680,488 $5,474,891 $6,100,802 $6,408,621 $6,809,468 $1,085,702 -$120,019 -$153,534 ROAD MAINTENANCE FUND Gas sales tax $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $2,309,954 $1,958,508 $2,009,339 Road Fund property tax $973,685 $983,922 $994,251 $1,004,682 $1,015,207 $1,025,867 $1,036,545 $812,527 $793,051 Total Road Fund Revenues $3,283,639 $3,293,876 $3,304,205 $3,314,636 $3,325,161 $3,335,821 $3,346,499 $2,771,035 $2,802,390 Total Road Fund expenses $1,155,632 $1,155,632 $1,155,632 $1,155,632 $1,155,633 $1,155,633 $1,155,631 $1,155,632 $1,155,630 Total Road Fund Operating Surplus $2,128,007 $2,138,244 $2,148,573 $2,159,004 $2,169,528 $2,180,188 $2,190,868 $1,615,403 $1,646,760 Revenue Neutrality payments -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 -$751,300 Rd Fnd surplus/deficit after RN pmts $1,376,707 $1,386,944 $1,397,273 $1,407,704 $1,418,228 $1,428,888 $1,439,568 $864,103 $895,460 Road Fund Balance $1,543,037 $2,929,981 $4,327,254 $5,734,958 $7,153,186 $8,582,074 $10,021,643 $10,885,746 $11,781,206 OTHER RESTRICTED FUND Revenue; payments = revenue $531,864 $1,955,689 $1,955,862 $1,955,860 $1,956,035 $10,956,036 $1,956,211 $1,956,386 $1,956,561 ALL FUNDS OPERATING SURPLUS/DEFICIT $6,083,773 $1,113,260 $1,191,676 $2,033,615 $1,726,047 $1,829,735 $2,525,270 $744,084 $741,926 ALL FUNDS BALANCE $7,497,209 $8,610,469 $9,802,145 $11,835,760 $13,561,807 $15,391,542 $11,107,345 $10,765,727 $11,627,672 34 rows selected. SQL> exit