Capital Improvement Planning, the template for this chapter, is a set of 10 separate but related worksheets. Together they calculate the relative importance of each project, based on a predetermined set of criteria, and provide a framework for allocating funds for the projects over a five year period.
The first set is composed of three worksheets which are used to enter the initial information for the model: The "Assumptions" worksheet records information about the city's budget--what resources are available, how much debt is outstanding, terms on which money will be borrowed, etc. These 11assumptions11 are fairly tame (most of the information can be obtained from the current budget), but their implications can be far-reaching. A one-point difference in interest rates or bond terms can easily price a project out of the market, as can a one-point change in the debt/revenue ratio.
The "Criterion Weights" worksheet is used for listing the criteria by which projects are to be judged, and the relative weight to be assigned to each of the criteria. The worksheet is deceptively simple. Once the criteria and their weights are determined, the important decisions have been made. Most of the rest of the spreadsheet is just an explicit spelling out of the implications which were inherent in this worksheet.
The "Debt Capacity for Capital Improvements" worksheet is a simple statement of how much will be made available for capital improvements in each of the next five years. The user must decide what limits (if any) to put on self-supported general obligation bonds, revenue bonds, and use of current funds. The user must also estimate how much money will be available through transfers from the State and Federal government. The worksheet will calculate how much is available for tax-supported general obligation bonds, based on the assumptions and limits set in the first worksheet.
The next two worksheets draw on the initial information to build the capital improvements plan. The "Program Priorities" worksheet scores each project for its contribution to the criteria which were set earlier. The user determines (or has some other person or persons determine) whether the project is essential, necessary, desirable, or irrelevant to achieving each criterion. The worksheet automatically tallies the total score for each project.
The "Project Costs" worksheet is a record of the cost of each project for each of the types of funds on which the city may draw. This information must be supplied (or estimated) by the user. The worksheet also lists, for reference purposes, the ranking (i.e., the total score) of each project, the department which is requesting it, and the type of capital project (e.g., streets, equipment, building, etc.) which it represents.
The last five worksheets, "Capital Improvement Funding," provide spaces for apportioning the intended projects over the next five years. The highest-ranking projects from the "Project Costs" worksheet are assigned by the user to the first funding worksheet. The worksheet keeps a running tally of the costs of the projects proposed for the year, and also provides, for comparison, the total revenue projected to be available for that year (from the "Debt Capacity" worksheet). When the user judges that the debt for the year has approached capacity, s/he proceeds to the next funding worksheet and repeats the process with the remaining projects.
The process for building the "Capital Improvement Funding" worksheets is clumsy. It would be preferable to have the spreadsheet rank and sort the projects by their scores, and then assign them to the funding worksheets in rank order, up to the point that a fund level is exceeded. Such routines are possible in spreadsheets, using "macros." That might be a more sophisticated modification the user might want to attempt.
© 1996 A.J.Filipovitch
Revised 11 March 2005