Capital Improvement Programming: Calculations
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--
The calculations for this spreadsheet are fairly straightforward.
The first step is to enter the data into the initial worksheet (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 19x4TAX-SUPPORTED G.O. BONDS xxxx xxxx xxxx xxxx xxxxSELF-SUPPORTED G.0. BONDS .... .... .... .... ....
TRANSFER FUNDS .... 0 0 0 0CURRENT FUNDS .... 0 0 0 0REVENUE BONDS .... .... .... .... .... Figure 1 Notice
that the "Criterion Weights" worksheet specifies five criteria. These
are listed only as an illustration.
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
PROGRAM PRIORITIESSCORING:ESSENTIAL=3 CRITERIANECESSARY=2DESIRABLE=1 WGT 0 0 0 0 0 0 QUALITY TOTAL ECONOMIC OF C/B HOUSINGPROJECT 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 REVENUEPROJECT 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 REVENUEPROJECT 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.
© 1996 A.J.Filipovitch
Revised 11 March 2005