Road Management Initiative Roads Economic Decision Model Version 3 1 Sub Saharan Africa Road Economi - Excel

Document Sample
Road Management Initiative Roads Economic Decision Model Version 3 1 Sub Saharan Africa Road Economi - Excel Powered By Docstoc
					       Road Management Initiative                                Roads Economic Decision Model Version 3.1                                                  Sub-Saharan Africa

Road Economic Decision Model (RED) Package Contents
File                                                                Description

RED - Read Me (version 3.2).XLS                                     This Excel workbook

RED - Main (version 3.2).XLS                                        Main economic evaluation module Excel workbook
RED - HDM-III VOC (version 3.2).XLS                                 HDM-III vehicle operating costs module Excel workbook
RED - HDM-4 VOC (version 3.2).XLS                                   HDM-4 vehicle operating costs module Excel workbook
RED - Risk (version 3.2).XLS                                        Risk analysis module Excel workbook
RED - Program (version 3.2).XLS                                     Program evaluation module Excel workbook

RED - User Guide & Case Studies (version 3.2).pdf                   Model documentation pdf document


RED - Africa Transport Technical Note.pdf                           SSATP Technical Note presenting the RED model

Version 3.2 Changes and Improvements
RED - HDM-4 VOC (version 3.2).XLS                                   - Fixed module to be used on computers with Windows configured with commas as decimal symbol

RED - Main (version 3.2).XLS                                        - Fixed calculation of the Financial Investment Costs at the Project-Alter. Solution worksheet

RED - Program (version 3.2).XLS                                     - Improved multi-criteria table on multi-criteria worksheet
                                                                    - Fixed Ctrl-l macro to place also inputs of Alternative 0 on the Main Module

RED - HDM-III VOC (version 3.2).XLS                                 - No changes

RED - Risk (version 3.2).XLS                                        - Improved module to enable a user to define different triangle distributions for the three project alternatives
                                                                    - Fixed presentation of Inputs Distribution worksheet

Version 3.1 Changes and Improvements
RED - HDM-4 VOC (version 3.1).XLS                                   - New module designed to estimate vehicle operating costs and speeds following the HDM-4
                                                                    relationships for motorized and non-motorized vehicles

RED - NMT (version 3.0).XLS                                         - Module was removed from the RED package because its functionality was replaced by the new
                                                                    RED - HDM-4 VOC (version 3.1).XLS module that also computes vehicle operating costs for
                                                                    non-motorized traffic following the HDM-4 relationships

RED - HDM-4 Workspace Sample.ZIP                                    - Sample HDM-4 workspace was removed from the RED package because its functionality was replaced
                                                                    by the new RED - HDM-4 VOC (version 3.1).XLS module that computes vehicle operating costs following
                                                                    the HDM-4 relationships for motorized and non-motorized vehicles

RED - Main (version 3.1).XLS                                        - Changed the multi-criteria indicators to (-10-Low, 0-Medium, 10-High)
                                                                    - Added investment economic costs multipliers for each alternative on the Constants Worksheet to deal
                                                                    with cases where project-alternatives have different financial to economic costs multipliers
                                                                    - Corrected calculation of maintenance net benefits in case of existing variable maintenance costs
                                                                    - Corrected calculation of diverted traffic benefits

RED - Program (version 3.1).XLS                                     - Changed the multi-criteria indicators to (-10-Low, 0-Medium, 10-High)
                                                                    - Added computation of overall multi-criteria indicator as a function of multi-criteria weights

RED - HDM-III VOC (version 3.1).XLS                                 - Improved the Typical VOC and Speeds worksheet by defining 5 road condition classes and adding
                                                                    the calculation of cubic polynomials relating the vehicle fleet vehicle operating costs and speeds to
                                                                    roughness
                                                                    - Combined into one worksheet the HDM-III and equations comparison and the comparison graphs

RED - Risk (version 3.1).XLS                                        - No changes

Version 3.0 Changes and Improvements
RED - HDM-III VOC (version 3.0).XLS                                 - Added a new worksheet presenting "Typical Vehicle Operating Costs and Speeds
                                                                     for Different Road Classes"
                                                                    - Added a new output text file named "compo.csv", which contains the VOC composition per
                                                                    road and vehicle type. If needed, this output file can be opened in Excel to study the VOC composition

RED - Main (version 3.0).XLS                                        - New structure for defining the wet season characteristics. Now the user enters
                                                                    the traffic and the road characteristics of the dry season and of the wet season separate
                                                                    - At the Control and Setup worksheet, added a "Road Identification Code" input
                                                                    - At the Control and Setup worksheet, added a "Duration of the Wet Season (days)" input
                                                                    - At the Control and Setup worksheet, added a "Population Served (persons)" input
                                                                    - At the Traffic worksheet, added input for traffic on the wet season separately from the dry season
                                                                    - At the Project-Alter. Main Features worksheet, changed the input format of the percent of
                                                                     investment cots from numbers to percentages (i.e., from 35 to 35%)
                                                                    - At the Project-Alter. Main Features worksheet, changed the input format of the percent of
                                                                    total accidents from numbers to percentages (i.e., from 35 to 35%)
                                                                    - At the Project-Alter. Main Features worksheet, the terrain type and road type are entered at the top
                                                                    of the worksheet

       7/5/2011 - 962238ee-c4a5-4ce8-afc9-4737c6ccf519.xls - RED Package Contents
    Road Management Initiative                                Roads Economic Decision Model Version 3.1                                                 Sub-Saharan Africa

                                                                 - At the Project-Alter. Main Features worksheet, the user enters the road length and condition
                                                                 for the dry and wet seasons separately
                                                                 - At the Project-Alter. Main Features worksheet, the diverted traffic road condition does not
                                                                 consider a dry and wet season separately
                                                                 - At the Project-Alter. Solution worksheet, added the following indicators:
                                                                     PV of Economic Normal Traffic User Costs (million $)
                                                                     PV of Economic Generated Traffic User Costs (million $)
                                                                     PV of Economic Society Costs (million $)
                                                                     Investment per Population Served ($/person)
                                                                     Population Served per Investment (person/000$)
                                                                 - Renamed all worksheets and labels to replace "Options" for "Alternatives"
                                                                 - Redefined the main inputs for the sensitivity and switching values analyses
                                                                 - Added present value of society costs graph
                                                                 - Added multi-criteria worksheet designed to store multi-criteria indicators for future reference

RED - HDM-4 VOC (version 3.0).xls                                - Changed the procedure to import data from HDM-4 outputs to make it compatible with
                                                                 HDM-4 version 1.3

RED - Program (version 3.0).XLS                                  - Module renamed from Network to Program
 previously named                                                - Added option to characterize both the roughness and the speeds of all vehicles
RED - Network (version 3.0).XLS                                  - Redefined the module to include a budget constraint optimization done with the EBM-32 model.
                                                                 - Redefined the module to perform two runs of the RED model for each road, thus permitting
                                                                 to analyze up to 7 project-alternatives per road
                                                                 - Redefined the module to store multi-criteria indicators
                                                                 - Option to import and export data from Main Module
                                                                 - New Road Sections Net Present Value worksheet
                                                                 - New Unconstrained Budget worksheet
                                                                 - New Budget Constraint A worksheet
                                                                 - New Budget Constraint B worksheet
                                                                 - New Budget Constraint C worksheet
                                                                 - New Budget Constraint D worksheet
                                                                 - New Budget Constraint E worksheet
                                                                 - New Recommended Program worksheet
                                                                 - New Program Net Present Value worksheet
                                                                 - New Multi-Criteria worksheet

RED - Risk (version 3.0).XLS                                     - Upgraded the module to make it compatible with the other modules.

RED - NMT (version 3.0).XLS                                      - No changes



Version 2.0 Changes and Improvements
RED - HDM-III VOC (version 2.0).XLS                              - New inputs to preserve basic information, but not being
                                                                 used by the model:
                                                                    Country Name/Region
                                                                    Year
                                                                    Currency Name
                                                                    Exchange Rate Divider to US$
                                                                 - Comparison and graphs worksheets renamed for clarity

RED - Main (version 2.0).XLS                                     - Added data validation on Project-Options Main Features
                                                                 - New economic indicators on Project-Options Solution:
                                                                    Financial Investment Costs (million $)
                                                                    PV of Economic Agency Costs (million $)
                                                                    Net Present Value per PV of Economic Agency Costs (ratio)
                                                                 - Added Efficiency Frontier graph

RED - NMT (version 2.0).XLS                                      - New module to compute non-motorized transport vehicle
                                                                 operating costs and speeds following the HDM-4 relationships

RED - HDM-4 VOC (version 2.0).xls                                - New module to collect vehicle operating costs and speeds from
                                                                 the HDM-4 model

RED - Network (version 2.0).XLS                                  - New module to evaluate automatically a network of sections




    7/5/2011 - 962238ee-c4a5-4ce8-afc9-4737c6ccf519.xls - RED Package Contents
     Road Management Initiative                                   Roads Economic Decision Model Version 3.1                                      Sub-Saharan Africa

Road Economic Decision Model (RED) Basic Steps
- On all RED workbooks, only enter values on cells that have a yellow background and don't insert or delete rows or columns.

- For a typical RED application, the steps are the following:

    1) For a given country, compute unit vehicle operating costs (VOC) and speeds function of roughness using the RED - HDM-III VOC Workbook
    or the RED - HDM-4 VOC Workbook or any other model, and define cubic polynomials relating unit VOCs and speeds to roughness, which
    are essential to the use of the RED - MAIN Workbook.

    Use the RED - HDM-III VOC Workbook to compute VOCs and speeds for motorized vehicles, following the HDM-III relationships,
    following the steps below:

          a) Enter the country specific vehicle fleet data on the Basic Input Data Worksheet.
          b) If necessary, enter the country specific calibration data on the Calibration Data Worksheet.
          c) Compute the results by pressing the corresponding button on the Compute Results Worksheet.
          d) Copy the results automatically into the RED - MAIN Workbook by pressing the corresponding button on the Compute Results Worksheet.
          Note that RED - HDM-III VOC copies all the corresponding results of the nine possible vehicle types into RED - MAIN.
          e) If needed, view the results on the following worksheets:
                Coefficients Worksheet-> Cubic polynomials coefficients relating vehicle operating costs and speeds to roughness (copied into the RED-MAIN Workbook)
                Speeds Worksheet -> Speeds function of roughness for all vehicles and road classes
                VOC Worksheet -> Vehicle operating costs function of roughness for all vehicles and road classes
                Typical VOC and Speeds -> Typical unit vehicle operating costs and speeds for different road classes
                HDM-III & Equations Comparison Worksheet -> Comparison between HDM-III results and cubic polynomials
                Graph VOC - Polynomial Worksheet -> Graph comparing HDM-III results and cubic polynomial equation
                Graph Speeds - Polynomial Worksheet -> Graph comparing HDM-III results and cubic polynomial equation
                Graph VOC - Exponential Worksheet -> Graph comparing HDM-III results and cubic exponential equation
                Graph Speeds - Exponential Worksheet -> Graph comparing HDM-III results and cubic exponential equation

    Use the RED - HDM-4 VOC Workbook to compute VOCs and speeds for motorized vehicles, following the HDM-4 relationships,
    following the steps below:

          a) Enter the country specific vehicle fleet data on the Basic Input Data Worksheet.
          b) If necessary, enter the country specific calibration data on the Calibration Data Worksheet.
          c) Compute the results by pressing the corresponding button on the Compute Results Worksheet.
          d) Copy the results automatically into the RED - MAIN Workbook by pressing the corresponding button on the Compute Results Worksheet.
          Note that RED - HDM-4 VOC copies all the corresponding results of the nine possible vehicle types into RED - MAIN.
          e) If needed, view the results on the following worksheets:
                Coefficients Worksheet-> Cubic polynomials coefficients relating vehicle operating costs and speeds to roughness (copied into the RED-MAIN Workbook)
                Speeds Worksheet -> Speeds function of roughness for all vehicles and road classes
                VOC Worksheet -> Vehicle operating costs function of roughness for all vehicles and road classes
                HDM-4 & Equations Comparison Worksheet -> Comparison between HDM-4 results and cubic polynomials
                Typical VOC and Speeds -> Typical unit vehicle operating costs and speeds for different road classes

    Other alternative is to use any other model to compute VOCs and speeds as a function of roughness, for motorized and non-motorized vehicles, and to
    copy the corresponding cubic polynomial results manually into the RED - MAIN workbook.

    2) Perform the economic evaluation of a given project (road) using the RED - MAIN Workbook.

          a) Enter the control data on the Control and Setup Worksheet.
          b) Review the VOC and Speeds cubic polynomial coefficients copied from the VOC workbooks on the Unit VOC and Speeds Worksheet.
          c) Enter the time and accidents data on the Time and Accidents Worksheet.
          d) Enter the traffic data on the Traffic Worksheet.
          e) Enter the project-alternatives data on the Project-Alter. Main Features Worksheet.
          f) If needed, enter additional net benefits on the Project-Alter. Other Benefits Worksheet.

          The economic evaluation results are computed automatically. Therefore, there is no need to press a button to compute the results.

          g) View a summary of the economic indicators results for all project-alternatives on the Project-Alter. Solution Worksheet.
          h) View the a graph of the Net Present Value or the Total Society Costs.
          i) View the detailed results for each project-alternative on the following worksheets:
                Alter. 1, 2 or 3 Feasibility Worksheet -> Input data and resulting cost streams and economic indicators
                Alter. 1, 2 or 3 User Impact Worksheet -> Benefits to road users
                Alter. 1, 2 or 3 Benefits Distribution Worksheet -> Distribution of net benefits among vehicle types
                Alter. 1, 2 or 3 Sensitivity Worksheet -> Sensitivity analysis for the main input parameters
                Alter. 1, 2 or 3 Switching Values Worksheet -> Sensitivity analysis for the main input parameters

          Note that to calculate the sensitivity and switching values, you have to press a button on the corresponding worksheet.

    3) If needed, perform a risk analysis using the RED - RISK Workbook.

          a) Enter the triangular distributions for the main input parameter on the Basic Input Data Worksheet.
          b) Enter the number of scenarios to evaluate on the Compute Results Worksheet.
          c) Perform the risk analysis by pressing the corresponding button on the Compute Results Worksheet.
          d) View the results on the following worksheets:
               Risk Analysis Solution Worksheet -> Summary risk analysis results for all project-options
               Alter. 1, 2 or 3 - Net Present Value Worksheet -> Output net present value frequency distribution
               Alter. 1, 2 or 3 - Rate of Return Worksheet -> Output rate of return frequency distribution
               Inputs Distribution Worksheet -> Frequency distributions for the main input parameters

    4) If you are evaluating a series of road sections, you can use the RED - PROGRAM Workbook to evaluate a series of sections automatically, with
    or without budget constraints.


     7/5/2011 - 962238ee-c4a5-4ce8-afc9-4737c6ccf519.xls - RED Basic Steps
Road Management Initiative                                   Roads Economic Decision Model Version 3.1                                           Sub-Saharan Africa


     a)   Enter the road sections characteristics on the RED Inputs Worksheet, where each row represents a road section and each column collects
          the corresponding RED Main Worksheet inputs. Note that you define up to 7 project-alternative per road section.
     b)   Press the Execute RED Model button on the RED Inputs Worksheet.
     c)   View the economic evaluation results for all road sections on the RED Outputs Worksheet.
     d)   View a graph of the NPV and the main results for a road section on the Road Section Net Present Value Worksheet.
     e)   View the unconstrained budget program on the Unconstrained Budget Worksheet.
     f)   Perform a budget constraint optimization on the Budget Constraint A, B, C, D, and E Worksheets. For each budget scenario, enter the
          available budget and press the Perform Budget Optimization and Collect Optimization Results buttons.
     g)   Define a recommended program on the Recommended Program Worksheet, by selecting one project-alternative per road.
     h)   View the program Net Present Value for the different budget constraints scenarios at the Program Net Present Value Worksheet.
     I)   View the multi-criteria results for the recommended program on the Multi-Criteria Worksheet. Press the Update Multi-Criteria Analysis button.




7/5/2011 - 962238ee-c4a5-4ce8-afc9-4737c6ccf519.xls - RED Basic Steps

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:56
posted:7/6/2011
language:Romanian
pages:4
Description: Polynomial Worksheet document sample