Likeremote

Subscribe to the latest remote jobs:

  • Likeremote jobs on https://LinkedIn.com/
  • Likeremote jobs on https://telegram.org/
  • Likeremote jobs on Reddit.com
CI

VBA/Excel Developer

Crescens Inc.
🇺🇸 United States
Remote
6 days ago
  • VBA
  • Excel
  • Temporal
6 days ago
Auto apply with Likeremote
Not scoredNo CV on file. Upload one and this job gets a score out of 100.Upload CV
Title: VBA/Excel Developer
Location: Salem, Oregon
Duration: 6+ months

Purpose
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 is critical for successful implementation of DSL's newly updated, filed, and soon-to-be-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 (OARs). 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 ORS 196.800 (Definitions for ORS 196.600 to 196.921) 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 Oregon Revised Statute (ORS) 183.310(9) to implement or interpret their specific statutes.

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.”

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 Client 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 141-085-0692 (Mitigation Accounting), is heavily reliant on the subject SCMAC as a tool.

What Problems Need to Be 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 team has assumed that Excel is the most appropriate application option for the tool. However, DSL has 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 available programs and applications.
  • Compare potential application options.
  • Select the most appropriate application or program.
  • Adapt the selected application for SCMAC.
  • Beta test the new 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.

SCMAC Functionality
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 and project-wide.
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.
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 the impact site and the CM site is positive, the CM site will have provided sufficient offset for proposed stream impacts.
  • When the SCMAU difference is negative, the CM site does not fully offset proposed stream impacts.
  • In this case, additional CM will be required.

Additional Calculations
The SCMAC must also perform two additional calculations.
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 change 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 provided in the procurement documentation.
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.

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, using plain language and simple formatting.
  2. Accessible from multiple access technologies, including computer, phone, and iPad, and systems without the need for specialized software.
  3. 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.
  4. The SCMAC tool must be common, easily obtained, and not costly.
  5. As fully automated as possible, with limited direct entry of data.
  6. Able to flag and label data entry errors in an obvious way so users can rapidly determine, 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.

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.
  4. 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.

Contractor Responsibilities
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.
  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.
  5. Beta test the new SCMAC using DSL-supplied stream project information.

Deliverables / Expected Outcomes
DSL will know that the work has been completed when the contractor has provided DSL with:
A. Three (required), 1–2-hour meetings throughout the life of the contract.
B. A written and verbal summary of the 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 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.
E. Written results from a beta test using several project examples.
F. A final version of the SCMAC in its updated format.

VBA/Excel Developer · Crescens Inc.

Auto apply with Likeremote