Conduct a financial analysis of water system operation for the City of Smallville over the next ten years. Use the data below. If you lack data, make assumptions or ask for it. Analyze O&M, capital costs and cash flows. The water system currently serves 100,000 people and is to be expanded to handle a population influx of 5% per year for the next ten years. Land use is mixed residential and commercial, but no industry. Base your estimates on residential demands and assume that commercial water use adds 15% to the residential use. You expand the system to accommodate growth and simultaneously maintain and renew the existing system during the period. The system is currently 15 years old and has depreciated on a 30-year depreciation cycle at 3.33% per year on a straight-line basis.

Your system expansion will be staged so that half is built now and half in five years. When you also invest in system renewal to overcome depreciation, the investments would be added to the costs of system expansion. System renewal is governed by the rule that current system value must not fall below 50% of replacement value.

You will take a loan for the first part of the construction and issue bonds for the next increment (in five years). You may vary from this if you choose different capital financing vehicles. Loans are revolving loans and come from an infrastructure bank. Annual loan payments begin in one year and continue for ten years. Recommend how to finance the expansion and renewal with funding from plant investment fees, water use fees, sales tax revenues, and property tax revenues. Commercial property has 25% of the assessed valuation of the residential property.

Current population	100,000 (33,333 households)
Rate of population growth	5% per year for ten years; 0% after that.
Per capita water usage (average)	150 gpcd
Land Use	Mixed residential and commercial.
Planning horizon for capital improvements	10 years to meet demands; 30 yrs for system life
Plant investment fee	$5,000 per house connection
Current water fees	$2.50/1000 gal
Property tax dedicated to water system improvements	0.8 mills
Assessed valuation residential (market value * 0.2)	$980 million
Sales tax dedicated to water system	0.8%
Current anticipated taxable sales	$800 million per year
Interest rate on loan (due in ten years)	8%
Interest rate on bonds (use 20 year life)	6%
Projected inflation rate	0%
Capital cost of new or replacement system	$3,000 for each new person
Current value of existing system (average age15 years)	Replacement value less 15 years depreciation
Capital improvement goal	System value not below 50% of replacement
Depreciation of assets	3.33% per year
O&M cost	$50 per capita per year

1. Set up a spreadsheet and forecast cash flows for the next fifteen years.

2. Schedule for capital improvements (the capital improvement program)