2035 Regional Transportation Plan
Financial Analysis Project: User’s Guide
November 5, 2006
Houston-Galveston Area Council of Governments
Wilbur Smith Associates
1.0 Introduction and Methodology
The 2035 Financial Model has been developed as a tool to assist the Houston-Galveston
Area Council of Government (H-GAC) in forecasting the financial cost and funding
sources associated with transportation activities in the 8-county TMA. This financial
model was built on an Excel spreadsheet with a Visual Basic interface that has been
designed to assist policy-makers in developing forecasts using different growth
assumptions. The forecasted results are based on primary data collected from over 50
agencies, including cities, counties, ports, airports, toll road authorities, transit agencies,
freight rail projects stemming from the Houston Rail Plan, and TxDOT sponsored
projects. The 2035 Financial Model uses historical data and planned short-term future
investments drawn from publicly available documents to establish expenditure and
revenue patterns that are carried out through the entire forecast period—2006 through
2035. The financial model uses aggregate data for key financial categories to develop
1.1 Data Sources
With respect to data collection, the goal of the project team was to collect primary
historical and forecast financial data, where possible. The intent was to be able to develop
estimates over the forecast period (defined as 2006-2035), which reflect historical
spending patterns. In addition, the project team sought to be as comprehensive, as
possible with respect to the number of agencies included in this analysis. Data sources
used to develop the 2035 Financial Model include the following:
• Comprehensive Annual Reports (CAFR)
• Budgets, particularly the Street/Bridge Departments, Capital Projects
Enterprise Funds, Debt Repayment Schedules, Airport Enterprise Fund (if
• Capital Investment Plans (CIP)
• Direct telephone and email communications with City and County officials
• Direct telephone and email communications with TxDOT, METRO, HCTRA
and other transportation agencies
• Information drawn from the Houston Rail Plan website: houstonrailplan.org
• Engineering Reports and Traffic Studies
1.2 Agency List
The 2035 Financial Model represents a summary of planned and forecasted revenues and
expenditures for 29 cities, 8 counties, and 15 transportation agencies. The primary
criterion for inclusion of cities in the financial model was largely based on existing
population, which was used as a proxy for the supply/demand for significant
transportation activities, especially during the 30-year forecast period. For the most part,
cities with approximately 10,000 inhabitants were included in this analysis. Figure 1
summarizes the agencies that were included within the financial model.
Figure 1: List of Agencies in the Financial Analysis Project, 2035 RTP
City County Transportation Agency
Alvin • Brazoria County • Colorado Valley Transit
Angleton • Chambers County • The District
Baytown • Ft. Bend County • Ft. Bend Toll Road Authority
Bellaire • Galveston County • Ft. Bend Transit
Conroe • Harris County • Grand Parkway Association
Dickinson • Liberty County • Galveston Island Transit
Freeport • Montgomery County • Gulf Coast Transit
Friendswood • Waller County • HCTRA
Galveston • Houston Rail Plan
Houston • Port of Freeport
Humble • Port of Galveston
Katy • Port of Houston
Lake Jackson • Transtar
La Marque • TxDOT-Beaumont District1
La Porte • TxDOT- Houston District
1. Includes only TxDOT expenditures in Waller County and Liberty County.
Most of the agencies listed in Figure 1 have a dedicated worksheet containing historical
and forecast financial data. However, there were a number of agencies included in this
analysis that are represented within a larger jurisdiction. For all practical purposes, these
entities are institutionally and/or legally considered to be components of the larger
agencies. Moreover, the larger agency may also be the primary funding partner. In some
cases, there may not be any formal constraints preventing the transfer of some of the
revenues generated by these agencies, e.g. tolls, aircraft landing fees, etc to the general
fund. A notable example is HCTRA, which along with the County Engineering
Department and the County Flood Control District are separate divisions within the
Harris County Public Infrastructure Department. Similarly, Ft. Bend Toll Road Authority
forms part of the Ft. Bend County Government. A brief description of the legal and
governance structure of Fort Bend Toll Road Authority highlights this reporting
Ft. Bend Toll Road Authority website, October 2006
“The Fort Bend Toll Road Authority is organized under the Texas
Transportation Corporation Act and the Texas Non-Profit Corporation
Act. The Authority was created to assist in the planning, designing,
financing and building of county roads and highways. In particular, the
Authority is to assist in the building of the Fort Bend Toll Road that will
extend from Sam Houston Parkway in Harris County to State Highway 6
in Fort Bend County. The (County) Commissioners Court appoints the
Authority’s governing body. The County has financial accountability
because it appoints a voting majority of the Board and the County can
impose its will. The Authority does not issue separate financial statements.
The agencies that have been combined under a single spreadsheet include the following:
• The “Houston” worksheet includes, the City of Houston, Transtar and Houston
• The “Harris County” worksheet includes Harris Count and HCTRA
• The “Ft. Bend County” worksheet includes, Ft. Bend County, the Ft. Bend County
Toll Road Authority, and Ft. Bend Transit.
• The “TxDOT Houston District”, includes TxDOT Beaumont District projects in
Liberty County and Waller County
1.3 Data Limitations
Because of the general difficulty and time intensive of nature of collecting project level
data from over 50 agencies, the financial model has been populated, in large part, using
aggregate data drawn from agency financial statements and budgets. Project level has
been drawn from only the largest agencies included in this analysis, such as the City of
Houston, Harris County, HCTRA, Houston Rail Plan, Ft. Bend County, Grand Parkway
Association, TxDOT, etc.
Although toll forecasts have been generated for this analysis, these forecasts do not
constitute formal traffic and revenue forecasts nor should these estimates be used to
support direct investment decisions. Instead, the revenue forecasts are intended to
provide an indicative or high-level “ballpark” estimate of the potential amount of
revenues generated by toll facilities during the forecast period for all of the agencies
included in this analysis.
Finally, the estimates presented in this report are subject to change, as the project team is
awaiting the collection of additional information. Specific data limitations related to the
estimates presented include the following:
• Because METRO’s New Starts application is not yet available, these results include
preliminary estimates based on METRO’s historical financial data. The estimates
listed will be updated to include the information provided by METRO once the data
New Starts application has been received and incorporated into the financial model.
Notwithstanding, historical financial information should indirectly capture revenue
and expenditure data contained in previous New Start applications.
• The project team was unable to collect data from the Port of Texas City. As a
privately-owned entity, it is not required to make its financial information publicly
• To the extent possible, the project team attempted to enter data from Capital
Investment Plans, which summarized the planned project level expenditures in the
short-term. Most of the CIPs that were used to populate the financial model typically
covered 3 to 5 year periods. However, the project team was able to locate CIPs for
only the largest agencies within the study area. In the manner, capital expenditures
estimates for the smaller agencies were based solely on historical patterns.
• During the course of the project, a number of smaller cities had been considered for
inclusion within the financial model. These agencies were excluded from the financial
model due either: (i) the inability to locate recent financial data; or; (ii) had less than
10,000 inhabitants, which suggested that agency’s transportation expenditures
comprised a relatively small percentage of total expenditures within the TMA. These
entities include, but are not limited to, Clute, Cleveland, Dayton, Deer Park, Galena
Park, Jersey Village, Liberty, Manvel, Meadows, and Santa Fe.
Notwithstanding, these entities can be added a later data subject to the availability of
financial data. In particular, the financial information for these entities can be added by
copying the “Template” worksheet, inserting a new Excel worksheet, entering the
financial data specific to that agency, and establishing links to the main summary
worksheets—“Revenues,” “Operational” “New Capacity” and “System Preservation” and
“Totals.” These worksheets are described in greater detail in the next section. The
“Summary” and “Charts” worksheets, which provide aggregate values for the entire
TMA by mode, expenditure category, and funding source, can be updated once these
links have been created.
2.0 Financial Model Structure
The 2035 Financial Model has been developed by taking the data collected from primary
data sources and entering this information for each agency. Financial data was then split
up according to financial category: (i) revenues; (ii) operational expenditures; (iii) capital
expenditures relating to added capacity and (iv) capital expenditures related to system
preservation. Each of these 4 main categories was then divided into specific sub-
categories, which are described in greater detail in the subsequent sections. In addition,
categories ii-iv, which correspond to cost side parameters, were then further subdivided
in accordance to the following transportation modes: (i) highway/roads/streets; (ii) transit
(bus and rail); (iii) bike and pedestrian; (iv) airport, (v) port & ferry; and (vi) freight rail
Within the financial model, links were established from each agency worksheet to
recombine the data by category. Each of these categories—revenues, operational
expenditures, added capacity, and system preservation—constitutes a separate worksheet.
In addition, a total expenditures worksheet, which sum up all operational and capital
expenditures, has also been created. This data is then link to a summary worksheet, which
shows annual as well as forecast period totals of all funding sources by type, a total of all
expenditures by type, and the fiscal gap (or surplus). The fiscal gap (or surplus) is the
difference between all expenditures and all revenues for the entire study area. Figure 2
provides an overview of the general structure of the 2035 Financial Model.
Figure 2: 2035 Financial Model Structure
Data Collection and Forecasting
City County Agency
Breakdown by Data Category
Revenues Added Capacity
Breakdown by Mode
Highways/Roads/ Bike & Port & Freight
Streets Pedestrian Ferry Rail
Summary & Charts
The Revenue category includes all potential sources of funds that agencies can use to
cover transportation expenditures. Specifically, revenues have been divided into the
• Federal, e.g. FHWA, FTA, Homeland Security, and FAA funds, etc.
• State, e.g. TxDOT, Motor Fuels Tax, State Infrastructure Bank, etc.
• Local. For cities and counties, most transportation expenditures are typically paid
for by funds generated within their respective General Funds—e.g. property tax,
sales tax, charges for services, etc., which can be used to pay for a wide range of
local government services. Local government revenues are not typically dedicated
toward transportation related expenditures, but rather, are used on a “pay-as-you-
basis.” The construction, expansion, rehabilitation, operation, and maintenance of
road and streets, which account for a large percentage of local government
revenues, are carried out in this manner. If local funds are not available in the
short-term, then cities and counties have the option of issuing bonds backed by
local government revenues.2
• Tolls. This category is defined as revenues generated from tolls on highway
• User Fees. This includes revenues generated from fares (transit and ferry),
landing fees and passenger charges (airports), and wharf and dockage charges
• Debt. This includes both General Obligation bonds backed by the taxing authority
of the local government agency, Revenue bonds backed by tolls and/or user fees,
and private bank loans.
• Other. This category can include investment income, asset sales, private grants,
concession/lease fees etc.
2.2 Operational Expenditures
This category includes (i) administrative (e.g. salaries, professional services and fees,
depreciation); (ii) operational activities by mode; (iii) maintenance activities by mode;
(iv) financing, including debt interest payments, principal payments, debt issuance costs;
and (v) other miscellaneous items.
Many cities combine use these debt issues to finance several types of infrastructure projects, including
roads, drainage, storm water, electricity, water, and sewerage, etc.
2.3 Added Capacity
With respect to capital expenditures for added capacity, the project team has attempted to
adhere to the category definitions used by TxDOT in the Unified Transportation Plan
(UTP). Within TxDOT’s 12-category classification system, TxDOT has designated
Categories 2-5, 7, and 9-12 as constituting added capacity (or “Build It”). Below are brief
descriptions of each of the categories:
• Category 2 Metropolitan Area (TMA) Corridor Projects. Funds under this
category are used for projects that address mobility needs within Transportation
Management Areas (TMA) and are represented by a Metropolitan Planning
• Category 3: Urban Area (Non-TMA). These projects address the mobility and
add capacity projects on major state highway system corridors serving the
mobility needs of urban areas that are not represented by (Non-TMA) MPOs.
• Category 4: Statewide Connectivity Corridor Projects. Funds under this
category are allocated toward mobility and added capacity projects on major state
highway system corridors which serve the mobility needs or improve connectivity
both between urban areas and/or throughout the state. Projects under this category
can include connectivity improvements along the Texas Trunk System the
National Highway System as well as connections from Texas Trunk System to
major ports on international borders or Texas water ports
• Category 5: Congestion Mitigation and Air Quality (CMAQ) Improvement.
Project under this category attempt to address the attainment of national ambient
air quality standard in the non-attainment areas (currently Dallas-Fort Worth,
Houston, Beaumont, and El Paso). Funds under this category cannot be used to
add capacity for single occupancy vehicles.
• Category 7: Metropolitan Mobility and Rehabilitation. These projects are
designed to address the transportation needs within metropolitan area boundaries
with populations greater than 200,000 and have been selected by MPO.
• Category 9: Transportation Enhancements. Projects under this category
include initiatives that are beyond the typical scope of transportation
enhancements, as outlined in TEA-21 and/or SAFETEA_LU. This category can
include projects that have been recommended by local government entities,
reviewed and recommended by committee, and selected by Texas Transportation
Commission. Moreover, funds can be used to renovate, build, and relocate safety
rest areas along the state highway system.
• Category 10: Supplemental Transportation Projects. This category includes:
(i) new landscape development, aesthetic improvements; (ii) rest area/picnic area
landscape development; (iii) erosion control and environmental mitigation
activities on the state highway system, (iv) the construction/rehabilitation of
roadways within or adjacent to state parks, fish hatcheries, etc. and (v) the
replacement of rough railroad crossing surfaces; and (vi) Contributions to
construct/ maintain signals.
• Category 11: District Discretionary. This category includes projects that have
been selected at the TxDOT district’s discretion.
• Category 12: Strategic Priority. This category includes projects, which promote
economic development, provide system continuity with adjoining states and
Mexico, increase efficiency of military deployment routes, or address other
strategic needs as determined by the Texas Transportation Commission.
Where available, the project team used the CIPs developed by the agencies in the analysis
to guide the entry of added capacity and system preservation data. The aggregate level
information that was entered into the financial model reflects the summation of planned
transportation projects for a given year. To the extent that the data contained CIP
adequately described the transportation projects under development, these expenditures
were allocated to either added capacity or system preservation depending level of
congruity with the TxDOT categories.
Because most of the difficulty of locating CIPs for the majority of the agencies in the
study area, the forecast of future year capital expenditures was based on historical
expenditure patterns. Typically, this data was not detailed with respect to the intended
purpose—e.g. added capacity or system preservation. As a result, historical expenditure
patterns were allocated to both categories based on an assumed rate—e.g. 50 percent of
Finally, cost contingency amounts, which are estimated as percentage of all capital
expenditures, are included under Added Capacity. Within the financial model, annual
cost contingency amounts have been defaulted at 10 percent of total annual capital
expenditures—both added capacity and system expenditures.
2.4 System Preservation
With respect to capital expenditures for added capacity, the project team has attempted to
adhere to the category definitions used by TxDOT in the Unified Transportation Plan
(UTP). Within TxDOT’s 12-category classification system, TxDOT has designated
Categories 2-5, 7, and 9-12 as constituting added capacity (or “Build It”). Below are
brief descriptions of each of the categories:
• Category 1: Preventative Maintenance and Rehabilitation. Funds used for the
rehabilitation and maintenance of existing state highway system. Funds under this
category can also be used to construct interchanges and high occupancy lanes.
• Category 6: Bridge Replacement and Rehabilitation Program. Funds
allocated for the replacement and/or improvement of functionally obsolete or
structurally deficient bridges. Within this category, funds could be used to
rehabilitate/construct highway overpasses, railroad underpasses, eliminate at-
grade highway-railroad crossings, and improve public safety in close proximity to
• Category 8: Safety Projects. Funds allocated under this category are designed to
improve safety. Specific project include accident reduction, automated railroad
crossing warning systems, pedestrian and bicycle provisions and risk mitigation
construction and/or operational improvements for specified network segments.
It should be noted that preliminary estimates generated by the financial model indicate
that system preservation expenditures during the forecast period accounts for a smaller
percentage of total expenditures as compared to the 2035 RTP. This difference can be
attributed to the following factors: (1) apart from the TxDOT UTP, it is difficult to
differentiate between new capacity and system preservation expenditures based on
historical spending data; (2) the inclusion of cost contingency under added capacity,
which accounts for about 3 to 4 percent of total expenditures; and (3) O&M expenditures
listed in a general fund budgets and/or annual reports may indirectly capture system some
The “Totals” worksheet represents a summation of the all expenditures for each agency.
Specifically, this worksheet represents the total of all operational, added capacity, and
system preservation for each agency as well as by all cities, all counties, and all
transportation agencies. As a result, this worksheet has been created by the establishment
of direct links from the “Operational”, “Added Capacity” and “System Preservation”
2.6 Forecasting Methodology
To establish a level of consistency between the agencies, several guiding principals have
been developed, which were used to forecast financial data for each of the agencies
during the study period. These guiding principals underlying this methodology include
(1) The annual growth rate that was used in estimating transportation activities for
each agency was HGAC’s forecasted average annual population growth rate for
each county. For example, estimated revenue/expenditures for the City of
Houston and Harris County would increase by the forecasted population growth
rate for Harris County (1.47%). Similarly, the transportation revenues and
expenditures for the City of Galveston would increase by the forecasted average
annual population growth rate for Galveston County (1.34%) and so on. Figure 3
summarizes the growth rates used during the forecast period. Within the financial
model, these are the default rates for each agency.
Figure 3: H-GAC Population Growth Rates
County Growth Rate Assumption
Brazoria County 1.93%
Chambers County 1.88%
Fort Bend County 2.58%
Galveston County 1.34%
Harris County 1.47%
Liberty County 1.67%
Montgomery County 2.84%
Waller County 2.45%
(2) A composite growth rate was used for agencies that overlap more than a single
county, e.g. Gulf Coast Transit, which is located in both Brazoria and Galveston
(3) The growth rate for TxDOT activities was increased by H-GAC’s forecasted
growth rate for the 8-county TMA (1.72%). This includes capital expenditures for
the TxDOT-Houston District, the projects included under the Houston Rail Plan,
and the Grand Parkway Association.
(4) Local revenues, tolls, user fees, and operational expenditures typically increase
slightly from year to year. As a result, these items tend to increase slightly were
inflated by using the last year that historical data was available (e.g. 2005) and
multiplying this amount by 1+Growth Rate. If expenditure data was drawn from
CIPs or other forward-looking documents, then these expenditures were
forecasted out from the final year of the planned investments (e.g. 2009).3
(5) Because capital expenditures tend to have greater variability, it was necessary to
smooth out historical data prior to developing long range forecasts. The intent was
to preclude one-time expenditures associated with a major expansion or
reconstruction project from potentially skewing the forecast. This smoothing
process was done by taking an average of the aggregate expenditures for added
capacity and for system preservation for each mode. The average amount was
then inflated by the respective county growth rate for each agency.4
To the extent possible, the forecast period has been displayed in a different color for some of the key
parameters, especially if the forecast period is based on a later year, e.g. 2008.
This was not always possible, especially for agencies with only one year of historical data, e.g. the City
(6) For agencies without a historical track record and/or the lack of available data on
O&M expenses, a fixed percentage of the total amount of capital expenses was
used. This amount was typically 5 percent of total capital expenditures.
(7) Similarly, federal and state funds tend to be variable and also needed to be
smoothed out to avoid the input of a skewed estimate. As a result, an average of
historical receipt of these funds was used and then inflated by the respective
county growth for that agency.
(8) Tolls and user fees were forecasted out at slightly higher growth rates than the
growth rates used for other sources of funds as well as for operational and capital
expenditures. With the exception of the Grand Parkway as well as a few planned
managed lanes facilities5, tolls were forecasted out from historical aggregates not
from direct project level data. No capacity assumptions were incorporated, as it is
extremely difficult using only financial data to predict if and when a facility
reaches capacity. Along these lines, revenue growth can be attributed from
potential future increases in toll rates as well as potential system expansions that
are presently unforeseen. Finally, it should be reemphasized that the revenue
forecast presented in this analysis solely ballpark estimates and not intended to
support investment decisions, such as bond issuances, etc. 6
(9) Debt proceeds are also difficult to estimate based on historical patterns. This is
because the timing and the amounts of debt issuances tend to fluctuate greatly
over time. Along these lines, some agencies issue debt in separate tranches, other
agencies can issue debt obligations that are used to pay for a number of
infrastructure investments (e.g. water and transportation), and finally agencies can
issue a number of bonds within a few years and then not issue bonds to pay for
transportation activities for several years thereafter. To develop a reasonable and
conservative forecast, it was assumed that only the largest agencies, e.g. City of
Houston, Harris County, etc. would issue debt for transportation activities unless
otherwise specified. In addition, the use of debt financing was also estimated for
entities that operate and maintain airport facilities, since these issuances can be
securitized using landing fees and are accounted for under separate enterprise
funds. For these agencies, a historical average was used throughout the forecast
period. Because of the variable nature of debt proceeds, this line item was not
inflated during the forecast period. Finally, it should be noted that METRO has
not issued any debt; therefore no debt proceeds or obligations was entered during
the forecast period.
(8) Other revenues, such as investment income and asset sales, are subject to variable
market forces as well as maybe one-time and can not be forecasted with any
accuracy. Although these revenues were recorded as revenues received, these
The I-10, I-610, SH 35 and SH 288 project
Traffic & revenue forecast that are used to support bond issuances typically have stringent
confidentiality requirements to protect the issuer with respect to cost of capital, interest rate spreads, etc.
items were not used in the forecast period. As a result, zeroes were entered for
these items during the forecast period.
(9) Existing debt obligations, specifically interest and principal payments, were
entered using the debt payment schedules listed in the annual reports. Not all the
entities included in this analysis specified the debt obligations for transportation
activities. Due to the data constraints, the interest and principal payments were not
readjusted for those agencies in which additional were incorporated into the
financial model during the forecast period.
(10) More specific detail relating to data sources, methodology, and/or assumptions
used at the agency level can be found using the Excel comment function. Cells
with comments have a red mark located in the top right corner.
2.7 Sensitivity Analyses
Based on the review of the previous financial model, related documentation, and the 2025
RTP as well as through discussions with H-GAC staff, the 2035 Financial Model allows
for the testing of a number of independent variables. To facilitate the development of
different sensitivity analyses, a user interface was developed for this model to easily
change these variables. Section 4 of this report summarizes the general workings of this
interface as well as provides a “how-to” guide to change these variables.
2.7.1 Converting from Nominal to Real Dollars. One of the key data points
incorporated in the development of the financial model was a differentiation between real
and nominal dollars. In order to convert nominal dollars into real dollars, the following
data points are necessary: (1) the nominal value over time; (2) a price index; and (3) a
base year. Within the economics literature, the typical price indices used for converting
from Nominal to Real include the Consumer Price Index (CPI), the Producer Price Index
(PPI), the Personal Consumption Expenditure index (PCE) and the GDP deflator. For the
purposes of this analysis, CPI was used. Over the previous 10 years, the historical growth
rate in the CPI increased on an average annual rate of approximately 2.53 percent.
Although this rate was used to convert from nominal to real, a different amount can be
entered. Moreover, the base year was set at 2006. However, this input variable can be
adjusted using the input entry form.
2.7.2 Cost contingency. Because of the rapid rise in materials and construction costs, a
sensitivity analysis was created to allow for different cost contingency factors. Within the
present model, the default rate was set at 10 percent of total capital expenditures.
2.7.3 Tolls and User Fees. Tolls and user fees can provide a variable mechanism for
increasing revenues, and by extension, reducing the potential fiscal gap need to pay for
future transportation expenditures. Separate inputs areas were developed to allow for the
entry of different growth rates for both tolls and user fees. The annual default growth rate
during the forecast period was set at 5 percent. This amount can be changed to allow for
the entry of historical growth rates and/or growth rates taken from accepted traffic and
2.7.4 Financing Sources. A sensitivity analysis was created to allow for different
financing scenarios for TxDOT projects, using a mixture of local funds, state funds,
Federal funds, tolls, user fees, debt, and other sources of funds. This sensitivity analysis
was created only for TxDOT projects, since: (1) most of the other agencies are financing
recurring activities, such as administrative expenses, O&M expenses, etc. (2) most of the
other agencies have a steady source of General funds revenues that can be used to finance
transportation activities on a pay-as-you-go basis; and (3) TxDOT activities are largely
project based, which is more conducive for developing different scenarios during the
2.7.5 Overall Rate. A sensitivity analysis was developed that would change all the
default rates to a single growth rate. In the development of the base version of the
financial model, all the default setting has been maintained.
The main outputs generated by the 2035 Financial model are summarized in the
“Summary” and “Charts” worksheets. The summary worksheet provides both an annual
estimate of revenues and expenditures for the entire study by category as well as an
aggregate of total revenues and expenditure for the forecast period between 2006 and
2035. Both revenues and expenditures are expressed in nominal and real dollars. An
example of the type of information presented in the “Summary” worksheet is listed in
(a) Funding Sources (in Nominal Dollars)
User Fees 29,119,649,267
(b) Expenditures by Mode (in Nominal Dollars)
Freight Rail 13,485,008,850
(c) Expenditures by Category (in Nominal Dollars)
Added Capacity 81,484,070,271
As previously noted, the debt/other represents the potential fiscal gap. Because of the
difficulty in forecasting upcoming debt issuances, it is possible that debt may constitute a
significant percentage of funding resources that may be used to reduce the potential fiscal
gap. The remainder could come from a variety of resources, such as additional user fees
and tolls, private equity capital, etc.
Finally, the “Charts” worksheets which is linked directly to the “Summary” worksheet
provides bar and pie charts that summarize the data above in graphic format. At present,
the charts have been developed based on real dollars.
4. User Interface Guide
• MANUAL EDIT: Click this button to open all the work sheets and exit the macro
mode to manually edit the spreadsheets using Microsoft excel.
• CONTINUE: Click this button to continue to main input screen.
• INPUT SET 1, 2 AND 3: Use these text boxes to enter the corresponding
parameters required for the HGAC finance model. These parameters can be
retrieved manually by viewing the assumptions worksheet. Please note that these
parameters apply to all cities/counties/agencies.
• SELECT THE CITY/COUNTY/AGENCY: This frame is designed to select the
desired City, County or Agency for which the user wishes to review/change the
city/county/agency specific inputs.
• USE BUTTON IN THE ABOVE MENTIONED FRAME: Click the USE
button corresponding to CITY/COUNTY/AGENCY to select a city, county or
agency. Clicking on the USE button corresponding to any one disables the other
two buttons to avoid multiple selections at the same time. The picture below
shows a sample list which appears on clicking the USE button for city. The
County and Agency boxes have a similar corresponding list. Scroll down and
choose the desired city/county/agency.
• INPUT NOW: Click this button to review the inputs corresponding to the
selection made in the previous step. Clicking this button will populate the
INPUT FOR THE SELECTION frame with the current values for the
city/county/agency as shown on the following page.
• INPUT FOR THE SELECTION FRAME: This frame will show the current
values for the selected city/county/agency in the model for the parameters.
Change/review the values as desired. Please note that the Fiscally constrained
variable can only have 2 possible values, Y or N which can be chosen from a
drop down menu.
• SAVE RECORD: The save record button feeds the new values for the selected
city/county/agency to the model. Please remember that this button has to be
clicked to save the new values entered, otherwise the model will retain the
• FRAME5: This frame has the option of providing the user with a tool which can
be used to override all the existing rates (revenues, O&M, Added Capacity and
Preservation) with a new value to model different extreme scenarios. Please note
that this override rate applies to all agencies/cities/counties. Enter an overall rate
in the textbox and press override now to feed the new rates to the model. If the
button OVERRIDE NOW is clicked without any value for the rate, an error
message will be shown as shown below.
• USE DEFAULT: The user may wish to revert back to the original (default rates)
after experimenting with some scenarios. The USE DEFAULT button can be used
to feed the model the default rates for all cities/counties/agencies. These default
rates are stored in the model and can be views in the ASSUMPTIONS_DISPLAY
• USE CURRENT: If satisfied with the inputs, click this button to view the model
• REVIEW: To see a spreadsheet view of all inputs entered, click this button and
the model will display a spreadsheet view of all inputs as shown below.
• A error message shows up if the sum of Local share, TxDOT share, Federal
Share, Tolls, User Fees, Debt, Private and Other rates does not equal to 100%.
Please recheck your inputs and then proceed.
• CONTINUE: After reviewing the inputs in the spreadsheet format, click
CONTINUE to view the model results.
• EDIT INPUTS: If some input is found unsatisfactory, click on EDIT INPUTS to
go back to the main input screen to change the inputs.
• INPUT FRAME: The view results dialog box can be used to view the
outputs of the model. The model can show the outputs by different categories,
cities (/counties/agencies) and the year. The Type of output desired box can be
used to select the category of output desired from the drop down menu.
• USE: The USE buttons work in the same way as in the main input form. The
Year button can be used to select a year from the drop down menu as shown
in the figures below.
• SHOW RESULTS: Click this button to see the output from the model for the
desired category, city/county/agency and year. The macro will display error
messages if a year or a required agency/city/county is not selected as shown
below and will prompt the user to enter them again.
• QUIT: After viewing the desired results, click QUIT to exit the macros.