Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.
Introduction This Cost Sharing Model is designed for use by transportation agencies that enter into agree- ments with human service agencies to provide transportation on behalf of agency clients. The model (1) computes a transportation providerâs fully allocated costs and (2) translates those costs into three common pricing mechanisms: price per vehicle mile, price per vehicle hour, and price per passenger trip. This spreadsheet model is one component of TCRP Report 144, Volume 1, âThe Transportation Services Cost Sharing Toolkit,â which also includes all of this information in an easily searchable electronic format. The spreadsheet allows the user to enter budget and service information. The model then auto- matically calculates the parameters for systems to fully allocate transportation costs. Once budget and service information are entered, the spreadsheet can be used repeatedly to cal- culate the fully allocated costs of a proposed contract service and the price the agency should charge for the service, using one of three common unit-of-service pricing mechanisms. The Cost Sharing Model Software Considerations The Cost Sharing Model is a macro-enabled, Excel 2007 workbook. If you are using Excel 2007 and you wish to save your entries or work done using the model, you will be prompted to save your work using this format. If you attempt to save your work in another format (e.g., Excel workbook [*.xlsx] or Excel 97-2003 Workbook [*.xls]), you will receive a warning that you may lose features in the workbook. You must save all work using the macro-enable format. If you are using earlier versions of Excel (i.e., Excel 97â2003), the program either will not per- mit you to open the file or will generate a warning when you open the Cost Sharing Model, depend- ing on your macro security settings. In the first instance, from a blank workbook, go to the menu bar, go to âToolsâ then âOptions,â and click on the âSecurityâ tab. Click on the macro security button and select the medium level of macro security. Then open the Cost Sharing Model. In the second instance, Excel may generate the warning pictured in Exhibit 9-1. This standard Excel fea- ture is designed to warn users that macros have the potential to be harmful and that workbooks containing macros should be opened from trusted sources only. Click on the âEnable Macrosâ but- ton when confronted with this box. Enabling macros is necessary for the Cost Sharing Model to work properly. 33 C H A P T E R 9 Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit
Basic Accounting Structure The budget and chart of accounts are based on a standardized chart of accountsâthe Uniform System of Accounts (USOA) created by the Federal Transit Administration (FTA) of the U.S. Department of Transportation (DOT). This is the basic reference document for the National Transit Database (NTD). It contains the accounting structure required by federal transit laws (pre- viously Section 15 of the Federal Transit Act). For more information on the USOA, visit FTAâs web- site at the following link: http://www.ntdprogram.gov/ntdprogram/pubs/reference/USOA.pdf. How the Cost Sharing Model Works Using service and cost data supplied by the person applying the Cost Sharing Model, the model performs calculations that generate unit cost figures (cost per mile, cost per hour, and cost per trip). The user provides data on the service alternative being examined, and the model generates the fully allocated cost of that service alternative. The modelâs user then can select one of the unit costs as a basis for creating a contract with the organization interested in purchasing the services described in that alternative. In greater detail, the steps in the model are as follows: ⢠The modelâs user enters previous data on â Services for the entire transportation operation; â Miles of service, hours of service, and, in some situations, the number of vehicles used; and â Line item expense data for the entire transportation operation. ⢠The model assigns the line item expenses to one of three cost categories: fixed costs, costs that vary by the number of miles, and costs that vary by the number of hours. ⢠The user enters data for the service alternative being considered: projected vehicle miles, vehi- cle hours, and passenger trips, as well as whether the service being considered will be provided as fixed route or demand responsive service. ⢠Using the expense and service data previously entered, the model calculates the fully allocated cost of the service alternative being considered. ⢠The user specifies how the service is to be priced for the organization interested in this service alternative: price per hour, price per mile, or price per passenger trip. ⢠The model calculates the price to be charged to recover all of the costs of that transportation service; the price is specified in terms of the unit cost chosen by the modelâs user. This process can be repeated many times for different services and different purchasing agencies. 34 Sharing the Costs of Human Services Transportation Exhibit 9-1. Security Warning box.
Using The Cost Sharing Model Sequence of Steps in the Model When you open the workbook, you will automatically be taken to the modelâs menu (Exhibit 9-2). The menu presents the four basic steps associated with the model: 1. Enter service data. 2. Enter expense data. 3. Compute fully allocated costs. 4. Compute the price of service. The model contains various navigation buttons throughout the workbook to take you to the var- ious entry and output sheets in the workbook. Simply click on these buttons to complete the task. Before You Start: Organize Your Data You may use the model for two purposes: (1) computing the fully allocated cost of a contract the agency contemplates entering into with a human service agency or (2) evaluating the profit and loss from an existing or past contract. Depending on your purpose, you should collect either projected service and budget data or actual service and budget data for the model as shown in Table 9-1. When using the model for either purpose, both budget and service data should represent the same time period, preferably on a fiscal year, calendar year, or other 12-month basis. Step 1: Enter Service Data Click on the âGoâ button to enter service data. You will be taken to a brief data entry screen where you will be asked to enter information about your transit system (Exhibit 9-3). Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit 35 Exhibit 9-2. Cost Sharing Model main menu.
First, indicate which modes of service your system operates by selecting the option from the box. The options are the following: ⢠Fixed route. ⢠Demand response. ⢠Both modes. Second, enter service data, under the appropriate mode, for fixed route and demand response services. These entries are for the entire system. Remember, the time period for these service param- eters should follow the guidance provided in Table 9-1. We recommend that you use data for an entire fiscal year in the model. The model requires three inputs: 1. Vehicle hours. 2. Vehicle miles. 3. Number of peak period vehicles (fixed route only). You may use either total vehicle miles and total vehicle hours or revenue hours and revenue miles. Whatever unit you use in Step 1, be sure to use the same units in Step 3. If you do not oper- 36 Sharing the Costs of Human Services Transportation If you want the model to . . . For your service data . . . For your budget data . . . Price a proposed service Use projected data Use projected data Estimate revenue profit/loss on an existing or past contract Use current actual data or historical data Use current actual data or historical data Table 9-1. Types of data to use for various purposes. Exhibit 9-3. Data entry screen.
ate fixed route services, simply leave that box blank. The total boxes will automatically add both fixed route and demand response entries; do not make your own entries in these cells. Note: When entering service data, enter numbers without commas or separators (e.g., 999999 rather than 999,999). The cell will automatically format your data. Also, you can click on the âBack to Menuâ button to return to the menu and select the next step, or you can click on the workbook tab at the bottom of the page to move to the next sheet when done with service data entry. Step 2: Enter Budget or Expense Data Budget or expense information for your system should be based on the same time period as the service data entered in the previous step. In other words, if budget data are next yearâs projected budget, the hours and miles data also should be next yearâs projection. If expense data are based on last yearâs numbers, service data similarly should be based on last yearâs actual performance. As noted in the introduction, the budget format employed in the model follows that incorpo- rated into the Uniform System of Accounts used for NTD reporting (Exhibit 9-4). Use of this for- mat has several advantages: ⢠The objects of expenditure are well known to many transportation agencies. ⢠This system has been the basis for transportation system reporting for many years. ⢠The chart of accounts is expressly constructed for transportation services. Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit 37 Exhibit 9-4. Chart of accounts.
If you are not familiar with the line items in the chart of accounts, additional help is available. For each item, a definition is incorporated into a comment field in each cell. To see the definition for a particular line item, move your cursor over any cell that has a small red triangle in the right side of the cell. The comment window will appear with the definition of the account (Exhibit 9-5). The total of all of your entries will automatically be calculated for all data entered. Check this figure to make sure that it equals your total budget or expense figures. If you do not enter all of your costs, the prices calculated by this model will be less than your total expenses, and you will lose money on your transportation services. Note: When entering budget data, enter numbers without commas, separators, or dollar signs (e.g., 999999 rather than $999,999). The cell will automatically format your data. Also, the Cost Sharing Model provides, at the userâs discretion, the opportunity to include depre- ciation in the computation of fully allocated costs. In most cases, however, it is recommended that the user not include depreciation in the modelâs computations. This decision typically will rest on the following factors: ⢠Agencies may not readily maintain depreciation schedules for capital assets. ⢠The Office of Management and Budget (OMB) does not allow the depreciation of assets acquired with federal funds to be charged as an allowable expense to another federal grant. ⢠Local shares of capital costs can vary substantially and may not be known to all agencies. ⢠Different states have different rules and levels of participation in capital funding. 38 Sharing the Costs of Human Services Transportation Exhibit 9-5. Definition comment field.
The vast majority of the users of the Cost Sharing Model probably will be in the same situation (i.e., using federal funds for capital purposes). As long as all participants follow the same proce- dures (e.g., everyone excludes capital costs), the Cost Sharing Model still will provide reasonable guidance for allocating costs among stakeholders. For more information about depreciation, see Appendix E. Step 3: Compute Fully Allocated Costs Once service and budget data are entered, you can now compute the fully allocated costs asso- ciated with any service. From the Menu, click Step 3. First, indicate if the service for which you want to compute the fully allocated cost is a fixed route service or a demand response service. Click on the appropriate button to signify the mode of ser- vice (Exhibit 9-6). The model allocates fixed expenses differently depending upon the mode of ser- vice. You must choose one of the modal options in this screen. Next, you will need to enter three measures associated with the service you wish to cost: ⢠Vehicle hours. ⢠Vehicle miles. ⢠Passengers. Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit 39 Exhibit 9-6. Cost calculator.
If you are costing a fixed route service, you also will need to enter the number of vehicles that will be assigned to the service at peak periods. Once you enter these service parameters, the fully allocated cost of the service will be automat- ically calculated in the last blue cell on this screen. The computations for calculating the fully allocated cost of the service are not visible in these spreadsheets. For details on the computation steps being performed, see Chapter 8 of this Toolkit. Step 4: Compute the Price of Service Pricing the Specified Transportation Service Now that you have computed the fully allocated cost of a service, you can express that cost in common pricing terms. The Cost Sharing Model provides three options for pricing transporta- tion services: 1. Price per mile. 2. Price per hour. 3. Price per trip. Select your pricing choice by clicking on the appropriate button. Based on data you entered in Steps 2 and 3, the price, per unit of service selected, is shown in the blue cell in this window (Exhibit 9-7). If you wish to see the price generated by another unit of service, simply click another button; the pricing automatically will be updated in the blue output cell. For example, if you have already cal- 40 Sharing the Costs of Human Services Transportation Exhibit 9-7. Pricing options.
culated the price per passenger trip, you also could calculate the cost per mile or the cost per hour. Different agencies may require different units of service for billing. Pricing Another Transportation Service Once you have entered the budget and service data in Steps 1 and 2, you can use this model repeatedly to price other transportation services as long as the pricing analysis requires the same temporal data as originally entered. First, save your initial calculations. Second, make a copy of these calculations. Then, as long as the information on projected service and budget or expense data that you entered in Steps 1 and 2 remains the same, you merely need to enter new data in Step 3 to obtain valid results (in Step 4) for the fully allocated cost of another transportation ser- vice or contract. Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit 41