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.