spool /private/sundry/ip_cfa.log set echo on -- Updated incorporation fiscal projections. -- -- Project fiscal results of El Dorado Hills incorporation -- if it had occurred in 2006 as a result of a Yes vote on 2005 Measure P. -- Both versions of this script account for enactment of AB 1602, -- which restored historic VLF revenue to cities incorporated -- between 2004 and 2007. -- -- This version, ip_cfa, uses strictly CFA data, -- use it to validate sql and Perl logic by comparing -- results directly with CFA tables E-1, E-3, and B-1. -- -- An alternate version, ip_gas_updated, factors in doubling -- of fuel tax revenues for the Road Fund between the 2004 data -- used in the CFA and generation of these results in 2008. -- set serveroutput on set linesize 160 set pagesize 100 set numformat $99,999,999 column "Item" justify center column "2007" justify center column "2008" justify center column "2009" justify center column "2010" justify center column "2011" justify center column "2012" justify center column "2013" justify center column "2014" justify center column "2015" justify center drop table a1; drop table e1; drop table e2; drop table e3; -- e1 is minus0 -- e3 is minus25 drop table minus50; drop table minus75; drop table minus100; drop table delta25; create table a1 ( rowcode number, "Item" varchar2(36), "2007" number, "2008" number, "2009" number, "2010" number, "2011" number, "2012" number, "2013" number, "2014" number, "2015" number); create table e1 as select * from a1; create or replace synonym minus0 for e1; create table e2 as select * from a1; create table e3 as select * from a1; create or replace synonym minus25 for e3; create table minus50 as select * from a1; create table minus75 as select * from a1; create table minus100 as select * from a1; create table delta25 as select * from a1; insert into minus0 values ( 0, 'GENERAL FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 1, ' Total General Fund Revenues', 14864353, 17335665, 18631512, 19908476, 20881445, 21434854, 21926657, 22064878, 22759987 ); insert into minus0 values ( 2, ' Total General Fund Expenditures', 6713588, 14551958, 15387757, 15611834, 15977259, 16418556, 15946352, 16294745, 16600742 ); insert into minus0 values ( 3, ' General Fund Operating Surplus', 8150765, 2783707, 3243754, 4296642, 4904186, 5016298, 5980305, 5770132, 6159245 ); -- Memo: -- Remember the sign is already incorporated in the data -- for the revenue neutrality payments, they're negative. -- They need to be ADDED to the operating surplus/deficit insert into minus0 values ( 4, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 5, ' Revenue Neutrality + Fire payments', -525307, -525307, -525307, -525307, -525307, -525307, -525307, -525307, -525307 ); insert into minus0 values ( 5.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus0 a, minus0 b where a.rowcode = 3 and b.rowcode = 5 ); -- Iterate to derive annual General Fund balances. -- Initial 'seed' amount is $1,247,106. Start with each year's -- net results (surplus/deficit) in each column, then accumulate -- previous balances from earlier years. -- insert into minus0 ( select 6.5, 'General Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus0 where rowcode = 6 ); update minus0 set "2007" = "2007" + 1247106 where rowcode = 6.5; update minus0 set "2008" = "2007" + "2008" where rowcode = 6.5; update minus0 set "2009" = "2008" + "2009" where rowcode = 6.5; update minus0 set "2010" = "2009" + "2010" where rowcode = 6.5; update minus0 set "2011" = "2010" + "2011" where rowcode = 6.5; update minus0 set "2012" = "2011" + "2012" where rowcode = 6.5; update minus0 set "2013" = "2012" + "2013" where rowcode = 6.5; update minus0 set "2014" = "2013" + "2014" where rowcode = 6.5; update minus0 set "2015" = "2014" + "2015" where rowcode = 6.5; insert into minus0 values ( 7, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 8, 'ROAD MAINTENANCE FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); -- Original CFA gas tax data, not forecasting increase in retail gas prices -- Why do revenues decrease in 2014 & 2015? -- -- insert into minus0 values -- ( 9, ' Gas sales tax', -- 1178548, 1178548, 1178548, 1178548, 1178548, -- 1178548, 1178548, 999239, 1025173, ); -- -- CFA data should be corrected for retail gas prices increase since 2004, -- and consequent increase in fuel sales tax revenues. -- State Board of Equalization projects 2008 revenues to be slightly -- over double those from 2004, when the CFA data was finalized. -- Both the CFA and this projection assume 0 growth in fuel tax revenues -- in future years. This is extremely conservative and probably -- is unrealistically low for projections to 2015. -- -- Values for 2004 retail fuel tax collections: -- -- -- Values for fuel tax revenue corrected to correspond to actual data for -- years 2007 and using the Department of Equalization's -- projection for 2008 for all following years. -- Correction factors are the ratio of state-reported fuel sales tax -- receipts to 2004 fuel sales tax receipts. Base numbers are the -- CFA projections for the city's share of this revenue. ----insert into minus0 values ----( 9, ' Gas sales tax', ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), ---- 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); ------Use uncompensated CFA values insert into minus0 values ( 9, ' Gas sales tax', 1178548, 1178548, 1178548, 1178548, 1178548, 1178548, 1178548, 999239, 1025173 ); insert into minus0 values ( 10, ' Road Fund property tax', 1517125, 1700410, 1884235, 2080962, 2265819, 2367083, 2481453, 2583419, 2685795 ); insert into minus0 ( select 11, ' Total Road Fund Revenues', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus0 a, minus0 b where a.rowcode = 9 and b.rowcode = 10 ); insert into minus0 values ( 12, ' Total Road Fund expenses', 1486400, 1588732, 1714836, 1817168, 1876713, 1960029, 2019575, 2079120, 2138666 ); insert into minus0 ( select 13, ' Total Road Fund Operating Surplus', a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" from minus0 a, minus0 b where a.rowcode = 11 and b.rowcode = 12 ); -- Memo: -- Remember the sign is already incorporated in the data -- for the revenue neutrality payments, they're negative. -- They need to be ADDED to the operating surplus/deficit insert into minus0 values ( 14, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 15, ' Revenue Neutrality payments', -751300, -751300, -751300, -751300, -751300, -751300, -751300, -751300, -751300 ); insert into minus0 values ( 15.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus0 a, minus0 b where a.rowcode = 13 and b.rowcode = 15 ); -- Iterate to derive annual Road Fund balances. -- Initial 'seed' amount is $166,537. Start with each year's -- net results (surplus/deficit) in each column, then accumulate -- previous balances from earlier years. -- insert into minus0 ( select 16.5, 'Road Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus0 where rowcode = 16 ); update minus0 set "2007" = "2007" + 166537 where rowcode = 16.5; update minus0 set "2008" = "2007" + "2008" where rowcode = 16.5; update minus0 set "2009" = "2008" + "2009" where rowcode = 16.5; update minus0 set "2010" = "2009" + "2010" where rowcode = 16.5; update minus0 set "2011" = "2010" + "2011" where rowcode = 16.5; update minus0 set "2012" = "2011" + "2012" where rowcode = 16.5; update minus0 set "2013" = "2012" + "2013" where rowcode = 16.5; update minus0 set "2014" = "2013" + "2014" where rowcode = 16.5; update minus0 set "2015" = "2014" + "2015" where rowcode = 16.5; insert into minus0 values ( 17, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 18, 'OTHER RESTRICTED FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 values ( 19, ' Revenue; payments = revenue', 829756, 4743357, 5498666, 5209944, 4415583, 2065252, 4670891, 4776530, 4882169 ); insert into minus0 values ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus0 a, minus0 b where a.rowcode = 6 and b.rowcode = 16 ); insert into minus0 values ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus0 ( select 23, 'ALL FUNDS BALANCE', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus0 a, minus0 b where a.rowcode = 6.5 and b.rowcode = 16.5 ); commit; insert into minus25 values ( 0, 'GENERAL FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 1, ' Total General Fund Revenues', 13978841, 16126410, 17113461, 17966793, 18777319, 19222097, 19624291, 19442255, 19976612 ); insert into minus25 values ( 2, ' Total General Fund Expenditures', 6557674, 13975724, 14600718, 14456507, 14890897, 15228336, 14736310, 15013333, 15264235 ); insert into minus25 values ( 3, ' General Fund Operating Surplus', 7421167, 2150686, 2512743, 3510286, 3886421, 3993762, 4887981, 4428921, 4712377 ); insert into minus25 values ( 4, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 5, ' Revenue Neutrality + Fire payments', -525307, -525307, -525307, -525307, -525307, -525307, -525307, -525307, -525307 ); insert into minus25 values ( 5.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus25 a, minus25 b where a.rowcode = 3 and b.rowcode = 5 ); -- Iterate to derive annual General Fund balance. -- Initial 'seed' amount is $1,247,106. Start with each year's -- net results (surplus/deficit) in each column, then accumulate -- previous balances from earlier years. -- insert into minus25 ( select 6.5, 'General Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus25 where rowcode = 6 ); update minus25 set "2007" = "2007" + 1247106 where rowcode = 6.5; update minus25 set "2008" = "2007" + "2008" where rowcode = 6.5; update minus25 set "2009" = "2008" + "2009" where rowcode = 6.5; update minus25 set "2010" = "2009" + "2010" where rowcode = 6.5; update minus25 set "2011" = "2010" + "2011" where rowcode = 6.5; update minus25 set "2012" = "2011" + "2012" where rowcode = 6.5; update minus25 set "2013" = "2012" + "2013" where rowcode = 6.5; update minus25 set "2014" = "2013" + "2014" where rowcode = 6.5; update minus25 set "2015" = "2014" + "2015" where rowcode = 6.5; insert into minus25 values ( 7, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 8, 'ROAD MAINTENANCE FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); ----insert into minus25 values ----( 9, ' Gas sales tax', ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), ---- 1178548*(4.9/2.5), 1178548*(4.9/2.5), 1178548*(4.9/2.5), ---- 1178548*(4.9/2.5), 999239*(4.9/2.5), 1025173*(4.9/2.5) ); ------Use uncompensated CFA values insert into minus25 values ( 9, ' Gas sales tax', 1178548, 1178548, 1178548, 1178548, 1178548, 1178548, 1178548, 999239, 1025173 ); -- 25%-reduced property tax values are derived from baseline in Table B-1 -- and total Road Fund revenues reported in Table E-3. Derivation is -- based on total_revenue = property_tax_revenue + gas_sales_tax_revenue, -- with gas sales tax revenue not dependent on growth, property tax -- revenue variant depending on growth. (Derivation done manually, -- results inserted below.) insert into minus25 values ( 10, ' Road Fund property tax', 1381265, 1521288, 1661739, 1811892, 1953166, 2031769, 2120226, 2140696, 2212609 ); insert into minus25 ( select 11, ' Total Road Fund Revenues', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus25 a, minus25 b where a.rowcode = 9 and b.rowcode = 10 ); -- CFA shows that decreased growth decreases Road Fund expenses. insert into minus25 values ( 12, ' Total Road Fund expenses', 1403708, 1480457, 1575035, 1651784, 1696443, 1758930, 1803589, 1848248, 1892907 ); insert into minus25 ( select 13, ' Total Road Fund Operating Surplus', a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" from minus25 a, minus25 b where a.rowcode = 11 and b.rowcode = 12 ); -- Memo: -- Remember the sign is already incorporated in the data -- for the revenue neutrality payments, they're negative. -- They need to be ADDED to the operating surplus/deficit insert into minus25 values ( 14, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 15, ' Revenue Neutrality payments', -751300, -751300, -751300, -751300, -751300, -751300, -751300, -751300, -751300 ); insert into minus25 values ( 15.5, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus25 a, minus25 b where a.rowcode = 13 and b.rowcode = 15 ); -- Iterate to derive annual Road Fund balances. -- Initial 'seed' amount is $166,537. Start with each year's -- net results (surplus/deficit) in each column, then accumulate -- previous balances from earlier years. -- -- There's a minor issue to resolve in the "seed" number for higher -- percentages of reduced growth. At 0% reduction it's $166,606, at 25% -- it's $166,537. This is the difference between the initial FY 2007 -- road fund balance and the net revenue for FY 2007. The equicalent -- seed funding for the General fund is identical for 0% and 25% reduced -- growth. It's not clear whether the Road Fund variance in this amount -- is a function of growth reduction or whether it may be an algorithmic -- artifact such as quantization error in deriving this amout. In -- any case the difference is small, $69. Remaining projections for -- 50%, 75%, and 100% reduced growth will subtract an additional $69 -- for each 25% reduction in growth. -- insert into minus25 ( select 16.5, 'Road Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus25 where rowcode = 16 ); update minus25 set "2007" = "2007" + 166537 where rowcode = 16.5; update minus25 set "2008" = "2007" + "2008" where rowcode = 16.5; update minus25 set "2009" = "2008" + "2009" where rowcode = 16.5; update minus25 set "2010" = "2009" + "2010" where rowcode = 16.5; update minus25 set "2011" = "2010" + "2011" where rowcode = 16.5; update minus25 set "2012" = "2011" + "2012" where rowcode = 16.5; update minus25 set "2013" = "2012" + "2013" where rowcode = 16.5; update minus25 set "2014" = "2013" + "2014" where rowcode = 16.5; update minus25 set "2015" = "2014" + "2015" where rowcode = 16.5; insert into minus25 values ( 17, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 18, 'OTHER RESTRICTED FUND', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 values ( 19, ' Revenue; payments = revenue', 755283, 4046440, 4612965, 4396423, 3800696, 4287948, 3992221, 4071494, 4150767 ); insert into minus25 values ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus25 a, minus25 b where a.rowcode = 6 and b.rowcode = 16 ); insert into minus25 values ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus25 ( select 23, 'ALL FUNDS BALANCE', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus25 a, minus25 b where a.rowcode = 6.5 and b.rowcode = 16.5 ); commit; insert into delta25 (select a.rowcode, a."Item", a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" from minus0 a, minus25 b where a.rowcode = b.rowcode ); delete delta25 where rowcode = 6; delete delta25 where rowcode = 6.5; delete delta25 where rowcode = 11; delete delta25 where rowcode = 13; delete delta25 where rowcode = 16; delete delta25 where rowcode = 16.5; delete delta25 where rowcode = 21; delete delta25 where rowcode = 23; select * from delta25; commit; -- Generate data for 50% reduced growth by applying delta between -- 0% and 25% reduced growth to 25% reduced growth data. -- Recompute dependent data such as subtotals that depend on -- data subject to growth deltas. insert into minus50 (select a.rowcode, a."Item", a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" from minus25 a, delta25 b where a.rowcode = b.rowcode ); insert into minus50 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 3 and b.rowcode = 5 ); insert into minus50 ( select 6.5, 'General Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus50 where rowcode = 6 ); update minus50 set "2007" = "2007" + 1247106 where rowcode = 6.5; update minus50 set "2008" = "2007" + "2008" where rowcode = 6.5; update minus50 set "2009" = "2008" + "2009" where rowcode = 6.5; update minus50 set "2010" = "2009" + "2010" where rowcode = 6.5; update minus50 set "2011" = "2010" + "2011" where rowcode = 6.5; update minus50 set "2012" = "2011" + "2012" where rowcode = 6.5; update minus50 set "2013" = "2012" + "2013" where rowcode = 6.5; update minus50 set "2014" = "2013" + "2014" where rowcode = 6.5; update minus50 set "2015" = "2014" + "2015" where rowcode = 6.5; insert into minus50 ( select 11, 'Total Road Fund Revenues', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 9 and b.rowcode = 10 ); insert into minus50 ( select 13, ' Total Road Fund Operating Surplus', a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" from minus50 a, minus50 b where a.rowcode = 11 and b.rowcode = 12 ); insert into minus50 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 13 and b.rowcode = 15 ); insert into minus50 ( select 16.5, 'Road Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus50 where rowcode = 16 ); update minus50 set "2007" = "2007" + 166468 where rowcode = 16.5; update minus50 set "2008" = "2007" + "2008" where rowcode = 16.5; update minus50 set "2009" = "2008" + "2009" where rowcode = 16.5; update minus50 set "2010" = "2009" + "2010" where rowcode = 16.5; update minus50 set "2011" = "2010" + "2011" where rowcode = 16.5; update minus50 set "2012" = "2011" + "2012" where rowcode = 16.5; update minus50 set "2013" = "2012" + "2013" where rowcode = 16.5; update minus50 set "2014" = "2013" + "2014" where rowcode = 16.5; update minus50 set "2015" = "2014" + "2015" where rowcode = 16.5; insert into minus50 values ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus50 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 6 and b.rowcode = 16 ); insert into minus50 values ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus50 ( select 23, 'ALL FUNDS BALANCE', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 6.5 and b.rowcode = 16.5 ); commit; -- Derive data for 75% reduced growth, same technique as for deriving 50% insert into minus75 (select a.rowcode, a."Item", a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" from minus50 a, delta25 b where a.rowcode = b.rowcode ); insert into minus75 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus75 a, minus75 b where a.rowcode = 3 and b.rowcode = 5 ); insert into minus50 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus50 a, minus50 b where a.rowcode = 3 and b.rowcode = 5 ); insert into minus75 ( select 6.5, 'General Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus75 where rowcode = 6 ); update minus75 set "2007" = "2007" + 1247106 where rowcode = 6.5; update minus75 set "2008" = "2007" + "2008" where rowcode = 6.5; update minus75 set "2009" = "2008" + "2009" where rowcode = 6.5; update minus75 set "2010" = "2009" + "2010" where rowcode = 6.5; update minus75 set "2011" = "2010" + "2011" where rowcode = 6.5; update minus75 set "2012" = "2011" + "2012" where rowcode = 6.5; update minus75 set "2013" = "2012" + "2013" where rowcode = 6.5; update minus75 set "2014" = "2013" + "2014" where rowcode = 6.5; update minus75 set "2015" = "2014" + "2015" where rowcode = 6.5; insert into minus75 ( select 11, 'Total Road Fund Revenues', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus75 a, minus75 b where a.rowcode = 9 and b.rowcode = 10 ); insert into minus75 ( select 13, ' Total Road Fund Operating Surplus', a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" from minus75 a, minus75 b where a.rowcode = 11 and b.rowcode = 12 ); insert into minus75 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus75 a, minus75 b where a.rowcode = 13 and b.rowcode = 15 ); insert into minus75 ( select 16.5, 'Road Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus75 where rowcode = 16 ); update minus75 set "2007" = "2007" + 166399 where rowcode = 16.5; update minus75 set "2008" = "2007" + "2008" where rowcode = 16.5; update minus75 set "2009" = "2008" + "2009" where rowcode = 16.5; update minus75 set "2010" = "2009" + "2010" where rowcode = 16.5; update minus75 set "2011" = "2010" + "2011" where rowcode = 16.5; update minus75 set "2012" = "2011" + "2012" where rowcode = 16.5; update minus75 set "2013" = "2012" + "2013" where rowcode = 16.5; update minus75 set "2014" = "2013" + "2014" where rowcode = 16.5; update minus75 set "2015" = "2014" + "2015" where rowcode = 16.5; insert into minus75 values ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus75 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus75 a, minus75 b where a.rowcode = 6 and b.rowcode = 16 ); insert into minus75 values ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus75 ( select 23, 'ALL FUNDS BALANCE', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus75 a, minus75 b where a.rowcode = 6.5 and b.rowcode = 16.5 ); commit; -- Derive data for 100% reduced growth, same technique as for -- deriving 50% and 75% insert into minus100 (select a.rowcode, a."Item", a."2007"-b."2007", a."2008"-b."2008", a."2009"-b."2009", a."2010"-b."2010", a."2011"-b."2011", a."2012"-b."2012", a."2013"-b."2013", a."2014"-b."2014", a."2015"-b."2015" from minus75 a, delta25 b where a.rowcode = b.rowcode ); insert into minus100 ( select 6, 'GF surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus100 a, minus100 b where a.rowcode = 3 and b.rowcode = 5 ); insert into minus100 ( select 6.5, 'General Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus100 where rowcode = 6 ); update minus100 set "2007" = "2007" + 1247106 where rowcode = 6.5; update minus100 set "2008" = "2007" + "2008" where rowcode = 6.5; update minus100 set "2009" = "2008" + "2009" where rowcode = 6.5; update minus100 set "2010" = "2009" + "2010" where rowcode = 6.5; update minus100 set "2011" = "2010" + "2011" where rowcode = 6.5; update minus100 set "2012" = "2011" + "2012" where rowcode = 6.5; insert into minus100 ( select 11, 'Total Road Fund Revenues', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus100 a, minus100 b where a.rowcode = 9 and b.rowcode = 10 ); insert into minus100 ( select 13, ' Total Road Fund Operating Surplus', a."2007" - b."2007", a."2008" - b."2008", a."2009" - b."2009", a."2010" - b."2010", a."2011" - b."2011", a."2012" - b."2012", a."2013" - b."2013", a."2014" - b."2014", a."2015" - b."2015" from minus100 a, minus100 b where a.rowcode = 11 and b.rowcode = 12 ); insert into minus100 ( select 16, 'Rd Fnd surplus/deficit after RN pmts', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus100 a, minus100 b where a.rowcode = 13 and b.rowcode = 15 ); insert into minus100 ( select 16.5, 'Road Fund Balance', "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus100 where rowcode = 16 ); update minus100 set "2007" = "2007" + 166330 where rowcode = 16.5; update minus100 set "2008" = "2007" + "2008" where rowcode = 16.5; update minus100 set "2009" = "2008" + "2009" where rowcode = 16.5; update minus100 set "2010" = "2009" + "2010" where rowcode = 16.5; update minus100 set "2011" = "2010" + "2011" where rowcode = 16.5; update minus100 set "2012" = "2011" + "2012" where rowcode = 16.5; update minus100 set "2013" = "2012" + "2013" where rowcode = 16.5; update minus100 set "2014" = "2013" + "2014" where rowcode = 16.5; update minus100 set "2015" = "2014" + "2015" where rowcode = 16.5; insert into minus100 values ( 20, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus100 ( select 21, 'ALL FUNDS OPERATING SURPLUS/DEFICIT', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus100 a, minus100 b where a.rowcode = 6 and b.rowcode = 16 ); insert into minus100 values ( 22, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); insert into minus100 ( select 23, 'ALL FUNDS BALANCE', a."2007" + b."2007", a."2008" + b."2008", a."2009" + b."2009", a."2010" + b."2010", a."2011" + b."2011", a."2012" + b."2012", a."2013" + b."2013", a."2014" + b."2014", a."2015" + b."2015" from minus100 a, minus100 b where a.rowcode = 6.5 and b.rowcode = 16.5 ); commit; -- Report. set echo off execute dbms_output.put_line( '=========================== Baseline Growth ===========================' ); select "Item", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus0 order by rowcode; execute dbms_output.put_line( '========================= 25% Reduced Growth =========================' ); select "Item", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus25 order by rowcode; execute dbms_output.put_line( '========================= 50% Reduced Growth =========================' ); select "Item", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus50 order by rowcode; execute dbms_output.put_line( '========================= 75% Reduced Growth =========================' ); select "Item", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus75 order by rowcode; execute dbms_output.put_line( '============================= Zero Growth =============================' ); select "Item", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015" from minus100 order by rowcode;