Document Laramie_doc_f8ab8c57da

DRAFT MEMORANDUM To: Richard Elliott, Director of Public Works Date: May 5, 2009 City of Laramie Malea Brown, Director of Administrative Services City of Laramie From: Dennis Jackson, Senior Consultant Red Oak Consulting Re: Water and Wastewater Rate and Fee Study Financial Plan Technical Memorandum Red Oak is pleased to provide this fifth-draft financial plan technical memorandum for the City of Laramie water and wastewater utilities. This version includes the City’s comments and changes received over the last several months, including: • A revised capital improvement plan, and funding plan • Evaluation of performance metrics, including renewal and replacement investment, and staffing levels • Analysis of the City’s indirect expenses • Analysis of Monolith Ranch cost allocations • Projections for vehicle replacement costs • Revised beginning fund balances • Reconciliation of revenue projections under existing rates The purpose of the financial plan is to examine the utilities as separate enterprise operations, and to establish the future revenue requirements for each utility. In addition, Red Oak developed a projection of cash flow and debt service coverage for the combined utilities over the ten-year study period. In this memorandum and attached tables, the term Fiscal Year is abbreviated “FY” and indicates the fiscal year beginning July 1 and ending June 30 (e.g. FY2009 or FY2009-10 means the fiscal year beginning July 1, 2009 and ending June 30, 2010). General Assumptions: The results provided in this memorandum are based on a number of assumptions as shown in Table 1. Changes made to these assumptions may materially affect the results. ---PAGE BREAK--- DRAFT Richard Elliott Page 2 Director of Public Works Table 1 General Assumptions Description Unit Growth Rate (percent per year) Water/Wastewater 0.5% Inflation O&M expenses Routine capital expenses, including vehicles CIP 3.5% 4.0% 10.0% Proposed Revenue Bond Terms Period, years Issuance costs Interest rate 20 3.0% 5.0% Proposed State and Other Loan Terms Period, years Issuance costs Interest rate 20 1.0% 4.0% Debt Service Coverage Requirements Water Wastewater 1.20 1.20 Number of Months Rates Effective 5 months effective means rates are adopted each January) 5 months Beginning Fund Balances and Target Reserves: Beginning fund balances consists of the total cash balance available at the beginning of fiscal year, less restricted cash reserves. The City maintains restricted cash reserves consisting of the following items: • Accrued vacation pay • One year of debt service payments • 90 days of operation and maintenance expenses • Investments (restricted amount) • One percent of the original cost of fixed assets (capital fund reserve) These restricted reserves are maintained as targets throughout the study period. Beginning fund balances and restricted cash reserves for FY2008-09 are shown in Table 2. ---PAGE BREAK--- DRAFT Richard Elliott Page 3 Director of Public Works Table 2 Beginning Fund Balances and Restricted Reserves - FY2008-09 Description Water Amount Wastewater Amount Unrestricted Cash $6,369,268 $3,719,839 Accrued Vacation Pay (99,572) (34,810) One year of Annual Debt Service Payments (1,384,000) (931,000) 90 days of O&M Expenses (868,000) (470,000) Investments (restricted) (393,800) (1,000,000) One Percent of Fixed Assets (Transfer to Capital Improvement Fund) (720,000) (313,000) Total Available Beginning Fund Balances $2,903,869 $971,029 In addition to the restricted reserves shown above, the financial plan maintains a 90 day reserve in the operating fund, and one percent of fixed assets in the capital fund. Investment Income Investment income is calculated based on the average annual fund balances and a 0.5 percent annual interest rate. Given the current economic environment, the 0.5 percent interest rate reflects the interest rate on low risk, short term cash investments. City Indirect Expenses Red Oak reviewed the City’s indirect cost allocations to the water and sewer funds, without performing a detailed indirect cost study. By definition, indirect costs are not directly associated with the delivery of a specific service. They are general rather than specific, and it can be difficult to determine the appropriate apportionment of indirect costs among several varied services. Within the City’s organization, there are internal service departments, external service departments, and business-type activities. Most internal services are non-revenue services provided by the City Council, City Manager, Administration, Legal, and General Government (including Finance, Public Works, Human Resources, Information Technology Services, and Maintenance services). External services include Police, Fire, Engineering/Planning, Highways and Streets, Parks and Recreation, Animal Control and Mosquito Control. Business-type activities include water, sewer, and solid waste services. One of the more difficult methodological problems is identification of which internal services should considered beneficial to external services or business-type activities, and how the cost of such benefits should be allocated. Attached Table 1-3 provides the City’s transfer cost analysis. By isolating the cost of each service, the City has distinguished the beneficiary of that service and the cost in relationship to the benefits derived. Based on City-derived allocations, the total overhead charges to the three enterprise funds total $901,876. A portion of this amount ($383,922) is allocated to the enterprise funds, and offset by unmetered/metered water charged to the general fund ($352,875), leaving a remaining overhead in the amount of $163,268 not charged to the enterprise funds. The City- derived transfers to the General Fund for indirect expenses are summarized in Table 3. ---PAGE BREAK--- DRAFT Richard Elliott Page 4 Director of Public Works Table 3 City Transfers to the General Fund for Indirect Expenses (FY2008-09) Enterprise Fund Indirect Expenses Water Utility $187,100 Wastewater Utility $102,700 The transfers shown in Table 3 do not recover remaining overhead not charged to the enterprise funds in the amount of $163,268. Without recovering the full cost of internal services costs, external services receive a subsidy funded by taxes paid by all local citizens and businesses. Increasingly, municipal water and sewer utilities are paying the full cost of their indirect benefits. Recovering indirect costs through water and sewer rates charged to customers provides a more equitable relationship between the beneficiaries of the service water and sewer customers) and the residents or businesses paying for the service. An equitable relationship does not exist when tax monies are used to subsidize services which are received by a minority of the taxpaying public, or by non-residents. With appropriate cost allocation, the City can establish basic fairness and equity between users of City services and those who pay for them, and control those costs on a continuing basis, thus assuring taxpayers that their tax monies are being spent properly. General fund reimbursements vary widely depending on the type, size, and financial policies of City or utility – ranging from general fund subsidy of the utility to relatively large utility payments to support the general fund. Cities similar to Laramie, with financially independent utilities, well-developed financial policies, and similar City/utility service areas typically pay between 8 and 15 percent of the general fund internal services budget. The higher percentage applies to smaller cities, which typically provide more support to the utility, including billing, customer service, finance, and IT services. As a utility grows, this percentage will decrease, as the utility assumes more responsibilities internally. In the City of Laramie, 12 percent of the internal services operating expenses budget ($7,350,011 in FY2006-07) would be reasonable based on comparisons with similar utilities. This would amount to $882,000 in the expected cost for reimbursement to the General Fund. Red Oak recommends maintaining the current City transfer cost allocations, and proportionately allocating internal services expenses to the three enterprise funds (water, wastewater, and solid waste) based on their operating expenses. It should be noted that utilities should also reimburse the Highways and Streets division for repairing or repaving streets due to utility work or damage. By applying the full cost of internal services to the enterprise funds, Red Oak arrived at the following recommended transfers to the General Fund as shown in Table 4. ---PAGE BREAK--- DRAFT Richard Elliott Page 5 Director of Public Works Table 4 Red Oak Recommended Transfers to the General Fund for Indirect Expenses (FY2008-09) Enterprise Fund Indirect Expenses Water Utility Less water charges to General Fund Water Utility - Net Transfer $450,347 (365,226) $85,121 Wastewater Utility $255,126 The supporting calculations are provided in attached Table 1-3: Transfer Cost Analysis. For a more accurate calculation of indirect costs and benefits, Red Oak recommends that the City conduct an indirect cost allocation study using an outside consultant. These studies should be performed periodically and will allow the City to revise its assumptions over time. Monolith Ranch Expenses The City acquired Monolith Ranch in the early 1980's with an eye on increasing the City's water supply. More recently, the City’s water rights study prepared by Fassett Consulting, LLC (Fassett, 2004) and the Laramie Water Management Plan prepared by WWC Engineering (WWC, 2006) noted that “Beginning many years ago, City officials made decisions and investments for the long-term security of a reliable water supply, most notably, the purchase of land and water rights associated with the Monolith Ranch.” The intended purpose of the Monolith Ranch is to add long term capacity, security, and reliability to the City’s water supply. For this reason, revenues or expenses associated with Monolith Ranch should continue to accrue to the water enterprise fund and not the general fund. In years with expenses that exceed revenues associated with the ranch, these expenses can only be recovered by two sources – water rates or plant investment fees. To the extent that the Monolith Ranch provides water supplies to future growth within the City, such costs could potentially be recovered through an additional water resources fee charged to new development. To the extent that Monolith Ranch provides additional security and reliability for existing supplies, such costs should be recovered from existing customers through water rates. Red Oak recommends that the City: • Continue to include Monolith Ranch within the water enterprise fund. • Study the potential to charge new development a water resources fee to recover Monolith Ranch expenses. • Consider drought-surcharge water rates to recover Monolith Ranch expenses. Red Oak included the ongoing expense of owning and operating the Monolith Ranch in this rate study, beginning with $275,000 in FY2009 and increasing to $546,000 over the study period. ---PAGE BREAK--- DRAFT Richard Elliott Page 6 Director of Public Works Vehicles and Equipment Red Oak assumed vehicle replacement costs of $38,000 for a regular water or wastewater vehicle. Vehicle costs were provided by the City and inflated at 4 percent per annum. Red Oak assumed the City would lease purchase major equipment over a three-year period, at a 4.5 percent interest rate. These projections are provided in the attached Tables 2-7 and 3-7. Performance Benchmarks In addition to the financial performance metrics described above, Red Oak evaluated the performance of each utility relative national averages published by the American Water Works Association (AWWA).1 In 2004, the utilities participated in the QualServe Benchmarking Performance Program sponsored by AWWA. Twenty-three performance benchmarks were initially selected for evaluation. A number of the financial performance benchmarks are inherently included in this study debt ratio, return on assets). In this study, the financial impact of the following two benchmarks was evaluated for each utility: • Employee Efficiency o Customer Accounts Per Employee (water and wastewater) o MGD Water Delivered Per Employee o MGD Wastewater Processed Per Employee. • Utility Renewal and Replacement o Water System Renewal/Replacement Rate o Wastewater System Renewal/Replacement Rate The utility has approximately 8,700 accounts served by 18.5 full-time equivalent (FTE) water employees and 12.5 wastewater employees. Part-time and seasonal employees are converted to FTEs based on the total number of compensated hours. Employee time from engineering and construction of new facilities are not included in this benchmark. To achieve the employee efficiency benchmark median, the City will need to add four water FTEs, and seven wastewater FTEs over the 10-year study period. These projections are summarized in Table 5 below. The costs of additional FTEs have been included in this financial plan. 1 Benchmarking Performance Indicators for Water and Wastewater Utilities: 2007 Annual Survey Data and Analyses Report, Published by American Water Works Association, Edition: 2008 - Softbound - 85 pp., ISBN [PHONE REDACTED]; Catalog No. 20674 ---PAGE BREAK--- DRAFT Richard Elliott Page 7 Director of Public Works Table 5 Water and Wastewater Employee Efficiency Year Water Utility Additional FTEs Wastewater Utility Additional FTEs 2009-10 1.0 1.0 2010-11 0.0 1.0 2011-12 0.0 1.0 2012-13 1.0 1.0 2013-14 0.0 1.0 2014-15 0.0 1.0 2015-16 1.0 1.0 2016-17 0.0 0.0 2017-18 1.0 0.0 2018-19 0.0 0.0 Total 4.0 7.0 These employee projections are provided in the attached Tables 2-8 and 3-8. The City has developed a capital plan that achieves the utility renewal and replacement benchmark median over the study period. No additional investments in the water transmission and distribution system or sewer collection system were required to reach the benchmark median. These projections are provided in the attached Tables 2-9 and 3-9. The costs of renewal and replacement investments have been included in this financial plan. Cash Flow Analysis Red Oak developed a 10-year financial plan for the water and wastewater utilities. For the purposes of this study, each utility’s financials have been divided into two funds, as follows: • Operating Fund • Capital Fund This assists in tracking operating-related financial activities and capital-related financial activities separately. User charge revenues should be sufficient to meet annual user charge revenue requirements, fund the capital replacement program, satisfy debt service coverage requirements, and provide adequate reserves. Red Oak developed one financial plan scenario for each utility that meet the following objectives: • Revenues are adjusted to meet annual revenue requirements and debt service coverage requirements. • Debt is issued as needed to support the capital improvement program. • The ending fund balance in the operating fund is maintained to meet minimum requirements. ---PAGE BREAK--- DRAFT Richard Elliott Page 8 Director of Public Works The attached tables 2-1 and 3-1 contain the consolidated cash flows for water and wastewater. Tables 6 and 7 below provide a summary of the annual revenue increases, debt issues, debt service coverage ratios, and ending fund balances. Table 6 Water Utility Fiscal Year Annual Revenue Increase Debt Issues Debt Service Coverage(a) Ending Operating Fund Balance(b) 2009 0% 1.42 $2,000,000 2010 19% 1.84 800,000 2011 17% $6,000,000 2.16 1,900,000 2012 17% $6,000,000 2.13 1,900,000 2013 17% $7,000,000 2.16 1,200,000 2014 17% $6,000,000 2.26 2,700,000 2015 17% $7,200,000 2.05 1,200,000 2016 8% $6,000,000 1.96 1,500,000 2017 5% $5,000,000 1.96 1,300,000 2018 0% $8,000,000 1.62 1,500,000 Minimum DSC coverage requirement is 1.20 times annual debt service. Minimum ending fund balance is equal to 90 days of O&M Expenses. Table 7 Wastewater Utility Fiscal Year Annual Increase Debt Issues Debt Service Coverage(a) Ending Operating Fund Balance(b) 2009 0% 1.59 $400,000 2010 19% 1.78 900,000 2011 18% $1,000,000 2.15 2,000,000 2012 17% $3,000,000 2.29 1,900,000 2013 17% 2.77 1,200,000 2014 17% 3.28 500,000 2015 17% 3.92 700,000 2016 12% 4.71 500,000 2017 0% 5.15 4,300,000 2018 0% 5.12 5,100,000 Minimum DSC coverage requirement is 1.20 times annual debt service. Minimum ending fund balance is equal to 90 days of O&M Expenses. ---PAGE BREAK--- DRAFT Richard Elliott Page 9 Director of Public Works The consolidated cash flows for the combined utility are provided in attached Table 1-1. Revenue Forecast and Customer Class Usage Characteristics Revenue projections are based on the City’s billing data for the calendar years 2004 through 2007. Red Oak identified usage trends, developed bill frequencies, and calculated rate revenue projections for the 10-year study period. This data was also used to develop customer class peaking factors for the cost of service allocations and rate design. Project Schedule and Next Steps In order to achieve the overall project schedule, it is understood that Red Oak and the City will first present the final financial plan and the proposed revenue increases (average across all customers) to internal staff and City officials City staff, Mayor, City Council, others), followed by presentation to the stakeholder committee. Subsequent activities are summarized below: • Finalize Financial Plan and Present to Stakeholder Committee o Stakeholder Advisory Committee, Meeting No. 1 • Cost of Service Analysis o Stakeholder Advisory Committee, Meeting No. 2 • Rate Design / Plant Investment Fees o Stakeholder Advisory Committee, Meeting No. 3 • Study Completed o Presentation / Worksession with City Council, Final Meeting • Rates Adopted • Rates Effective Proposed dates for the above activities are to be determined with the City. ---PAGE BREAK--- DRAFT Richard Elliott Page 10 Director of Public Works The following tables are attached to this memorandum: Combined Water and Wastewater Tables Table 1-1 Combined Funds - Projection of Cash Flow and Debt Service Coverage Table 1-2 Summary of Existing Debt Table 1-3 Indirect Transfer Cost Analysis Water Tables Table 2-1 Water Fund - Projection of Cash Flow and Debt Service Coverage Table 2-2 Operating Fund Cash Flow Table 2-3 Capital Fund Cash Flow Table 2-4 Water Fund Existing Debt Table 2-5 Capital Improvement Program Table 2-6 Operation and Maintenance Expenses Table 2-7 Vehicles Analysis Table 2-8 Employee Efficiency Analysis Table 2-9 Renewal and Replacement Analysis Wastewater Tables Table 3-1 Wastewater Fund - Projection of Cash Flow and Debt Service Coverage Table 3-2 Operating Fund Cash Flow Table 3-3 Capital Fund Cash Flow Table 3-4 Wastewater Fund Existing Debt Table 3-5 Capital Improvement Program Table 3-6 Projected Operation and Maintenance Expenses Table 3-7 Vehicles Analysis Table 3-8 Employee Efficiency Analysis Table 3-9 Renewal and Replacement Anaylsis ---PAGE BREAK--- DRAFT Table 1-1 Combined Water and Wastewater Utility Fund Cash Flow Analysis Projection of Cash Flow and Debt Service Coverage: With Proposed Revenue Increases Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 4,010,000 3,940,000 1,410,000 5,250,000 5,360,000 4,180,000 5,110,000 4,070,000 4,160,000 8,130,000 Revenues 2 Revenue from Existing Service Rates (water and wastewater) 8,930,774 8,931,600 8,938,568 8,942,158 8,945,693 8,948,572 8,958,003 8,959,954 8,965,120 8,962,759 3 Revenue from Impact Fees 400,000 400,000 400,000 400,000 400,000 400,000 400,000 400,000 400,000 400,000 4 Additional Service Revenue Required 0 707,085 2,468,896 4,433,962 6,710,596 9,375,249 12,503,490 15,428,492 17,068,454 17,514,366 5 Total Service Rate Revenue 9,330,774 10,038,685 11,807,464 13,776,120 16,056,289 18,723,821 21,861,493 24,788,446 26,433,574 26,877,125 Other Cash Inflows 6 Grants 1,350,000 8,000,000 4,231,250 2,267,375 1,550,000 2,834,393 3,017,839 1,000,000 1,000,000 1,000,000 7 Bond Proceeds 0 0 7,000,000 9,000,000 7,000,000 6,000,000 7,200,000 6,000,000 5,000,000 8,000,000 8 State & Other Loan Proceeds 290,000 3,090,000 620,000 30,000 120,000 903,507 993,861 0 0 0 9 Specific Purpose Tax Proceeds 541,213 0 0 2,500,000 3,050,000 1,000,000 1,000,000 1,000,000 0 0 10 Total Other Cash Inflows 2,181,213 11,090,000 11,851,250 13,797,375 11,720,000 10,737,900 12,211,700 8,000,000 6,000,000 9,000,000 11 Miscellaneous Revenue 279,276 279,276 279,276 279,276 279,276 279,276 279,276 279,276 279,276 279,276 12 Interest 159,162 103,162 96,473 126,003 115,820 131,090 154,758 122,721 209,418 144,349 13 Total Revenues 11,950,000 21,510,000 24,030,000 27,980,000 28,170,000 29,870,000 34,510,000 33,190,000 32,920,000 36,300,000 Revenue Requirements 14 Operation & Maintenance Expense 5,512,469 5,455,373 5,781,627 6,072,838 6,495,716 6,926,460 7,162,885 7,511,968 7,752,833 7,998,710 Debt Service 15 Existing 2,300,794 2,304,870 1,743,886 1,743,251 1,726,676 1,733,957 1,764,139 1,749,907 1,361,063 1,380,213 16 Proposed 0 0 866,943 1,591,371 2,161,871 2,709,853 3,938,383 4,901,383 5,703,783 6,987,583 17 Total Debt Service 2,300,794 2,304,870 2,610,829 3,334,622 3,888,547 4,443,810 5,702,522 6,651,290 7,064,846 8,367,796 18 Routine Capital 1,593,000 1,325,000 1,469,650 1,603,546 1,762,781 1,885,690 1,454,486 1,512,666 2,731,964 2,883,150 19 Total Revenue Requirements 9,410,000 9,090,000 9,860,000 11,010,000 12,150,000 13,260,000 14,320,000 15,680,000 17,550,000 19,250,000 20 Annual Surplus (Deficiency) 2,540,000 12,420,000 14,170,000 16,970,000 16,020,000 16,610,000 20,190,000 17,510,000 15,370,000 17,050,000 21 Ending Balance Before Other Cash Outflows 6,550,000 16,360,000 15,580,000 22,220,000 21,380,000 20,790,000 25,300,000 21,580,000 19,530,000 25,180,000 Other Cash Outflows 22 Capital Improvements 2,266,600 14,570,900 9,190,000 15,486,200 16,032,100 14,615,600 20,018,800 16,330,300 10,396,500 14,973,000 23 Indirect Expenses 340,247 352,156 364,481 377,238 390,442 404,107 418,251 432,890 448,041 463,722 24 Debt Service Reserve 0 0 561,700 722,200 561,700 481,500 577,700 481,500 401,200 641,900 25 Debt Issuance Expense 2,900 30,900 216,200 270,300 211,200 180,000 216,000 180,000 150,000 240,000 26 Total Other Cash Outflows 2,609,747 14,953,956 10,332,381 16,855,938 17,195,442 15,681,207 21,230,751 17,424,690 11,395,741 16,318,622 27 Ending Balance Excess (Shortfall) 3,940,000 1,410,000 5,250,000 5,360,000 4,180,000 5,110,000 4,070,000 4,160,000 8,130,000 8,860,000 28 Target Ending Balance 2,030,000 2,160,000 2,320,000 2,550,000 2,820,000 3,080,000 3,330,000 3,580,000 3,750,000 3,950,000 Revenue Bond Debt Service Coverage (target 120%) 29 Simple 167.6% 198.1% 229.9% 231.2% 245.7% 265.7% 258.4% 259.8% 265.7% 225.9% 30 Enhanced 185.0% 215.4% 245.2% 243.2% 256.0% 274.7% 265.4% 265.8% 271.3% 230.7% ---PAGE BREAK--- DRAFT Table 1-2 Water and Wastewater Utility Summary of Existing Debt Fiscal Year Ending June 30 Water Sewer Water Sewer WY WDC - Refi Loan WY WDC GO Refinancing GO Security 1st Bank Loan Loan Loan TOTAL TOTAL Year Series 2008 Series 2003 #008 Series 2001 Series 2000 Series 2001 Series 2002 Series 2005 Series 2008 #075 Series 1999 #015 Series 2003 #006 2008 187,854 $ 116,203 $ 75,876 $ 56,887 $ 390,638 $ 541,213 $ 18,408 $ - $ 57,444 $ 820,533 $ 1,387,078 $ 877,977 $ 2,265,054 $ 2009 172,607 $ 116,203 $ 75,876 $ 56,887 $ 385,529 $ 541,213 $ 18,408 $ 56,096 $ 57,444 $ 820,533 $ 1,366,722 $ 934,073 $ 2,300,794 $ 2010 195,893 $ 116,203 $ 75,876 $ 56,887 $ 384,726 $ 541,213 $ 56,096 $ 57,444 $ 820,533 $ 1,370,797 $ 934,073 $ 2,304,870 $ 2011 177,675 $ 116,203 $ 75,876 $ 56,887 $ 383,173 $ 56,096 $ 57,444 $ 820,533 $ 809,813 $ 934,073 $ 1,743,886 $ 2012 174,475 $ 116,203 $ 75,876 $ 56,887 $ 385,738 $ 56,096 $ 57,444 $ 820,533 $ 809,178 $ 934,073 $ 1,743,251 $ 2013 171,025 $ 116,203 $ 75,876 $ 56,887 $ 372,613 $ 56,096 $ 57,444 $ 820,533 $ 792,603 $ 934,073 $ 1,726,676 $ 2014 167,375 $ 116,203 $ 75,876 $ 56,887 $ 383,544 $ 56,096 $ 57,444 $ 820,533 $ 799,884 $ 934,073 $ 1,733,957 $ 2015 188,088 $ 116,203 $ 75,876 $ 56,887 $ 393,013 $ 56,096 $ 57,444 $ 820,533 $ 830,066 $ 934,073 $ 1,764,139 $ 2016 183,150 $ 116,203 $ 75,876 $ 56,887 $ 383,719 $ 56,096 $ 57,444 $ 820,533 $ 815,834 $ 934,073 $ 1,749,907 $ 2017 178,025 $ 116,203 $ 75,876 $ 56,887 $ 56,096 $ 57,444 $ 820,533 $ 426,991 $ 934,073 $ 1,361,063 $ 2018 197,175 $ 116,203 $ 75,876 $ 56,887 $ 56,096 $ 57,444 $ 820,533 $ 446,141 $ 934,073 $ 1,380,213 $ 2019 189,493 $ 116,203 $ 75,876 $ 56,887 $ 56,096 $ 57,444 $ 438,458 $ 113,540 $ 551,999 $ 2020 189,493 $ 116,203 $ 75,876 $ 55,718 $ 56,096 $ 57,444 $ 437,289 $ 113,540 $ 550,830 $ 2021 189,493 $ 116,203 $ 75,876 $ 56,096 $ 57,444 $ 381,572 $ 113,540 $ 495,112 $ 2022 189,493 $ 116,203 $ 56,096 $ 57,444 $ 305,695 $ 113,540 $ 419,236 $ 2023 189,493 $ 116,203 $ 56,096 $ 305,696 $ 56,096 $ 361,792 $ 2024 189,493 $ 56,096 $ 189,493 $ 56,096 $ 245,589 $ 2025 188,826 $ 56,096 $ 188,826 $ 56,096 $ 244,922 $ 2026 56,096 $ - $ 56,096 $ 56,096 $ 2027 56,096 $ - $ 56,096 $ 56,096 $ 3,319,126 $ 1,859,240 $ 1,062,269 $ 738,358 $ 3,462,690 $ 1,623,638 $ 36,815 $ 1,065,824 $ 861,662 $ 9,025,859 $ 12,102,136 $ 10,953,345 $ 23,055,481 $ ---PAGE BREAK--- DRAFT Table 1-3 Combined Water and Wastewater Utility Fund Indirect Transfer Cost Analysis Fiscal Year Ending June 30 ADMIN SERVICES - UTILITIES SALARY (6/30/2008) FICA TAX 7.65% PENSION 8.43% EMPLOYER MEDICAL WORKERS' COMP. LTD/LIFE INSURANCE TOTAL PERCENT CHARGED DIFFERENCE Utility Billing Garcia Shawn 47,528 $ 3,636 4,007 10,023 223 174 65,591 $ 100.00% 65,591 $ Utility Billing Nottage Heather 36,254 $ 2,773 3,056 6,936 170 148 49,338 $ 100.00% 49,338 $ Utility Billing Wisseman Monique 33,675 $ 2,576 2,839 6,936 158 123 46,307 $ 100.00% 46,307 $ Utility Billing Swanson Joanna 29,765 $ 2,277 2,509 10,135 140 109 44,935 $ 80.00% 35,948 $ - ADMIN SERVICES - FINANCE - *Accounting Supervisor Payne Rojean 67,069 $ 5,131 5,654 6,936 315 245 85,350 $ 33.00% 28,166 $ Adm. Services Director Brown Malea 95,597 $ 7,313 8,059 11,081 2,562 350 124,962 $ 33.00% 41,238 $ Accountant D'Onofrio Barb 53,872 $ 4,121 4,541 10,023 253 197 73,008 $ 33.00% 24,093 $ Accountant Lamb Joyce 45,302 $ 3,466 3,819 6,936 213 166 59,901 $ 33.00% 19,767 $ *Senior A/P Clerk Wickersham Kathy 43,059 $ 3,294 3,630 10,023 202 158 60,366 $ 10.00% 6,037 $ Payroll Jensky Rose 44,132 $ 3,376 3,720 10,135 207 162 61,733 $ 20.00% 12,347 $ - INFORMATION TECHNOLOGY: - IT Manager Green Sally 68,162 $ 5,214 5,746 6,936 1,827 249 88,135 $ 20.00% 17,627 $ IT Analyst Rhodes Jonathon 43,099 $ 3,297 3,633 6,936 1,586 158 58,710 $ 20.00% 11,742 $ IT Network Specialist Achter Matt 35,402 $ 2,708 2,984 6,936 949 130 49,109 $ 20.00% 9,822 $ IS Intern Zabel Quintin 15,640 $ 1,196 1,318 419 18,574 $ 20.00% 3,715 $ GIS Kraft Shawn 47,590 $ 3,641 4,012 6,936 1,275 174 63,628 $ 50.00% 31,814 $ - HUMAN RESOURCES - HR Generalist (OPEN) 50,000 $ 3,825 4,215 10,135 235 183 68,593 $ 14.00% 9,603 $ HR Director Curry Lori 77,549 $ 5,932 6,537 10,023 2,078 284 102,405 $ 14.00% 14,337 $ - CITY MANAGER OFFICE - Assistant City Manager (OPEN) 75,000 $ 5,738 6,323 6,936 2,010 275 96,281 $ 33.00% 31,773 $ Exec. Admin Assistant Brown Keri 42,058 $ 3,217 3,545 6,936 198 154 56,109 $ 20.00% 11,222 $ City Manger Jordan Janine 104,000 $ 7,956 8,767 2,787 381 123,891 $ 33.00% 40,884 $ Management Analyst Holmes Jon 41,033 $ 3,139 3,459 6,936 1,100 150 55,817 $ 33.00% 18,420 $ Grant Writer Reese Sarah 22,646 $ 1,732 1,909 6,936 106 83 33,413 $ 38.00% 12,697 $ - *City Clerk Morris-Jones Sue 76,176 $ 5,827 6,422 10,135 2,042 279 100,881 $ 20.00% 20,176 $ Admin Asst Culver LaDene 32,052 $ 2,452 2,702 11,081 151 117 48,555 $ 20.00% 9,711 $ - PUBLIC WORKS: - - Engineer Ketcham Larry 75,000 $ 5,738 6,323 11,081 2,010 275 100,426 $ 25.00% 25,106 $ Engineer Trowbridge Katharine 52,541 $ 4,019 4,429 6,936 1,408 192 69,526 $ 50.00% 34,763 $ Engineer Jaap Eric 51,253 $ 3,921 4,321 6,936 1,374 188 67,992 $ 50.00% 33,996 $ Engineering Tech. Earl Heather 40,061 $ 3,065 3,377 10,023 188 147 56,861 $ 50.00% 28,431 $ Public Works Director Elliott Richard 85,500 $ 6,541 7,208 10,023 2,291 313 111,876 $ 67.00% 74,957 $ *P.W. Adm. Assist Schilling Sheryl 44,286 $ 3,388 3,733 6,936 208 162 58,713 $ 50.00% 29,357 $ Inspector (OPEN) 47,598 $ 3,641 4,013 6,936 1,276 174 63,638 $ 50.00% 31,819 $ Warm Liability Insurance 284,300 $ 25% 71,075 $ A) TOTAL OVERHEAD CHARGES 901,876 $ B) ENTERPRISE FUND TRANSFER: (RECOMMENDED FOR FY09) Water 187,108 $ Wastewater 102,732 $ Solid Waste 94,082 $ TOTAL ENTERPRISE CHARGE: 383,922 $ C) DIFF. (A - OVERHEAD - B ENT FUND TRS.) 516,143 $ D) UNMETERED/METERED WATER CHARGED TO GENERAL FUND (352,875) $ E) REMAINING OVERHEAD NOT CHARGED TO ENTERPRISE FUNDS: 163,268 $ *Note: Salary includes longevity pay Enterprise Fund Expenses Used for Proportion Allocation Water $4,267,022 48.25% Wastewater 2,417,312 27.33% Solid Waste 2,159,998 24.42% TOTAL ENTERPRISE FUND EXPENSES: $8,844,332 Expenses provided by 2007 CAFR, pg. 31 Red Oak Recommended Enterprise Fund Transfers for FY 2008 Red Oak Recommended Enterprise Fund Transfers for FY 2009 Water $435,118 Water $450,347 Less: Water Charged to General Fund (352,875) Less: Water Charged to General Fund (365,226) Wastewater 246,499 Wastewater 255,126 Solid Waste 220,260 Solid Waste 227,969 TOTAL OVERHEAD ENTERPRISE CHARGE: $549,001 TOTAL OVERHEAD ENTERPRISE CHARGE: $568,216 ---PAGE BREAK--- DRAFT Table 2-1 Water Utility Consolidated Water Fund Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 3,000,000 2,950,000 960,000 4,210,000 2,620,000 2,100,000 3,690,000 2,280,000 2,690,000 2,550,000 Revenues 2 Revenue from Existing Water Service Rates Retail 5,487,047 5,484,044 5,485,505 5,483,974 5,483,366 5,481,303 5,485,744 5,483,207 5,482,709 5,475,373 Wholesale 0 0 0 0 0 0 0 0 0 0 3 Revenue from Impact Fees 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 4 Additional Water Service Revenue Required 0 434,153 1,504,628 2,692,199 4,081,696 5,705,610 7,613,548 9,299,412 10,287,958 10,724,182 5 Total Water Service Rate Revenue 5,687,047 6,118,197 7,190,133 8,376,173 9,765,062 11,386,913 13,299,292 14,982,619 15,970,667 16,399,555 Other Cash Inflows 6 Grants 1,350,000 7,390,000 1,460,000 270,000 750,000 200,000 1,000,000 1,000,000 1,000,000 1,000,000 7 Bond Proceeds 0 0 6,000,000 6,000,000 7,000,000 6,000,000 7,200,000 6,000,000 5,000,000 8,000,000 8 State & Other Loan Proceeds 290,000 3,090,000 620,000 30,000 120,000 0 0 0 0 0 9 Specific Purpose Tax Proceeds 541,213 0 0 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 0 0 10 Total Other Cash Inflows 2,181,213 10,480,000 8,080,000 7,300,000 8,870,000 7,200,000 9,200,000 8,000,000 6,000,000 9,000,000 11 Miscellaneous Revenue 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 12 Interest 119,030 78,246 54,985 75,900 61,873 77,518 77,325 53,056 54,539 54,804 13 Total Revenues 8,220,000 16,910,000 15,560,000 15,980,000 18,930,000 18,900,000 22,810,000 23,270,000 22,260,000 25,690,000 Revenue Requirements 14 Operation & Maintenance Expense 3 552 317 3 397 445 3 545 310 3 746 090 3 886 401 4 126 968 4 271 480 4 474 772 4 674 572 4 863 839 14 Operation & Maintenance Expense 3,552,317 3,397,445 3,545,310 3,746,090 3,886,401 4,126,968 4,271,480 4,474,772 4,674,572 4,863,839 Debt Service 15 Existing 1,366,722 1,370,797 809,813 809,178 792,603 799,884 830,066 815,834 426,991 446,141 16 Proposed 0 0 786,700 1,270,400 1,840,900 2,322,400 3,477,800 4,440,800 5,243,200 6,527,000 17 Total Debt Service 1,366,722 1,370,797 1,596,513 2,079,578 2,633,503 3,122,284 4,307,866 5,256,634 5,670,191 6,973,141 18 Routine Capital 1,318,000 850,000 975,650 1,089,786 1,228,470 1,370,952 919,159 955,925 994,162 1,033,929 19 Total Revenue Requirements 6,240,000 5,620,000 6,120,000 6,920,000 7,750,000 8,620,000 9,500,000 10,690,000 11,340,000 12,870,000 20 Annual Surplus (Deficiency) 1,980,000 11,290,000 9,440,000 9,060,000 11,180,000 10,280,000 13,310,000 12,580,000 10,920,000 12,820,000 21 Ending Balance Before Other Cash Outflows 4,980,000 14,240,000 10,400,000 13,270,000 13,800,000 12,380,000 17,000,000 14,860,000 13,610,000 15,370,000 Other Cash Outflows 22 Capital Improvements 1,940,000 13,157,400 5,432,900 9,896,000 10,834,400 7,931,900 13,818,300 11,400,000 10,396,500 11,436,100 23 General Fund (Indirect Expenses) 85,121 88,100 91,184 94,375 97,679 101,097 104,636 108,298 112,088 116,011 24 Debt Service Reserve 0 0 481,500 481,500 561,700 481,500 577,700 481,500 401,200 641,900 25 Debt Issuance Expense 2,900 30,900 186,200 180,300 211,200 180,000 216,000 180,000 150,000 240,000 26 Total Other Cash Outflows 2,028,021 13,276,400 6,191,784 10,652,175 11,704,979 8,694,497 14,716,636 12,169,798 11,059,788 12,434,011 27 Ending Balance Excess (Shortfall) 2,950,000 960,000 4,210,000 2,620,000 2,100,000 3,690,000 2,280,000 2,690,000 2,550,000 2,940,000 28 Target Ending Balance 1,330,000 1,420,000 1,500,000 1,650,000 1,800,000 1,940,000 2,110,000 2,270,000 2,430,000 2,590,000 Revenue Bond Debt Service Coverage (target 120%) 29 Simple 141.6% 183.9% 215.8% 213.0% 215.6% 226.1% 204.9% 196.1% 195.7% 162.6% 30 Enhanced 173.2% 215.5% 242.9% 233.8% 232.1% 240.0% 215.0% 204.3% 203.3% 168.8% Required Water Service Rate Increases 31 Overall Annual Increase 0.00% 19.00% 17.00% 17.00% 17.00% 17.00% 17.00% 8.00% 5.00% 0.00% 32 Cumulative Increase 0.00% 19.00% 39.23% 62.90% 90.59% 122.99% 160.90% 181.77% 195.86% 195.86% Recommended target balance equals 90 days operating expense. Total revenue less O&M expense, divided by total debt service. Total revenue plus impact fee revenue less O&M expense, divided by total debt service. ---PAGE BREAK--- DRAFT Table 2-2 Water Utility Operating Fund Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 2,551,000 2,040,000 840,000 1,910,000 1,890,000 1,200,000 2,680,000 1,190,000 1,460,000 1,270,000 Revenues Revenue from Existing Water Service Rates 2 Retail 5,487,047 5,484,044 5,485,505 5,483,974 5,483,366 5,481,303 5,485,744 5,483,207 5,482,709 5,475,373 3 Wholesale 0 0 0 0 0 0 0 0 0 0 4 Additional Water Service Revenue Required 0 434,153 1,504,628 2,692,199 4,081,696 5,705,610 7,613,548 9,299,412 10,287,958 10,724,182 5 Total Water Service Rate Revenue 5,487,047 5,918,197 6,990,133 8,176,173 9,565,062 11,186,913 13,099,292 14,782,619 15,770,667 16,199,555 Transfers FROM 6 Capital Fund 0 0 0 0 0 0 0 0 0 0 7 Debt Service Reserve Fund 0 0 0 0 0 0 0 0 0 0 8 Total Transfers 0 0 0 0 0 0 0 0 0 0 9 Miscellaneous Revenue 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 232,776 10 Interest 91,830 57,646 54,985 75,900 61,873 77,518 77,325 53,056 54,539 54,804 11 Total Revenues 5,811,653 6,208,619 7,277,894 8,484,849 9,859,711 11,497,207 13,409,393 15,068,451 16,057,982 16,487,135 Revenue Requirements 12 O ti & M i t E 3 552 317 3 397 445 3 545 310 3 746 090 3 886 401 4 126 968 4 271 480 4 474 772 4 674 572 4 863 839 12 Operation & Maintenance Expense 3,552,317 3,397,445 3,545,310 3,746,090 3,886,401 4,126,968 4,271,480 4,474,772 4,674,572 4,863,839 Debt Service 13 Existing 1,366,722 1,370,797 809,813 809,178 792,603 799,884 830,066 815,834 426,991 446,141 14 Proposed 0 0 786,700 1,270,400 1,840,900 2,322,400 3,477,800 4,440,800 5,243,200 6,527,000 15 Total Debt Service 1,366,722 1,370,797 1,596,513 2,079,578 2,633,503 3,122,284 4,307,866 5,256,634 5,670,191 6,973,141 16 Routine Capital 1,318,000 850,000 975,650 1,089,786 1,228,470 1,370,952 919,159 955,925 994,162 1,033,929 17 Total Revenue Requirements 6,237,039 5,618,243 6,117,473 6,915,454 7,748,374 8,620,205 9,498,505 10,687,332 11,338,925 12,870,908 18 Annual Surplus (Deficiency) (425,386) 590,376 1,160,421 1,569,396 2,111,337 2,877,002 3,910,888 4,381,120 4,719,057 3,616,227 19 Ending Balance Before Transfers 2,125,614 2,630,376 2,000,421 3,479,396 4,001,337 4,077,002 6,590,888 5,571,120 6,179,057 4,886,227 Transfers TO 20 Capital Fund 0 1,700,000 0 1,500,000 2,700,000 1,300,000 5,300,000 4,000,000 4,800,000 3,300,000 21 General Fund (Indirect Expenses) 85,121 88,100 91,184 94,375 97,679 101,097 104,636 108,298 112,088 116,011 22 Debt Service Reserve Fund 0 0 0 0 0 0 0 0 0 0 23 Total Transfers 85,121 1,788,100 91,184 1,594,375 2,797,679 1,401,097 5,404,636 4,108,298 4,912,088 3,416,011 24 Ending Balance Excess (Shortfall) 2,040,000 840,000 1,910,000 1,890,000 1,200,000 2,680,000 1,190,000 1,460,000 1,270,000 1,470,000 25 Target Ending Balance 880,000 840,000 870,000 920,000 960,000 1,020,000 1,050,000 1,100,000 1,150,000 1,200,000 Revenue Bond Debt Service Coverage (target 120%) 26 Simple 141.6% 183.9% 215.8% 213.0% 215.6% 226.1% 204.9% 196.1% 195.7% 162.6% 27 Enhanced 173.2% 215.5% 242.9% 233.8% 232.1% 240.0% 215.0% 204.3% 203.3% 168.8% Required Water Service Rate Increases 28 Overall Annual Increase 0.00% 19.00% 17.00% 17.00% 17.00% 17.00% 17.00% 8.00% 5.00% 0.00% 29 Cumulative Increase 0.00% 19.00% 39.23% 62.90% 90.59% 122.99% 160.90% 181.77% 195.86% 195.86% Recommended target balance equals 90 days operating expense. Total revenue less O&M expense, divided by total debt service. Total revenue plus impact fee revenue less O&M expense, divided by total debt service. ---PAGE BREAK--- DRAFT Table 2-3 Water Utility Capital Fund Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 447,000 910,000 120,000 2,300,000 740,000 900,000 1,010,000 1,100,000 1,240,000 1,290,000 Sources of Funds 2 Revenue from Impact Fees 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 3 Specific Purpose Tax Proceeds 541,213 0 0 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 0 0 4 Transfer From Operating Fund 0 1,700,000 0 1,500,000 2,700,000 1,300,000 5,300,000 4,000,000 4,800,000 3,300,000 5 Grants 1,350,000 7,390,000 1,460,000 270,000 750,000 200,000 1,000,000 1,000,000 1,000,000 1,000,000 6 Bond Proceeds 0 0 6,000,000 6,000,000 7,000,000 6,000,000 7,200,000 6,000,000 5,000,000 8,000,000 7 State & Other Loan Proceeds 290,000 3,090,000 620,000 30,000 120,000 0 0 0 0 0 8 Interest 27,200 20,600 0 0 0 0 0 0 0 0 9 Total Sources 2,408,413 12,400,600 8,280,000 9,000,000 11,770,000 8,700,000 14,700,000 12,200,000 11,000,000 12,500,000 Uses of Funds Capital Improvements 10 Scheduled 1,940,000 13,157,400 5,432,900 9,896,000 10,834,400 7,931,900 13,818,300 11,400,000 10,396,500 11,436,100 11 Deferred 0 0 0 0 0 0 0 0 0 0 12 Subtotal CIP 1,940,000 13,157,400 5,432,900 9,896,000 10,834,400 7,931,900 13,818,300 11,400,000 10,396,500 11,436,100 13 Debt Service Reserve 0 0 481,500 481,500 561,700 481,500 577,700 481,500 401,200 641,900 14 Debt Issuance Expense 2,900 30,900 186,200 180,300 211,200 180,000 216,000 180,000 150,000 240,000 p , , , , , , , , , , 15 Transfer TO Operating Fund 0 0 0 0 0 0 0 0 0 0 16 Total Uses 1,942,900 13,188,300 6,100,600 10,557,800 11,607,300 8,593,400 14,612,000 12,061,500 10,947,700 12,318,000 17 Annual Surplus (Deficiency) 465,513 (787,700) 2,179,400 (1,557,800) 162,700 106,600 88,000 138,500 52,300 182,000 18 Ending Balance 910,000 120,000 2,300,000 740,000 900,000 1,010,000 1,100,000 1,240,000 1,290,000 1,470,000 19 Target Ending Balance 1.0% 450,000 580,000 630,000 730,000 840,000 920,000 1,060,000 1,170,000 1,280,000 1,390,000 ---PAGE BREAK--- DRAFT Table 2-4 Water Utility Summary of Existing Debt Fiscal Year Ending June 30 Water Water WY WDC - Refi Loan WY WDC GO Refinancing GO Security 1st Bank TOTAL Year Series 2008 Series 2003 #008 Series 2001 Series 2000 Series 2001 Series 2002 Series 2005 2008 187,854 $ 116,203 $ 75,876 $ 56,887 $ 390,638 $ 541,213 $ 18,408 $ 1,387,078 $ 2009 172,607 $ 116,203 $ 75,876 $ 56,887 $ 385,529 $ 541,213 $ 18,408 $ 1,366,722 $ 2010 195,893 $ 116,203 $ 75,876 $ 56,887 $ 384,726 $ 541,213 $ 1,370,797 $ 2011 177,675 $ 116,203 $ 75,876 $ 56,887 $ 383,173 $ 809,813 $ 2012 174,475 $ 116,203 $ 75,876 $ 56,887 $ 385,738 $ 809,178 $ 2013 171,025 $ 116,203 $ 75,876 $ 56,887 $ 372,613 $ 792,603 $ 2014 167,375 $ 116,203 $ 75,876 $ 56,887 $ 383,544 $ 799,884 $ 2015 188,088 $ 116,203 $ 75,876 $ 56,887 $ 393,013 $ 830,066 $ 2016 183,150 $ 116,203 $ 75,876 $ 56,887 $ 383,719 $ 815,834 $ 2017 178,025 $ 116,203 $ 75,876 $ 56,887 $ 426,991 $ 2018 197,175 $ 116,203 $ 75,876 $ 56,887 $ 446,141 $ 2019 189,493 $ 116,203 $ 75,876 $ 56,887 $ 438,458 $ 2020 189,493 $ 116,203 $ 75,876 $ 55,718 $ 437,289 $ 2021 189,493 $ 116,203 $ 75,876 $ 381,572 $ 2022 189,493 $ 116,203 $ 305,695 $ 2023 189,493 $ 116,203 $ 305,696 $ 2024 189,493 $ 189,493 $ 2025 188,826 $ 188,826 $ 2026 - $ 2027 - $ 3,319,126 $ 1,859,240 $ 1,062,269 $ 738,358 $ 3,462,690 $ 1,623,638 $ 36,815 $ 12,102,136 $ ---PAGE BREAK--- DRAFT Table 2-5 Water Utility Capital Improvement Program Fiscal Year Ending June 30 Line Funding Distribution Budget Projected Total No. Description Source Transmission 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2009-2013 $ $ $ $ $ $ $ $ $ $ $ Major Capital 1 North Laramie Water Line B d 0 0 0 0 0 2,295,000 5,226,100 0 0 0 7,521,100 2 Replace Priority #1 Break Pipes B/GSLIB d 0 1,650,000 1,815,000 1,996,500 2,196,200 2,415,800 0 0 0 0 10,073,500 3 Replace Priority #2 Pipe Breaks B/GSLIB d 0 0 0 0 0 0 4,871,800 5,359,000 5,894,900 6,484,400 22,610,100 4 Replace pipe in Z1 & Z2 SPT/B d 0 0 0 0 1,464,100 1,610,500 1,771,600 1,948,700 0 0 6,794,900 5 Analysis of 20/24 Transmission Lines C d 0 385,000 0 0 0 0 0 0 0 0 385,000 6 New 24" Pipe to repl Grand Ave. pipes B d 0 0 0 3,660,300 4,026,300 0 0 0 0 0 7,686,600 7 New 12" to repl West Lar pipe C d 0 0 0 0 0 901,900 0 0 0 0 901,900 8 New PRV Stations 11th/Lewis, 4th/? C d 0 0 0 0 0 547,600 0 0 0 0 547,600 9 Repl Z1 Pipe in 9th (10") C d 0 0 0 0 0 0 1,771,600 0 0 0 1,771,600 10 New 12" Main - 22nd St. Grand to Reynolds C d 0 0 0 798,600 0 0 0 0 0 0 798,600 11 Replace 12" Main - Reynolds, 15th to 22nd C d 0 0 302,500 0 0 0 0 0 0 0 302,500 12 Replace 18" Main - McCue/Baker to Lyon C d 0 0 0 638,900 0 0 0 0 0 0 638,900 13 Repl Zone 4 Main w/ 16" from IHPS to Q-Horse C d 0 0 665,500 0 0 0 0 0 0 0 665,500 14 Replace Mains in area 9th-19th, Sp Cr to Sky C d 0 0 0 0 0 0 0 3,897,400 4,287,200 4,715,900 12,900,500 15 Soldier Spgs Control Upgrade C d 0 0 0 0 146,400 0 0 0 0 0 146,400 16 Electric Valves C d 0 286,000 0 452,500 0 0 0 0 0 0 738,500 17 Expansion Tank C d 0 132,000 0 0 0 0 0 0 0 0 132,000 18 On Site Chlorine Generation C t 985,000 770,000 677,600 0 0 0 0 0 0 0 2,432,600 19 Raw Water Pipeline 67% 33% t 880,000 7,700,000 0 133,100 146,400 161,100 177,200 194,900 214,400 235,800 9,842,900 20 Raw Water Point of Diversion 67% 33% t 0 0 60,500 99,800 366,000 0 0 0 0 0 526,300 21 Backwash Water Reuse C t 75,000 440,000 0 0 0 0 0 0 0 0 515,000 22 Aerial Photography - 50% C t 0 49,500 0 0 0 0 0 0 0 0 49,500 23 Water Fill Station C t 0 94,900 0 0 0 0 0 0 0 0 94,900 24 Reservoir Cleaning C t 0 0 96,800 0 0 0 0 0 0 0 96,800 25 Clarifier Effluent Pipe B t 0 0 0 652,200 0 0 0 0 0 0 652,200 26 Clearwell Storage SPT/B t 0 0 0 1,464,100 1,756,900 0 0 0 0 0 3,221,000 27 Building Addition - 50% C t 0 0 0 0 732,100 0 0 0 0 0 732,100 28 South Laramie Water Delivery 67% 33% t 0 1,650,000 1,815,000 0 0 0 0 0 0 0 3,465,000 29 Total Capital Improvements - Inflated Dollars 1,940,000 13,157,400 5,432,900 9,896,000 10,834,400 7,931,900 13,818,300 11,400,000 10,396,500 11,436,100 96,243,500 ---PAGE BREAK--- DRAFT Table 2-6 Water Utility Summary of Projected Operation and Maintenance Expenses Fiscal Year Ending June 30 Line Estimated Budgeted Projected No. Description 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ $ Water Administration Personnel 1 Current 137,669 213,861 221,346 229,093 237,112 245,410 254,000 262,890 272,091 281,614 291,471 2 Additional 0 70,000 72,450 74,986 155,221 160,653 166,276 258,144 267,179 368,707 381,611 3 Contractual 143,256 205,190 212,372 219,805 227,498 235,460 243,701 252,231 261,059 270,196 279,653 4 Materials and Supplies 33,596 34,500 35,708 36,957 38,251 39,590 40,975 42,409 43,894 45,430 47,020 5 Capital Equipment (Routine Capital) 9,470 0 40,000 41,600 43,264 44,995 46,794 48,666 50,613 52,637 54,743 6 Vehicles and Equipment 0 80,000 58,240 89,232 91,439 103,838 212,015 133,456 191,917 149,641 180,534 7 Total Administration 323,991 603,551 640,115 691,673 792,784 829,946 963,762 997,795 1,086,752 1,168,225 1,235,032 Water Pumping & Wells 8 Personnel 150,288 165,327 171,113 177,102 183,301 189,717 196,357 203,229 210,342 217,704 225,324 9 Contractual 146,558 159,850 165,445 171,235 177,229 183,432 189,852 196,496 203,374 210,492 217,859 10 Materials and Supplies 47,090 61,200 63,342 65,559 67,854 70,228 72,686 75,230 77,863 80,589 83,409 11 Capital Equipment (Routine Capital) 361,178 0 60,000 62,400 64,896 67,492 70,192 72,999 75,919 78,956 82,114 12 Total Water Pumping & Wells 705,114 386,377 459,900 476,297 493,279 510,868 529,086 547,955 567,499 587,741 608,706 Water Filter Treatment Plant 13 Personnel 261,337 349,580 361,815 374,479 387,586 401,151 415,191 429,723 444,763 460,330 476,442 14 Contractual 159,619 125,991 130,401 134,965 139,688 144,578 149,638 154,875 160,296 165,906 171,713 15 Materials and Supplies 195,213 223,567 231,392 239,491 247,873 256,548 265,527 274,821 284,440 294,395 304,699 16 Capital Equipment (Routine Capital) 78,740 0 75,000 78,000 81,120 84,365 87,739 91,249 94,899 98,695 102,643 17 Total Water Filter Treatment Plant 694,908 699,138 798,608 826,934 856,267 886,642 918,096 950,668 984,398 1,019,326 1,055,496 Water T&D 18 Personnel 349,875 369,926 382,873 396,274 410,144 424,499 439,356 454,734 470,649 487,122 504,171 19 Contractual 157,709 267,900 277,277 286,981 297,026 307,421 318,181 329,318 340,844 352,773 365,120 20 Materials and Supplies 156,855 183,650 190,078 196,730 203,616 210,743 218,119 225,753 233,654 241,832 250,296 21 Capital Equipment (Routine Capital) 471,922 1,018,000 250,000 260,000 270,400 281,216 292,465 304,163 316,330 328,983 342,142 22 Total Water T&D 1,136,361 1,839,476 1,100,228 1,139,986 1,181,185 1,223,879 1,268,120 1,313,967 1,361,477 1,410,710 1,461,730 Water Meters 23 Personnel 295,475 299,698 310,187 321,044 332,281 343,910 355,947 368,405 381,300 394,645 408,458 23 Contractual 6,152 43,325 44,841 46,411 48,035 49,716 51,457 53,257 55,121 57,051 59,048 24 Materials and Supplies 164,155 342,650 100,000 103,500 107,123 110,872 114,752 118,769 122,926 127,228 131,681 25 Capital Equipment (Routine Capital) 9,806 300,000 300,000 403,650 494,906 609,795 727,530 250,000 260,000 270,400 281,216 26 Total Water Meters 475,589 985,673 755,029 874,605 982,344 1,114,294 1,249,686 790,431 819,347 849,324 880,402 Water Monolith Ranch 27 Personnel 84,173 81,646 84,504 87,461 90,522 93,691 96,970 100,364 103,877 107,512 111,275 28 Contractual 43,444 81,825 84,689 87,653 90,721 93,896 97,182 100,584 104,104 107,748 111,519 29 Materials and Supplies 130,429 111,800 115,713 119,763 123,955 128,293 132,783 137,431 142,241 147,219 152,372 30 Capital Equipment (Routine Capital) 98,617 0 125,000 130,000 135,200 140,608 146,232 152,082 158,165 164,491 171,071 31 Total Water Monolith Ranch 356,663 275,271 409,905 424,877 440,398 456,488 473,168 490,460 508,386 526,971 546,237 Water Rights 32 Personnel 0 59,456 61,537 63,691 65,920 68,227 70,615 73,087 75,645 78,292 81,032 33 Contractual 13,064 21,375 22,123 22,897 23,699 24,528 25,387 26,275 27,195 28,147 29,132 34 Materials and Supplies 0 0 0 0 0 0 0 0 0 0 0 Capital Equipment (Routine Capital) 0 0 0 0 0 0 0 0 0 0 0 35 Total Water Rights 13,064 80,831 83,660 86,588 89,619 92,755 96,002 99,362 102,840 106,439 110,164 36 Pipes Repair and Replacement 0 0 0 0 0 0 0 0 0 0 0 37 Total O&M (exluding capital equipment) 2,675,957 3,552,317 3,397,445 3,545,310 3,746,090 3,886,401 4,126,968 4,271,480 4,474,772 4,674,572 4,863,839 38 Total Capital Equipment (Routine Capital) 1,029,734 1,318,000 850,000 975,650 1,089,786 1,228,470 1,370,952 919,159 955,925 994,162 1,033,929 39 Total Operation & Maintenance 3,705,690 4,870,317 4,247,445 4,520,960 4,835,876 5,114,871 5,497,920 5,190,639 5,430,697 5,668,735 5,897,767 ---PAGE BREAK--- DRAFT Table 2-7 Water Utility Vehicles Analysis Fleet Schedule 2010 2011 2012 2013 2014 2015 2016 2017 2018 Total Replacement Water Vehicles $56,000 $35,000 $0 $0 $18,000 $60,000 $46,000 $0 $65,000 $280,000 Replacement Water Equipment $0 $47,500 $119,000 $125,000 $67,500 $0 $170,000 $47,500 $0 $576,500 Total Replacement Vehicles and Equipment $56,000 $82,500 $119,000 $125,000 $85,500 $60,000 $216,000 $47,500 $65,000 $856,500 New Water Vehicles $38,000 $0 $0 $38,000 $0 $0 $0 $38,000 $0 $0 $76,000 New Water Equipment 78,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Total New Vehicles and Equipment $0 $0 $38,000 $0 $0 $0 $38,000 $0 $0 $76,000 Input: Lease Purchase Terms 2010 2011 2012 2013 2014 2015 2016 2017 2018 Amount of Issue - Big Equipment 0 0 119,000 125,000 0 0 170,000 0 0 Interest Rate - % 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% Payment Period - years 3 3 3 3 3 3 3 3 3 Lease Purchase Schedule - Big equipment 2010 2011 2012 2013 2014 2015 2016 2017 2018 2010 Issue $0 $0 $0 $0 $0 $0 $0 $0 $0 2011 Issue 0 0 0 0 0 0 0 0 2012 Issue 43,289 43,289 43,289 0 0 0 0 2013 Issue 45,472 45,472 45,472 0 0 0 2014 Issue 0 0 0 0 0 2015 Issue 0 0 0 0 2016 Issue 61,841 61,841 61,841 2017 Issue 0 0 2018 Issue 0 2019 Issue 2020 Issue $0 $0 $43,289 $88,761 $88,761 $45,472 $61,841 $61,841 $61,841 Total Vehicle Expense on O&M Total Vehicles/small trucks $56,000 $35,000 $38,000 $0 $18,000 $60,000 $84,000 $0 $65,000 $356,000 Big Equipment $0 $47,500 $43,289 $88,761 $156,261 $45,472 $61,841 $109,341 $61,841 $614,306 Vehicles and Equipment $56,000 $82,500 $81,289 $88,761 $174,261 $105,472 $145,841 $109,341 $126,841 $970,306 Vehicles and Equipment (inflated) $58,240 $89,232 $91,439 $103,838 $212,015 $133,456 $191,917 $149,641 $180,534 $1,210,312 ---PAGE BREAK--- DRAFT Table 2-8 Water Utility Employee Efficiency Analysis Fiscal Year Ending June 30 Line Budget Projected No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ Benchmark Comparison 1 Water Accounts 8,559 8,645 8,731 8,818 8,907 8,996 9,086 9,176 9,268 9,361 9,455 2 Personnel 3 Current 18.5 19.5 19.5 19.5 20.5 20.5 20.5 21.5 21.5 22.5 4 Additional 1.0 0.0 0.0 1.0 0.0 0.0 1.0 0.0 1.0 0.0 5 Total Personnel 19.5 19.5 19.5 20.5 20.5 20.5 21.5 21.5 22.5 22.5 Total Customer Accounts per Employee 443.3 447.7 452.2 434.5 438.8 443.2 426.8 431.1 416.0 420.2 6 Minimum - Benchmark 319 7 Median - Benchmark 467 8 Percent of Minimum 139.0% 140.4% 141.8% 136.2% 137.6% 138.9% 133.8% 135.1% 130.4% 131.7% 9 Percent of Median (percent of total) 94.9% 95.9% 96.8% 93.0% 94.0% 94.9% 91.4% 92.3% 89.1% 90.0% Benchmark Comparison 1 MGD Flow 5.0 2 Personnel 3 Current 18.5 19.5 19.5 19.5 20.5 20.5 20.5 21.5 21.5 22.5 4 Additional 1.0 0.0 0.0 1.0 0.0 0.0 1.0 0.0 1.0 0.0 Total Personnel 19.5 19.5 19.5 20.5 20.5 20.5 21.5 21.5 22.5 22.5 5 Total MGD Delivered per Employee 0.26 0.26 0.26 0.24 0.24 0.24 0.23 0.23 0.22 0.22 6 Minimum - Benchmark 0.18 7 Median - Benchmark 0.26 8 Percent of Minimum 142.5% 142.5% 142.5% 135.5% 135.5% 135.5% 129.2% 129.2% 123.5% 123.5% 9 Percent of Median (percent of total) 98.6% 98.6% 98.6% 93.8% 93.8% 93.8% 89.4% 89.4% 85.5% 85.5% Total includes additional personnel. Additional Personnel Salary $70,000 ---PAGE BREAK--- DRAFT Table 2-9 Water Utility Water Utility Renewal and Replacement Analysis Fiscal Year Ending June 30 Line Budget Projected No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ Cit f L i W t Di t ib ti S t City of Laramie Water Distribution System 1 Replacement Cost New 85,631,700 $ 2 Capital Investment (Renewal & Replacement) 0 2,453,000 2,783,000 3,087,900 3,806,700 4,026,300 8,415,000 11,205,100 10,182,100 11,200,300 3 System Investment 1,018,000 250,000 260,000 270,400 281,200 292,500 304,200 316,300 329,000 342,100 4 Additional System Investment Required 0 0 0 0 0 0 0 0 0 0 5 City of Laramie, Distribution System Investment 1,018,000 $ 2,703,000 $ 3,043,000 $ 3,358,300 $ 4,087,900 $ 4,318,800 $ 8,719,200 $ 11,521,400 $ 10,511,100 $ 11,542,400 $ Water Utility Industry Benchmarks $ $ $ $ $ $ $ $ $ $ 6 Minimum - Benchmark 1.6% 1,370,100 $ 1,507,100 $ 1,657,800 $ 1,823,600 $ 2,006,000 $ 2,206,600 $ 2,427,300 $ 2,670,000 $ 2,937,000 $ 3,230,700 $ 7 Median - Benchmark 4.9% 4,196,000 $ 4,615,600 $ 5,077,200 $ 5,584,900 $ 6,143,400 $ 6,757,700 $ 7,433,500 $ 8,176,900 $ 8,994,600 $ 9,894,100 $ 8 Percent of Minimum 74% 179% 184% 184% 204% 196% 359% 432% 358% 357% 9 Percent of Median 24% 59% 60% 60% 67% 64% 117% 141% 117% 117% Total Distribution System Investment Needed includes Additional System Investment Required. ---PAGE BREAK--- DRAFT Table 3-1 Wastewater Utility Consolidated Wastewater Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 1,010,000 990,000 440,000 1,040,000 2,730,000 2,080,000 1,420,000 1,790,000 1,470,000 5,580,000 Revenues Revenue from Existing Service Rates 2 Retail 3,443,727 3,447,556 3,453,063 3,458,184 3,462,327 3,467,269 3,472,259 3,476,747 3,482,411 3,487,386 3 Wholesale 0 0 0 0 0 0 0 0 0 0 4 Impact Fee Revenue 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 5 Additional Service Revenue Required 0 272,932 964,268 1,741,763 2,628,900 3,669,639 4,889,942 6,129,080 6,780,496 6,790,184 6 Total Wastewater Service Rate Revenue 3,643,727 3,920,488 4,617,331 5,399,947 6,291,227 7,336,908 8,562,201 9,805,827 10,462,907 10,477,570 Other Cash Inflows 7 Grants 0 610,000 2,771,250 1,997,375 800,000 2,634,393 2,017,839 0 0 0 8 Bond Proceeds 0 0 1,000,000 3,000,000 0 0 0 0 0 0 9 State & Other Loan Proceeds 0 0 0 0 0 903,507 993,861 0 0 0 10 Specific Purpose Tax Proceeds 0 0 0 1,500,000 2,050,000 0 0 0 0 0 11 Total Other Cash Inflows 0 610,000 3,771,250 6,497,375 2,850,000 3,537,900 3,011,700 0 0 0 12 Miscellaneous Revenue 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 13 Interest Income 40,132 24,917 41,488 50,103 53,947 53,572 77,433 69,664 154,878 89,545 14 Total Revenues 3,730,000 4,600,000 8,480,000 11,990,000 9,240,000 10,970,000 11,700,000 9,920,000 10,660,000 10,610,000 Revenue Requirements 15 Operation & Maintenance Expense 1,960,152 2,057,927 2,236,317 2,326,749 2,609,315 2,799,492 2,891,405 3,037,196 3,078,261 3,134,871 Debt Service 16 Existing 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 17 Proposed 0 0 80,243 320,971 320,971 387,453 460,583 460,583 460,583 460,583 18 Total Debt Service 934,073 934,073 1,014,316 1,255,044 1,255,044 1,321,526 1,394,656 1,394,656 1,394,656 1,394,656 19 Routine Capital 275,000 475,000 494,000 513,760 534,310 514,738 535,327 556,740 1,737,802 1,849,221 20 Total Revenue Requirements 3,170,000 3,470,000 3,740,000 4,100,000 4,400,000 4,640,000 4,820,000 4,990,000 6,210,000 6,380,000 21 Annual Surplus (Deficiency) 560,000 1,130,000 4,740,000 7,890,000 4,840,000 6,330,000 6,880,000 4,930,000 4,450,000 4,230,000 22 Ending Balance Before Other Cash Outflows 1,570,000 2,120,000 5,180,000 8,930,000 7,570,000 8,410,000 8,300,000 6,720,000 5,920,000 9,810,000 Other Cash Outflows 23 Capital Improvements 326,600 1,413,500 3,757,100 5,590,200 5,197,700 6,683,700 6,200,500 4,930,300 0 3,536,900 24 Indirect Expenses 255,126 264,056 273,297 282,863 292,763 303,010 313,615 324,592 335,952 347,711 25 Debt Service Reserve 0 0 80,200 240,700 0 0 0 0 0 0 26 Debt Issuance Expense 0 0 30,000 90,000 0 0 0 0 0 0 27 Total Other Cash Inflows 581,726 1,677,556 4,140,597 6,203,763 5,490,463 6,986,710 6,514,115 5,254,892 335,952 3,884,611 28 Ending Balance Excess (Shortfall) 990,000 440,000 1,040,000 2,730,000 2,080,000 1,420,000 1,790,000 1,470,000 5,580,000 5,930,000 29 Target Ending Balance 700,000 740,000 820,000 900,000 1,020,000 1,140,000 1,220,000 1,310,000 1,320,000 1,360,000 Debt Service Coverage (target 120%) 30 Simple 158.8% 178.0% 215.0% 228.9% 277.4% 328.2% 392.3% 471.0% 515.2% 512.1% 31 Enhanced 185.2% 204.4% 239.3% 248.6% 297.1% 346.9% 409.9% 488.7% 532.8% 529.8% Required Wastewater Service Rate Increases 32 Overall Annual Increase 0.00% 19.00% 18.00% 17.00% 17.00% 17.00% 17.00% 12.00% 0.00% 0.00% 33 Cumulative Increase 0.00% 19.00% 40.42% 64.29% 92.22% 124.90% 163.13% 194.71% 194.71% 194.71% Recommended target balance equals 90 days operating expense. Total revenue less O&M expense, divided by total debt service. Total revenue plus impact fee revenue less O&M expense, divided by total debt service. ---PAGE BREAK--- DRAFT Table 3-2 Wastewater Utility Operating Fund Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 793,500 890,000 450,000 940,000 1,860,000 1,230,000 490,000 700,000 470,000 4,340,000 Revenues Revenue from Existing Service Rates 2 Retail 3,443,727 3,447,556 3,453,063 3,458,184 3,462,327 3,467,269 3,472,259 3,476,747 3,482,411 3,487,386 3 Wholesale 0 0 0 0 0 0 0 0 0 0 4 Additional Service Revenue Required 0 272,932 964,268 1,741,763 2,628,900 3,669,639 4,889,942 6,129,080 6,780,496 6,790,184 5 Total Wastewater Service Rate Revenue 3,443,727 3,720,488 4,417,331 5,199,947 6,091,227 7,136,908 8,362,201 9,605,827 10,262,907 10,277,570 Transfers FROM 6 Capital Fund 0 0 0 0 0 0 0 0 0 0 7 Debt Service Reserve Fund 0 0 0 0 0 0 0 0 0 0 8 Total Transfers 0 0 0 0 0 0 0 0 0 0 9 Miscellaneous Revenue 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 46,500 10 Interest Income 33,732 22,917 41,488 50,103 19,047 16,972 35,933 27,064 109,178 47,645 11 Total Revenues 3,523,959 3,789,905 4,505,319 5,296,550 6,156,774 7,200,380 8,444,634 9,679,391 10,418,585 10,371,715 Revenue Requirements 12 Operation & Maintenance Expense 1,960,152 2,057,927 2,236,317 2,326,749 2,609,315 2,799,492 2,891,405 3,037,196 3,078,261 3,134,871 12 Operation & Maintenance Expense 1,960,152 2,057,927 2,236,317 2,326,749 2,609,315 2,799,492 2,891,405 3,037,196 3,078,261 3,134,871 Debt Service 13 Existing 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 934,073 14 Proposed 0 0 80,243 320,971 320,971 387,453 460,583 460,583 460,583 460,583 15 Total Debt Service 934,073 934,073 1,014,316 1,255,044 1,255,044 1,321,526 1,394,656 1,394,656 1,394,656 1,394,656 16 Routine Capital 275,000 475,000 494,000 513,760 534,310 514,738 535,327 556,740 1,737,802 1,849,221 17 Total Revenue Requirements 3,169,225 3,467,000 3,744,633 4,095,553 4,398,669 4,635,755 4,821,388 4,988,592 6,210,719 6,378,748 18 Annual Surplus (Deficiency) 354,734 322,905 760,686 1,200,997 1,758,105 2,564,625 3,623,246 4,690,799 4,207,867 3,992,967 19 Ending Balance Before Transfers 1,148,234 1,212,905 1,210,686 2,140,997 3,618,105 3,794,625 4,113,246 5,390,799 4,677,867 8,332,967 Transfers TO 20 Capital Fund 0 500,000 0 0 2,100,000 3,000,000 3,100,000 4,600,000 0 2,850,000 20 General Fund (Indirect expenses) 255,126 264,056 273,297 282,863 292,763 303,010 313,615 324,592 335,952 347,711 21 Debt Service Reserve Fund 0 0 0 0 0 0 0 0 0 0 22 Total Transfers 255,126 764,056 273,297 282,863 2,392,763 3,303,010 3,413,615 4,924,592 335,952 3,197,711 23 Ending Balance After Transfers 890,000 450,000 940,000 1,860,000 1,230,000 490,000 700,000 470,000 4,340,000 5,140,000 24 Target Ending Balance 480,000 510,000 550,000 570,000 640,000 690,000 710,000 750,000 760,000 770,000 Debt Service Coverage (target 120%) 25 Simple 158.8% 178.0% 215.0% 228.9% 277.4% 328.2% 392.3% 471.0% 515.2% 512.1% 26 Enhanced 185.2% 204.4% 239.3% 248.6% 297.1% 346.9% 409.9% 488.7% 532.8% 529.8% Required Wastewater Service Rate Increases 27 Overall Annual Increase 0.00% 19.00% 18.00% 17.00% 17.00% 17.00% 17.00% 12.00% 0.00% 0.00% 28 Cumulative Increase 0.00% 19.00% 40.42% 64.29% 92.22% 124.90% 163.13% 194.71% 194.71% 194.71% Recommended target balance equals 90 days operating expense. Total revenue less O&M expense, divided by total debt service. Total revenue plus impact fee revenue less O&M expense, divided by total debt service. ---PAGE BREAK--- DRAFT Table 3-3 Wastewater Utility Capital Fund Cash Flow Analysis Fiscal Year Ending June 30 Line No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ 1 Beginning Fund Balance 220,000 100,000 0 100,000 880,000 870,000 960,000 1,110,000 1,020,000 1,270,000 Sources of Funds 2 Impact Fee Revenue 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 3 Specific Purpose Tax Proceeds 0 0 0 1,500,000 2,050,000 0 0 0 0 0 4 Transfer From Operating Fund 0 500,000 0 0 2,100,000 3,000,000 3,100,000 4,600,000 0 2,850,000 5 Grants 0 610,000 2,771,250 1,997,375 800,000 2,634,393 2,017,839 0 0 0 6 Bond Proceeds 0 0 1,000,000 3,000,000 0 0 0 0 0 0 7 State & Other Loan Proceeds 0 0 0 0 0 903,507 993,861 0 0 0 8 Interest 6,400 2,000 0 0 34,900 36,600 41,500 42,600 45,700 41,900 9 Total Sources 206,400 1,312,000 3,971,250 6,697,375 5,184,900 6,774,500 6,353,200 4,842,600 245,700 3,091,900 Uses of Funds Capital Improvements 10 Scheduled 326,600 1,413,500 3,757,100 5,590,200 5,197,700 6,683,700 6,200,500 4,930,300 0 3,536,900 11 Deferred 0 0 0 0 0 0 0 0 0 0 12 Subtotal CIP 326,600 1,413,500 3,757,100 5,590,200 5,197,700 6,683,700 6,200,500 4,930,300 0 3,536,900 13 Debt Service Reserve 0 0 80,200 240,700 0 0 0 0 0 0 14 Debt Issuance Expense 0 0 30,000 90,000 0 0 0 0 0 0 15 Transfer TO Operating Fund 0 0 0 0 0 0 0 0 0 0 15 Transfer TO Operating Fund 0 0 0 0 0 0 0 0 0 0 16 Total Uses 326,600 1,413,500 3,867,300 5,920,900 5,197,700 6,683,700 6,200,500 4,930,300 0 3,536,900 17 Annual Surplus (Deficiency) (120,200) (101,500) 103,950 776,475 (12,800) 90,800 152,700 (87,700) 245,700 (445,000) 18 Ending Balance 100,000 0 100,000 880,000 870,000 960,000 1,110,000 1,020,000 1,270,000 830,000 19 Target Ending Balance 1.0% 220,000 230,000 270,000 330,000 380,000 450,000 510,000 560,000 560,000 590,000 ---PAGE BREAK--- DRAFT Table 3-4 Wastewater Utility Summary of Existing Debt Fiscal Year Ending June 30 Wastewater Wastewater Loan Loan Loan TOTAL Year Series 2008 #075 Series 1999 #015 Series 2003 #006 2008 - $ 57,444 $ 820,533 $ 877,977 $ 2009 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2010 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2011 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2012 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2013 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2014 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2015 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2016 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2017 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2018 56,096 $ 57,444 $ 820,533 $ 934,073 $ 2019 56,096 $ 57,444 $ 113,540 $ 2020 56,096 $ 57,444 $ 113,540 $ 2021 56,096 $ 57,444 $ 113,540 $ 2022 56,096 $ 57,444 $ 113,540 $ 2023 56,096 $ 56,096 $ 2024 56,096 $ 56,096 $ 2025 56,096 $ 56,096 $ 2026 56,096 $ 56,096 $ 2027 56,096 $ 56,096 $ 1,065,824 $ 861,662 $ 9,025,859 $ 10,953,345 $ ---PAGE BREAK--- DRAFT Table 3-5 Wastewater Utility Capital Improvement Program Fiscal Year Ending June 30 Funding Collection Budgeted Projected Total Description Sources Treatment 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2009-2013 $ $ $ $ $ $ $ $ $ $ $ Major Capital West Laramie Outfall MRG/GSLIB/C c 0 1,320,000 1,452,000 0 0 0 0 0 0 0 2,772,000 B-Line W. of RR to Mainlift SPT/C c 0 0 0 1,996,500 2,196,200 0 0 0 0 0 4,192,700 C1-Line Reynolds 30th - McConnell GSLIB/C c 0 0 0 0 0 0 1,505,800 1,656,400 0 0 3,162,200 C-Line 15 Kearney N. Canby GSLIB/C c 0 0 0 0 0 1,530,000 1,683,000 0 0 0 3,213,000 B2-Line Sprg Crk & Corthell/11th C c 0 0 0 0 0 0 0 1,714,900 0 0 1,714,900 C-Line Hancock to Canby SPT c 0 0 0 0 805,300 0 0 0 0 0 805,300 Abandon 27" (700) tie into new A-Line C c 0 44,000 0 0 0 0 0 0 0 0 44,000 Insituform N. Force Main C c 0 0 423,500 0 0 0 0 0 0 0 423,500 South Laramie Sewer Extension 75% GSLIB/B c 0 0 1,815,000 1,996,500 0 0 0 0 0 0 3,811,500 North Laramie Sewer Extension 67% 33% c 0 0 0 0 0 2,737,900 3,011,700 0 0 0 5,749,600 Automated Septage Receiving GSLIB/C t 0 0 0 0 0 0 0 0 0 3,536,900 3,536,900 Biosolids Improvements C t 0 0 0 0 2,196,200 0 0 0 0 0 2,196,200 Primary Clarifier or Grease Separator C t 0 0 0 0 0 2,415,800 0 0 0 0 2,415,800 Package Plant for South Laramie C t 0 0 0 0 0 0 0 1,559,000 0 0 1,559,000 Grit Removal Sys. Rehabiliation C t 306,600 0 0 0 0 0 0 0 0 0 306,600 South Laramie Sewer Study C t 20,000 0 0 0 0 0 0 0 0 0 20,000 Aerial Photography - 50% C t 0 49,500 0 0 0 0 0 0 0 0 49,500 Lighting System C t 0 0 24,200 0 0 0 0 0 0 0 24,200 Radiant Heating C t 0 0 42,400 0 0 0 0 0 0 0 42,400 Building Addition - 50% B t 0 0 0 665,500 0 0 0 0 0 0 665,500 West Laramie Lift Station GSLIB/C 0 0 0 931,700 0 0 0 0 0 0 931,700 Total Capital Improvements - Inflated Dollars 326,600 1,413,500 3,757,100 5,590,200 5,197,700 6,683,700 6,200,500 4,930,300 0 3,536,900 37,636,500 ---PAGE BREAK--- DRAFT Table 3-6 Wastewater Utility Summary of Projected Operation and Maintenance Expenses Fiscal Year Ending June 30 Line Estimated Budgeted Projected No. Description 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ $ Wastewater Administration Personnel 1 Current 114,863 168,545 174,444 180,550 186,869 193,409 200,179 207,185 214,436 221,942 229,710 2 Additional 0 70,000 144,900 224,957 310,441 401,633 498,828 602,335 623,417 645,236 667,820 3 Contractual 77,723 164,266 170,015 175,966 182,125 188,499 195,096 201,925 208,992 216,307 223,878 4 Materials and Supplies 3,297 6,600 6,831 7,070 7,318 7,574 7,839 8,113 8,397 8,691 8,995 5 Capital Equipment (Routine Capital) 13,000 0 40,000 41,600 43,264 44,995 46,794 48,666 50,613 52,637 54,743 6 Vehicles and Equipment 0 80,000 39,520 72,280 9,360 130,491 150,771 63,931 110,760 49,400 0 7 Total Wastewater Administration 208,883 489,411 575,710 702,423 739,376 966,600 1,099,507 1,132,155 1,216,615 1,194,213 1,185,145 Wastewater Collection 8 Personnel 163,377 202,381 209,464 216,796 224,383 232,237 240,365 248,778 257,485 266,497 275,825 9 Contractual 13,666 21,050 21,787 22,549 23,339 24,155 25,001 25,876 26,781 27,719 28,689 10 Materials and Supplies 29,314 57,300 59,306 61,381 63,530 65,753 68,054 70,436 72,902 75,453 78,094 11 Capital Equipment (Routine Capital) 3,237,703 275,000 275,000 286,000 297,440 309,338 321,711 334,580 347,963 361,881 376,356 12 Total Wastewater Collection 3,444,061 555,731 565,557 586,726 608,691 631,483 655,131 679,670 705,131 731,550 758,964 Wastewater Treatment 13 Personnel 382,012 443,686 459,215 475,288 491,923 509,140 526,960 545,403 564,492 584,250 604,698 14 Contractual 381,878 413,350 427,817 442,791 458,289 474,329 490,930 508,113 525,897 544,303 563,354 15 Materials and Supplies 50,111 94,500 97,808 101,231 104,774 108,441 112,236 116,165 120,230 124,438 128,794 16 Capital Equipment (Routine Capital) 193,564 0 100,000 104,000 108,160 112,486 116,986 121,665 126,532 131,593 136,857 17 Total Wastewater Treatment 1,007,565 951,536 1,084,840 1,123,309 1,163,145 1,204,396 1,247,112 1,291,346 1,337,151 1,384,584 1,433,703 Wastewater Biosolids 18 Personnel 0 0 0 0 0 0 0 0 0 0 0 19 Contractual 25,138 88,050 91,132 94,321 97,623 101,039 104,576 108,236 112,024 115,945 120,003 20 Materials and Supplies 0 2,000 2,070 2,142 2,217 2,295 2,375 2,459 2,545 2,634 2,726 21 Capital Equipment (Routine Capital) 0 0 60,000 62,400 64,896 67,492 29,246 30,416 31,633 32,898 34,214 22 Total Wastewater Biosolids 25,138 90,050 153,202 158,864 164,736 170,826 136,198 141,111 146,202 151,477 156,943 Wastewater Industrial Pretreatment 23 Personnel 78,469 81,759 84,621 87,582 90,648 93,820 97,104 100,503 104,020 107,661 111,429 24 Contractual 13,292 18,725 19,380 20,059 20,761 21,487 22,239 23,018 23,823 24,657 25,520 25 Materials and Supplies 1,409 3,540 3,664 3,792 3,925 4,062 4,204 4,352 4,504 4,662 4,825 26 Capital Equipment (Routine Capital) 0 0 0 0 0 0 0 0 0 0 0 27 Total Wastewater Industrial Pretreatment 93,170 104,024 107,665 111,433 115,333 119,370 123,548 127,872 132,348 136,980 141,774 Wastewater Lift Station 28 Personnel 0 0 0 0 0 0 0 0 0 0 0 29 Contractual 24,538 38,400 39,744 41,135 42,575 44,065 45,607 47,203 48,856 50,565 52,335 30 Materials and Supplies 1,938 6,000 6,210 6,427 6,652 6,885 7,126 7,376 7,634 7,901 8,177 31 Capital Equipment (Routine Capital) 2,129,110 0 0 0 0 0 0 0 0 0 0 32 Total Wastewater Lift Station 2,155,586 44,400 45,954 47,562 49,227 50,950 52,733 54,579 56,489 58,466 60,513 33 Pipes Repair and Replacement 0 0 0 0 0 0 0 0 0 1,158,792 1,247,051 33 Total O&M (exluding capital equipment) 1,361,027 1,960,152 2,057,927 2,236,317 2,326,749 2,609,315 2,799,492 2,891,405 3,037,196 3,078,261 3,134,871 34 Total Capital Equipment (Routine Capital) 5,573,377 275,000 475,000 494,000 513,760 534,310 514,738 535,327 556,740 1,737,802 1,849,221 35 Total Operation & Maintenance 6,934,404 2,235,152 2,532,927 2,730,317 2,840,509 3,143,625 3,314,230 3,426,732 3,593,936 4,816,063 4,984,093 ---PAGE BREAK--- DRAFT Table 3-7 Wastewater Utility Vehicles Analysis Fleet Schedule 2010 2011 2012 2013 2014 2015 2016 2017 2018 Total Replacement Wastewater Vehicles $0 $22,000 $0 $42,000 $92,000 $16,000 $0 $0 $0 $172,000 Replacement Wastewater Equipment $0 $47,500 $9,000 $125,000 $7,500 $0 $68,500 $47,500 $0 $305,000 Total Vehicles and Equipment $0 $69,500 $9,000 $167,000 $99,500 $16,000 $68,500 $47,500 $0 $477,000 New Wastewater Vehicles $38,000 $38,000 $0 $0 $38,000 $0 $0 $38,000 $0 $0 $114,000 New Wastewater Equipment 50,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Total New Vehicles and Equipment $38,000 $0 $0 $38,000 $0 $0 $38,000 $0 $0 $114,000 Input: Lease Purchase Terms 2010 2011 2012 2013 2014 2015 2016 2017 2018 Amount of Issue - Big Equipment 0 0 0 125,000 0 0 0 0 0 Interest Rate - % 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% Payment Period - years 3 3 3 3 3 3 3 3 3 Lease Purchase Schedule - Big equipment 2010 2011 2012 2013 2014 2015 2016 2017 2018 2010 Issue $0 $0 $0 $0 $0 $0 $0 $0 $0 2011 Issue $0 0 0 0 0 0 0 0 2012 Issue $0 0 0 0 0 0 0 2013 Issue $45,472 45,472 45,472 0 0 0 2014 Issue $0 0 0 0 0 2015 Issue $0 0 0 0 2016 Issue $0 0 0 2017 Issue $0 0 2018 Issue $0 2019 Issue 2020 Issue $0 $0 $0 $45,472 $45,472 $45,472 $0 $0 $0 Total Vehicle Expense on O&M Total Vehicles/small trucks $38,000 $22,000 $0 $80,000 $92,000 $16,000 $38,000 $0 $0 $286,000 Big Equipment $0 $47,500 $9,000 $45,472 $52,972 $45,472 $68,500 $47,500 $0 $316,416 Vehicles and Equipment $38,000 $69,500 $9,000 $125,472 $144,972 $61,472 $106,500 $47,500 $0 $602,416 Vehicles and Equipment (inflated) $39,520 $75,171 $10,124 $146,784 $176,381 $77,782 $140,147 $65,007 $0 $730,916 ---PAGE BREAK--- DRAFT Table 3-8 Wastewater Employee Efficiency Analysis Fiscal Year Ending June 30 Line Budget Projected No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ Benchmark Comparison 1 Wastewater Accounts 8,390 8,474 8,559 8,644 8,731 8,818 8,818 8,818 8,818 8,818 8,818 2 Personnel 3 Current 12.5 13.5 14.5 15.5 16.5 17.5 18.5 19.5 19.5 19.5 4 Additional 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.0 0.0 0.0 5 Total Personnel 13.5 14.5 15.5 16.5 17.5 18.5 19.5 19.5 19.5 19.5 Total Customer Accounts per Employee 627.7 590.3 557.7 529.1 503.9 476.6 452.2 452.2 452.2 452.2 6 Minimum - Benchmark 297 7 Median - Benchmark (percent of total) 530 8 Percent of Minimum 211.3% 198.7% 187.8% 178.2% 169.7% 160.5% 152.3% 152.3% 152.3% 152.3% 9 Percent of Median 118.4% 111.4% 105.2% 99.8% 95.1% 89.9% 85.3% 85.3% 85.3% 85.3% Benchmark Comparison 1 MGD Flow 4.28 2 Personnel 3 Current 12.5 13.5 14.5 15.5 16.5 17.5 18.5 19.5 19.5 19.5 4 Additional 1.0 1.0 1.0 1.0 1.0 1.0 1.0 0.0 0.0 0.0 Total Personnel 13.5 14.5 15.5 16.5 17.5 18.5 19.5 19.5 19.5 19.5 5 Total MGD Delivered per Employee 0.32 0.30 0.28 0.26 0.24 0.23 0.22 0.22 0.22 0.22 6 Minimum - Benchmark 0.14 7 Median - Benchmark (percent of total) 0.22 8 Percent of Minimum 226.5% 210.8% 197.2% 185.3% 174.7% 165.3% 156.8% 156.8% 156.8% 156.8% 9 Percent of Median (percent of total) 144.1% 134.2% 125.5% 117.9% 111.2% 105.2% 99.8% 99.8% 99.8% 99.8% Total includes additional personnel. Additional Personnel Salary $70,000 ---PAGE BREAK--- DRAFT Table 3-9 Wastewater Utility Renewal and Replacement Analysis Fiscal Year Ending June 30 Line Budget Projected No. Description 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 $ $ $ $ $ $ $ $ $ $ Benchmark Comparison 1 Replacement Cost New 25,681,975 $ 2 Capital Investment 0 1,364,000 2,370,500 726,000 1,005,000 4,267,900 3,462,600 359,600 0 0 3 System Investment 275,000 275,000 286,000 297,440 309,338 321,711 334,580 347,963 361,881 376,356 4 Additional System Investment Needed 0 0 0 0 0 0 0 0 880,000 915,000 5 City of Laramie, Collection System Investment 275,000 $ 1,639,000 $ 2,656,500 $ 1,023,440 $ 1,314,338 $ 4,589,611 $ 3,797,180 $ 707,563 $ 1,241,881 $ 1,291,356 $ 6 Minimum - Benchmark 2.2% 565,003 $ 621,504 $ 683,654 $ 752,020 $ 827,222 $ 909,944 $ 1,000,938 $ 1,101,032 $ 1,211,135 $ 1,332,249 $ 7 Median - Benchmark 2.6% 667,731 $ 734,504 $ 807,955 $ 888,750 $ 977,625 $ 1,075,388 $ 1,182,927 $ 1,301,219 $ 1,431,341 $ 1,574,476 $ 8 Percent of Minimum 290.1% 427.4% 149.7% 174.8% 554.8% 417.3% 70.7% 112.8% 106.6% 9 Percent of Median 245.5% 361.7% 126.7% 147.9% 469.5% 353.1% 59.8% 95.4% 90.2% Total Collection System Investment Needed includes Additional System Investment Needed. Percent of Median includes Total Collection System Investment.