Data for capital improvement planning basically come from two
sources: budget information comes from the finance department
or the appropriate department heads, information about values
comes from the community.
Most of the data for the "Assumptions" section at the
beginning of the model can be obtained from existing budgets.
In some cases, the analyst may wish to alter data based on prior
trends. Current events may lead one to suspect that there may
be change in the rate of growth of the budget, or the interest
rate on a bond. In such cases, the finance department or the city's
bond consultant should be able to provide advice. Similarly, the
debt capacity of a city is determined by past financial performance
tempered by expert judgment of an appropriate debt/revenue ratio.
A list of projects and their anticipated costs would have to be
obtained from the department heads.
Obtaining information about values is much less straightforward.
This spreadsheet requires three separate value judgments: Which
are the appropriate criteria, what are their relative weights,
and what are the merits of each project in terms of each of the
criteria? There are a variety of methods that can be used to answer
these questions. The same method may be used for more than one
set of judgments; different methods may be used for each set.
None is clearly superior to all the others, although each shades
the results differently.
Probably the most common method used to obtain criteria, weights,
and scores is expert judgment." Someone, or a relatively
small group of people, decides which values are appropriate. The
problem, of course, is to decide who should make such judgments.
The most frequent choice is the chief administrator (or her/his
cabinet) or else the chief policy maker--the mayor or the council.
The administrator mav have a clearer perception of the functional
performance of each of the projects; the mayor may have a clearer
perception of the political implications of each of the projects.
In either event, the "expert" is making an attempt to
judge what the community wants (or would want, if it knew all
the facts). So why not let the people themselves make the value
judgments?
A community survey is, in fact, a tool which is commonly used
to make value judgments in capital improvement programs. It has
the advantage of eliminating the middlemen. No expert has to guess
at what the people want--they can tell you directly. Unfortunately,
it rarely works that way in practice. The issues are often technical,
and sometimes very complex. It is hard for "the man on the
street" to make an informed judgment. Further, even if one
is familiar with the technical details of the projects, it is
difficult to design a survey which can generalize individual preferences
into a single array of values (see Arrow, 1963). In other words,
the process of creating consensus across a community of values
is not an objective, mathematical process like solving a simultaneous
equation of n unknowns; it is, instead a political process which
requires negotiation and compromise.
There are other techniques that have been used--Delphi, multiattribute
method, and Q-sort, among others. These techniques are primarily
derived from the first two, and represent a greater sophistication
in method but no basic advance in underlying philosophy.
The calculations for this spreadsheet are fairly straightforward.
The first step is to enter the data into the initial worksheets
(Figure 1).
ASSUMPTIONS CRITERION WEIGHTS BEGINNING YEAR ...... HEALTH ...... CURRENT TAX REVENUE ...... SAFETY ...... BUDGET GROWTH RATE ...... ECONOMIC DEVELOPMT ...... INTEREST RATE ...... QUALITY OF LIFE ...... DEBT/REVENUE RATIO ...... COST/BENEFIT RATIO ...... CURRENT TAX DEBT ...... HOUSING STOCK ...... TAX DEBT RETIRED-- 0 ...... 1 ...... 2 ...... 3 ...... 4 ...... TERMS OF BOND ...... ISSUE COST FACTOR ...... DISCOUNT FACTOR ...... DEBT SERVICE CONST xxxxx DEBT CAPACITY FOR CAPITAL IMPROVEMENTS 19x0 19x1 19x2 19x3 19x4 TAX-SUPPORTED G.O. BONDS xxxx xxxx xxxx xxxx xxxx SELF-SUPPORTED G.0. BONDS .... .... .... .... .... TRANSFER FUNDS .... 0 0 0 0 CURRENT FUNDS .... 0 0 0 0 REVENUE BONDS .... .... .... .... .... Figure 1
Notice that the "Criterion Weights" worksheet specifies
five criteria. These are listed only as an illustration. Minneapolis
uses 12 criteria in its capital improvements planning model; Red
Wing, MN, uses 14. The more criteria included in the analysis,
the more the analysis will be able to separate the projects. On
the other hand, there is also greater need to carefully distinguish
the relative weights as the number of criteria increases.
Notice also that the spreadsheet calculates the debt capacity
for tax-supported general obligation bonds, but the user must
enter the appropriate values for the other four sources of funds
for each year. The worksheet will assume that transfer funds and
current funds available for capital improvements will be the same
for each of the five years--once the value for the first year
is entered, it will be carried to each of the four succeeding
years. The user may override this assumption by simply entering
the appropriate values for any of the succeeding years as well.
Once the initial assumptions are entered, the user enters each
project and its score on each of the criteria (Figure 2). The
"xxxx" represents the total points (the weighted ranking)
for each project. The worksheet recommends a four-point scoring
system ("3" if essential, "1" if desirable,
"0" if irrelevant or undesirable), similar to the one
used in Red Wing, MN. The Minneapolis capital improvement program
uses a more complicated scoring system; this has the advantage
of making more sophisticated distinctions, but it greatly increases
the difficulty both of use and of interpretation. Note that the
spreadsheet automatically transfers the criteria weights, specified
in the initial assumptions worksheets, into the program priorities
worksheet.
PROGRAM PRIORITIES SCORING: ESSENTIAL=3 CRITERIA NECESSARY=2 DESIRABLE=1 WGT 0 0 0 0 0 0 QUALITY TOTAL ECONOMIC OF C/B HOUSING PROJECT NAME POINTS HEALTH SAFETY DEVELOPMT LIFE RATIO STOCK ............ xxxx ...... ...... ...... ...... ...... ...... ............ xxxx ...... ...... ...... ...... ...... ...... ............ xxxx ...... ...... ...... ...... ...... ...... ............ xxxx ...... ...... ...... ...... ...... ...... Figure 2
In the third step, the user enters the project costs into the
worksheet (Figure 3). The worksheet automatically transfers the
weighted rank score from the program priorities worksheet. The
user must assign the cost of the program to one or more funds
which will be used to pay for it. The worksheet also includes
two columns (not illustrated in Figure 3) to flag program characteristics
which might be useful for management purposes. One column, labeled
DEPARTMENT, allows the user to monitor the amount of money going
to each department. The second column, labeled TYPE, allows the
user to monitor the amount of money going to each type of use
(equipment, building, streets, parks, etc.). These two items are
not used elsewhere in the spreadsheet and entering the appropriate
data is optional.
PROJECT COSTS FUND TYPE TAX SELF TRANSFER CURRENT REVENUE PROJECT NAME RANK G-O BOND G-O BOND FUNDS FUNDS BONDS ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... Figure 3
In the fourth and final step, the projects are allocated to one
(or more) of the five years of the Capital Improvement Plan (Figure
4). The worksheets for each of the five years are identical, except
for designating which year is in question. Notice that the entries
in the worksheet are identical to the Project Cost Worksheet,
with the addition of rows for projected and running totals.
CAPITAL IMPROVEMENT FUNDING FUND TYPE TAX SELF TRANSFER CURRENT REVENUE PROJECT NAME RANK G-O BOND G-O BOND FUNDS FUNDS BONDS PROJECTED TOTALS 0 0 0 0 0 RUNNING TOTALS 0 0 0 0 0 ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... ............ 0 ...... ...... ...... ...... ...... Figure 4
The "projected totals" row is drawn from the appropriate
column of the debt capacity worksheet. The transfer is handled
automatically by the spreadsheet. The "running totals"
column is the sum, by fund type, of the cost of all the projects
already entered on that worksheet. Because of this, care must
be taken to add or delete rows between the first and last rows
given on the worksheet. If the last row is deleted, the sum of
the column could include irrelevant information. If rows are added
beyond the last given row, those values would not be included
in the running total.
Allocating the projects to a selected year must be done manually.
Many spreadsheets have the ability to sort and transfer rows of
information; if you are using one of them, you can reprogram the
spreadsheet to perform this step automatically. Whether it is
done manually or automatically, the highest-ranking project should
be entered onto the first year's worksheet, followed by the highest
remaining project, and so on until an additional project would
run the total for a fund over its projected limit. It is not necessary
to key in the data for each project--use the "Copy"
function to transfer the entire row.
Once one of the funds has been exhausted for the year, the user must decide between two procedures. On the one hand, the highest-ranked of the remaining projects can be assigned to the next year, and the process repeated for that worksheet. The user may choose to carry forward the unused balance of revenue available to the remaining funds from the previous year. On the other hand, the user may choose to continue assigning projects which draw on the other funds until all of the available resources are allocated as far as possible. This has the advantage of making the most use of all available resources; it has the disadvantage of moving projects ahead of higher priority projects which have the misfortune to be funded from shallow (or more popular) pockets.