Attachment_A_Sasquatch_System_Requirements.xlsx
XLSX spreadsheet 104 KB Posted
- Attached to
- School Apportionment System Modernization State and local contract opportunity
- Solicitation number
- RFP No. 2026-12
- Issued by
- Adams County, Asotin County, Benton County, Chelan County, Clallam County, Clark County, Columbia County, Cowlitz County, Douglas County, Ferry County, Franklin County, Garfield County, Grant County, Grays Harbor County, Island County, Jefferson County, King County, Kitsap County, Kittitas County, Klickitat County, Lewis County, Lincoln County, Mason County, Okanogan County, Pacific County, Pend Oreille County, Pierce County, San Juan County, Skagit County, Skamania County, Snohomish County, Spokane County, Stevens County, Thurston County, Wahkiakum County, Walla Walla County, Whatcom County, Whitman County, Yakima County, Asotin City, Clarkston City, Clarkston Heights-Vineland CDP, West Clarkston-Highland CDP, Benton City, Chelan City, Chelan Falls CDP, Clallam Bay CDP, Lewisville CDP, Rock Island City, Pacific Beach CDP, Whidbey Island Station CDP, Mercer Island City, Pacific City, Bainbridge Island City, Kingston CDP, Kitsap Lake CDP, Kittitas City, Klickitat CDP, Okanogan City, Anderson Island CDP, Fort Lewis CDP, Fox Island CDP, Herron Island CDP, Ketron Island CDP, North Fort Lewis CDP, Pacific City, Raft Island CDP, Stevenson City, Hat Island CDP, Lake Stevens City, Snohomish City, Spokane City, Spokane Valley City, Puget Island CDP, Garfield Town, Yakima City, Washington
About this file
This document is a comprehensive requirements specification for the Washington State Office of Superintendent of Public Instruction (OSPI) detailing the modernization of the School Apportionment Financial Systems (SAFS) into the School Apportionment System for Quality, Accountability, Transparency, and Calculations Hub (SASQUATCH). The project aims to replace the current legacy system that allocates operating revenue across Washington's public K-12 education system, with the new system required to handle complex financial calculations, enrollment reporting, budgeting, and data management for 295 school districts. The requirements specification covers multiple work sections including technical infrastructure, data collection, reporting, enrollment tracking, personnel reporting, and financial processing, with an extensive list of over 200 specific functional and non-functional requirements.
The proposed system must address significant current pain points, including manual data processing, slow calculation speeds, lack of integration between systems, limited reporting capabilities, and outdated technological infrastructure. Key technical requirements include implementing robust security measures, supporting ADA compliance, enabling real-time data processing, providing comprehensive reporting tools, and creating a flexible system that can adapt to legislative changes. The system must integrate with various state platforms like One Washington, support multiple file formats, provide role-based access controls, and maintain detailed audit trails. The requirements emphasize creating a modern, scalable solution that can handle complex financial calculations, support multiple user types (districts, ESDs, OSPI staff), and provide transparent, accurate financial reporting across Washington's educational ecosystem.
View the file
Other files for this state and local contract opportunity
| File | Type | Posted |
|---|---|---|
| RFP_2026-12_Sasquatch_Apportionment.pdf | ||
| Contract_Intake_Form_01.24.docx | DOCX document | |
| _Exhibit_A_Assurances_2026-12.docx | DOCX document | |
| _Exhibit_B_Qualification_Affirmations_2026-12.docx | DOCX document | |
| Attachment_B_High-Level_As-Is_Workflows.pdf | ||
| Attachment_C_RFP_Demonstration_Scenarios.docx | DOCX document | |
| Attachment_D_BIDDER_5_SECTION_COST_BREAKDOWN.xlsx | XLSX spreadsheet | |
| _Exhibit_F_Contract_Issues_List_2026-12.docx | DOCX document |
On GovTribe
Work with this file on GovTribe
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
SYS ALL Requirements
| ID | Requirement Description | Background Title | Requirement Background ["Pain Point"] | Work Section | Legacy SAFS System area of interest | matchy | Blank | Blank | Vendor Readiness | Vendor Readiness Explanation (If Small or Large Customization, Third Party Product, Other, or Cannot Address |
| 001APP | The apportionment system must be able to directly ingest and process revenue forecasting and budget preparation data without requiring staff to manually download, regroup, and re-upload files. By automating the regrouping of item codes and ensuring alignment with apportionment categories, the system will eliminate a repetitive, time-consuming task that creates risk of human error. This will increase efficiency during monthly processing cycles and reduce the dependency on staff intervention for routine data preparation. | Manual Handling of revenue forecasting and budget preparation Data | Currently, the apportionment process requires staff to download F-203 data, manually regroup item codes to match apportionment categories, and then re-upload the data into the system. This step is both repetitive and error-prone, adding unnecessary complexity to monthly processing. SME confirmed that despite earlier hopes of streamlining, this remains “absolutely still happening” and continues to be one of the most significant inefficiencies in the workflow. | Data Collection | Apportionment | Apportionment | ||||
| 002APP | The new solution must be designed to process apportionment calculations significantly faster, ideally within one hour, and without locking staff out of the system during processing. This requires scalable system architecture capable of handling large datasets efficiently while supporting concurrent operations. Reducing calculation time and enabling parallel activity will improve productivity, allow staff to verify results sooner, and ensure timely payments to districts. | Long Processing Times for Calculations | One of the most critical issues raised is system performance. Running apportionment calculations can take between four to six hours, during which time staff are locked out of making other updates. This bottleneck delays downstream processes such as report verification and compliance adjustments. SME noted that this not only slows monthly processing but also creates stress during peak deadlines when timely funding distributions are critical. | Data Calculation | Apportionment | Apportionment | ||||
| 003APP | The apportionment system should allow staff to run calculations for specific subsets of districts (e.g., only charter schools or selected districts) in addition to statewide runs. It should also support running multiple calculations in parallel to accommodate testing scenarios or partial recalculations. This flexibility will save staff considerable time and reduce the burden of processing one district at a time, ensuring that recalculations can be targeted and efficient. | Annual Carryover Recovery Outside the System | The system restricts staff to processing calculations either for all school districts or one at a time. It does not allow selective runs, such as calculating only for charter schools or a subset of districts. This limitation adds considerable manual work, especially when staff need to test or reprocess smaller groups. The lack of flexibility contributes to inefficiency and prolongs processing cycles. | Data Calculation | Apportionment | Apportionment | ||||
| 004APP | The system must automatically map revenue codes to budget codes, eliminating the current reliance on Excel spreadsheets. This functionality should be configurable to adjust for legislative or budgetary changes without requiring manual intervention each month. Automating the crosswalk will reduce errors, standardize reporting, and provide greater confidence in the data submitted to the Budget Office and other stakeholders. | Manual Compilation of Reports in Adobe | After calculations, staff must manually convert revenue codes into budget codes using Excel spreadsheets before reports can be shared with the Budget Office. This manual crosswalk is time-consuming and prone to human error. It represents a critical control point that should be automated within the system to improve accuracy and reduce redundant handling of financial data. | Data Calculation | Apportionment | Apportionment | ||||
| 005APP | All compliance-related calculations that are currently performed using standalone Excel tools should be integrated into the apportionment system. This includes Learning Assistance Program (LAP) allocations, High Poverty calculations, Physical/Social/Emotional Support (PSES) compliance, and K-3 class size compliance. Incorporating these tools will provide a single source of truth for funding calculations, reduce risk from undocumented or unsupported spreadsheets, and allow for real-time updates and error checking within the system itself. | ADA Compliance for Reports | A significant number of compliance-related calculations are performed outside of the apportionment system using Excel-based tools. SME described how programs like the Learning Assistance Program (LAP), High Poverty, Physical/Social/Emotional Support (PSES), and Kindergarten–3rd Grade (K-3) class size compliance require manual updates and re-uploads into the apportionment system. These tools were inherited and not fully documented, creating risks when they break or require troubleshooting. Integrating these functions into the system would minimize errors and improve sustainability. | Data Calculation | Apportionment | Apportionment | ||||
| 006APP | The system must include built-in functionality to process carryover recovery annually, using F-196 data directly rather than relying on external spreadsheets. By automating this once-a-year process, the system will ensure consistency, reduce the risk of manual miscalculation, and provide clearer audit trails. This will also allow districts to view and verify recovery calculations in real time, improving transparency and accountability. | At year-end, carryover recovery must be processed using external spreadsheets. Data from F-196 reports, provided by finance staff, is compared against district expenditures, and unspent funds are clawed back. SME explained that this once-a-year process is entirely manual, requiring reconciliation outside the apportionment system before uploading adjustments. This introduces inefficiencies and potential audit risks. | Data Calculation | Apportionment | Apportionment | |||||
| 007APP | The system should be able to compile individual district reports into a single consolidated package automatically, without requiring Adobe Acrobat scripting or manual manipulation. This includes generating both district-level and statewide rollups in standardized formats, with the ability to schedule or batch reports as needed. By automating report compilation and distribution, staff time will be saved, errors minimized, and reports delivered faster to fiscal teams and the public website. | The apportionment system produces multiple individual reports for districts, but it lacks the ability to generate a consolidated package. SME must download the reports, then use Adobe Acrobat and JavaScript scripts to compile them into a single deliverable. This manual step is both technical and cumbersome, and SME admitted she does not fully understand the code in the script, making it fragile if errors arise. Automating report compilation within the system would significantly reduce reliance on Adobe scripting and minimize risk. The current format of the PDF reports does not meet ADA accessibility standards. | Data Reporting | Apportionment | Apportionment | |||||
| 008APP | While reports have not been processed through ADA compliance tools in recent years, the system should still be capable of generating reports in an ADA-compliant format if required by state or federal guidelines. This may include automated tagging, accessible formatting, and compatibility with assistive technologies. Clarifying the requirement with OSPI leadership will determine whether ADA compliance remains mandatory, but the system should be designed with accessibility in mind to ensure long-term compliance flexibility. | The 2018 process indicated that all reports had to undergo ADA compliance remediation before posting. SME stated she has never performed ADA remediation and suspects that OSPI has an exception to bypass this requirement. This means the original pain point may no longer apply, but it remains an area requiring clarification. | Data Reporting | Apportionment | Apportionment | |||||
| 001BUD | The system must migrate to a modern, database-driven architecture interactive front end. This should allow users (districts, ESDs, auditors, unions) to report data dynamically without reliance on Access or manual file handling. Historical data must be standardized and version-tracked so that cross-year comparisons remain valid even when formulas or item codes change. This self-service capability would streamline audit support, reduce IT workload, and ensure timely delivery of accurate financial information to auditors. | Manual and Outdated Systems (Access, Excel, Legacy Design) | Much of the financial reporting (F195/F197) relies on outdated tools such as Access databases, Excel spreadsheets, and early-1990s style processes. For example, Access files must be manually created and uploaded to OSPI’s site. Item dictionaries and formulas change year to year, creating inconsistencies in historical data. Districts and unions rely on these outputs but may not be aware of underlying calculation changes. This creates unnecessary bottlenecks and dependencies, especially during audit season when requests are frequent and time sensitive. The process adds delays, consumes IT bandwidth, and increases the risk of errors being introduced during hand-offs. | Data Reporting | Budgeting | Budgeting | ||||
| 002BUD | The system must allow authorized OSPI staff to adjust F-197 balances and item codes directly through a controlled user interface, with full audit logging to track changes. This would eliminate reliance on IT staff for routine balance corrections, reduce turnaround times for resolving errors, and ensure that financial data used by districts, ESDs, and OSPI is always accurate and up to date. | Inability to Correct Values Without IT Support (F197 & F200) | SME frequently encounters issues where balances (e.g., beginning fund balances in F197) do not roll forward correctly. Currently, he cannot change many item codes himself and must request IT to manually fix errors. SME explained that balances frequently fail to roll forward correctly from the prior year, and many of the item codes are locked for editing. This means that even simple corrections—sometimes for small dollar amounts—require OSPI staff to call IT support. Such dependency results in days of delays, creates unnecessary workload for IT, and prevents timely resolution of errors. This limitation undermines OSPI’s ability to reconcile balances quickly and to keep financial reporting accurate. | Data Calculation | Budgeting | Budgeting | ||||
| 003BUD | The system must generate automated notifications (emails or dashboard alerts) to OSPI reviewers when districts or ESDs submit extensions. Notifications should also cascade across stakeholders (district → ESD → OSPI) to ensure smooth workflows. | Lack of Automated Notifications (F200 Extensions) | When school districts submit budget extensions (F200), there is no automated alert to SME. He must manually log in and check for pending submissions. This is inefficient and risks delays in review and approval. | Data Collection | Budgeting | Budgeting | ||||
| 004BUD | The system must automatically detect and apply the most recent F-200 submission for each district and fund when updating appropriations. Historical submissions should remain archived for audit purposes, but only the latest submission should feed into current year budget calculations and F-196 actuals. This ensures consistency, accuracy, and transparency in budget reporting, even when multiple extensions are submitted throughout the year. | Limited Transparency into F-200 History and Difficulty Managing Multiple F-200 (Budget Extension) Submissions | Districts frequently submit multiple F-200 budget extensions within the same fiscal year—for example, one in November due to levy revenues and another in March due to grants. Historically, the system struggled to identify which extension was the “most current” for reporting purposes. This created confusion in applying appropriation values and resulted in reporting errors. Although partial fixes were made, SME emphasized that ensuring the system always selects the latest valid submission is critical for budget accuracy. | Data Calculation | Budgeting | Budgeting | ||||
| 005BUD | The system should include built-in mechanisms to monitor and enforce timely updates from ESDs. Features should include automated reminders for overdue submissions, dashboards displaying real-time submission status across districts, and escalation alerts when delays exceed thresholds. By providing transparency and accountability, OSPI would have the ability to track compliance and mitigate the risks caused by late updates. | Dependence on ESD Timeliness (F197 Treasurer’s Report) | F-197 reporting is dependent on Educational Service Districts (ESDs) to input reconciled balances based on Treasurer’s Reports. However, ESDs often lag in completing these updates, sometimes by four to six months. As a result, OSPI lacks access to current financial data for projections and reconciliations. This delay introduces risk in financial oversight and reduces the accuracy of forecasts. Compounding the problem, OSPI has no authority or tools within the system to enforce timeliness or to monitor lagging submissions. |
There is not an RCW to enforce timely updates of the F-197 by the ESDs.
| OSPI has the role of development and access to the F-197, but this system was created as a tool for the district’s to monitor cash balances and payments. | Data Collection | Budgeting | Budgeting | ||||
| 006BUD | The system must have a clearly defined, transparent, and consistent framework for edits and business rules. Each edit should be documented, with criteria specifying whether it references current-year or prior-year data, and error messages should be written in plain language that users can understand. Additionally, edits should be configurable so OSPI staff can update rules without IT intervention. This would reduce manual checks, streamline reviews, and improve user confidence in the accuracy of system validations. | Business Rules and Edits | Edits in the budgeting systems (F-195, F-200) are sometimes unclear, inconsistent, or misaligned with actual business rules. SME explained that edits are often used interchangeably with “business rules,” and in some cases, edits force unnecessary manual checks against prior-year values. This inconsistency increases workload for staff, creates confusion for districts, and reduces confidence in the system’s error-checking functionality. | Data Collection | Budgeting | Budgeting | |
| 007BUD | The system must replace Access-based reporting with a modern, secure, and scalable solution. A centralized data mart or SQL-based repository should store F-195 data, with self-service reporting capabilities provided through a user-friendly visualization tool such as Tableau. This would allow districts, unions, and OSPI to filter, query, and analyze data without technical barriers, ensuring accuracy, transparency, and long-term sustainability of reporting. | Reliance on Outdated Access Files for F-195 Reporting | F-195 budget reporting outputs are currently generated as Microsoft Access database files. SME noted that this is a legacy approach, prone to corruption, and requires manual intervention to create and distribute. Access is no longer a supported platform for enterprise reporting, and its limitations prevent flexible, modern analysis. School districts and unions depend on these files for extracting program-level and object-level data, but the process is cumbersome and risks inconsistencies year to year, especially when item codes or formulas change. |
| A CSV file format would be better in the future. It would allow for the front end user to have access to all the data on back-end | All | Budgeting | Budgeting | ||||
| 001INT | The apportionment system must store data in a centralized relational database that retains both current and historical data in a consistent schema. Lookup tables for schools, account codes, and activity codes must be integrated, ensuring a single source of truth. This structure should support direct query, automated reporting, and the ability to generate dashboards without relying on manual Excel manipulations. | Data stored in Excel instead of a centralized database | Currently, critical apportionment and enrollment data is not housed in a centralized database but rather in disparate Excel files and extracts. SME explained that apportionment outputs only exist as Excel extracts stored on the S-Drive, which are inconsistent year over year. This leads to fragile processes, as the manual handling of files risks data corruption and makes it harder to build reliable dashboards. | Data Calculation | Data Integration Reporting | Data Integration Reporting | |
| 002INT | The system should automate data ingestion and transformation, eliminating the need for manual formatting. Enrollment data should flow directly from the source system into the apportionment database, reducing dependency on staff and ensuring consistent, verified inputs. | Dependence on manual processes and staff (SME for enrollment data) | Data pipelines rely heavily on manual intervention. For enrollment data, SME must request specific pivoted reports from SME, who produces them manually in Excel. This dependency creates bottlenecks, delays, and version control issues where multiple files can circulate, creating doubt in data accuracy. | Data Collection | Data Integration Reporting | Data Integration Reporting | |
| 003INT | The apportionment system must include fully maintained lookup/reference tables for schools, accounts, and activity codes within the same database environment. This ensures that all reporting processes can directly query human-readable names alongside financial and enrollment data | Missing or fragmented reference data (codes, schools, activities) | When creating reports, SME must manually join data from multiple servers to resolve codes into meaningful labels (e.g., school names, activity names). This adds complexity, slows down report generation, and introduces opportunities for mismatched joins. | Data Collection | |||
| Data Calculation | Data Integration Reporting | Data Integration Reporting | |||||
| 004INT | The system must enable SQL-based stored procedures for core reporting needs, reducing reliance on R scripts and manual processing. This supports automation, improves reliability, and makes reporting auditable and repeatable. | Reports not structured as reusable stored procedures | Currently, SME uses R scripts to join, clean, and prepare data for Tableau visualizations. While functional, this approach lacks scalability and reusability. Routine reports could be encapsulated in SQL stored procedures for consistency, automation, and reduced maintenance. |
| Customer prefers fully automation of the scripts over the current system only if an internal SAFS staff member can have access to the code for maintenance and troubleshooting | Data Reporting | Data Integration Reporting | Data Integration Reporting | ||||||
| 005INT | The system should enforce schema stability and provide access to 10–20 years of historical data. Historical datasets must be retained in a standardized format, enabling longitudinal studies, forecasting, and legislative reporting without manual archival. | Data inconsistency across years and lack of historical depth | Apportionment extracts change column layouts from year to year, breaking queries and requiring SME to re-engineer Power Query steps. Additionally, prior-year retention is limited, with only recent data available (post-2019). This prevents long-term trend analysis and weakens forecasting capability. | Data Calculation | Data Integration Reporting | Data Integration Reporting | |||
| 006INT | The system should support forecasting and trend analytics by retaining deep historical data and providing data models optimized for predictive reporting. Standardized schemas and consistent historical records will enable more robust analysis to support decision-makers. | Limited ability for forecasting and proactive analytics | Currently, SME can only report on what happened last year. Without structured, historical, and standardized data, forecasting trends such as levy collection or district financial health is difficult and time-intensive. This limits the ability to provide proactive insights for policy and decision-making. | Data Calculation | |||||
| Data Reporting | Data Integration Reporting | Data Integration Reporting | |||||||
| 001ENR | The new system must calculate totals directly from source enrollment data in real time. Any discrepancies should be automatically flagged. The reporting engine should ensure consistency between displayed totals and exported data, eliminating manual reconciliation. | Reports not showing correct totals | During reporting, totals displayed on the screen sometimes don’t reflect actual student-level data. This occurs because the current system relies on cached calculations that may time out, resulting in zero or incomplete totals. Districts and OSPI staff then waste time reconciling mismatched numbers, and confidence in the system’s accuracy is eroded. | Data Calculation | Enrollment Reporting | Enrollment Reporting | |||
| 002ENR | The system must preserve original data when revisions are made. Revision forms must be pre-populated with prior values, and validations must block submission of “zero” revisions unless explicitly intended. | Revisions erase previously reported numbers | When districts create a revision and hit “Save,” sometimes all numbers in the file are zeroed out due to system slowness. This not only forces resubmission but also risks OSPI unknowingly processing blank files, which could lead to incorrect funding allocations. | Data Calculation | Enrollment Reporting | Enrollment Reporting | |||
| 003ENR | The system must maintain clear separation between original files and revisions. Only the selected revision should be deleted, and a confirmation step plus audit log must protect original submissions. | Deleting a revised file deletes the original | Staff noted that deleting a faulty revision sometimes also deletes the original baseline file. This creates data integrity risks and could erase official enrollment counts needed for auditing and apportionment. | Data Calculation | Enrollment Reporting | Enrollment Reporting | |||
| 004ENR | The system must enforce strict validations at data entry: headcount values must be integers, and FTE values must be decimals with exactly two places. | Only accept numbers with correct format | Districts sometimes enter invalid formats (e.g., decimals in headcount, or FTE without proper decimal places). These formatting errors disrupt apportionment calculations and require staff intervention. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 005ENR | The system must allow OSPI staff to “lock” submissions for a defined period each month. During this lock window, districts/ESDs cannot submit revisions until OSPI reopens the system. | Ability to prohibit revisions during processing | Currently, while OSPI staff are running monthly enrollment and apportionment calculations, districts can still submit changes. This disrupts processing and creates data mismatches. SME specifically requested a “lock button” to prevent this. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 006ENR | The system must display edits in a clear, user-friendly way, highlighting critical errors. Submissions should be blocked until critical edits are resolved, and comments must be meaningful. School-level edit visibility should also be considered. | Edit process not effective | Districts are required to run edits before submission, but often they don’t review them carefully. Some districts provide placeholder comments without addressing the discrepancies. This reduces the quality of enrollment data and pushes the burden to OSPI staff. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 007ENR | The system should give OSPI the ability to enforce program/school-level validation rules – limiting what type of enrollment can be reported at a given school type (e.g., Open Doors in R-schools, Skill Centers only at designated schools). This could include limiting the grades that can be reported to specific schools (e.g., Kindergarten reported at a high school). | Missing school-level validations | Currently, districts can submit logically inconsistent enrollments, such as non-Skill Center FTE at a skill center. These errors go undetected until OSPI staff review reports manually, delaying correction. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 008ENR | The new system must integrate ALE and P223 collections. Submissions should be cross-validated automatically, with discrepancies flagged immediately for districts before final submission. | Manual reconciliation between P223 and ALE | OSPI staff must manually compare P223 and ALE data collections to ensure counts match. When mismatches occur, staff must email districts and request corrections. This is repetitive, slow, and prone to oversight. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 009ENR | The new system must support electronic batch uploads for ALE data, allowing districts to upload files rather than manually entering records. | Lack of electronic uploads for ALE | If ALE remains its own collection, districts are forced to manually key data into the system one district at a time. This is tedious and error-prone, especially for large districts with multiple ALE programs. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 0010ENR | The new system must digitize these collections, providing secure online forms for submission. Data must flow directly into the central system for reporting and apportionment calculations. | ||||||||
| Paper-based collections still in use (E672, E525, P213, P223YC, UW enrollment) | Institutional Education (E672), Home/Hospital (E525), Non-High (P213), Washington Youth Academy (P223YC) and UW enrollment reports are still submitted via paper or email, then manually entered into spreadsheets. This adds workload and increases the chance of transcription errors. | Data Collection | |||||||
| Data Calculation | Enrollment Reporting | Enrollment Reporting | |||||||
| 0011ENR | The system must automate apportionment file preparation. It should calculate FTEs and generate apportionment-ready outputs in the required formats, eliminating reliance on manual Excel work. | Manual apportionment file preparation | Currently, SME and others manually extract data, run pivot tables in Excel, and build text files for apportionment. This process is inefficient, repetitive, and highly error-prone. | Data Calculation | Enrollment Reporting | Enrollment Reporting | |||
| 0012ENR | The system must automatically track submission status and generate reminders for outstanding districts. A dashboard should display submission compliance in real time. | Manual identification of non-submitting districts | OSPI staff must manually determine which districts have not submitted their files and then send individual reminder emails. This is time-consuming and risks missing districts. | Data Collection | Enrollment Reporting | Enrollment Reporting | |||
| 0013ENR | The new system should maintain version control of submissions. Original monthly counts should be archived and accessible for audit purposes, even if later revisions occur. | Inconsistent data when revisions override confirmed counts | Earlier, OSPI staff noted that finalized monthly counts could be overwritten by later revisions, making it impossible to retrieve the original version. SME clarified this is less critical now, as final counts are accepted as authoritative, but some staff still see value in audit history. | Data Collection | |||||
| Data Calculation | Enrollment Reporting | Enrollment Reporting | |||||||
| 0014ENR | The system must generate ADA-compliant reports by default. Interactive web reports or accessible PDFs should be provided, ensuring compliance with accessibility standards. | Non-ADA compliant reports | Reports posted on the web are static PDFs and not ADA compliant. This limits accessibility for users with disabilities and does not meet state and federal compliance standards. | Data Reporting | Enrollment Reporting | Enrollment Reporting | |||
| 001EXP | The apportionment system must include a secure, cost-effective digital certification workflow that complies with OSPI and SAO approval standards. It should allow for flexible integration with digital signature platforms (e.g., Adobe, DocuSign) and ensure an auditable approval trail. This requirement reduces costs, eliminates paper, and ensures OSPI staff can validate certifications in real time without depending on costly external platforms. | Manual & fragmented certification/sign-off process | Previously, districts and ESDs mailed signed certification pages, which was replaced by DocuSign. While effective, SME noted DocuSign is expensive and may be excessive for the level of security needed. He suggested Adobe’s certificate-based signing could achieve the same outcome at lower cost. | All | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 002EXP | The updated system should continue supporting electronic retention and provide secure storage with search and retrieval capabilities, ensuring records are audit-ready without paper. | ADA Compliance for Reports | OSPI staff previously printed expenditure pages for review and record retention. SME confirmed this is no longer required since documents are stored electronically. | Data Reporting | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 003EXP | The system must automate federal reporting crosswalks by embedding mapping logic between the state chart of accounts and federal formats (F-33, NPEFS, SFFLS). It should allow OSPI staff to configure and maintain mapping rules, generate outputs in federally required formats, and update crosswalks as federal definitions evolve. This reduces manual rework, ensures accuracy, and accelerates compliance reporting. | Complex crosswalk for federal reporting (F-33, NPEFS, SFFLS) | OSPI’s chart of accounts differs from federal formats. SME must manually build crosswalks by pulling SQL data into Tableau, exporting to Excel, then re-exporting into custom Excel workbooks. This is highly inefficient and error-prone. | Data Calculation | |||||
| Data Reporting | Financial Expenditures Reporting | Financial Expenditures Reporting | |||||||
| 004EXP | The system must empower OSPI staff with role-based administrative controls to manage rollover tasks, edit configurations, and update report templates without external support. This ensures greater agility, reduces costs, and allows staff to align system updates with workload cycles. Consultants should only be required for major upgrades or structural changes, not routine adjustments. | Reliance on Consultants for Rollover/System Changes | SME expressed frustration at relying on external consultants for even minor changes like adjusting formulas, edits, or report text. This dependence creates delays, raises costs, and limits flexibility. Internal staff lack the permissions or tools to perform basic updates. | All | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 005EXP | The system must include embedded documentation, training guides, and knowledge transfer materials. It should also store business rules (e.g., edits, approval conditions) in a transparent, staff-accessible format. This reduces onboarding pain, preserves institutional knowledge, and ensures continuity when staff transitions occur. | Lack of Documentation & Training | SME inherited the system with minimal knowledge transfer and had to reverse-engineer processes, creating inefficiency and risk. He highlighted that institutional knowledge was lost when prior staff left. | All | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 006EXP | The system must include automated notification workflows tied to status changes (system open, submission received, edits required, approval completed). Notifications should be configurable by user role and include both email and in-system alerts. This reduces manual communication overhead, improves timeliness, and ensures districts/ESDs have clear visibility into status changes. | Manual Notifications (System Open, Submission Status) | Notifications such as system opening, submission readiness, and approvals are currently sent via manual emails. This increases administrative overhead and creates potential for delays or missed deadlines. | Data Collection | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 007EXP | The system must support direct online submission of F-185 reports by ESDs with validation and edits built in. It should allow aggregation within the system, generate standard reports, and integrate ESD data into federal reporting calculations. This eliminates manual Excel handling, ensures consistency, and improves data quality before OSPI staff receive files. | F-185 Reporting by ESDs (Excel Driven, No Edits) | ESDs currently complete F-185 in Excel templates with no built-in edits. SME aggregates their submissions manually via Tableau and Excel joins. The process is inefficient, error-prone, and lacks quality control. | Data Collection | Financial Expenditures Reporting | Financial Expenditures Reporting | |||
| 001TRB | The system must be enhanced to include a robust data warehouse that stores historical apportionment and financial data. This repository should support multi-year analysis, allow users to query past records without reloading files, and ensure easy traceability for audits and policy validation. | Lack of Historical Data Storage | The apportionment process currently operates more like a calculator, performing real-time calculations but without storing historical data. As a result, SME and her team must manually extract and compile years of Excel files to perform any trend analysis, forecasting, or validation. This is time-consuming, creates risks of data inconsistencies, and prevents efficient responses to legislative or district-level inquiries. | Data Calculation | |||||
| Data Reporting | Financial Policy Tribal Research | Financial Policy Tribal Research | |||||||
| 002TRB | The system must enforce policy-driven retention schedules and automate the secure archiving or deletion of records in accordance with RIM rules. This will reduce database load, improve performance, and ensure compliance with state and federal audit standards. | Absence of Automated Record Retention / Destruction | The current system does not have automated record retention or destruction processes in place. Over time, this creates large, unmanageable datasets and raises compliance concerns since OSPI is required to adhere to strict records management (RIM) rules. The lack of automation leaves staff with the burden of manually managing records, which increases the risk of errors and missed compliance deadlines. | Data Calculation | Financial Policy Tribal Research | Financial Policy Tribal Research | |||
| 003TRB | The new system should provide direct database access, user-defined fields, and no-code calculation capabilities to minimize reliance on manual Excel work. It should also include built-in data normalization features to streamline reporting and reduce the risk of discrepancies. | Reliance on Manual Excel Processing & Error-Prone Workflows | Much of the work today depends on exporting Excel files, cleaning them, and creating temporary databases. This reliance on manual processes increases errors, limits scalability, and consumes staff time that could otherwise be spent on higher-value analysis. It also leads to duplicate efforts when different teams rebuild similar datasets for their own reporting. | Data Calculation | Financial Policy Tribal Research | Financial Policy Tribal Research | |||
| 004TRB | The system should provide a dedicated sandbox environment that allows for safe testing of new formulas, legislative updates, or code changes. This environment must mirror production data, ensuring accurate modeling without disrupting live operations. | No Sandbox Environment for Legislative Updates & Testing | When new legislation or policy changes impact apportionment formulas, SME and her team cannot test or simulate these updates within the system. Instead, they are forced to build and maintain large Excel-based models (“Mega” and “Mighty”), which are cumbersome, fragile, and prone to inaccuracies. This makes it difficult to provide timely, accurate feedback to policymakers. | Data Calculation | Financial Policy Tribal Research | Financial Policy Tribal Research | |||
| 005TRB | The system must have native ADA-compliant reporting capabilities, automatically generating outputs that meet accessibility standards. This will reduce remediation effort, improve turnaround time for publishing reports, and ensure equitable access to information. | Non-ADA Compliant Reports | Reports generated from the current system are not ADA-compliant. SME often spends days manually remediating reports to meet accessibility standards before publishing them. This adds unnecessary delays, limits transparency, and introduces risks of non-compliance with accessibility laws. | Data Reporting | Financial Policy Tribal Research | Financial Policy Tribal Research | |||
| 006TRB | The system should provide a unified data model and shared access framework to ensure that all teams use the same authoritative source. Standardized reporting and integration across systems will reduce duplication, eliminate inconsistencies, and increase trust in outputs. | Data Silos & Inconsistent Use of S-275 Data | Different OSPI teams pull S-275 and related data in different ways, often resulting in inconsistent outputs and interpretations. This lack of standardization undermines confidence in the data and forces additional reconciliation work across departments. | All | Financial Policy Tribal Research | Financial Policy Tribal Research | |||
| 001PRS | The new system must integrate with the confidentiality program database and automatically identify records flagged for redaction. Built-in logic should prevent unauthorized exposure by applying redactions before reports are published. Additionally, the system should maintain an audit trail of redactions to meet compliance requirements and reduce the reliance on individual staff expertise. | Manual Redaction of Confidential Records | The current S-275 process still relies on manual redaction of personally identifiable information for individuals in the Address Confidentiality Program. SME explained that certain records cannot be made public, requiring a manual step to identify and remove these from Access reports before publishing. This adds risk of oversight, introduces delays, and consumes staff resources, especially since confidential records must be verified against certification system data. This issue was first captured in 2018 and remains unresolved today. | Data Calculation | |||||
| Data Reporting | Personnel Reporting | Personnel Reporting | |||||||
| 002PRS | The future system should generate ADA-compliant outputs by default. Reports should follow Section 508 and WCAG standards without manual intervention, ensuring accessibility for screen readers and other assistive technologies. By automating accessibility formatting, OSPI staff can focus on analysis rather than rework, and the agency can ensure consistent compliance with legal accessibility standards. | Manual ADA Compliance Updates | All public-facing reports must meet ADA accessibility requirements, but currently the formatting is applied manually by OSPI staff after generating Access/Excel outputs. SME confirmed that this process is not tied to a fixed monthly cycle but arises whenever reports are prepared, requiring repetitive adjustments such as table reformatting and accessible PDF conversion. This increases turnaround time and introduces inconsistency in accessibility compliance. | Data Reporting | Personnel Reporting | Personnel Reporting | |||
| 003PRS | The system must centralize all calculations within a secure and auditable environment. This includes compliance ratios, apportionment metrics, and K-12 penalties. Automated workflows should pull enrollment data directly, apply standardized formulas, and generate results that can be traced and validated. Eliminating Excel/Access dependencies will ensure continuity if key staff leave and allow broader staff access to calculations. | Manual Calculations for Apportionment and Ratios | SME explained that while the S-275 system collects data, almost all calculations for staff-per-student ratios, compliance checks (K-3, K-12 penalties), and PSES staffing are performed outside the system in Excel or Access. Additionally, enrollment supervisor provides P223 files externally, which SME then uses for apportionment calculations. This separation from the core system introduces errors, requires specialized staff knowledge, and creates dependencies on spreadsheets that are not scalable or auditable. | Data Calculation | Personnel Reporting | Personnel Reporting | |||
| 004PRS | The new system should provide a secure, intuitive interface where both districts and ESDs can submit, review, and correct data directly. The UI should allow file uploads with built-in validation, but also permit authorized edits without forcing resubmission. Features like guided forms, upload status dashboards, and real-time feedback would reduce submission errors and make the process more efficient for non-technical users. | Unfriendly User Interface for Data Submissions | School districts currently submit S-275 data using structured text files formatted to a rigid layout. SME confirmed that while OSPI and ESDs can correct data directly in the system, districts cannot — they must resubmit files. The system lacks a user-friendly web interface, requiring technical expertise at the district level and limiting transparency. SME also noted that historical processes where ESDs reviewed district files are no longer consistently followed, leaving districts largely unsupported. | Data Collection | Personnel Reporting | Personnel Reporting | |||
| 005PRS | The future system must replace Access with a modern database or warehouse solution. Role-based security should allow multiple OSPI staff to run calculations, extract reports, and access data without bottlenecks. Centralizing the data in a supported environment will also enable integration with downstream systems and improve business continuity by reducing reliance on a single expert. | Dependence on Access Database for Staff Calculations | Staffing data is still processed and analyzed in Microsoft Access. SME described a workflow where district submissions are exported from EDS into Access, where queries and macros are run to prepare compliance and apportionment data. This approach restricts access to one SME and limits data availability to a small group. Access is also an aging technology with limited IT support, creating risk for long-term sustainability. | Data Calculation | |||||
| Data Reporting | Personnel Reporting | Personnel Reporting | |||||||
| 006PRS | The system should provide automated notifications via email, dashboard alerts, or SMS integration. For example, when the system opens for the year, all districts should receive a system-generated notice. If a district misses a submission cutoff, automated reminders should trigger. These alerts should be configurable to reduce manual follow-up by OSPI staff. | Manual Notifications to Districts/ESDs | Notifications to school districts and ESDs — such as opening of the reporting year or reminders for late submissions — are still handled manually. SME sends emails or calls districts when deadlines approach. This consumes staff time, creates delays, and risks districts missing deadlines if communication is inconsistent. This was noted in 2018 and persists today | Data Collection | Personnel Reporting | Personnel Reporting | |||
| 007PRS | The system must include automated publishing options that generate simplified datasets and reports with predefined field subsets. Configurable export formats (CSV, Excel, PDF) should be supported, with automated scheduling. This ensures consistency and reduces staff effort while maintaining control over sensitive data. | ||||||||
| Manual Production of Simplified Data Versions | SME confirmed that staff still manually create simplified versions of S-275 data in Access, Excel, and PDFs for public or stakeholder consumption. This requires reformatting and extraction of selected fields. The process is repetitive and error-prone, with little standardization across outputs. | Data Reporting | Personnel Reporting | Personnel Reporting | |||||
| 008PRS | The system should support configurable edit rules with different enforcement levels. Certain errors should block submission until resolved, while others may remain as warnings. This would improve data quality while still allowing flexibility. A dashboard for tracking unresolved edits should also be provided, so districts know exactly what requires attention. | Data Validation and Edit Limitations | SME described three levels of edits: (1) format-level checks (blocking), (2) warnings, and (3) errors. While format errors stop processing, warnings and errors do not — meaning flawed data can pass through the system. In practice, many edits serve only as advisory, and OSPI must rely on districts to self-correct. This reduces data quality and creates inconsistencies across districts. | Data Collection | Personnel Reporting | Personnel Reporting | |||
| 009PRS | The system should include automated reconciliation workflows for mismatched records. Exception dashboards should flag conflicts (e.g., mismatched SSN, name changes) and route them to the appropriate OSPI team for timely resolution. Data synchronization between S-275 and eCertification should be near real time to minimize errors. | ||||||||
| eCertification System Integration Issues | SME explained that S-275 and the eCertification system share common fields like Social Security numbers. In the past, mismatches caused frequent reconciliation issues, though improvements have reduced the volume to only a handful per year. Still, when mismatches occur, OSPI certification staff must manually resolve them, sometimes taking weeks. This creates frustration for districts and delays in record acceptance. | Data Collection | Personnel Reporting | Personnel Reporting | |||||
| 0010PRS | The new system must provide robust ad hoc reporting tools with a user-friendly interface. Users should be able to filter, aggregate, and export staffing data without requiring database skills. Role-based permissions should control access to sensitive data fields, while still enabling flexible reporting for authorized users. This capability would empower staff across OSPI to self-serve and reduce bottlenecks on specialized personnel. | Lack of Ad Hoc Reporting Capability | SME described frequent ad hoc data requests that cannot be fulfilled directly in EDS. Instead, he manually extracts data from Access or Excel, creating custom datasets on demand. This is time-intensive and error-prone, and it prevents broader OSPI staff from accessing staffing data without SME intervention. | Data Reporting | Personnel Reporting | Personnel Reporting | |||
| 001SAFS | The system must provide reporting functionality that enables OSPI’s Apportionment team to analyze and respond to legislative requests for details on maintenance-level budget drivers | Support planning maintenance-level budget | The system must provide a report to support OSPI's apportionment team to respond to the legislature's requests for information about the drivers of the maintenance-level budget. | Data Calculation | All | SAFS | Epic | ||
| 002SAFS | The Apportionment System must allow OSPI to aggregate and disaggregate district-level financial data to facilitate comprehensive reporting. The system should replace manual Excel-based aggregation processes with automated, coded data integration from F-196 and other sources. Users must be able to combine and separate data for reporting at the district, ESD, and school levels. | Support data aggregation and disaggregation for reporting | OSPI is in the early stages of a process by which we'll be able to identify all EDS data by codes, taking what was an Excel based-system to aggregate Annual Financial Statement data with less manual processing. As such, OSPI wants an easy Apportionment System function that aggregates data from the districts to create the report in the format that we want. Authorized users could then crosswalk all ESD and District data, aggregate it for our reports, then disaggregate it to the school level for DOE and Census. | All | All | SAFS | Epic | ||
| 003SAFS | The system shall have the ability to integrate directly with the State of Washington’s One Washington (OneWA) platform to exchange payment and financial data. The system shall include an method of transmitting approved invoice and apportionment payment data, incorporating all OneWA-required data fields (e.g., SWV vendor identifiers and payment codes). The integration shall follow OneWA’s published interface and security specifications, ensuring seamless, automated payment initiation and reconciliation between the Apportionment and Workday systems | Provide connectivity between WorkDay and Apportionment | The system shall support direct integration with One Washington (One WA), Washington's cloud-based, enterprise-wide system for finance, procurement, budget, HR and payroll processesto exchange invoice and payment data, e.g., to transmit approved invoice data to One WA for payment processing. | Data Reporting | External Integrations | SAFS | Epic | Technical |
This is the start of the file's text. The full file is on GovTribe.
File details come from the government source that posted it. Updated .