P3-VALUE 2.0: Quick Start Guide

January 2016
Table of Contents



Acronym Definition
AP Availability Payment
BCA Benefit Cost Analysis
BS Balance Sheet
CF Cash Flow
CFADS Cash Flows Available to Debt Service
DSCR Debt Service Coverage Ratio
DSRA Debt Service Reserve Account
GPL General Purpose Lanes
IRI International Roughness Index
IRR Internal Rate of Return
ML/TL Managed Lanes or Tolled Lanes
MMRA Major Maintenance Reserve Account
O&M Operations and Maintenance
PDBCA Project Delivery Benefit-Cost Analysis
PL Profit & Loss
PSC Public Sector Comparator or Conventional Delivery
P3 Public-Private Partnership
V/C Volume/Capacity Ratio
VDF Volume Delay Function
WACC Weighted Average Cost of Capital

Use of Quick Start Guide

The purpose of the Quick Start Guide is to provide the user with a brief overview of the P3-VALUE 2.0 tool. The Quick Start Guide describes a series of basic step-by-step instructions used to navigate the inputs and outputs of the tool. For more detailed instructions and the methodology behind the tool, please see the Guide to P3-VALUE 2.0 (currently under development).

Opening P3-VALUE 2.0

  1. Open P3-VALUE 2.0 excel file.
  2. When opening the file, Excel may prompt the user to approve the use of macros. To do so, click "Enable editing" and/or "Enable content" on the yellow bar across the top of the screen.
  3. After the model opens, the following user form will appear.
    Welcome screen
    View larger version of the Welcome screen
  4. Select the "Model Navigator" to access the full model. The "Training Navigator" contains four training modules that provide limited access to only the most relevant inputs and outputs for a particular training session.

Using the Model Navigator

If "Model Navigator" is selected, the following user form will appear. The Model Navigator enables the user to navigate the P3-VALUE 2.0 tool. If the user wishes to switch to the Training Navigator, one may do so by clicking the "Go to Training Navigator" button on the top right. The user can exit the Navigator at any point by clicking on the exit "X" at the top right corner of the Navigator. To access the Navigator again, simply click on the "Navigator" button on the toolbar (see below).

Model Navigator options screen

View larger version of the Model Navigator options

The Model Navigator has two different view options: a high level and a detailed level view. Users can choose between the high level view and detailed level view using the radio buttons at the top left of the Navigator.

In the high level view, the user has access only to the inputs, landing sheets (see below) and outputs. Detailed calculation sheets are hidden to provide the user with overall understanding of the model flow and logic. By clicking on any of the buttons in the high level view, the Model Navigator will either list the relevant input sheets (for the "Inputs" button), output sheets (for the "Risks", "VfM Outputs" and "PDBCA Outputs" buttons) or take the user to the relevant landing sheet (for all other buttons). For the listed input and output sheets, the user can click on them to directly access these input and output sheets.

In the detailed level view, all sheets are visible and accessible to the user. By clicking on any of the buttons in the detailed level view, the Model Navigator will list all relevant worksheets. The screenshot to the right shows what the user will see if he or she clicks on the P3 button (in the Value-for-Money section). Model Navigator options screen

View larger version of the Model Navigator options

The user can click on any of the listed worksheets to navigate to a particular worksheet. While the high level view enables the user to navigate the model through landing sheets, the detailed level view enables the user to navigate to each individual sheet in the model.

Using the Landing Sheets

The landing sheets provide an overview of each module's components (elements used as inputs to the calculations in the module, calculations carried out within the module, and a list of outputs and modules that are affected by this module's calculations) in a flow chart format. As an example, the screenshot below shows the landing sheet for the Revenues module.

Model Flow Diagram example

View larger version of Model Flow Diagram

The landing sheet can also be used to navigate the model. To access the landing sheet, click on any button in the high level view. By double clicking on any of the sheet names listed on the landing sheet, the user will be taken to the selected sheet. This functionality works both in the high level and detailed level view (in the high level view, the selected sheet will in fact be unhidden to be visible to the user).

Using the Toolbar

The model also contains a toolbar that is visible in all worksheets (except on the Home worksheet). The toolbar contains the following three buttons.

Toolbar with Home, Navigator, and Model Optimizer buttons

The user can use the toolbar to navigate or optimize the model:

  • Click the Home button to view the model flow diagram.
  • Click the Navigator button to load the Model Navigator and navigate to a worksheet.
  • Click the Model Optimizer button to calculate the required subsidy/availability payment while simultaneously optimizing the project's financing.

Enter Inputs

  1. Navigate to the following worksheets (input worksheet tabs are highlighted in yellow) to enter inputs. All yellow cells within these sheets can be altered whereas non-highlighted cells should not be modified.
    • InpTiming&Cost: This sheet contains project timing, cost, and escalation inputs. In addition, it contains non-changeable (constant) inputs, which are used throughout the model and should not be changed.
    • InpTraffic&Toll: This sheet contains traffic and tolling inputs as well as roadway characteristics.
    • InpSeries: This sheet contains time series inputs for pre-construction, construction, traffic ramp-up, and subsidy/milestone payments for Conventional Delivery and P3 scenarios.
    • InpFin: This sheet contains discount rate, tax and financial inputs including subsidies, interest rates, target debt service coverage ratios (DSCRs), equity returns, reserve requirements, etc.
    • InpRisk: This sheet contains pure risk, base variability, lifecycle performance risk, and revenue uncertainty adjustment inputs.
    • InpBCA: This sheet contains all additional inputs required for benefit-cost analysis (BCA) calculations.
  2. Use the drop down box in cell F6 of the InpFin sheet to toggle between different combinations of P3 and Conventional Delivery scenarios:
    • PSC: Tolled, P3: Toll concession
    • PSC: Tolled, P3: Availability Payment
    • PSC: Not tolled, P3 Availability Payment
    You can enter scenario-specific inputs into columns I, J, and K of the InpFin sheet. The selected scenario column will be highlighted in yellow while the other input columns are greyed out.

Optimize P3-VALUE 2.0

Once inputs have been entered and reviewed, click the Model Optimizer button, which calculates the required subsidy/availability payment required to make the project financially feasible while simultaneously optimizing the project's financing.

Review Detailed Calculations

Use the Model Navigator to navigate to the various modules and worksheets to review the detailed calculations used for VfM and PDBCA. To be able to access the calculation sheets, the user should switch to the detailed level view. The user may want to review some or all of the following calculation modules:

  • Shared Elements (Both VfM & PDBCA): Core Calculations, which include timing and escalation, traffic, construction and O&M cost, base variability, pure risk and lifecycle performance risk calculations
  • VfM: Revenues, PSC & P3 financial calculations
  • PDBCA: Travel costs and benefit calculations

Review Outputs: Risk, VfM and PDBCA

Click on the various output sheets under each of the output modules - Risks, VfM Outputs and PDBCA Outputs in the Model Navigator to review the results of the risk analysis, VfM analysis, and Project Delivery BCA.

back to top
back to top