National Academies Press: OpenBook

Sharing the Costs of Human Services Transportation (2011)

Chapter: Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit

« Previous: Chapter 8 - Applying the Cost Model to Various Scenarios
Page 33
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 33
Page 34
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 34
Page 35
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 35
Page 36
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 36
Page 37
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 37
Page 38
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 38
Page 39
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 39
Page 40
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 40
Page 41
Suggested Citation:"Chapter 9 - Instructions for Using the Cost Sharing Software of the Cost Sharing Toolkit." National Academies of Sciences, Engineering, and Medicine. 2011. Sharing the Costs of Human Services Transportation. Washington, DC: The National Academies Press. doi: 10.17226/14490.
×
Page 41

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

Next: Glossary »
Sharing the Costs of Human Services Transportation Get This Book
×
MyNAP members save 10% online.
Login or Register to save!
Download Free PDF

TRB’s Transit Cooperative Research Program (TCRP) Report 144: Sharing the Costs of Human Services Transportation, Volume 1: The Transportation Services Cost Sharing Toolkit and Volume 2: Research Report explore issues and potential solutions for identifying and sharing the cost of providing transportation services for access to community-based human services programs. Collectively, the two volumes examine current practices and offer strategies for collecting necessary data, addressing administrative and policy-related issues, and establishing cost allocation procedures.

Volume 1: The Transportation Services Cost Sharing Toolkit leads the user through the process of setting up the necessary cost accounting system, identifying the data requirements and the measurement parameters, and describing procedures for applying the model. This volume concludes with instructions for using the actual Cost Sharing Model.

Volume 2: The Research Report summarizes all of the study components that contributed to formation of the Toolkit. It includes an extended evaluation of current experience and describes the regulatory environment that frames transportation service delivery requirements.

An executive summary of the report is included with the printed report.

The report includes the Cost Sharing Model along with instructions for setup and application on a CD-ROM, which is packaged with the reports.

The CD-ROM is also available for download from TRB’s website as an ISO image. Links to the ISO image and instructions for burning a CD-ROM from an ISO image are provided below.

Help on Burning an .ISO CD-ROM Image

Download the .ISO CD-ROM Image

(Warning: This is a large file and may take some time to download using a high-speed connection.)

CD-ROM Disclaimer - This software is offered as is, without warranty or promise of support of any kind either expressed or implied. Under no circumstance will the National Academy of Sciences or the Transportation Research Board (collectively “TRB’) be liable for any loss or damage caused by the installation or operations of this product. TRB makes no representation or warrant of any kind, expressed or implied, in fact or in law, including without limitation, the warranty of merchantability or the warranty of fitness for a particular purpose, and shall not in any case be liable for any consequential or special damages.

  1. ×

    Welcome to OpenBook!

    You're looking at OpenBook, NAP.edu's online reading room since 1999. Based on feedback from you, our users, we've made some improvements that make it easier than ever to read thousands of publications on our website.

    Do you want to take a quick tour of the OpenBook's features?

    No Thanks Take a Tour »
  2. ×

    Show this book's table of contents, where you can jump to any chapter by name.

    « Back Next »
  3. ×

    ...or use these buttons to go back to the previous chapter or skip to the next one.

    « Back Next »
  4. ×

    Jump up to the previous page or down to the next one. Also, you can type in a page number and press Enter to go directly to that page in the book.

    « Back Next »
  5. ×

    To search the entire text of this book, type in your search term here and press Enter.

    « Back Next »
  6. ×

    Share a link to this book page on your preferred social network or via email.

    « Back Next »
  7. ×

    View our suggested citation for this chapter.

    « Back Next »
  8. ×

    Ready to take your reading offline? Click here to buy this book in print or download it as a free PDF, if available.

    « Back Next »
Stay Connected!