COMPLIANCE REVIEW REPORT GENERATOR — SETUP INSTRUCTIONS
=========================================================
Version: September 2026  |  Benefits & Compliance Unit

────────────────────────────────────────────────────────
WHAT'S IN THIS PACKAGE
────────────────────────────────────────────────────────
  Compliance_Data.xlsx      — Data store (holds all historical review data)
  Compliance_Reports.xlsx   — Report generator (the tool you use daily)
  vba/Bootstrap.bas         — One-time setup macro (import this first)
  vba/DataImport.bas        — Data import & sync logic
  vba/ReportEngine.bas      — Report calculation engine
  vba/ExportPDF.bas         — PDF export logic
  vba/Utilities.bas         — Shared helper functions

────────────────────────────────────────────────────────
FOLDER STRUCTURE ON YOUR SHARED DRIVE
────────────────────────────────────────────────────────
Create this folder structure on the shared drive:

  Compliance Review/
  ├── Compliance_Data.xlsm          ← data store goes here
  ├── Compliance_Reports.xlsm       ← report tool goes here
  ├── Source Files/                 ← drop new monthly exports here
  └── PDF Reports/                  ← exported PDFs save here

────────────────────────────────────────────────────────
ONE-TIME SETUP (do this once, then you're done)
────────────────────────────────────────────────────────

STEP 1: Save the files as macro-enabled workbooks
  a. Open Compliance_Data.xlsx in Excel
  b. File → Save As → change type to "Excel Macro-Enabled Workbook (*.xlsm)"
  c. Save as Compliance_Data.xlsm in the "Compliance Review" folder
  d. Close it.

  e. Open Compliance_Reports.xlsx in Excel
  f. File → Save As → "Excel Macro-Enabled Workbook (*.xlsm)"
  g. Save as Compliance_Reports.xlsm in the same folder

STEP 2: Import the VBA modules (one time only)
  a. With Compliance_Reports.xlsm open, press Alt+F11 (opens VBA editor)
  b. In the VBA editor: File → Import File
  c. Import each of the 6 .bas files from the vba/ folder:
       Bootstrap.bas
       DataImport.bas
       ReportEngine.bas
       ExportPDF.bas
       Utilities.bas
       Charts.bas
  d. Close the VBA editor (Alt+Q or close the window)

STEP 3: Run the bootstrap setup
  a. Press Alt+F8 (opens macro dialog)
  b. Select "Bootstrap_Setup" → click Run
  c. Follow the prompts:
     - Locate Compliance_Data.xlsm when asked
     - Choose the "PDF Reports" folder for PDF output
  d. When prompted to sync data, click Yes if you have data to load,
     or No to skip (you can import data later)

STEP 4: Verify it worked
  The HOME tab should now show clickable buttons:
  GENERATE REPORT | Import New Data | navigation links
  You're ready to use the tool.

────────────────────────────────────────────────────────
IMPORTING NEW DATA (monthly or biweekly)
────────────────────────────────────────────────────────
  1. Save the new export file from Smartsheets into the "Source Files" folder
  2. Open Compliance_Reports.xlsm
  3. Click "Import New Data" on the HOME tab
  4. Select dataset type: 1 = Claim Processing, 2 = Benefit Eligibility
  5. Enter the period label (e.g. 2026-09)
  6. Browse to and select the export file
  7. The data imports and syncs automatically

  NOTE: Duplicate Issue IDs for the same period are automatically skipped.
  You can safely re-run an import if needed.

────────────────────────────────────────────────────────
GENERATING A REPORT
────────────────────────────────────────────────────────
  1. Open Compliance_Reports.xlsm
  2. On HOME tab, enter date range (Period From / Period To)
     - Single period: enter the same period in both fields (e.g. 2026-08)
     - Range: enter different periods (e.g. 2026-07 to 2026-09)
  3. Select report type:
     - Full Program: all staff, all managers
     - Manager Team: one manager's team (select from Manager dropdown)
     - Staff Member: individual staff (select from Staff dropdown)
  4. Click GENERATE REPORT
  5. The report tab opens automatically with all metrics populated

────────────────────────────────────────────────────────
ADDING ROOT CAUSE ANALYSIS & EXPORTING PDF
────────────────────────────────────────────────────────
  1. After generating the report, scroll to the ROOT CAUSE ANALYSIS section
  2. Fill in the gold-highlighted cells:
     - Summary of Findings
     - Key Error Patterns
     - Coaching / Action Plan
  3. Click EXPORT AS PDF
  4. PDF saves to the "PDF Reports" folder with an automatic filename
  5. Your analysis is also saved to the ANALYSIS tab for historical reference

────────────────────────────────────────────────────────
SCORE THRESHOLDS (default)
────────────────────────────────────────────────────────
  Green  = 90% and above
  Yellow = 80–89%
  Red    = below 80%

  To change thresholds: open Compliance_Data.xlsm → Settings tab
  Update the Green Threshold and Yellow Threshold values.

────────────────────────────────────────────────────────
IF SOMETHING BREAKS
────────────────────────────────────────────────────────
  - "Data store not found": Re-run Bootstrap_Setup (Alt+F8)
  - "No data in report": Click "Import New Data" first, then try again
  - Buttons missing after file move: Re-run Bootstrap_Setup
  - VBA error on open: Check that macros are enabled
    (File → Options → Trust Center → Enable all macros)
  - Columns changed in source data: Contact whoever maintains this tool
    to update the column mapping in build.py and rebuild

────────────────────────────────────────────────────────
STAFF AND MANAGER LISTS (AUTOMATIC)
────────────────────────────────────────────────────────
  Staff and manager lists update automatically every time you import data.
  When a new month's export contains a staff member or manager not seen
  before, they appear in the dropdowns on the next import — no manual steps.

  Staff are linked to the manager shown in their most recent data period.
  If a staff member moves to a new manager, that link updates automatically
  after you import the period where the change first appears.
