The EXCEL Spreadsheets for RAP

Before you start!!! - Make a copy (renamed) of the file "Rapid Appraisal and Benchmarking BLANK.xls" (or a file with a similar name having the word "BLANK" in it) and store the original file in a safe location. Every time you begin a new project, make a new copy of the original and use the new copy for that new project.

The worksheets for RAP are described in the Table below

Worksheets Within the EXCEL File
Worksheet Description
1. Input - Year1
For an average water year, requires input (mostly monthly) of:
- Crop names
- Irrigation Water Salinity
- Crop threshold ECe values
- Field crop coefficients, by month
- Areas of crops
- Water supply
- Precipitation
- Recirculation and groundwater pumping
- Special agronomic requirements
4. External Indicators (ignore these, except to input needed "CI" values)
Automatic computations of monthly and annual values of various water supply indicators. These are temporary values- except the user must input "CI" values. The final, important values can be found in the worksheet '14. World Bank BMTI Indicators'
5. Project Office Questions
Most of the data for this sheet are obtained from the Project office. They include:
- General project conditions
- Water supply location
- Ownership of land and water
- Currency
- Budgets
- Project operation, as described by office staff
- Stated water delivery service at various levels in the system.
6. Project Employees
Requests information regarding employee training, motivation, dismissal, and work descriptions.
7. WUA
Data for Water User Associations (WUA) that were not obtained in the "Project Office Questions" are obtained here. This requires asking questions in the Project Office as well as having interviews with Water User Associations. Questions are related to:
- Size of WUAs
- Strength of organization
- Functions
- Budgets
- Water charges
8. Main Canal
Data for the Main Canal, including
- Control of flows
- General canal characteristics
- Cross regulators
- General conditions
- Operation rules
- Turnouts
- Communications
- Regulating reservoirs
- The level of service provided to the next lower level
9. Second Level Canals Same as Main Canal
10. Third Level Canals Same as Second Level Canals
11. Final Deliveries
Information regarding the level of water delivery service to individual ownership units, and at the last point of operation by paid employees.
12. Internal Indicators
This worksheet summarized the internal indicators that were calculated in the previous worksheets, plus asks for input regarding a few extra indicators. Weighted category indicators are computed for groups of sub-indicators.
13. Benchmark Indicators (ignore these)
This worksheet holds intermediate calculated values. Ignore this page.
14. World Bank BMIT Indicators This, plus worksheet 12, provide the final summary for the exercise.

 

General Guidelines for EXCEL Worksheet Usage

Names and Types

  1. The worksheet names within any EXCEL file are identified at the bottom of the screen. These must not be changed.
  2. The EXCEL file has two general types of worksheets:
    1. Input worksheets. These worksheets request data.
      1. In the first worksheet, the data is manipulated and/or used in computations on the far right hand side of the data sheets, out of view of the input pages. If one is interested, some computations can be seen by scrolling the pages to the right.
      2. In the worksheets numbered 5 - 11, a few internal computations appear vertically in line with input data.

    2. SUMMARY worksheets. These are worksheets 4, 12, 13, and 14. The two important ones are 12 and 14. Worksheets 4 and 12 require a limited number of input values, but their primary function is to summarize various data, computed values, and indicators.

Cell Coloring and Input Conventions

  1. The color convention for the first Input - Year"x" worksheets is as follows:
    1. Blank cell - indicates a place for data input
    2. Shaded cell - contains a default or calculated value or an explanation, or indicates that no data entry is required. In general, any values within the shaded cells should not be changed unless one understands all of the programming.
    3. Red letters - indicate computed values
    4. Blue values - indicate values that were transferred from elsewhere in the file. They may be computed or input elsewhere.
  2. The color convention for the worksheet 4. - External Indicators is as follows:
    1. Blank cell - in the "Est. CI" column only - requires the manual input of a value.
    2. Shaded cell - indicates values that are linked to previous worksheets or are calculated within this worksheet.
    3. Red letters - indicate values computed within this worksheet.
    4. Blue values - indicate values that were transferred from elsewhere in the file.
  3. Conventions for all worksheets 5-13 are:
    1. Blank cells with a light lined border require input.
    2. Blank cells with a dark lined border indicate that the value is needed, but that it requires information that may only be available at a later time.
    3. Any cell that is filled with a pattern or which is shaded should not receive input.
    4. Shaded cells contain formulas and will show the results of automatic computations.
    5. Cells with patterns are merely dividers between sections, or indicate that no data is needed.

The first INPUT worksheet requires data for a single year, but it is important to provide data for multiple years (i.e, run the program several times with new data), because an examination of only a single year can be misleading for many projects that have wide fluctuations in climate and water supply.