Application Developer Excel to Application Modernization

Hi

Please find my direct client job requirement for your consideration.

Title: Application Developer Excel to Application Modernization

Locations: Remote

The purpose of this request is to create and execute a contract for services. A desired contract outcome for DSL is that the successful contractor will remaster an existing draft Stream Compensatory Mitigation Accounting Calculator (SCMAC) , which is currently in Microsoft excel workbook format, into a more user-friendly application format. The SCMAC (which is explained in greater detail in the sections that follow) is critical for successful implementation of DSL s newly updated, filed, and soon to become active rules that will take effect on May 3, 2027, to implement a new stream compensatory mitigation (CM) accounting protocol within Division 85, Section -0692, of Oregon Administrative Rules (OAR s). The SCMAC will be made available to the public on January 1, 2027.

History and how we got to this point

DSL (and specifically DSL s mitigation team) has worked for many years to develop a more consistent and predictable stream CM rules, policy, and associated tools. These collaborative efforts culminated in the creation of a stream CM accounting protocol that will serve DSL s Removal-Fill Permitting Program and its applicants.

The new rules were filed in early 2026 and will become effective on May 3 rd , 2027.

Updates to state rules relating to the stream CM accounting protocol address historic challenges in review and implementation by:

1) establishing a framework, in rule, for evaluating stream CM proposals

2) providing added transparency for applicants and consultants on required stream CM criteria. This includes the creation of the novel SCMAC that provides the ability for DSL staff, permit applicants, and stream improvement practitioners to determine the amount and type of CM required for permitted activities.

What problems do you need resolved?

DSL does not have a final version of the SCMAC tool.

Until now, the DSL team has been working in Microsoft Excel to create the current draft SCMAC. The most recent version of the draft SCMAC is provided as Attachment 1, for reference. The team has assumed that Excel is the most appropriate application option for the tool. However, we have discovered anecdotally through coordination inside and outside the agency, that there may be other programs or applications that could better serve both Agency goals and eventual users of the SCMAC. Due to limited capacity, DSL mitigation staff have not been able to research, compare and test various programs and applications for their potential use in replacing the Excel version of the draft SCMAC. Therefore, DSL needs to contract with an entity that will evaluate, select and adapt the most appropriate application or program for the SCMAC.

In general, the purpose of the SCMAC is to allow an applicant or voluntary stream habitat improvement entity the ability to input baseline functions and values data (using scores from the Stream Function Assessment Method (SFAM)) from their project site, and functions and values scores based on the predicted condition of the stream, post project implementation. The calculator should be able to accurately calculate total SCMAUs (either positive or negative) through a series of internal calculations as outlined below (and as described on the document included as Attachment 2 to this procurement request).

Specifically, the SCMAC will need to auto-calculate the change in value-weighted stream functions between the baseline and predicted conditions to determine whether gains or losses in stream functions are occurring. These value-weighted functional gains and losses are calculated both individually (first) and project-wide (last). The changes to value-weighted functions are then summed for both the baseline and predicted stream conditions and displayed as positive or negative stream compensatory mitigation accounting units (SCMAUs). Where the total SCMAUs are negative, CM is required. When the total SCMAUs are positive, CM may not be required.

As mentioned briefly above, the SCMAC must also allow regulatory and regulated entities the ability to compare total adjusted SCMAUs between an impact site and a CM site to determine whether stream impacts at the impact site are fully offset by the CM site. When the SCMAU difference between impact site and the CM site is positive, the CM sit will have provided sufficient offset for proposed stream impacts. When the SCMAU difference between the impact site and the CM site is negative, this means that the CM site does not fully offset proposed stream impacts. In this case, additional CM will be required.

Beyond the calculations described above, there are two other calculations that the SCMAC must do. The first calculation is based on the degree of vegetative temporal loss. This temporal loss (negative) adjustment factor will be applied (multiplied) to each unadjusted value-weighted functional changes described above. The second calculation is based on the degree of administrative protection obtained for a stream project. This positive adjustment factor will need to be applied to the total site SCMAUs. A simplified version of the driving formula behind the current SCMAU calculator is below:

What are our goals?

The overarching goal of this procurement is to contract with an entity that can evaluate, select, adapt, and beta test (alongside DSL mitigation staff) the most appropriate application for the SCMAC. The most appropriate SCMAC application will be:

User friendly

1) Easy to understand and use. The tool or application must be simple (uses plain language and simple formatting).

2) Accessible from multiple access technologies (computer, phone, I-pad) and systems without the need for specialized software. The software chosen for the SCMAC tool or application must be commercial off-the-shelf with broad distribution and availability to both DSL staff and the public. Broadly, the successful SCMAC tool must be common and easily obtained (not costly).

3) As fully automated as possible (with limited direct entry of data).

4) Able to flag and label data entry errors in an obvious way so that users are able to determine, rapidly and in real time, what data is missing or entered incorrectly.

Accurate

1) Able to provide consistent and accurate outputs

Adaptable

1) Able to be easily updated and customized by DSL staff without the need to contract with an external software expert, for example.

Robust

1) Introduces low (or no) user error

2) Able to withstand and readily accept modifications and consistent use.

3) Internal formulas/ calculations cannot be modified by external parties. The application must be able to be locked so that external parties cannot modify it beyond direct data entry where allowed.

DSL staff are open to including additional functionalities in the decision matrix if the contractor thinks they are critical for the new SCMAC program, tool or application to contain.

Under this procurement, DSL is requesting that a contractor:

1. Assess the current (Excel) version of the SCMAC for general understanding of its functionality

2. Compare the Excel version of the SCMAC against other available applications and tools using a selection matrix (see more details on the selection matrix, below),

3. Select, in coordination with DSL mitigation staff, the most appropriate application, which may be Excel, with justification provided to DSL staff,

4. Adapt the SCMAC by converting it from the current version to a new version to achieve the desired goals, and

5. Beta Test the new SCMAC using DSL-supplied stream project information.

DSL will know that the work has been done when contractor has provided DSL with:

A. Three (required), 1-2 -hour meetings throughout the life of the contract (see details in Schedule section, below)

B. A written and verbal summary of contractor s general understanding of SCMAC core functionality and use objectives in DSL s removal-fill program.

C. A written and verbal evaluation of various applications and tools that could serve as the final version of the SCMAC. The DSL- created selection matrix (see Table 1, below) must be used by the contractor to evaluate and compare alternative tools and applications. Excel should be one of the applications evaluated.