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_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> -- An alternate 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> 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 SQL> ----( 9, ' Gas sales tax', SQL> ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), SQL> ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), SQL> ---- 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); SQL> ------Use uncompensated CFA values SQL> SQL> insert into minus0 values 2 ( 9, ' Gas sales tax', 3 1178548, 1178548, 1178548, 4 1178548, 1178548, 1178548, 5 1178548, 999239, 1025173 ); 1 row created. 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> 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,537. 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" + 166537 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 SQL> ----( 9, ' Gas sales tax', SQL> ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), SQL> ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), SQL> ---- 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); SQL> ------Use uncompensated CFA values SQL> SQL> insert into minus25 values 2 ( 9, ' Gas sales tax', 3 1178548, 1178548, 1178548, 4 1178548, 1178548, 1178548, 5 1178548, 999239, 1025173 ); 1 row created. 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 2031769, 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> 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,314 $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> 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> 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> 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> 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 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $999,239 $1,025,173 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 $2,695,673 $2,878,958 $3,062,783 $3,259,510 $3,444,367 $3,545,631 $3,660,001 $3,582,658 $3,710,968 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 $1,209,273 $1,290,226 $1,347,947 $1,442,342 $1,567,654 $1,585,602 $1,640,426 $1,503,538 $1,572,302 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 $457,973 $538,926 $596,647 $691,042 $816,354 $834,302 $889,126 $752,238 $821,002 Road Fund Balance $624,510 $1,163,436 $1,760,083 $2,451,125 $3,267,479 $4,101,781 $4,990,907 $5,743,145 $6,564,147 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 $8,083,431 $2,797,326 $3,315,094 $4,462,377 $5,195,233 $5,325,293 $6,344,124 $5,997,063 $6,454,940 ALL FUNDS BALANCE $9,497,074 $12,294,400 $15,609,494 $20,071,871 $25,267,104 $30,592,397 $36,936,521 $42,933,584 $49,388,524 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 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $999,239 $1,025,173 Road Fund property tax $1,381,265 $1,521,288 $1,661,739 $1,811,892 $1,953,166 $2,031,769 $2,120,226 $2,140,696 $2,212,609 Total Road Fund Revenues $2,559,813 $2,699,836 $2,840,287 $2,990,440 $3,131,714 $3,210,317 $3,298,774 $3,139,935 $3,237,782 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 $1,156,105 $1,219,379 $1,265,252 $1,338,656 $1,435,271 $1,451,387 $1,495,185 $1,291,687 $1,344,875 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 $404,805 $468,079 $513,952 $587,356 $683,971 $700,087 $743,885 $540,387 $593,575 Road Fund Balance $571,342 $1,039,421 $1,553,373 $2,140,729 $2,824,700 $3,524,787 $4,268,672 $4,809,059 $5,402,634 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 $7,300,665 $2,093,458 $2,501,388 $3,572,335 $4,045,085 $4,168,542 $5,106,559 $4,444,001 $4,780,645 ALL FUNDS BALANCE $8,714,308 $10,807,766 $13,309,154 $16,881,489 $20,926,574 $25,095,116 $30,201,675 $34,645,676 $39,426,321 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 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $999,239 $1,025,173 Road Fund property tax $1,245,405 $1,342,166 $1,439,243 $1,542,822 $1,640,513 $1,696,455 $1,758,999 $1,697,973 $1,739,423 Total Road Fund Revenues $2,423,953 $2,520,714 $2,617,791 $2,721,370 $2,819,061 $2,875,003 $2,937,547 $2,697,212 $2,764,596 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 $1,102,937 $1,148,532 $1,182,557 $1,234,970 $1,302,888 $1,317,172 $1,349,944 $1,079,836 $1,117,448 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 $351,637 $397,232 $431,257 $483,670 $551,588 $565,872 $598,644 $328,536 $366,148 Road Fund Balance $518,105 $915,337 $1,346,594 $1,830,264 $2,381,852 $2,947,724 $3,546,368 $3,874,904 $4,241,052 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 $6,517,899 $1,389,590 $1,687,682 $2,682,293 $2,894,937 $3,011,791 $3,868,994 $2,890,939 $3,106,350 ALL FUNDS BALANCE $7,931,473 $9,321,063 $11,008,745 $13,691,038 $16,585,975 $19,597,766 $23,466,760 $26,357,699 $29,464,049 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 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $999,239 $1,025,173 Road Fund property tax $1,109,545 $1,163,044 $1,216,747 $1,273,752 $1,327,860 $1,361,141 $1,397,772 $1,255,250 $1,266,237 Total Road Fund Revenues $2,288,093 $2,341,592 $2,395,295 $2,452,300 $2,506,408 $2,539,689 $2,576,320 $2,254,489 $2,291,410 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 $1,049,769 $1,077,685 $1,099,862 $1,131,284 $1,170,505 $1,182,957 $1,204,703 $867,985 $890,021 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 $298,469 $326,385 $348,562 $379,984 $419,205 $431,657 $453,403 $116,685 $138,721 Road Fund Balance $464,868 $791,253 $1,139,815 $1,519,799 $1,939,004 $2,370,661 $2,824,064 $2,940,749 $3,079,470 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 $5,735,133 $685,722 $873,976 $1,792,251 $1,744,789 $1,855,040 $2,631,429 $1,337,877 $1,432,055 ALL FUNDS BALANCE $7,148,638 $7,834,360 $8,708,336 $10,500,587 $12,245,376 $14,100,416 $16,731,845 $18,069,722 $19,501,777 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 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $1,178,548 $999,239 $1,025,173 Road Fund property tax $973,685 $983,922 $994,251 $1,004,682 $1,015,207 $1,025,827 $1,036,545 $812,527 $793,051 Total Road Fund Revenues $2,152,233 $2,162,470 $2,172,799 $2,183,230 $2,193,755 $2,204,375 $2,215,093 $1,811,766 $1,818,224 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 $996,601 $1,006,838 $1,017,167 $1,027,598 $1,038,122 $1,048,742 $1,059,462 $656,134 $662,594 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 $245,301 $255,538 $265,867 $276,298 $286,822 $297,442 $308,162 -$95,166 -$88,706 Road Fund Balance $411,631 $667,169 $933,036 $1,209,334 $1,496,156 $1,793,598 $2,101,760 $2,006,594 $1,917,888 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 $4,952,367 -$18,146 $60,270 $902,209 $594,641 $698,289 $1,393,864 -$215,185 -$242,240 ALL FUNDS BALANCE $6,365,803 $6,347,657 $6,407,927 $7,310,136 $7,904,777 $8,603,066 $3,187,462 $1,886,575 $1,764,354 34 rows selected. SQL> @ip_gas_updated