Amendment #1 - Q&A - ITN PEAA 2026_0007.pdf

Amendment #1 - Questions & Answers (Q&A)

ITN PEAA 2026*0007

Mainstem Water Admin Spreadsheet & Water Operations and Exchanges Approval Tool

A. The following questions, and the answer to each, were received:

1. Are these two efforts intended to replace existing tools/spreadsheets currently used for Arkansas River Basin administration, or are they new tools/spreadsheets to be developed from the ground up?

i. New

2. For each tool, what data is expected to be entered manually by DWR staff versus imported through existing interfaces, APIs, downloads, or other automated data feeds?

i. The provided example Water District 16 Admin Spreadsheet downloads and pulls in data available from other sources, such as streamflow data available from Colorado’s Decision Support

System (CDSS). Both proposed tools should similarly pull in available data to be the most helpful.

3. Does DWR require the final deliverables to be native Microsoft Excel workbooks, or may supporting databases, scripts, APIs, or automation tools be utilized provided the delivered solution remains spreadsheet-based?

i. Ideally, the deliverables will be completely self-contained in the

Microsoft Excel workbook (extra sheets/tabs used as "database tables" would be better than an external database, and scripting/automation should be done within VBA code). The VBA code, can make requests to DWR's REST API as needed, but there should not be a third party hosting the data or any API's. If

"supporting" databases are absolutely needed, they must be a

"file-based" database solution (like SQLite) that would be included when distributing the Excel file and kept in the same folder as the

Excel file.

4. Are there existing DWR analysis, reporting, GIS, water rights, streamflow, water quality, or internal business systems that either tool must integrate with or export data to?

i. The tools may obtain data from DWR’s existing tools using REST services. Such data may include streamflows or diversion data. In the future, the tools may be used to generate diversion records to be integrated into DWR’s database.

5. In addition to the documents linked in Section 1.8, will DWR provide existing spreadsheets/tools, sample datasets, data dictionaries, business rules, macros/formulas, decree summaries, and related documentation needed for development?

i. Nothing additional is available at this time.

6. Is there an incumbent contractor/vendor or internal DWR team currently supporting the existing Mainstem Water Admin Spreadsheet, the Water

Operations and Exchanges Approval Tool documents, or related Arkansas

River Basin tools?

i. The tools that are the subject of this ITN are not existing. The vendor who created a similar water admin spreadsheet for a tributary (water district 16) is LRE Water.

7. Are any on-site activities anticipated or required, such as kickoff, requirements workshops, user testing, training, or stakeholder meetings?

If so, please identify expected locations, frequency, attendees, and whether remote participation and remote project delivery are acceptable.

i. Yes, there will be at least one in-person meeting with Division 2 staff in Pueblo for each tool and that meeting should include critical members of the project team to gather business requirements.

8. Section 1.2 states that DNR may award to a single or multiple respondents, while Section 2.1 states that DNR will enter into a single contract. Please clarify whether DWR anticipates one contract for both tools, separate awards/contracts by tool, or one prime contract with subcontractor support.

i. DNR may award for development of both tools to a single respondent, meaning DWR will enter into an agreement with one vendor for development of both tools. OR DWR may award to multiple respondents to have a vendor develop each tool, meaning there would be a separate agreement with each awarded vendor.

DWR will decide which option provides best value to the state.

Whether awarding to a single or to multiple respondents, the language in 2.1 clarifies that DWR will only enter a single contract with the prime contractor(s) that are awarded, and it will be the contractors responsibility to subcontract as needed.

9. For pricing purposes, should respondents assume fixed-price delivery for each tool, task-based/milestone pricing, or another payment structure?

i. See section 4.6 for pricing submission instructions and section 2.3 for measurement and payment details to the awarded contractor(s).

Pricing should be submitted as a lump sum, and invoices should be submitted upon completion of each task in development of the tool(s).

10.What accessibility, security, user access, version control, and deployment expectations apply to the spreadsheet-based tools?

i. For accessibility requirements, see section 14 of addendum 1, Additional Terms and Conditions for Information Technology, to the

PO Terms and Conditions linked in the ITN document.

11.Can you clarify, is DWR envisioning the selected firm will assist DWR while DWR develops a spreadsheet, or to assist DWR by developing a spreadsheet?

i. Assist DWR by developing the spreadsheet tools.

12.Will the Mainstem Admin sheet(s) and the Operations & Exchanges sheet(s) be linked, or will they be standalone? If linked, how? If standalone, how does DWR anticipate verification that the values in the two files will be done (automatically? Manually?)?

i. Each tool is standalone. It is not clear what “verification” means in the question. Data will be imported and formulas may complete calculations and these items should be correct.

13.(Water Operations and Exchanges and Approval Tool) Because numerous decrees have flow limitations that are dependent on the type of water in the river, determining exchange potential will require knowing the amount of native (available for appropriation), transbasin /wholly consumable, and reservoir release water in the river at essentially every gauge and diversion point on the Arkansas River. Is DWR expecting the consultant to develop these flows from scratch, develop them using the methodology in the LRE/Spheros Colors of Water tool or the DWR Colors of Water tool (we note these tools currently produce different results), or simply rely on the output from one of these tools? If the exchange tool is going to rely on the DWR Colors of water tool, will DWR QAQC the tool prior to the development of the exchange tool, or is there money for the contractor to QAQC the tool, so the output from it matches the most recent steam gauge data on the CDSS Stations portal?

i. DWR does not see that the exchange tool needs to rely on the

Colors of Water since water users can exchange on any water in the stream, so long as that is in accordance with decrees and the priority system.

14.(Water Operations and Exchanges and Approval Tool) Exhibit B does not list the geographic extent of the exchange tool, though the list of exchanges provided in DWR’s Excel file do not extend below the Fort

Lyon Canal. The majority of the exchanges above Pueblo go into Twin, Turquoise, and Clear Creek reservoirs. Will the tool include calculating the exchange potential on Lake Fork Cr, lake Cr, Clear, and/or other tributaries

(Fourmile Creek, Grape Creek)?

i. Yes, the tool will need to incorporate exchange potential into water district 11 reservoirs based on the tributary sources.

15.(Water Operations and Exchanges and Approval Tool) The list of exchanges provided in the Excel file does not include some of the smaller exchanges (99CW170(A)/(B)) or rights that divert at alternate points but which are administered on the same priority system as decreed exchanges (81CW92, 83CW18, 99CW169(A)). Given that the DWR has not provided detailed information about the exchanges, does the DWR budget include time for the consultant to QAQC the list, and to develop details regarding each of the exchanges?

i. The scope is limited to the decrees listed in the attached spreadsheet (45 total) and to build the tool in a way that allows for additional decrees to be added in the future. The materials provided also include summaries of the decree requirements. However, the consultant is expected to verify actual decree terms and conditions and incorporate them appropriately in the tool

16.(Water Operations and Exchanges and Approval Tool) Different decrees include different minimum flow requirements (e.g., flows at

Avondale in 84CW62 -63 -64, 99CW169(A), 10CW004, etc), so it’s not possible to calculate one number that accurately represents the exchange potential between two points that is applicable to all exchanges. Does

DWR expect the tool to be able to accurately calculate the exchange potential for any specific exchange, including all the individual flow requirements, or does it envision calculating a generic exchange potential based on physically available flow that will then be subsequently limited by the specific requirements of individual decrees?

i. The tool could consider generic exchange potential and then, depending on the requested exchanges and their priorities, https://docs.google.com/spreadsheets/d/1QfSvubMSquz1Ryg9wSrrXe96cRl9dJZ-/edit?gid=599318042#gid=599318042 determine which exchanges can operate based on priorities and individual exchange decree requirements.

17.(Water Operations and Exchanges and Approval Tool) Many decree limitations include data that is not telemetered (e.g., the purpose for

Reclamation particular Fry-Ark Project releases from Twin, or the minimum observable flow in the river between two points). How does

DRW anticipate incorporating this data into the tool (by hand, relying on daily phone calls or emails about the operations of others?)?

i. It seems that water users requesting an operation that requires non-telemetered data would need to provide information as to how their proposed operation complies with decree terms and conditions that can’t be readily verified by DWR. This information that cannot be verified by DWR could be provided along with a request operate an exchange, or as a follow up to their request, in order for the request to be approved or denied. The way that this can reasonably work within the tool may be further developed as part of the project.

18.(Water Operations and Exchanges and Approval Tool) Exhibit B para 1 states the spreadsheet will need to track volumetric limits. Is this just the rates and volumetric limits of water moving through the exchange decrees, or does DWR expect that to also include the volumetric limits associated with the source water for the exchanges (e.g., Rocky Ford, Colo Canal, etc)?

i. The volumetric limits applied to the exchanges (not replacement sources)

19.(Water Operations and Exchanges and Approval Tool) Has DWR decided if the tool should calculate exchange potential for conditional rights or to infrequently-used locations? Including these will significantly increase the cost and complexity of the tool, but not including them precludes using the tool for helping users prove up conditional rights. If

DWR wants to include conditional rights, will that include both conditional rights to/from existing structures, and/or to/from non-existent structures?

i. In order to have a defined scope, the project is limited to the exchanges listed in the linked spreadsheet with the concept that the tool could be expanded in the future to incorporate additional operations.

20.(Water Operations and Exchanges and Approval Tool) Some flow locations in the Arkansas basin have live telemetry, while others only have average daily values. Is DWR comfortable with mixing data with these two timesteps?

i. Yes.

21.(Water Operations and Exchanges and Approval Tool) Does DWR expect the exchange tool to hold historical data, or simply look at the exchange potential at the current moment in time? If historical data, will it use instantaneous data whenever the tool is run, or average daily values?

i. We expect the tool to generally rely on yesterday’s average daily.

The only historical data that seems to be relevant is that needed to consider the decreed exchange volumetric limits.

22.(Water Operations and Exchanges and Approval Tool) If the listed budget is inadequate to accomplish what DWR envisions in the scope of work, how does DWR anticipate adjusting to that reality (simplify scope?

Increase budget?)?

i. DWR does not have additional budget that can be added to the project at this time. If a vendor does not think the scope can be accomplished in the budget, their submittal may describe what can be accomplished in the budget and how much additional budget would be required to complete the specified scope.

23.Do the Section 2.2 end dates or the Exhibit month ranges govern the project schedules?

i. The Exhibit month ranges are the anticipated time that DWR expects the awarded contractor will need to complete each task and the entire development of the tool(s). The section 2.2 end dates are expected to line up with the exhibit timeline ranges from the anticipated project start date (PO execution). Both the Exhibit timelines and the section 2.2 dates are suggested timelines and

DWR would like to see a proposed project schedule by the

Respondents that corresponds with the proposed project solution.

24.How does DWR envision the incorporation of transit losses?

i. Transit losses should be incorporated into both spreadsheets in situations where there is an analysis of water delivery in the stream.

This does not seem to be a common situation in either tool.

25.How does DWR interpret WCAG Level AA compliance for macro-enabled

Excel deliverables?

i. DWR understands that WCAG does not have a direct mapping for desktop files, such as Excel workbooks. However, the four core principles can be applied to these deliverables. Also, the Office 365

Accessibility Checker may be used to identify and resolve any issues before deployment.

B. All other terms and conditions of ITN PEAA 2026*0007 - Mainstem Water Admin Spreadsheet & Water Operations and Exchanges Approval Tool remain the same.