1 / 35

Using Spreadsheet Models for Toll Revenue Forecasting

Using Spreadsheet Models for Toll Revenue Forecasting. Don Hubbard, PE, AICP Senior Supervising Planner PB. Topics Covered. Why Are New Methodologies Needed? Description of Spreadsheet Models Advantages & Disadvantages A Sample Application Conclusions. Why Are New Methodologies Needed?.

fermina
Download Presentation

Using Spreadsheet Models for Toll Revenue Forecasting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Spreadsheet Models for Toll Revenue Forecasting Don Hubbard, PE, AICPSenior Supervising Planner PB

  2. Topics Covered • Why Are New Methodologies Needed? • Description of Spreadsheet Models • Advantages & Disadvantages • A Sample Application • Conclusions

  3. Why Are New Methodologies Needed? Travel is a derived demand … so is travel demand forecasting

  4. The trouble with traffic models … • Post-project studies have found that traditional 4-step models have a poor record for accuracy for toll roads • … and accuracy has not improved over the last thirty years • Models are slow, noisy, cumbersome, opaque • Output not focused on issues of highest concern to clients (terms of the agreement) Private investors are used to a different kind of analysis tool and are less tolerant of 4-Step models than DOTs have been

  5. What Do Investors Want? • Ability to test variations of the things that they have some influence over (toll structure, number of lanes, duration of contract, exempt classes of vehicles) • Ability to perform sensitivity tests of the things they cannot control • Transparent & easy to check • Fast (able to test options during negotiations) • Seamless connection to financial post-processors This describes a spreadsheet, not a traditional 4-step model

  6. Description ofSpreadsheet Models

  7. Structure • Mimics a traditional model • But with simplified trips generation & distribution • Primary focus is on traffic assignment and post-processing

  8. Trip Generation & Distribution • Traffic counts are done for different periods of different types of days • User groups split out to extent data allows • Growth factors based on population & employment forecasts by catchment area

  9. Peak Spreading • Excess peak period traffic results in longer peak • Revised traffic then goes to diversion model

  10. Traffic Diversion • Split between tollway & non-tolled alternative based on ratio of costs • Starts with a seed value for the split, then iterates assignment to produce a stable result

  11. Post-Processing • Outputs from the diversion model are traffic volumes and revenues for each period • The volumes can be fed into LOS analysis and used to forecast when capacity improvements will be needed • Revenues can be aggregated to annual levels for use in financial analyses

  12. Sample Sheetfor Single Period

  13. Sample Volume& LOS Output Schematic Southbound Northbound

  14. Managing the Process • All scenario inputs are entered into a single page • Macros then open other workbooks, process data, and close • Summary results then copied into master file • Fast, compact results

  15. Advantages &Disadvantages

  16. Advantages • Often quicker & easier to create • They force you to examine your assumptions, so may be more rigorous • Less noise than traditional models, so more accurate for small changes • Can feed directly to/from other models (land use, financial models) • Better control over the process (for the same reasons that airplanes are more maneuverable when not using auto-pilot)

  17. Disadvantages • Limited to well-defined corridors with only a few realistic alternative routes • Single-purpose models; cannot replace 4-step models for general modeling use • Agencies may be reluctant to accept alternatives to a regional model if one exists

  18. Sample Application:North Luzon Expressway

  19. Project Background Angeles City (500,000) San Fernando (500,000) Old tollway extending northwards from Metro Manila Leased to private company under an upgrade-operate-transfer agreementVaries from 8-lane freeway in south to 4-lane expressway in northAlternate route is 2-to-4 lane undivided highway Source: PB Asia Manila (12 Million)

  20. Key Features 50 miles of freeway16 interchanges$377 million costNeed to keep costs down; toll increase politically sensitiveNeeded detailed volume forecasts for each ramp to do “just enough” and “just in time” upgrading Urban Section Rural Section

  21. Model Requirements Also needed detailed cost and revenue projections to arrange for various loan packages Banks required that all assumptions be open to scrutinyModel must be able to predict, on the spot, the effect of changes in assumptions Costs Revenue $

  22. Background for the NLE Model Existing regional lacked detail in study corridor Ramp volumes varied erratically for different study yearsInvestors unwilling to take risks on unreliable forecasts

  23. New Approach - Spreadsheet • 9 months spent trying to fix regional model, only 3 months remained before firm forecasts were needed • Determined that the regional model was unlikely to produce the needed accuracy within the time available • Decided to replace the regional model with a spreadsheet model

  24. Trip Generation O-D table taken from toll receipts from previous 5 yearsGrowth rates for each O-D pair were based on the expected population and employment growth at each end

  25. Growth of O-D Table The existing volumes at each ramp were then factored up, based on future volumes of the O-D pairs served, to make “Base Demand” 2010 2020 2030 Existing

  26. Other Input Assumptions Next added:- Assumed tolls- Toll sensitivity- Income assumptions Diversion Curve Income Growth

  27. Capacity Constraints Explicit capacity constraints were made for: - Receiving capacity of local roads - Toll plaza capacity - Mainline capacity

  28. Peak Spreading Separate sheets were done for each peak period and for the off-peak period, with spillover (peak spreading) based on conditions during the peak hour Peak Spill-Over Off-Peak

  29. Schedule for Upgrading LOS Threshold Ramp volumes were automatically compared to service thresholdsProduced an upgrading schedule for each of 40+ ramps Ramp Volumes Year Upgrade Needed

  30. Financial Results The resulting volumes for each ramp-to-ramp pair, for each vehicle class, were converted into annual revenuesThese were automatically fed into the financial spreadsheets Annuali-zation Factor Volume Revenue

  31. Application During Negotiations The model was able to quickly answer questions like, “What happens if the government refuses to approve toll increases after the first 5 years?” ? - Traffic increases- Upgrading needed sooner- Revenue/veh decreases- Rate of return declines

  32. Results of the NLE Model The methodology was robust and defendableThe resulting forecasts were reasonable Past Future The client was able to get financing; upgrading now underway“Asia-Pacific Transport Project of the Year”Project Finance Magazine (London)

  33. Conclusions

  34. Conclusions • Model types should be considered tools in a toolbox; different types are needed for different tasks • There are circumstances where spreadsheet models are likely to produce better results than traditional models • Well-defined corridor with limited routes • Uncertainties about input assumptions more likely source of error than computational mechanics

  35. Don Hubbard Senior Supervising Planner PB Tel. (916) 567-2555 hubbardd@pbworld.com

More Related