Application Systems Analyst / Excel Expert

Requisition ID: Mitigation Calculator

Application Systems Analyst / Excel Expert

Location:775 Summer St NE # 100   Salem,  Oregon   

Duration: 12+ Months

Client: OR DSL

100% Remote Job

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.

Definitions

CM (Compensatory Mitigation) – From Division 85, Section -0510, of Oregon’s Administrative Record, means “activities conducted by a permittee or third party to create, restore, enhance or preserve the functions and values of the waters of this state to compensate for the removal-fill related adverse impacts of project development to waters of this state or to resolve violations of  to 196.905. Compensatory mitigation for removal-fill activities does not affect permit requirements of other state departments.”

OAR (Oregon Administrative Rules) - Oregon Administrative Rules (OARs), often called just “Administrative Rules,” are created by entities with rulemaking authority per  to implement or interpret their specific statutes. (definition taken from: )

SCMAC (Stream Compensatory Mitigation Accounting Calculator) – An application (tool) used to determine the amount (i.e. – “stream compensatory mitigation accounting units (SCMAUs)”) based on changes in SFAM value-weighted functions, stream length, and adjustments per state policy resulting from removal-fill activities in a stream.

SCMAU (Stream Compensatory Mitigation Accounting Unit) – The positive or negative numerical output from SCMAC.

SFAM (Stream Function Assessment Method) - From Division 85, Section -0510, of Oregon’s Administrative Record, is “a method for assessing functions and values of waters of this state that flow in channels.” The three main resources for the SFAM are the User Manual ( ), the excel Workbook ( ), which includes a summary “Scores” tab th

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 3rd, 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.

Successful implementation of updates to the CM accounting protocol for streams, as found within OAR (Mitigation Accounting), is heavily reliant on the subject SCMAC as a tool. Links to the “clean” and “track changes” versions of the new rules can be found here:   - “Clean” version of updated rules   - “Track changes” version of updated rules

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:

It is critical that this foundational formula be preserved in any new application or tool.  DSL mitigation staff will walk the successful contractor through the calculator and its intended functionality so that all are on the same page regarding project outcomes in this regard. A presentation further explaining the required calculations behind the new stream accounting protocol is included as Attachment 3 to this procurement.

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.

D.      A recommendation of the most appropriate application (tool) for the SCMAC with accompanying justification, based on the evaluation in step C, above.

E.       Written results from a beta test using several project examples, and,

F.       A final version of the SCMAC in its updated format.

ORS 196.800 – Definitions for ORS 196.600 to 196.921

As used in ORS 196.600 (Definitions for ORS 196.600 to 196.655) to 196.921 (Applicability), unless the context requires otherwise, (1) “Channel relocation”…