Rural Transit Need and Demand Spreadsheet Note: This spreadsheet is intended to accompany the TCRP W49 (Project B-36) Workbook, and should not be used without first reviewing the Workbook. Instructions 1) Click on the 'Analysis Setup' tab, enter a description of your analysis at the top, then check the boxes associated with the analysis procedures that you would like to apply. 2) Click on 'Input' tab and enter data in the boxes provided. The shaded boxes represent data that is not needed for your analysis, based on the analysis procedures that you chose in Step 1. 3) After entering all of the necessary data on the "Input' tab, the 'Output' tab will display the results of the selected analyses will be displayed. 4) To print out a report of your inputs and results, click here: Reports will be printed on your default printer. Click here to start over. Click here to clear the Peer Data Worksheet. Note: If macros are disabled, the above buttons will not function. This will not affect spreadsheet calculations. The reports can still be printed using the File -> Print command. The data fields on the 'Input' form can be cleared manually to begin a new analysis. It is recommended to save a blank copy of the spreadsheet, which can be used to begin new analyses. NOTE: If you are having trouble with Macros, click on the BLUE 'Macro Instructions' tab. Rural Transit Need and Demand Analysis Setup Service Area: Analysis Description: Additional Description: Select the analysis procedures that you would like to apply by clicking on the appropriate boxes. Need - Number of Persons Need - Number of Trips Demand - Program Demand - Non Program Demand - Small City Fixed Rural Urban center population must be less than 50,000 Demand - Commuters to Urban Centers Daily commuter round trips must be between 2,000 and 30,000 Select the program types to include in the Program Trips estimation analysis. Do you know the number of program participants? Developmental Services: Adult No Developmental Services: Case Management No Developmental Services: Children No Developmental Services: Pre-School No Group Home No Headstart No Job Training No Mental Health Services No Mental Health Services: Case Management No Nursing Home No Senior Nutrition No Sheltered Workshop No Substance Abuse No SERVICE AREA CHARACTERISTICS INPUT TABLE -- Fill In All Unshaded Boxes Service Area: Analysis Description: Additional Description: Estimated Annual Number Transit Need Inputs Program Type Enter Number of number of of Days Service Participants participants is Provided Number of persons residing in households with income below the poverty level: Developmental Services: Adult #N/A Number of persons residing in households owning no vehicles: Households Persons Developmental Services: Case Management #N/A 1-Person households: 0 Developmental Services: Children #N/A 2-Person households: 0 Developmental Services: Pre-School #N/A 3-Person households: 0 Group Home #N/A 4-or-more-Person households: 0 Headstart #N/A Job Training #N/A Mental Health Services #N/A Mobility Gap: Mental Health Services: Case Management #N/A Enter State (from drop-down list): Nursing Home #N/A Senior Nutrition #N/A General Public Rural Demand Sheltered Workshop #N/A Estimate rural transit trips based on vehicle-miles Substance Abuse #N/A Rural vehicle-miles: Annual Vehicle-Miles Estimate rural transit trips based on vehicle-hours Enter Demographics Inputs demographic American data (from US Community Decennial Census website Survey Table Census Table Rural vehicle-hours: Annual Vehicle-Hours or other source) Number Number Total Population S0101 QT-P1 Persons Age 16 and Above S0101 QT-P1 Small City Fixed Route Inputs Persons Age 60 and Over S0101 QT-P1 Population of City Persons Persons Age 16 to 64 S0101 QT-P1 College and University Enrollment: Students Mobility Limited Population S1801 QT-P21 Annual Revenue-Hours of Service: Annual Revenue-Hours Mobility Limited 16 to 64 S1801 QT-P21 Families Below Poverty Level C17013 PCT157 Commuter to Urban Centers Inputs Persons Age 16 to 59 S0101 QT-P1 Person work trips: Persons Age 75 and Above S0101 QT-P1 Straight-line distance Miles Persons Age 65 and Above S0101 QT-P1 Persons Age 64 or Less Living Below Poverty Level C17001 PCT142 Service Availability Inputs While not currently available for all areas, the preferable source of demographic data is the American Size of Service Area Square Miles Community Survey, available at: http://factfinder.census.gov/servlet/DatasetMainPageServlet?_lang=en&_ts=257695067045&_ds_name=A Vehicle-Miles Available to Persons Age 60 and Above Annual Vehicle-Miles CS_2008_3YR_G00_&_program Vehicle-Miles Available to Persons with Mobility At that website, first click "Clear all selections," then Select "Enter a Table Number." Limitations Age 16 to 64 Annual Vehicle-Miles For the number of persons residing in households that do not own vehicles, enter table number C08201 Taxi Vehicle-Miles Available to General Public Annual Vehicle-Miles For the number of persons residing in households below the poverty level, enter table number C17001 Non-Taxi Vehicle-Miles Available to General Public Annual Vehicle-Miles If required data are not available from the American Community Survey for the desired location, data may be obtained from the Decennial Census. Click on the link below: http://factfinder.census.gov/servlet/DatasetMainPageServlet?_program=DEC&_submenuId=datasets_1&_l ang=en At that website, first click "Clear all selections," then Select "Enter a Table Number." RURAL TRANSIT NEED/DEMAND ESTIMATION - OUTPUT TABLE Service Area: Analysis Description: Additional Description: Estimation of Transit Need Total need for passenger transportation service: 0 Persons Total households without access to a vehicle: 0 Households State Mobility Gap: Use postal code Daily 1-Way Psgr.-Trips per Household Total need based on mobility gap: #VALUE! Daily 1-Way Passenger-Trips #VALUE! Annual 1-Way Passenger-Trips General Public Rural Non-Program Demand Estimate of rural transit trips based on vehicle-miles Rural transit trips: 0 Annual 1-Way Passenger-Trips Estimate of rural transit trips based on vehicle-hours. Rural transit trips: 0 Annual 1-Way Passenger-Trips Trips per person based on vehicle-hours per person Trips per person: #DIV/0! Annual Trips per Person Non-Program Demand based on TCRP B3 Methodology Demand for Persons Age 60 and Above 0 Annual 1-Way Passenger-Trips Demand for Persons with Mobility Limitations Age 16 to 64 0 Annual 1-Way Passenger-Trips Demand for General Public 0 Annual 1-Way Passenger-Trips Total Rural Non-Program Demand 0 Annual 1-Way Passenger-Trips Small City Fixed Route Annual Ridership: 0 Annual 1-Way Passenger-Trips Commuters to Urban Centers Commuter trips by transit between counties: N/A Daily 1-Way Passenger Trips #VALUE! Annual 1-Way Passenger-Trips Rural Program Demand Annual Program Trip Estimation Developmental Services: Adult #N/A Annual 1-Way Passenger-Trips Developmental Services: Case Management #N/A Annual 1-Way Passenger-Trips Developmental Services: Children N/A Annual 1-Way Passenger-Trips Developmental Services: Pre-School #N/A Annual 1-Way Passenger-Trips Group Home #N/A Annual 1-Way Passenger-Trips Headstart #N/A Annual 1-Way Passenger-Trips Job Training #N/A Annual 1-Way Passenger-Trips Mental Health Services #N/A Annual 1-Way Passenger-Trips Mental Health Services: Case Management #N/A Annual 1-Way Passenger-Trips Nursing Home #N/A Annual 1-Way Passenger-Trips Senior Nutrition #N/A Annual 1-Way Passenger-Trips Sheltered Workshop #N/A Annual 1-Way Passenger-Trips Substance Abuse N/A Annual 1-Way Passenger-Trips Total Rural Program Demand 0 Annual 1-Way Passenger-Trips Peer Data Worksheet Input Data from Peer Transit Systems or Existing Transit Service Name of Peer System Population of Area Size of Area Served (Square Miles) Annual Vehicle-Miles of Service Provided Annual Vehicle-Hours of Service Provided Service Type (Fixed Route, Route- Deviation, Demand-Response) Number of One-Way Trips Served per Year Degree of Coordination with Other Carriers (Low, Medium, High) Annual Vehicle- Annual Results of Peer Data Comparison Population miles vehicles-hours Input Data for My System: 0 0 0 Observed Trip Demand Estimate Based On: Rates Annual Vehicle- Annual vehicles- Peer Values Population miles hours Trips per Capita Maximum #N/A #N/A Average #N/A #N/A Median #N/A #N/A Minimum #N/A #N/A Trips per Vehicle-Mile Maximum #N/A #N/A Average #N/A #N/A Median #N/A #N/A Minimum #N/A #N/A Trips per Vehicle-Hour Maximum #N/A #N/A Average #N/A #N/A Median #N/A #N/A Minimum #N/A #N/A Values expected for my system Maximum #N/A #N/A #N/A Average #N/A #N/A #N/A Minimum #N/A #N/A #N/A The TCRP B36 Spreadsheet contains a few simple macros that make the spreadsheet more user-friendly. Unfortunately, some macros can contain computer viruses, so Microsoft Office has a function that will disable ALL macros if the security level is set too high. This safety feature can protect users, but usually causes problems by disabling useful features in spreadsheets. The macros contained in the TCRP B36 Spreadsheet are SAFE. These instructions will explain how to remove the security warnings and use the spreadsheet normally. Note, however, that the calculations in the spreadsheet will still function correctly without use of the macros. This version of the TCRP B36 Spreadsheet contains a security certification created by LSC Transportation Consultants, Inc. that will allow users to operate the macros in the spreadsheet with the macro security setting on 'High'. Since the locally created security certificate was not issued by a licensed certificate dealer, authorized by Microsoft, the user must complete the following steps to accept LSC's certificate. Please note that these procedures are not necessary if macro security is set to low, and if macro security is set to medium, the user can simply check the box to 'Enable Macros.' When the spreadsheet is loaded, a 'Security Warning' dialog box will appear. Click on the box to ‘Always trust macros from this source’. Click ‘Enable macros’. If the ‘Always trust macros from this source’ checkbox is unable to be selected, complete the following procedure: Click 'Details' or 'Digital Signature Details'. Click on 'View Certificate', and then click 'Install Certificate'. This will open the 'Certificate Import Wizard'. Click 'Next' twice, and then click 'Finish'. Now, another security warning will appear, again notifying the user that the LSC certificate has not been validated by Microsoft. Click 'Yes' at the bottom of this dialog box. After this step, click 'OK' on all of the open dialog boxes. The ‘Always trust macros (or content) from this source’ checkbox should now be available. If not, save and close the spreadsheet. Then, re-open the spreadsheet. When the spreadsheet opens, the same Security Warning dialog box will appear. Click the button for 'Always trust content from this source'. Clickspreadsheet will now function with all of the features working properly. This process should only have to be The 'Enable Macros'. completed the first time the spreadsheet is used on a new computer. The spreadsheet will open normally for future uses. Office 2007 users must follow these additional steps. Macros will likely be automatically disabled upon opening the spreadsheet. Click on the Office graphical icon in the upper-left corner of the display. Click 'Excel Options'. Check the box for 'Show Developer Tab in Ribbon'. Click on the 'Developer' tab. Click on 'Macro security'. Check the appropriate security option for the user's firm or agency. Click 'Disable all macros except digitally signed macros'. When the 'Security Warning' dialog box appears, click 'Options'. Follow the above steps to allow the LSC certificate. Then click on 'Trust all documents from this publisher'. The spreadsheet will now function properly. This procedure should not need to be repeated for future uses on the same computer.
Pages to are hidden for
"Input"Please download to view full document