Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements (2011)

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.

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-1

C-2 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements Objective To assist in assessments of alternative fuel buses for 2010 and beyond by updating and expanding TCRP Report 38 (1998) Guidebook Cost-model spreadsheet (FuelCost ) TCRP C-19 Introduction to TCRP C-19 Basic Use of FuelCost2 Advanced Use of FuelCost2 Changes that Cause Critical Errors Changes for Other Vehicles Changing Technology Maturity Ranks 2 Contents

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-3 The Guide and Spreadsheet tool calculates lifecycle costs of 40-ft bus purchase options: Powertrains Conventional Hybrid Electric All-Electric (battery) Catenary electric (trolleybus) Fuel Cells Fuels Diesel Biodiesel Gasoline Ethanol CNG LNG LPG DME Hydrogen Fuels/ Powertrains An Excel file Basic use is largely self-evident – requires minimal Excel experience More advanced applications possible – requires moderate Excel experience 5 The Model: FuelCost2

C-4 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements Sheets for cursory comparisons INPUT Basic OUTPUT Graphs For more detailed assessments INPUT Detail For advanced use Default Data Fuel Economy Calculations 6 7 Worksheets 7 Microsoft Excel 2000, 2003, 2007, or 2010 Security settings must be set to allow operation of macros FuelCost2 Use Requirements

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-5 1. Click on the “INPUT Basic” sheet (one of the tabs at the bottom of the screen) 2. Click the arrow to the right of the first question – if a drop-down box appears, macros are enabled, otherwise, you must change Excel’s security level. Confirm if Macros are Enabled 8 9 If the Excel Security settings are set to “Low”, there will be no security pop-up boxes, and macros will work. For some older versions of Excel, if the security level is extremely high, no security pop-up boxes may appear, and macros won’t work. If Excel Security settings are Medium, the following pop-up boxes will appear: Click “Enable Macros” Excel Security Settings: Pre-2007

C-6 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 10 If Excel Security settings are High or Very High, one of the following pop-up boxes may appear. Click “OK” Excel Security Settings: Pre-2007 11To adjust Excel Security settings in Excel 2000 or 2003, click “Tools”, “Options” . . .

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-7 12 Select the “Security” tab, Click “Macro Security” . . . . E x c e l S e c u r i t y S e t t i n g s : P r e - 2 0 0 7 13 Close Excel (you don’t need to “save” the file ). Re-open Excel for the new settings to take effect Click “Medium”, and then “OK” in each pop-up box. E x c e l S e c u r i t y S e t t i n g s : P r e - 2 0 0 7

C-8 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 14 Excel Security Settings: 2007 & Up 15 Excel Security Settings: 2007 & Up

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-9 16 If you don’t see the “Security Warning” Ribbon, Click the “Developer Tab” Then click “Macro Security”. E x c e l S e c u r i t y S e t t i n g s : 2 0 0 7 & U p 17 Select “Disable all macros with notification.” Click “OK” Close Excel (you don’t need to “save” the file ). Re-open Excel for the new settings to take affect E x c e l S e c u r i t y S e t t i n g s : 2 0 0 7 & U p

C-10 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 18 If you don’t see the Security Warning Ribbon and you don’t see the “Developer” Tab: Click the “Office Button”, then Click “Excel Options” E x c e l S e c u r i t y S e t t i n g s : 2 0 0 7 & U p 19 S e c u r i t y S e t t i n g s : E x c e l Select “Show Developer . . .” Click “OK” Then click the “Developer” tab and follow the previous steps

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-11 20 Initial Input: Agency ownership type, labor rate, rate of return For each option (A,B,C): Bus length, Mileage, Tax rate, Powertrain INPUT Basic Sheet 21 When powertrain isn’t all-electric, select on-board fuel, next questions vary with selection. INPUT Basic Sheet

C-12 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 22 When powertrain isn’t all-electric, select on-board fuel, next questions vary with selection. INPUT Basic Sheet 23 INPUT Basic Sheet

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-13 INPUT Basic Sheet 24 Enter fuel economy and fuel price INPUT Basic Sheet 25 Enter fuel economy and fuel price

C-14 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 26 Enter project schedule and financing I N P U T B a s i c S h e e t 27 Enter project schedule and financing I N P U T B a s i c S h e e t

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-15 View cursory comparisons (up to 3 options) > Lifecycle costs > Lifecycle emissions Based on use default data > Key Limitation! Default data is rarely good for a specific project. Provides a starting point for comparisons 28 For higher-level analyses, use “INPUT Detail” sheet After Entering Basic input Default data provided for each selected option 29 INPUT Detail

C-16 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 3 0 Default data provided for each selected option Four Ways to Account for Subsidies 1. Adjust price to include subsidy 2. Enter % cost paid by owner 3. Enter Annual subsidy value ($/yr) 4. Enter a lump sum subsidy value I n p u t D e t a i l Based on “Input Basic” entries and default data or data entered on “Input Detail” Lifecycle costs Lifecycle emissions Relative (ranked) external costs 31 F u e l C o s t 2 O u t p u t

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-17 32 Output 33 Output

C-18 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 34 Quick version of “Key Assumptions” prints on same page as the summary output tables Click here to save results in a file Output 35 Lower rows of OUTPUT sheet display all input data – these print on a second page, and can be used to fully compare and re-create the modeled scenarios. Output

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-19 36 Project Life: Total Cash Outflow Project Life: Other Costs Project Life: Total Cash Outflows Project Life: Capital Costs Graphs Graphs 37 Project Life: Offsets (Cost reductions) Year 6 O&M Costs Project Life: Exhaust Emissions

C-20 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements Password-protected sheets to prevent accidental change s 38 Sheets that allow greater tailoring Default Dat a Calculations Fuel Econom y The password is “test” A d v a n c e d U s e o f F u e l C o s t 2 39 D e f a u l t D a t a S h e e t Row Headers (Column D) – m ust include cells with identical headers to “Input Detail”, Column D. (Extra Default Values row headers are okay) Column Headers (Rows 5 and 6) – m ust contain the same text values as shown for powertrain and fuel choices on “INPUT Basic”. (Extra Default Values Column headers are okay) Data Quality (Row 4) – c onditional formating, for “Moderate” and “Limited” . Facility Capital Costs – T he $/50 buses (Row 11) is the same units shown in the Guidebook. Rows 12 and 13 are used by a macro to calculate costs based on the total number of scheduled bus purchases (on “Input Basic”) Tech Maturity Rankings and 60-ft bus multipliers – in the far right columns

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-21 40 F u e l E c o n o m y S h e e t Fuel Economy Estimates for All-Electric (i.e., battery electric and catenary – t rolleybus) are based on previous-generation technology, and are only adjusted by hotel load months (no adjustment based on speed) Conventional and Conventional Hybrids – f uel economy estimates based on TCRP C-15 (Report 132) equations relating average speed and fuel economy. • Original equations for conventional diesel and CNG, and hybrid diesel and gasoline • Fuel economy for other fuels is adjusted based on energy content and relative differences in engine efficiency Fuel Cells – double conventional fuel economy. Hotel Load Adjustments – r oughly based on C-15 peak hotel-load months. 41 Option A -- Rows 2 to 36 (Col B to AB) Option B -- Rows 38 to 72 (Col B to AB) Option C -- Rows 74 to 108 (Col B to AB) Indirect Costs Ranking – t o the right, Columns AD to AH Other reference cells – C olumn AJ and beyond, for tax depreciation tables and named range used in equations to calculate interest and principle payoff. C a l c u l a t i o n s S h e e t

C-22 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements Array equations on the “Calculations ” s heet Array equations have outer curly brackets { } If you click on an array cell as for editing the contents, you must exit the cell by either : Press “Esc”, or pressing together: “Ctrl”, “Shift”, “Enter” (this key combination tells Excel this is an “array” and { } is automatically added. ) If you just press “Enter”, the { } will be lost, and the equation will no longer work as an array If you press “Enter” accidentally, you can click “Undo” 42 C h a n g e s t h a t C a u s e C r i t i c a l E r r o r s Row and Column Headers on “INPUT Detail” and “Default Values” Must be the same header text for the macro to properly fill “INPUT Detail” Don’t need to be in the same order (the macro uses a “Find” method) Default Values, Row 3 Combines powertrain and fuel (written in white text ) Used by the macro to fill in INPUT Detail dat a 43 C h a n g e s t h a t C a u s e C r i t i c a l E r r o r s

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-23 44 Default Data Calcula - tions Fuel Economy INPUT Detail OUTPUT (tables) Graphs INPUT Basic Bus lengt h Data (entries or default) Fuel Choice Fuel Choice Bus Length Column Headers Default Dat a Column Headers W o r k s h e e t F l o w Fuel & Pwrtr Choice Fuel Economy Fuel Choice Bus Length Lifecycl e calculations 1. INPUT Basic Sheet – c hange the drop-down choices for Bus Lengt h 2. Default Values – T wo Approaches: Easier -- Use a multiplier to convert 40-ft bus data (as is done for 60-ft buses), or Harder -- Change all data that are affected by bus length to represent new length – this changes the “standard” vehicle shown on Default Data sheet (currently 40-ft bus) . 3. Fuel Economy Sheet – change equations if you will be using the fuel economy “Help” button on Input Basic 45 C h a n g e s f o r O t h e r V e h i c l e s

C-24 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements On the INPUT Basic Sheet: 1. Select Columns P:R (they should be highlighted) 2. Right click while keeping mouse over selected area 3. Click “Unhide” (To “Hide” again, Select Col Q, Right click, Select “Hide”) 46 1 . C h a n g e B u s L e n g t h M e n u 47 1. Change Bus Length Menu (con’t) INPUT Basic Sheet, Cells Q12:Q13 -- This is the text used in the Bus Length drop-down menu Changes in these cells will change the drop-down menu The menu is set to read only 2 “lengths” The first “length” (Q12) is the “standard”, and refers to the data on DefaultValues sheet The second “length” (Q13) is calculated as a multiplier applied to the “standard”

Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements C-25 DefaultValues Sheet – C olumns F to AH Makes another vehicle type replace the current standard (i.e.,40-ft bus data) Consider : (to help assure appropriate relative values) Establish default value for a “base” option, i.e., one column (fuel/powertrain combination) and set other default values relative to the base value Example : the price of a new gasoline bus (Cell H9) Cell H9 = F6 * 0.75 (where F6 is the price of the conventional fuel) Set default values for other powertrains as a value that is relative to a base powertrain (i.e., as an equation) 49 2 . H a r d e r W a y t o A d j u s t D e f a u l t V a l u e s 48 2 . E a s y W a y t o A d j u s t D e f a u l t D a t a Apply multipliers to adjust 40-ft bus data to values for another vehicle . . . DefaultData Sheet – Columns AQ and AR Confirm that AR1 shows the new “length” (needs to match the drop-down list text exactly, doesn’t need to be a length, e.g., “van”) Enter multipliers in Column AR for each ite m

C-26 Guidebook for Evaluating Fuel Choices for Post-2010 Transit Bus Procurements 3. FuelEconomy Sheet Adjustments Adjust fuel economy calculations for the new vehicle length . . . Easy Way–no changes, just let fuel economy be adjusted based on the multiplier entered on DefaultValues, Cell AR8 (as is currently done for 60-ft buses) Harder Way – change the polynomial curve constants in cells C33 to F35 to represent the “new” vehicle type. 50 51Technology Maturity Rank Changes Relative rankings on Default Values sheet, Columns AK to AO Change ranked values as desired If more than 4 ranks are used, equations for rank assessments will need expansion on the Calculations sheet, Columns AD to AH