RST Common Operating Picture

RST Common Operating Picture Excel Professional Development Requirements 1. Project Overview I am seeking an Excel professional to refine and professionalize an existing Religious Support Team Common Operating Picture, referred to as the RST COP. The RST COP is intended to provide one standardized tool that supports: 1. Tactical mission execution by individual Religious Support Teams. 2. Operational management by the Wing Chaplain. 3. Strategic advisement to installation, Wing, and higher-headquarters leaders. The finished product should convert decentralized RST updates into a reliable, visually professional, and decision-ready operating picture. The tool should reduce repeated requests for information by allowing RSTs to maintain their information routinely. When leadership asks for an update, the Wing Chaplain should be able to review a current operating picture without requiring every team to create another report. ⸻ 2. Problem the Tool Is Intended to Solve Religious-support information is often distributed across: * Individual trackers * Emails * Calendars * Briefing slides * Verbal updates * Service attendance records * Facility-use records * Counseling statistics * Unit-engagement notes This creates several problems: * RSTs repeatedly recreate reports. * Information may be outdated or inconsistent. * Leaders receive activity numbers without operational context. * Different teams may use different definitions. * Trends are difficult to track over time. * Leadership cannot easily connect unit conditions, counseling trends, religious-service participation, facility demand, RST coverage, manpower, geography, and future requirements. * Important decisions or requests for assistance may not be clearly elevated. The RST COP should establish one standardized reporting structure while separating detailed RST working information from senior-leader decision views. ⸻ 3. Primary Question The workbook should help leadership answer: What is the current state of religious support and installation spiritual and organizational health, what trends or capability gaps require attention, what is the Chaplain Corps doing in response, and what decision or assistance is required from leadership? Supporting questions include: * Which units, populations, and facilities require engagement first? * What concerns are increasing or decreasing? * Where is counseling demand concentrated? * Is RST coverage aligned with current installation demand? * Which religious services are growing, declining, at risk, or experiencing capability gaps? * How are Chaplain Corps facilities being used? * What significant requirements are approaching? * What risks remain after the Chaplain Corps response? * What requires Wing Chaplain, commander, or higher-headquarters awareness or action? ⸻ 4. Intended Users Senior Leaders * Wing Chaplain * Deputy Wing Chaplain * Wing Commander and installation leaders * Higher-headquarters or MAJCOM Chaplain Corps leaders These users need concise status, trend, risk, response, and decision information. Operational Users * Chaplains * Religious Affairs Airmen * Religious Support Teams * Service leads * Facility leads These users need simple, clearly identified data-entry areas and tactical mission-management views. Workbook Administrator A designated HC data owner will be responsible for: * Data-quality review * Formula maintenance * Dropdown and lookup-list maintenance * User access * Version control * Fiscal-year rollover * Archiving * Troubleshooting * Coordination with the Wing Chaplain ⸻ 5. Recommended Workbook Organization Please organize the workbook in this order: 1. START HERE 2. Leadership Dashboard 3. Strategic Calendar 4. Installation Map 5. RST – Senior 6. RST – Deputy 7. RST – Flightline 8. RST – RTV 9. Religious Services 10. Attendance & Facility Use 11. Counseling Log 12. Map & Facility Input Supporting calculation, lookup, archive, or configuration sheets may be added. These sheets should normally be hidden from operational users. The tab order should reflect the intended workflow: 1. Learn how to use the tool. 2. Review the strategic picture. 3. Manage RST execution. 4. Update mission information. 5. Maintain supporting source data. ⸻ 6. Functional Requirements by Sheet START HERE Create a professional landing page containing: * Purpose of the RST COP * Intended users * User responsibilities * Update frequency * Privacy and confidentiality warning * Color legend * Definitions of key indicators * Explanation of editable and calculated cells * Navigation links to major sheets * Fiscal-year rollover overview * Point of contact or data owner * Workbook version * Last major revision date Recommended navigation buttons include: * View Leadership Dashboard * View Strategic Calendar * View Installation Map * Update My RST * Enter Counseling Activity * Update Religious Services * Update Attendance * Update Facility Information ⸻ Leadership Dashboard The dashboard should provide a clean command-level view containing: * Installation status * Units supported * Aggregate counseling sessions during the previous six months * Six-month counseling trend * Counseling activity by RST * RST coverage and unit-health summary * Total religious-service attendance * Attendance by selected service * Facility foot traffic * Religious-service status * Capability and coverage gaps * Upcoming leadership-visible requirements * Leadership decisions or assistance required The dashboard should allow leaders to identify: * Current condition * Trend * Operational meaning * Chaplain Corps response * Remaining risk * Leadership decision or assistance required The dashboard should not become overcrowded with tactical information. Detailed information should remain on the supporting sheets. ⸻ Strategic Calendar Create a professional visual calendar for: * Projects * Courses * Training * Religious services * Major engagements * Installation events * Leadership-visible requirements The calendar should: * Allow month and year selection * Provide a six-month planning view * Use RST color coding * Identify event owner * Identify leadership-visible events * Pull from one authoritative event table * Avoid duplicate entry * Identify scheduling conflicts where practical * Show significant upcoming requirements on the Leadership Dashboard ⸻ RST Tactical Sheets Maintain separate sheets for: * RST – Senior * RST – Deputy * RST – Flightline * RST – RTV Each RST sheet should contain: * Weekly availability * Assigned units * Last engagement * Next engagement or visit due * Unit-health status * Concern category * Short operational assessment * Support requested * Leadership decision required * Last updated * Summary indicators Each RST should have a consistent visual identity: * Dark assigned-color header * Lighter coordinated table body * Clear borders * Consistent fonts and spacing * Clearly marked input cells * Return-to-dashboard navigation RST information should automatically populate the appropriate dashboard summaries. ⸻ Counseling Log and Trend Analysis The Counseling Log must contain aggregate information only. Approved fields should include: * Date * Supporting RST * General concern category * Optional non-identifying note The workbook must not collect: * Names * Personal identifiers * Diagnoses * Detailed counseling narratives * Privileged communication * Information that could reasonably identify a counselee The dashboard should calculate: * Rolling six-month counseling volume * Monthly trends by category * Counseling activity by RST * Top concern categories * Percentage or directional change where meaningful Small-number reporting should be grouped, suppressed, or otherwise controlled when there is a reasonable possibility of identifying an individual or small population. ⸻ Religious Services Create one authoritative religious-services table containing: * Service or program * Faith group * Day * Time * Location * Lead * Supporting RST * Status * Monthly attendance * Attendance trend * Volunteer coverage * Facility or resource requirement * Holy day or observance * Religious accommodation requirement * Capability gap * Leadership assistance required Service attendance should be stored as monthly historical data. Users should not repeatedly overwrite one attendance cell and lose prior months. The workbook should allow leaders to compare service attendance and identify: * Rising participation * Declining participation * Services at risk * Volunteer gaps * Facility constraints * Religious-accommodation requirements * Leadership assistance required ⸻ Attendance & Facility Use Create structured monthly inputs for: * Reporting month * Service * Service attendance * Facility * Facility foot traffic Users should be able to select a month and service through dropdowns or approved filtering controls. The dashboard should display: * Total attendance for the selected month * Attendance by service * Six- or twelve-month attendance trends * Facility use by location * Six- or twelve-month facility-use trends * Highest-use services * Lowest-use services * Highest-use facilities * Significant changes in demand Facilities currently considered include: * Chapel on the RTV side * Oasis Airman Ministry Center * Resource Room * Oasis at Moten Facility names, locations, and operational details will be validated by authorized personnel before operational use. ⸻ 7. Installation Map and GeoBase Limitation Map Purpose The Installation Map is intended to help authorized RST personnel understand: * Where supported units and populations are located * Which RST is responsible for each area * Estimated manpower associated with each facility or location * Engagement priority * Facility usage * Current status * Recent and planned engagement activity This information should help the team make informed engagement decisions based on location, supported population, manpower, and mission demand. Technical Limitation The map will not be generated or dynamically updated within Excel. The authoritative map will be developed using an approved Department of the Air Force GeoBase platform. An authorized user will then export or capture an approved static image and manually insert it into the workbook. The Excel professional should create: * A professional map-image frame * Instructions for replacing the map image * Map title * “As of” date * Source field * Approval field * Required marking location * Standardized legend * Facility-reference table * RST color coding * Engagement-priority symbols * Status indicators * Last-update field The workbook should not: * Connect directly to GeoBase * Store geographic coordinates unnecessarily * Generate an installation map from workbook data * Automatically transmit geospatial information * Provide unrestricted location drill-down * Assume that map information is approved for public or external distribution Map Legend The legend should help authorized users interpret: * Facility identifiers * Supported units * Estimated supported population * Assigned RST * Engagement priority * Current status * Last engagement * Next planned engagement * Facility capacity or usage * Capability gaps The legend and facility-reference information should be maintained by authorized HC personnel. ⸻ 8. Information-Protection Requirements The operational workbook may contain protected installation, facility, manpower, unit-location, capability, and mission information. The appropriate security manager or classification authority must determine whether the information is: * Classified * Controlled Unclassified Information * Subject to operational-security restrictions * Subject to another dissemination or handling requirement The workbook must be: * Stored only on an information system authorized for the highest level of information contained within it * Marked according to applicable classification or CUI guidance * Accessible only to authorized personnel with the appropriate clearance, access, and need to know * Transmitted only through approved government channels * Reviewed before printing, briefing, exporting, emailing, or posting to a collaboration platform * Sanitized before release to personnel not authorized to receive the operational information The Excel professional should build visible locations for: * Overall classification or control markings * Page or sheet markings where required * Distribution statement * “As of” date * Data owner * Approval authority * Version number The developer should not independently determine the classification or CUI status. Final markings and handling requirements will be provided by the appropriate government authority. ⸻ 9. Sanitized Development Requirement If the Excel professional is not specifically authorized to receive the operational information, they must receive only a sanitized development copy. The sanitized copy should contain: * Fictional facilities * Fictional unit names * Fictional manpower totals * Fictional attendance data * Fictional counseling data * Fictional engagement priorities * Fictional events * A fictional or blank installation map * No coordinates * No actual operational assessments * No real capability gaps * No actual protected mission information The professional should build the workbook’s: * Structure * Formatting * Formulas * Tables * Charts * Filters * Slicers * Navigation * Protections * Static-map frame * Legend * Update process Authorized military personnel will insert the actual operational information only after the completed template is returned to an approved government environment. ⸻ 10. Map Update Process The final workbook should contain instructions for this process: 1. An authorized user updates or obtains the map through the approved GeoBase environment. 2. The map is reviewed for classification, CUI, operational-security, and dissemination concerns. 3. An approved static image is exported or captured. 4. The previous workbook image is replaced. 5. The map date, source, legend, and approval information are updated. 6. Facility, unit, population, and manpower information is validated. 7. The Wing Chaplain or designated official approves the map for operational use. 8. The workbook remains within the authorized environment. The map should be reviewed: * When significant unit or facility changes occur * When manpower distribution changes materially * When mission requirements change * During the fiscal-year rollover * At another frequency directed by leadership ⸻ 11. Data Architecture The workbook should follow this principle: Enter information once and use it in multiple views. Whenever practical: * Convert source ranges into structured Excel Tables. * Use named ranges or structured references. * Separate source data, calculations, and presentation. * Avoid formulas based on large fixed blank ranges. * Eliminate duplicate entry. * Use centralized lookup tables. * Use dropdowns for standardized categories. * Keep manual inputs visually separate from formulas. * Create visible error messages for missing required information. * Ensure charts and formulas include new rows automatically. * Use PivotTables, Power Query, Power Pivot, dynamic arrays, or other appropriate Excel capabilities when they improve reliability. * Avoid fragile merged-cell layouts in source-data areas. * Avoid unnecessary VBA or macros unless there is a documented operational need and the approved environment supports them. Please identify any features that require a specific version of Excel. ⸻ 12. Visual and User-Experience Standards The workbook should be elevated to a professional command-level product. The desired style is: * Clean * Modern * Military-professional * Consistent across all sheets * High contrast * Easy to scan * Clearly bordered * Minimal unnecessary white space * Consistent fonts * Consistent row heights and column widths * Controlled use of color * Clear input and formula areas * Charts that support decisions Each sheet should display: * Sheet title * Sheet purpose * Data owner * Update frequency * Last updated * Return-to-dashboard link Recommended tab colors: * Dark navy: START HERE * Blue: Leadership and strategic views * Assigned RST colors: RST tactical sheets * Green: Religious Services and Attendance * Gold: Counseling * Gray: Administrative and source-data sheets Charts should have: * Clear titles * Labeled axes * Defined reporting periods * Readable legends * Consistent color logic * Minimal visual clutter Do not add charts simply for appearance. Every chart should help users understand condition, trend, comparison, demand, risk, or priority. ⸻ 13. Status Definitions Develop and document consistent Green, Yellow, and Red criteria. Suggested definitions: * Green: Requirements are being met and there is no significant unresolved gap. * Yellow: An emerging concern, declining trend, reduced capability, or leadership awareness is required. * Red: A mission-impacting gap, immediate risk, or leadership decision or assistance is required. The status logic must be: * Transparent * Documented * Adjustable * Tested * Consistent across the workbook A Red condition must visibly display as Red. It should not remain Green because of an incorrect formula, formatting rule, or priority order. Automated status should support Wing Chaplain judgment, not replace it. The Wing Chaplain should have an approved method to validate or override the calculated status with a documented rationale. ⸻ 14. Long-Term Sustainment The recommended sustainment model is: * One approved master template * One operational workbook for each fiscal year * One read-only archive for each completed fiscal year * One aggregate historical summary for multi-year comparison A continuously expanding workbook is not recommended because it may become: * Too large * Too slow * Difficult to audit * Filled with outdated information * Vulnerable to formula errors * Difficult to manage during personnel turnover * Confusing when current and historical information are mixed Fiscal-Year Rollover At the beginning of each fiscal year: * Create a new operational workbook from the approved master template. * Archive the previous fiscal-year workbook as read-only. * Carry forward validated active services. * Carry forward validated facilities and supported populations. * Carry forward current RST assignments. * Carry forward open projects and unresolved leadership actions. * Revalidate unit-health assessments before carrying them forward. * Preserve three to six months of aggregate trend data. * Archive completed calendar activities. * Archive detailed prior-year entries according to approved guidance. * Update definitions, lookup lists, and user assignments. * Have the Wing Chaplain validate the new workbook before operational use. The data owner should complete rollover during the final two weeks of September or another period directed by leadership. The rollover process must be documented so it can be completed without the original developer. ⸻ 15. Required Deliverables Please provide: 1. Completed pilot workbook containing fictional demonstration data 2. Clean operational template without fictional data 3. Data dictionary defining every field 4. Green, Yellow, and Red decision rules 5. RST user guide 6. Workbook-administrator guide 7. Static GeoBase map-replacement instructions 8. Map legend instructions 9. Fiscal-year rollover instructions 10. Formula and automation map 11. Excel-version compatibility requirements 12. SharePoint or Teams compatibility assessment 13. Information-protection recommendations 14. Formula, filter, chart, link, and protection test log 15. Short turnover session or recorded demonstration All formulas, queries, macros, connections, and automation should be documented. The workbook must not depend entirely on the original developer. ⸻ 16. Acceptance Criteria The product will be considered successful when: * A new RST user can update it with minimal instruction. * Information is entered once and appears in the correct downstream views. * New rows are automatically included in formulas and charts. * Filters, dropdowns, and slicers work properly. * The dashboard reflects Green, Yellow, and Red conditions accurately. * Leaders can identify status, trend, meaning, response, risk, and decision requirements. * Counseling information cannot reasonably identify an individual. * The operational map can be replaced without breaking the workbook. * The map legend remains clear and usable. * Protected operational information is not required during external development. * The workbook remains functional after fiscal-year rollover. * An assigned HC data owner can maintain the product. * Normal use does not generate formula errors. * The product functions in the approved Microsoft environment. * The workbook can be briefed without exposing unnecessary tactical detail. ⸻ 17. Requested Professional Assessment Before beginning major development, please review the current workbook and provide recommendations concerning: * Whether Excel remains the correct long-term platform * Which sheets or fields are redundant * Whether the workbook should use PivotTables, Power Query, Power Pivot, Office Scripts, VBA, or another approach * SharePoint and Teams multi-user performance * Version-control requirements * Data-protection risks * Dashboard usability * Workbook performance * Long-term scalability * Annual rollover * Maintenance workload * Development timeline * Estimated cost * Training requirements * Recommended phases of development Please preserve the operational intent while improving the workbook’s: * Structure * Automation * Reliability * Visual professionalism * Ease of use * Security * Continuity * Sustainability The immediate goal is not to create an unrestricted enterprise system. The goal is to produce a controlled, professional, and sustainable Excel-based pilot that can be evaluated by HC leadership before broader implementation.