A02.12 PWS Enhance HydroBID Water Management Model dated 2024.04.10.docx

FOR OFFICIAL USE ONLY (BEFORE AWARD/OR MOD)

19 OCT 2023

PERFORMANCE WORK STATEMENT (PWS)

Enhance HydroBID water management model suite to facilitate linkage to a front-end excel interface.

1. BACKGROUND

The Institute for Water Resources (IWR) is a USACE Field Operating Activity (FOA) located within the Washington, DC National Capital Region (NCR), in Alexandria, VA and with satellite centers in New Orleans, LA, Davis, CA, Golden, CO, and Pittsburgh, PA. IWR was created in 1969 to analyze and anticipate changing water resources management conditions, and to develop planning methods and analytical tools to address economic, social, institutional, and environmental needs in water resources planning and policy.

IWR is currently responsible for a number water resources activities or programs, including evaluation of Civil Works (CW) strategic plans, CW policies and programs, investment and financial analysis, performance measurements and evaluations, infrastructure analysis, climate change and other water related activities. Internationally, IWR provides technical assistance to international organizations and other countries, encompassing a broad range of water and related land resources issues. This activity is part of a larger USACE package of OASA(CW)-approved activities funded through the Interagency & International Services Division of USACE HQ, under the authority of WRDA 1996 section 234, as amended.

The HydroBID suite of tools was developed by the Inter-American Development Bank (IDB) and includes three main component models, (1) a hydrologic model (HydroBID WAM), (2) a water regulation and allocation model (HydroBID ALLOC), and (3) a hydraulic model (HydroBID Flood). The HydroBID Support Center or the “Centro de Soporte HydroBID” (CeSH) provides free technical support across Latin America and the Caribbean (LAC) region. Included with the HydroBID suite are also free global land use, soils, and hydrography datasets. More information about the HydroBID suite of tools and the CeSH can be found on the HydroBID webpage.

Shared Vision Planning (SVP) is a planning approach that integrates (1) planning, (2) collaboration, and (3) system modeling, with the main goal being to support a more collaborative planning process that is centered around a system model, including all impacted stakeholders (e.g., local communities, irrigators, hydropower generators, environmental groups, etc.). A successful SVP system model must be able to adequately model the system of interest (i.e., as defined by stakeholders), produce output metrics relevant to stakeholders, and include interface views that allow stakeholders to interact with and evaluate model results. Institutionalization of the system model is also critical for longer term SVP adoption.

IWR began discussions with the HydroBID team in FY23 to discuss the development of an institutionally sustainable system model for a Shared Vision Planning (SVP) pilot subbasin in the Dominican Republic. While IWR had developed and trained the local team on a excel-based model in FY23, the need for a more sustainable model became apparent when local collaborators expressed the desire to expand the SVP approach and model to the whole Dominican Republic. The IWR and HydroBID teams determined that the ideal system model would include the flexibility of excel for interface development and the long-term technical support of the HydroBID suite of models.

With this contract, IWR seeks services and support in adding the following capabilities to the HydroBID suite of tools:

· Develop the capabilities to couple excel and ‘HydroBID WAM’, ‘HydroBID ALLOC’ and ‘HydroBID Flood’ to facilitate collaborative modelling with stakeholders by building a capability to:

· Develop customized front-end interfaces in Excel,

· Define desired water resources actions and their relationships with HydroBID inputs in the Excel interface,

· Define performance metrics and their relationships with HydroBID outputs in the Excel interface.

· Define different water management actions or projects in the Excel interface,

· Call HydroBID from the Excel interface to run simulations,

· View and compare the simulated results of different actions or projects on the Excel interface,

· Save and compare multiple actions on the excel interface.

· Create building blocks for excel interfaces that can be used by stakeholders to easily create their own customized interfaces.

· Model simple constituent and phosphorus transport.

· Develop the capability to stress test (or sensitivity analysis) the status quo, or a project or action. This entails iterative simulations under incrementally more stressful meteorological scenarios or water demand/use scenarios.

2. SCOPE

The scope of this PWS is as follows:

Develop an open-source Microsoft Excel plugin that links a flexible Excel interface with the full suite of HydroBID tools (WAM, ALLOC, and Flood).

The purpose of this task is to couple the HydroBID and MS Excel to enable users to run HYDROBID simulations view results from excel. The purpose is to facilitate the development of collaboratively designed ‘front ends’ to allow decision makers run simulations and view data on excel without requiring directly accessing the HYDROBID model. The current HydroBID suite of models lacks the ability to translate actions such as improved agricultural practices to direct model inputs like subbasin Curve Number. It also does not allow for the definition of relationships between performance metrics and direct model outputs (e.g., monthly crop production and profits as a function of irrigation supply). These relationships can be easily and flexibly defined in Excel, which is a user-friendly and globally accessible software. The Excel plugin developed under this task should link Excel to HydroBID, allowing users to define custom functions relating (1) planning actions with direct HydroBID model inputs, and (2) direct HydroBID outputs with stakeholder-defined performance metrics. By seamlessly linking Excel and HydroBID, the proposed plugin will combine the flexibility of Excel to define input and output functions, and visualizations with the robust HydroBID modeling engine backed by the CeSH.

The Excel plugin needs to be able to read the following results of the HydroBID suite:

· HydroBID WAM: water balance (e.g., daily runoff, evapotranspiration, etc.), daily routed sediment / water quality load

· HydroBID Alloc: MODSIM results (demand nodes, reservoirs, etc.)

· HydroBID Flood: HEEF_Damage layer table of content results Develop capability to perform stress tests of water resources systems.

The HydroBID-Excel plugin will have the capability to implement iterative simulation of climatic scenarios. The user would define ranges to adjust meteorological time series inputs to include changes in mean, variability, persistence (i.e., length of drought periods) on the Excel interface. The user would manage the ability to execute iterative simulations with incremental changes in the meteorological time series inputs.

Develop capability to assess land use change scenarios from an dashboard in Excel The HydroBID-Excel plugin will enable the simulation of land use change scenarios at a subbasin level. The user should be able to input a percentage of change for certain types of land use in a specified subbasin, run the model, view the results in the Excel interface, and compare different runs.

Develop building blocks for Excel interfaces The HydroBID-Excel plugin should also provide building blocks for Excel interfaces that allow the user to easily build customized interfaces.

The contractors will iterate with the IWR team to design key visualization templates in the Excel interface. These key visualizations should include, but are not limited to, (1) a spatial map view in which metric values are visualized in the map with relative coloring and summarized in an accompanying table (see Figure 1); (2) time series plots; and (3) metrics comparison visualizations such as spider plots, interactive x-y plots, bar plots, etc. (see Figure 2).

Additionally, users should be able to customize the location(s) (i.e., subbasin), time step, season / time period(s), data summarizing method (e.g., mean, maximum, minimum, standard deviation of the metric over the specified time period), metric units (e.g., m3/s, ton/year, etc.), and alternatives viewed.

Figure 1. Example of spatial map view with summary table. Users can explore the spatial distribution of a selected metric across the spatial extent of interest.

Figure 2. Example metric comparison visualizations, spider plot (top left), table + bar chart with points (top right), bar charts directly in table (bottom left), and x-y axis comparison of two metrics (bottom right).

Develop HydroBID capability to model GWLF based nutrient and simple constituent transport (particulate and dissolved).

The HydroBID WAM hydrologic model is based on the Generalized Watershed Loading Functions (GWLF) model. GWLF includes a nutrient (nitrogen and phosphorus) transport routine that is not included in the current HydroBID WAM model. This task should add in the GWLF nutrient transport as well as a generalized simple constituent transport routine to the HydroBID WAM and HydroBID Alloc models.

This task requires that both nutrient and simple constituent transport be incorporated in the HydroBID WAM and Alloc models. Both nutrients and simple constituents should have both dissolved and particulate phases. Concentrations should be a function of land cover and transport off the land should be a function of rural vs. urban land use (see Figure 1).

Figure 3. Diagram of nutrient sources and phases taken from the GWLF manual (Haith et al. 1992).

Documentation and training.

The contractor should document the capabilities of the Excel-HydroBID plugin as well as how to use it in a user’s manual in English and Spanish. Additionally, the contractor should provide one in-person training to the Dominican technical and facilitation team with the final product of this training being the incorporation and validation of the Excel-HydroBID plugin into the Guayubin HydroBID models (WAM, Alloc, and Flood) to be developed by the Dominican team.

2. GENERAL REQUIREMENTS

The following sub-sections provide details of various general requirements for this effort.

a. Non-Personal Services The contractor shall provide strictly non-personal services and contractor employees are not subject to direct supervision and control by the Government. The contractor shall advise and support the Government but shall not make final decisions or certifications on behalf of the Government nor perform any inherently governmental functions. The contractor and its employees shall not represent the Government nor appear to represent the Government in performance of these contract services. Contractor personnel shall clearly identify themselves as contractor employees in all interactions during contract performance.

b. Place of Performance All work will be performed at the contractor’s site.

c. Period of Performance The period of performance for this task order is twelve (12) months.

d. Materials It is anticipated that no materials will be purchased by the contractor for the execution of this scope.

e. Travel One trip to the Dominican Republic will be required to train the Dominican technical and facilitation team on how to use the Excel-HydroBID plugin. This training should focus on the Gauyubin HydroBID model developed by the Dominican technical team. The deliverable of this training will be a final version of the Guayubin HydroBID model with the Excel plugin incorporated and validated by the Dominican team. The contractor shall provide services necessary to plan and schedule travel and travel shall be reimbursed in accordance with Federal Acquisition Regulation (FAR) 31.205-46. The contractor shall be reimbursed for actual allowable costs and not to exceed the amount allowed in the Joint Travel Regulations (JTR). No fee is allowable on travel costs.

3. PERFORMANCE REQUIREMENTS (TASK AREAS)

TASK 1

(i) The contractor shall develop an open-source Microsoft Excel plugin that links a flexible excel interface with the full suite of HydroBID tools (WAM, ALLOC, and Flood). The contractor will enable the following workflow: (1) users define desired water resources actions and their relationships with HydroBID inputs in the Excel interface, (2) users define performance metrics and their relationships with HydroBID outputs in the Excel interface, (3) users iteratively design their Excel customized interface, (4) users define a water management alternative (combination of actions) in the Excel interface, (5) users can develop a dashboard in Excel interface to call and run HydroBID for alternatives, (6) HydroBID runs and exports results to same Excel file (user should have option to run any or all three of the sub-models, WAM, Alloc, and Flood), (7) users view alternative results from the Excel interface, (8) users can save and compare multiple alternatives from their custom-designed Excel interface.

TASK 2

(ii) The contractor shall develop the capability to iteratively run HydroBID from Excel over a range of potential climatic scenarios through the Excel-HydroBID plugin. This task requires that the Excel-HydroBID plugin enables batch runs of HydroBID from the Excel interface. Users should be able to define and run a range of adjusted meteorological time-series and define the increments of change for different months or seasons. This capability is to be managed through the Excel interface to support iterative simulations in HydroBID. Results from this batch run should be exported from HydroBID into the Excel interface.

TASK 3

(iii) The contractor shall develop the capability to run land use change scenarios through the Excel HydroBID plugin. This requires that the Excel interface access and update the HydroBID land use database in SQLite with values in the Excel file. The user should also be able to add new land uses (e.g., change cropland to cropland with best management practices). An example of this link is in the Excel prototype provided as part of this PWS in the ConnectToSQLite and updateSQLdb VBA macro subroutines.

TASK 4

(iv) The contractor shall facilitate two collaborative virtual sessions with IWR and the HydroBid Community of Practice to determine key building block templates for integrated water resources management to be developed for the Excel interface.

(v) During the second virtual session (iv) the contractor will present the building blocks that will be developed for final feedback by the community of practice..

(vi) The contractor will develop key template blocks in Excel front end to facilitate users to customize interfaces.

TASK 5

(vii) The contractor shall develop nutrient and simple constituent transport (particulate and dissolved) functionality to the HydroBID WAM and Alloc models based on the GWLF equations (Haith et al. 1992).

(viii) Update HydroBID user's manual with the new nutrient and constituent functionality for both the English and Spanish versions.

TASK 6

(ix) The contractor shall develop a user’s manual for the Excel-HydroBID plugin in English and Spanish.

(x) The contractor shall provide four virtual trainings technical staff of the North Yaque del Norte Commission (CRYN) implementing the USAID SVP Guayubin project on how to install and use the Excel-HydroBID plugin. IWR will provide the points of contact. The broader HydroBID community of practice could be invited to observe the application of the plugin to the Guayubin.

(xi) The contractor shall provide an in-person workshop with the CRYN team in the Dominican Republic for a final training of the Excel-HydroBID application, and the validation (approval by CRYN executive secretary) for Guayubin application.

4. DELIVERABLES AND DELIVERY SCHEDULE

The table below identifies a set of deliverables that are required under this task order.

5. PERFORMANCE REQUIREMENTS SUMMARY (PRS)

6. SECURITY

The work will be UNCLASSIFIED and network or facility access is not required except for participation in meetings. Contractor access supporting meetings at a government site shall be coordinated by the government technical point of contact (TPOC) or the contracting officer representative (COR). The contractor(s) will not require a Common Access Card (CAC).

7. APPLICABLE DOCUMENTS

Attached is a notated Excel-HydroBID WAM plugin prototype to inform task 1 as well as the GWLF user’s manual to inform task 5.

8. CONTRACTING OFFICER REPRESENTATIVE AND/OR TECHNICAL POINT OF CONTACT (TPOC)

A contracting officer representative (COR) and/or technical point of contact (TPOC) will be appointed on each task order. The COR and TPOC have no authority to direct the contractor in any way to alter the contractual obligation. The contracting officer is the only individual authorized to make changes to the contract.

image1.png image2.png image3.png oleObject1.bin