Building an Operational Risk Register for Leadership

The Business Situation

Morrow Vale Equipment Services is a representative 82-person business that maintains industrial equipment and distributes replacement parts. Its leadership team includes the chief operating officer, finance director, compliance manager, IT manager, service operations director, and supply chain manager.

The compliance manager administers the operational risk process, but the risks themselves are owned across the business. Leadership regularly discusses equipment downtime, supplier concentration, cybersecurity, customer service continuity, cash exposure, contract obligations, and regulatory requirements.

The company had approximately 60 active risks. In a typical month, the team added or materially revised 10 risks and completed about 20 scheduled risk reviews. Leadership discussed the most significant items during a monthly operating meeting and performed a more complete review each quarter.

The existing process relied on a Google Sheet, documents in Google Drive, email, and occasional DocuSign requests. The spreadsheet listed risk titles and owners, but causes, controls, residual exposure, actions, review evidence, and approval history were recorded inconsistently.

The objective was not to automate risk judgment. The business needed a practical register that made scoring transparent, assigned responsibility, collected approval evidence, and reminded owners about actions, renewals, and obligations.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The Existing Process

The original process developed gradually rather than through a formal system design. It worked while the company had a short list of risks, but it became difficult to maintain as risk categories, evidence requirements, and leadership expectations expanded.

1. A department manager identified a risk during a meeting, in an email, or while reviewing an incident.

2. The compliance manager added a row to a shared Google Sheet. Sometimes only a title, owner, and general rating were available.

3. The manager emailed the proposed owner for more information about causes, impacts, controls, and treatment actions.

4. The risk owner replied by email or added comments to a separate document. Information was then copied back into the spreadsheet.

5. Leadership discussed the risk and assigned a rating such as low, medium, or high. The basis for the rating was not always documented.

6. If a formal acknowledgment was required, the compliance manager created a document, found the current approvers, and sent it through DocuSign.

7. The signed file was downloaded and placed in Google Drive. The spreadsheet was updated manually with the approval date and file link.

8. Action and renewal dates were monitored using calendar reminders, email flags, and spreadsheet filters.

9. Before each leadership meeting, the compliance manager reconciled the spreadsheet, Drive folders, DocuSign envelopes, and email threads.

The spreadsheet was not the primary problem by itself. The larger problem was the absence of controlled intake, defined statuses, linked actions, consistent approval routing, and an auditable connection between the register and supporting documents.

What the New System Needed to Do

Leadership agreed on the requirements before selecting the final tool configuration. This prevented the implementation from becoming a direct copy of the old spreadsheet.

Risk scoring method

The register used a simple 5 by 5 scoring model. Risk owners selected likelihood and impact independently. Airtable multiplied the values and assigned a descriptive rating. The formula did not decide whether the business should accept the risk.

• Low: score from 1 through 4
• Moderate: score from 5 through 9
• High: score from 10 through 16
• Critical: score from 17 through 25

Inherent risk represented exposure before considering controls. Residual risk represented the owner’s assessment after existing controls. Both used the same transparent calculation. Any override required a note and compliance review.

Implementation Approaches Considered

Improving the spreadsheet

The team could have added protected columns, formulas, and filtered views to Google Sheets. This would have improved scoring consistency, but linked controls, actions, approvals, and documents would still have been difficult to govern. Spreadsheet row edits also provided limited workflow control.

Airtable without integration automation

Airtable could provide structured records, linked tables, forms, formulas, and operational views. However, manually preparing documents, sending envelopes, saving completed files, and updating approval status would preserve several high-effort steps.

Airtable with Zapier orchestration

This option retained familiar Google Workspace and DocuSign tools while adding a structured system of record and event-driven automation. It supported the current volume without requiring a custom application.

Dedicated GRC software

A specialized governance, risk, and compliance platform would be appropriate if the business required complex control frameworks, regulatory mappings, formal attestations, large audit teams, or enterprise-wide policy management. Those requirements were outside the initial scope.

Custom application

A custom application could enforce sophisticated rules and user experiences, but the company would become responsible for software hosting, identity integration, security testing, backups, releases, and support. That burden was not proportionate to approximately 60 active risks and 30 monthly workflow events.

The Selected Solution

Morrow Vale selected Airtable as the operational risk system of record, Zapier as the automation layer, Google Docs for document preparation, Google Drive for controlled storage, DocuSign for signatures, and Gmail for operational notifications.

The existing Google Drive and DocuSign environments were retained. The Google Sheet ceased to be the active register after migration and was preserved as read-only historical evidence.

The automation removed document copying, envelope preparation, routine status checks, folder naming, and reminder tracking. Risk scoring, control evaluation, risk acceptance, approval, and closure remained human-controlled.

System Architecture and Data Flow

Airtable holds the current operational state. Zapier watches controlled Airtable views and DocuSign events. Google Docs and Drive hold human-readable evidence, while DocuSign records formal approval. Returned identifiers are written back to Airtable so each risk can be traced across systems.

• Intake: An internal Airtable form and controlled Airtable interface.
• System of record: Airtable base with linked Risks, Controls, Actions, Approvals, and Automation Log tables.
• Automation layer: Zapier workflows using OAuth connections and idempotency checks.
• Document storage: Google Drive, with Google Docs used to generate approval packs.
• Notifications: Gmail for operational notices and DocuSign for signature requests.
• Reporting: Airtable views and Interfaces based on current register data.
• AI layer: Optional risk-statement quality review added only after the core workflow is stable.

1. Risk submission: A manager submits category, cause, impact, proposed owner, controls, and review cadence through Airtable. Required fields and allowed values are validated. Airtable creates a record and returns its internal record ID. Incomplete records enter Validation Required rather than continuing to approval.

2. Record identification: Airtable assigns an autonumber and formula-based Risk ID. A duplicate fingerprint is calculated from normalized title, category, and business area. Potential matches are sent to human review instead of being merged automatically.

3. Owner review: The proposed risk owner confirms the narrative, selects inherent and residual likelihood and impact, links controls, and creates treatment actions. Formula fields calculate the scores and ratings.

4. Approval trigger: When all required fields are valid and Approval Requested is selected, the record enters a dedicated Airtable view. Zapier detects the record, receives its risk data, and searches the Approvals table for the same automation key.

5. Idempotency check: The automation key combines the Airtable record ID and review version. If the key already exists, Zapier stops. Otherwise, it marks the risk as Preparing and creates an approval record.

6. Drive preparation: Zapier searches for a folder named with the Risk ID. If it does not exist, Zapier creates it under the active risk directory. Google Drive returns a folder ID and URL, which are written to Airtable.

7. Document generation: Zapier sends validated risk fields to a Google Docs template. Google Docs creates the approval pack in the risk folder and returns the document ID and URL. A document-creation failure marks the approval as Failed and prevents an envelope from being sent.

8. Signature request: Zapier selects a two-signer or three-signer DocuSign template based on the approved routing tier. It maps recipient names, emails, risk identifiers, scores, narratives, and the approval-pack URL. DocuSign returns an envelope ID and initial status.

9. Status synchronization: DocuSign envelope events trigger a second Zap. Zapier finds the approval by envelope ID, normalizes the DocuSign status, and updates Airtable. Unknown envelopes are written to the exception log.

10. Completed document storage: When the envelope reaches completed status, Zapier transfers the signed document and completion evidence exposed by the DocuSign connection into the Executed subfolder in Google Drive. It writes the resulting Drive link to the approval and risk records.

11. Action and obligation reminders: A scheduled Zap queries Airtable for actions, renewals, and obligations reaching reminder thresholds. It sends the appropriate owner notification, records the reminder stage, and escalates overdue items according to policy.

12. Reporting and review: Airtable views show current ratings, overdue work, upcoming reviews, outstanding signatures, and automation failures. Leadership reviews the data, but any acceptance or closure decision is made by an authorized person.

Data Structure

The Airtable base contains five related tables. One risk can have many controls, actions, approvals, and automation log entries. Each action or approval links back to exactly one risk.

Risks table

Controls table

Actions and obligations table

Approvals table

Automation Log table

The Automation Log acts as an operational dead-letter queue. It stores Run ID, Automation Name, Risk ID, Action ID, Approval ID, Event Type, Event Key, Start Time, End Time, Result, Attempt Number, Error Category, Error Message, Recovery Owner, and Resolved Date.

Workflow Statuses and Ownership

A DocuSign decline does not automatically reject or close a risk. It moves the record to Change Requested, preserves the decline reason, and requires a new review version before another envelope can be sent.

Records move backward when controls are found ineffective, evidence is missing, scoring changes materially, or an approver requests revision. Only the compliance manager can void an active envelope or reopen a closed risk.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create a production Airtable base named Operational Risk Register and a separate test base named Operational Risk Register TEST. The required Airtable subscription must support the selected record volume, collaborator permissions, forms, interfaces, and automation connections.

2. Assign the compliance manager as base owner. Give risk administrators permission to edit configuration and records. Give risk owners permission only to the records and interfaces needed for their work where the available Airtable permission model supports that boundary. Provide leadership with read or comment access unless editing is required.

3. Create a dedicated Google Workspace automation identity such as risk-automation@YOUR_DOMAIN. Do not connect Zapier through a departing employee’s account.

4. Create an organization-owned shared drive named Risk Governance where the available Google Workspace configuration supports shared drives. Otherwise, use an admin-owned folder with documented ownership and restricted sharing.

5. Create a DocuSign automation user or controlled integration user with permission to send the approved internal templates. A DocuSign administrator should create the templates and control changes to recipient roles, tabs, reminders, and expiration settings.

6. Create the Zapier workspace and limit editor access to the compliance administrator and technical backup owner. Connect Airtable, Google Drive, Google Docs, Gmail, and DocuSign using OAuth. Zapier stores the resulting connection tokens, so no passwords or API keys should be placed in fields or Zap notes.

7. Create a shared compliance inbox such as risk-register@YOUR_DOMAIN for failure alerts, escalations, and replies. Configure access for the primary and backup administrators.

8. Create test users for a requester, risk owner, executive approver, and compliance approver. Use non-production records and a test DocuSign template during development.

9. Document permission boundaries before connecting tools. Zapier should be able to read and update only the relevant Airtable base, create files only in the risk-governance Drive location, and send only approved DocuSign templates.

Step 2: Build the Intake

Create an Airtable form connected to the Risks table. Use an authenticated internal interface or restricted form-sharing method where available. Do not expose the risk form publicly.

Where conditional form fields are supported, show Supplier Name for supplier risks, System Name for technology risks, Customer Segment for customer risks, and Contract Reference for legal or contractual risks. If conditional fields are unavailable, enforce the requirement through the Validation Result formula and compliance review.

The form confirmation should state that submission does not constitute risk acceptance or approval. It should provide the generated Risk ID after review through a follow-up notification rather than exposing the full register.

Potential duplicates are not automatically deleted. The Duplicate Fingerprint formula identifies similar submissions, and a Zapier search checks for an active risk with the same fingerprint. Matching records enter Manual Review so the compliance manager can merge, link, or retain them as distinct risks.

Spam is controlled by using authenticated internal access, restricted form distribution, and organization-managed accounts. Incomplete submissions remain in Draft or Validation Required and cannot enter the approval view.

Step 3: Create the System of Record

Create the Risks, Controls, Actions, Approvals, and Automation Log tables described earlier. Use linked-record fields rather than copying the Risk ID into every descriptive field.

Configure the following naming conventions:

• Risk ID: RISK-YYYY-NNNN
• Control ID: CTRL-YYYY-NNNN
• Action ID: ACT-YYYY-NNNN
• Approval ID: APR-YYYY-NNNN
• Drive folder: Risk ID followed by a sanitized short title
• Approval pack: Risk ID, review version, and approval date
• Executed file: Risk ID, review version, and Executed

Create the following controlled views:

• Intake – New Drafts
• Compliance – Validation Required
• Automation – Approval Ready
• Automation – Reminder Candidates
• Leadership – High and Critical
• Owners – Open Actions
• Compliance – Upcoming Reviews
• Compliance – Approval Exceptions
• Operations – Automation Failures
• Archive – Closed Risks

The Approval Ready view should include only records where Validation Result is READY, Approval Requested is selected, Approval Status is Not Requested, and Automation Status is not Processing.

Use Airtable record history and the Automation Log for operational traceability. For fields requiring formal historical snapshots, create a new Approvals record rather than overwriting the prior approved version.

Step 4: Connect the Tools

Create and test each OAuth connection independently before building multi-step workflows. Interface labels may vary by connector version, so select the action that performs the described trigger or destination operation.

After each destination action, write the returned identifier back to Airtable. A workflow should never depend only on file names or email subjects when a stable document, folder, envelope, or record ID is available.

Step 5: Build the Core Automation

Automation A: Validate a new risk

• Trigger: New risk record created through the intake form.
• Conditions: Confirm required fields and test the duplicate fingerprint against active records.
• Actions: Assign Risk ID, set initial status, notify the proposed owner, and create an Automation Log entry.
• Fields updated: Status, Validation Result, Automation Status, Last Automation Run, and Error Message.
• Notification: Owner receives a link to review the record.
• Exception: Missing data or a potential duplicate sets Manual Review and alerts compliance.

Automation B: Prepare the approval pack and send the envelope

• Trigger: Risk enters the Automation – Approval Ready view.
• Conditions: Validation Result equals READY, Approval Requested is selected, and required recipient emails are present.
• Actions: Search for the automation key, lock the record, find or create the Drive folder, create the Google document, create the approval record, select the DocuSign template, send the envelope, and update Airtable.
• Fields updated: Approval Status, Automation Status, Drive Folder Link, Approval Pack Link, Envelope ID, Status, Last Automation Run, and Retry Count.
• Notification: DocuSign sends the signature request. Compliance receives a preparation confirmation.
• Exception: Any failure before envelope creation leaves the record in Failed. A failure after envelope creation requires an envelope search before retrying.

The exact action order is important:

1. Read the Airtable record and internal record ID.
2. Build the automation key as the internal Airtable record ID plus review version.
3. Search the Approvals table for the key.
4. Stop if a matching approval already has an envelope ID.
5. Update Automation Status to Processing.
6. Create an Automation Log record with result Running.
7. Search Google Drive for the risk folder under the approved parent.
8. Create the folder only when no exact folder ID or matching governed folder exists.
9. Create the Google Docs approval pack from the controlled template.
10. Create or update the Approval record with the document URL.
11. Select the two-signer or three-signer DocuSign path.
12. Send the approved template with dynamic recipient and risk fields.
13. Capture the envelope ID immediately.
14. Update the Approval and Risk records with the envelope ID and Sent status.
15. Clear Approval Requested so the record cannot immediately re-enter the trigger view.
16. Mark the Automation Log and risk automation status Completed.

Automation C: Synchronize DocuSign status

• Trigger: DocuSign reports an envelope status change.
• Conditions: Envelope subject or custom field identifies the operational risk workflow.
• Actions: Find the Approval by Envelope ID, normalize status, update timestamps, and branch on completed, declined, or voided.
• Fields updated: Envelope Status, Approval Status, risk Status, Completed Date, Decline Reason, and Last Automation Run.
• Notification: Compliance is notified of declines, voids, and unknown envelopes.
• Exception: An unknown envelope creates an Automation Log record and is not attached to a risk automatically.

Automation D: Store executed documents

• Trigger: A governed envelope reaches completed status.
• Conditions: The envelope ID matches an Approval record and an executed file has not already been stored.
• Actions: Obtain the completed document file made available through the DocuSign connection, upload it to the risk’s Executed folder, and update the links.
• Fields updated: Executed File Link, Completed Date, Last Review Date, Next Review Due, Approval Status, and risk Status.
• Notification: Risk owner, executive approver, and compliance receive final confirmation.
• Exception: If file transfer fails, approval remains Completed but document storage status becomes Failed Document Transfer.

Before relying on the completed-document step, test what the current DocuSign connector exposes. The connection must provide the signed document as a file or authorized download value that the Google Drive action accepts. If it exposes metadata only, use a supported DocuSign completed-document download action or the organization’s approved DocuSign cloud-storage capability rather than passing an unauthenticated URL.

Automation E: Send action and obligation reminders

• Trigger: Scheduled Zap runs once each business morning.
• Conditions: Action is not completed or cancelled, Reminder Stage differs from Last Reminder Stage, and a valid owner email exists.
• Actions: Retrieve reminder candidates, loop through the records, send the appropriate email, and update reminder fields.
• Fields updated: Last Reminder Stage, Last Reminder Sent, Status, and Automation Log.
• Notification: Owner receives 30-day, 14-day, 7-day, or overdue notice.
• Exception: Invalid owner email or missing escalation owner sends the item to Manual Review.

Step 6: Add Approvals, Reminders, and Escalations

Create two controlled DocuSign templates.

The routing tier is calculated as a suggestion but confirmed by the compliance manager before Approval Requested is selected. If the risk owner and executive approver are the same person, the compliance manager assigns an authorized alternate rather than sending duplicate roles to the same signer.

Configure DocuSign reminder and expiration behavior according to the company’s approved policy. In this representative implementation, the working rule was an initial reminder after two days, repeating every two days, with compliance review before expiration at 14 days. Confirm that the organization’s DocuSign account supports the selected settings.

If an approver is unavailable, compliance does not edit an envelope silently. It voids the original envelope with a documented reason, updates the approver in Airtable, increments the review version when content or routing evidence must change, and sends a new envelope.

A decline causes the following actions:

1. Record the decline status, timestamp, and reason.
2. Set the risk status to Change Requested.
3. Set Approval Requested to unchecked.
4. Notify the risk owner and compliance manager.
5. Require content correction and a new review version.
6. Preserve the declined envelope ID and document links.

Action and obligation reminders use these thresholds:

• Thirty calendar days before the due date for renewals and contractual obligations.
• Fourteen calendar days before the due date.
• Seven calendar days before the due date.
• On the first overdue business-day run.
• Every seven days while overdue, subject to the escalation rule.
• Escalation to the owner’s manager or designated executive after five overdue days.

Each notification contains the Action ID, Risk ID, description, due date, owner, current status, and a controlled link to the Airtable record. It does not include sensitive risk details when email is not an approved channel for that classification.

Step 7: Add Documents and File Management

Create the following Google Drive structure:

Risk Governance/
  01 Active Risks/
    RISK-2026-0047 - Single Source Control Module/
      01 Approval Packs/
      02 Executed/
      03 Control Evidence/
      04 Action Evidence/
  02 Templates/
  03 Closed Risks/
  04 Automation Exceptions/
  05 Historical Register/

Use the Risk ID as the stable folder prefix. Sanitize the title by removing unsupported characters and limiting its length. The folder ID, not the title, is the authoritative integration reference after creation.

Approval packs use the naming pattern:

RISK-2026-0047_V02_Approval-Pack_2026-07-15

Executed files use:

RISK-2026-0047_V02_Executed_2026-07-17.pdf

Risk owners receive access only to their assigned risk folders where practical. Leadership receives read access to approved evidence, while legal privilege and detailed security risks use restricted subfolders. Public links and unrestricted organization-wide links are disabled.

Google Docs version history manages changes to draft approval packs. Once a DocuSign envelope is sent, the source version is treated as frozen. A material change requires a new document and review version.

Attachments submitted through Airtable are copied promptly to the governed Drive folder. The resulting Drive link is stored in Airtable. Duplicate documents are identified using the source name, size, related record, and upload date, but uncertain matches remain subject to human review.

A failed upload does not block evidence that an approval occurred. It creates a document-transfer exception, alerts compliance, and leaves the completed DocuSign envelope ID intact for manual recovery.

Retention periods should be approved by legal and compliance based on contract, employment, regulatory, insurance, and litigation requirements. Closed risk folders move to the closed-risk location but are not deleted merely because the risk status changes.

Step 8: Add Reporting and Operational Views

Create Airtable views and Interfaces for the following operational queues:

• New records: Created in the last seven days and not yet validated.
• Awaiting action: Open actions grouped by owner and due date.
• Overdue records: Actions past due or risks past the next review date.
• Incomplete records: Validation Result is not READY.
• Exceptions: Automation Status is Failed or Manual Review.
• Rejected items: Latest approval status is Declined.
• Items by owner: Active risks and actions grouped by accountable person.
• Upcoming deadlines: Due within 30 days.
• Recently completed work: Completed during the last 30 days.
• Automation failures: Unresolved Automation Log records.
• Processing time: Days from submission to completed approval.
• Volume by status: Count of risks by status and residual rating.
• Manual-review queue: Duplicate candidates, invalid routing, and document failures.

Leadership’s primary interface shows active risk count, residual rating distribution, high and critical risks, overdue actions, upcoming reviews, outstanding signatures, and recent material changes.

Calculated reporting fields include:

• Days Open
• Days Until Review
• Days Overdue
• Approval Cycle Days
• Open Action Count
• Overdue Action Count
• Latest Approval Status
• Unresolved Exception Count

Airtable updates these views from the live base. Dashboard ownership remains with the compliance manager, while the technical backup owner verifies filters and formulas after structural changes.

Alert thresholds include any Critical residual risk, any High risk without an approved owner, any approval outstanding for more than seven days, any action more than five days overdue, and any unresolved automation failure older than one business day.

Step 9: Add Security and Governance Controls

• Least privilege: Give users only the base, interface, folder, template, and automation access required for their role.
• Role-based access: Separate configuration rights from normal risk-record editing.
• Sensitive fields: Restrict legal privilege, personal data, customer details, financial estimates, and security vulnerabilities.
• Shared links: Disable public links and review organization-wide Drive permissions.
• Credentials: Store OAuth tokens in managed connectors. Do not place passwords or access tokens in Airtable.
• Activity logs: Retain Airtable record history, Zapier task history, DocuSign envelope evidence, Drive activity, and Automation Log entries.
• Former employees: Disable their identity-provider account, remove Airtable access, transfer Drive ownership where needed, and replace automation connections.
• Retention: Apply approved retention periods to risks, approvals, contracts, and supporting evidence.
• Backups: Export the register on a defined schedule and verify that Drive files remain recoverable under the organization’s backup policy.
• Privacy: Collect only information needed for risk governance. Avoid placing unnecessary employee, customer, or supplier personal data in narratives.
• Regulatory review: Legal and compliance should assess requirements relevant to the company’s industry and jurisdiction.
• AI restrictions: Do not send privileged material, credentials, personal data, detailed vulnerabilities, or restricted contracts to an unapproved AI service.
• Human approval: Keep scoring, acceptance, closure, and policy exceptions under authorized human control.

Step 10: Deploy and Test

1. Build the complete workflow in the test Airtable base, test Drive folder, test Google Docs template, and non-production DocuSign template.

2. Create sample records for each category, rating, routing tier, status, and exception condition.

3. Run technical tests using dedicated requester, owner, executive, and compliance accounts.

4. Complete user acceptance testing with the compliance manager, two risk owners, and one executive approver.

5. Pilot the system with five to ten active risks. Do not migrate all records until approval, document storage, and reminders have operated successfully.

6. Reconcile every pilot record across Airtable, Google Drive, DocuSign, and Zapier task history.

7. Export the existing spreadsheet, preserve it as read-only historical evidence, and map each active row to a new Risk ID.

8. Activate the production Zaps in sequence: intake, approval preparation, status synchronization, document storage, and reminders.

9. Monitor every run during the first two weeks. The compliance manager owns business exceptions, and the technical backup owner handles connection or mapping problems.

10. Maintain a rollback plan that pauses Zaps, returns approvals to manual sending, and preserves Airtable records without deleting any generated envelope or document.

11. Publish role-specific instructions covering intake, owner review, approval, action completion, exception recovery, and support contacts.

Code and Configuration

No custom application code is required for the core implementation. Airtable formulas, Google Docs templates, DocuSign templates, and native Zapier triggers and actions provide the necessary behavior. This reduces the number of separately hosted components, but it does not eliminate configuration, testing, monitoring, or maintenance.

Airtable Risk ID formula

Place this formula in the Risk ID formula field. It requires a Risk Sequence autonumber field.

"RISK-" & DATETIME_FORMAT(CREATED_TIME(), "YYYY") & "-" & RIGHT("0000" & {Risk Sequence}, 4)

Expected output is a value such as RISK-2026-0047. Test it by creating several records and confirming that identifiers remain unchanged when the title or owner changes.

Inherent score and rating formulas

IF(
  AND({Likelihood}, {Impact Score}),
  {Likelihood} * {Impact Score}
)

IF(
  {Inherent Score} = BLANK(),
  BLANK(),
  IF(
    {Inherent Score} >= 17,
    "Critical",
    IF(
      {Inherent Score} >= 10,
      "High",
      IF(
        {Inherent Score} >= 5,
        "Moderate",
        "Low"
      )
    )
  )
)

Create equivalent formulas for residual score and rating by replacing the source field names. If a score does not calculate, confirm that the inputs are number fields rather than text or single-select labels.

Next review formula

IF(
  {Last Review Date},
  DATEADD(
    {Last Review Date},
    SWITCH(
      {Review Cadence},
      "Monthly", 1,
      "Quarterly", 3,
      "Semiannual", 6,
      "Annual", 12,
      3
    ),
    "months"
  )
)

The default of three months applies only as a defensive fallback. Validation should prevent an unrecognized cadence from reaching approval.

Validation Result formula

IF(
  AND(
    {Risk Title},
    {Category},
    {Business Area},
    {Risk Statement},
    {Cause},
    {Impact Narrative},
    {Likelihood},
    {Impact Score},
    {Residual Likelihood},
    {Residual Impact},
    {Risk Owner},
    {Executive Approver},
    {Review Cadence},
    {Review Version}
  ),
  "READY",
  "MISSING REQUIRED DATA"
)

This formula checks presence, not quality. The risk owner and compliance manager remain responsible for assessing whether the content is meaningful.

Duplicate fingerprint formula

LOWER(
  TRIM({Risk Title}) & "|" &
  TRIM({Category}) & "|" &
  TRIM({Business Area})
)

The Zap searches active risks for the same value. A match creates a review flag rather than automatically merging records.

Action reminder-stage formula

IF(
  OR({Status} = "Completed", {Status} = "Cancelled"),
  "None",
  IF(
    {Due Date} < TODAY(),
    "Overdue",
    IF(
      DATETIME_DIFF({Due Date}, TODAY(), "days") <= 7,
      "7 Days",
      IF(
        DATETIME_DIFF({Due Date}, TODAY(), "days") <= 14,
        "14 Days",
        IF(
          DATETIME_DIFF({Due Date}, TODAY(), "days") <= 30,
          "30 Days",
          "None"
        )
      )
    )
  )
)

The scheduled Zap sends a message only when Reminder Stage is not None and does not equal Last Reminder Stage. For repeated overdue reminders, add a separate calculated field that becomes eligible when at least seven days have passed since Last Reminder Sent.

Google Docs approval template

Place the following content in a controlled Google Docs template under the Templates directory. Zapier’s document-from-template action should expose the placeholders as mapping fields. Placeholder syntax can vary, so confirm it with a test document before production use.

OPERATIONAL RISK REVIEW

Risk ID: {{RISK_ID}}
Review Version: {{REVIEW_VERSION}}
Risk Title: {{RISK_TITLE}}
Category: {{CATEGORY}}
Business Area: {{BUSINESS_AREA}}

Risk Statement
{{RISK_STATEMENT}}

Cause
{{CAUSE}}

Potential Impact
{{IMPACT_NARRATIVE}}

INHERENT ASSESSMENT
Likelihood: {{LIKELIHOOD}}
Impact: {{IMPACT_SCORE}}
Score: {{INHERENT_SCORE}}
Rating: {{INHERENT_RATING}}

EXISTING CONTROLS
{{CONTROL_SUMMARY}}

Control Effectiveness: {{CONTROL_EFFECTIVENESS}}

RESIDUAL ASSESSMENT
Likelihood: {{RESIDUAL_LIKELIHOOD}}
Impact: {{RESIDUAL_IMPACT}}
Score: {{RESIDUAL_SCORE}}
Rating: {{RESIDUAL_RATING}}

OPEN ACTIONS AND OBLIGATIONS
{{ACTION_SUMMARY}}

Risk Owner: {{RISK_OWNER_NAME}}
Executive Approver: {{EXECUTIVE_APPROVER_NAME}}
Review Cadence: {{REVIEW_CADENCE}}
Next Review Due: {{NEXT_REVIEW_DUE}}

Prepared from Airtable record: {{AIRTABLE_RECORD_URL}}
Automation Key: {{AUTOMATION_KEY}}

Zapier must receive plain text for linked controls and actions. Use Airtable rollup or lookup fields to create readable summaries before mapping them to the template.

DocuSign template configuration

Test each template by sending it to test recipients. Verify routing order, required tabs, dynamic field mapping, reminder behavior, expiration behavior, and the envelope ID returned to Zapier.

Status normalization

Configure the DocuSign status Zap with the following mapping:

sent or delivered       -> Approval Status: Sent
completed               -> Approval Status: Completed; Risk Status: Monitoring
declined                -> Approval Status: Declined; Risk Status: Change Requested
voided                  -> Approval Status: Voided; Risk Status: Change Requested
unknown or empty        -> Automation Status: Manual Review

Deploy the configuration by turning on the Zaps only after successful test envelopes have updated Airtable and stored completed documents in the test Drive location. Inspect Zapier task history, Airtable Automation Log records, DocuSign envelope history, and Google Drive activity when troubleshooting.

Failure Handling and Operational Reliability

The implementation treats failures as records requiring ownership. A failed Zap task is not considered resolved merely because an alert was sent.

Idempotency is enforced with an automation key before any signature request is sent. Folder and document actions use stored external IDs. A retry must first search Airtable, Google Drive, and DocuSign for prior completion.

Connector retries are appropriate for read operations and clearly idempotent updates. Non-idempotent actions, especially sending an envelope, require a search before replay so a timeout does not create a duplicate request.

The Automation Log is the manual-review and dead-letter queue. Unresolved failures appear in a dedicated Airtable view. The primary administrator reviews it each business day, while the backup owner receives an escalation for failures older than one business day.

A weekly reconciliation compares approvals marked Completed against records with executed-document links. It also compares risks in Awaiting Approval against active DocuSign envelope IDs and checks actions whose reminder stage changed without a recorded notification timestamp.

A Complete Example

The supply chain manager submits a risk titled Single-source control module delays field repairs. The category is Supplier, and the business area is Parts and Procurement.

The original submission states that the company relies on one approved supplier for a specialized control module. A production interruption or transportation delay could prevent timely field repairs, extend customer equipment downtime, and create service-credit exposure.

The submission initially omits evidence for the stated safety-stock control. Airtable assigns the internal record and generates RISK-2026-0047, but Validation Result remains MISSING REQUIRED DATA. The record enters Validation Required, and no approval automation starts.

The owner adds the Drive link to the current inventory policy and creates two linked controls:

• Maintain four weeks of safety stock, owned by the inventory manager.
• Review supplier delivery performance monthly, owned by the supply chain manager.

The owner also creates action ACT-2026-0089 to qualify a secondary supplier by September 30, 2026. The action owner is the procurement lead, with the supply chain manager as escalation owner.

The owner selects inherent likelihood 4 and impact 4. Airtable calculates an inherent score of 16 and a High rating. After considering safety stock and supplier monitoring, the owner selects residual likelihood 3 and residual impact 3. Airtable calculates a residual score of 9 and a Moderate rating.

Compliance confirms the quarterly review cadence, the executive approver, and review version 2. Because the inherent rating is High, the organization’s policy requires the three-signer route even though residual risk is Moderate.

When Approval Requested is selected, the record enters the Approval Ready view. Zapier builds the sample automation key:

recEXAMPLE0047|V2

Zapier confirms that no existing Approval record uses that key. It creates or finds the folder RISK-2026-0047 - Single Source Control Module, generates the version 2 approval pack, and stores the returned Google document link in Airtable.

Zapier sends the three-signer DocuSign template to the risk owner, chief operating officer, and compliance manager in sequence. DocuSign returns the representative sample envelope ID:

11111111-2222-4333-8444-555555555555

Airtable changes the risk status to Awaiting Approval and the approval status to Sent. Each DocuSign status event updates the linked Approval record.

The risk owner signs first. The chief operating officer requests no changes and signs second. The compliance manager confirms the evidence link, rating calculation, action due date, and review cadence before signing last.

When DocuSign reports completed status, Zapier stores the executed file in the risk’s Executed folder. Airtable receives the Drive URL, sets Approval Status to Completed, changes the risk to Monitoring, records the review date, and calculates the next quarterly review date.

Thirty days before the secondary-supplier action is due, the scheduled reminder Zap emails the procurement lead. If the action remains incomplete five days after the due date, the supply chain manager receives an escalation. Completion requires an evidence link and completion date rather than a status change alone.

The final record contains the Risk ID, scores, owners, controls, action, approval version, DocuSign envelope ID, approval pack link, executed-document link, review date, next review date, reminder evidence, and automation log entries.

Implementation Cost

The following amounts are representative planning assumptions, not published vendor prices or verified client costs. Actual licensing depends on users, features, transaction volume, contract terms, and existing subscriptions.

A company already paying for some or all of these tools may have a lower incremental software cost. Existing subscriptions still require setup, governance, monitoring, and administrative labour.

Estimated Time and Cost Savings

The estimate uses conservative representative assumptions. Recovered time is additional capacity and does not automatically reduce payroll.

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

30 × 55 ÷ 60 = 27.5 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

30 × 20 ÷ 60 = 10 hours of routine handling

30 × 10% × 15 ÷ 60 = 0.75 hours of exception handling

10 + 0.75 + 4 = 14.75 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

27.5 – 14.75 = 12.75 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

12.75 × $65 = $828.75

Net estimated monthly value: Monthly labour value minus recurring tool costs

$828.75 – $175 = $653.75

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

$12,000 ÷ $653.75 = approximately 18.4 months

If internal participation cost is included in the investment basis, the payback period increases. If the company already owns sufficient tool capacity, handles more risk events, or spends more time reconciling documents, the period may decrease.

The recovered hours may represent:

• Additional compliance and management capacity
• Quicker preparation for leadership meetings
• Reduced overtime during reviews or audits
• Fewer repetitive administrative tasks
• Ability to handle more risks without proportional administration growth
• Lower dependency on one employee’s spreadsheet and inbox

Non-financial benefits include clearer ownership, fewer follow-ups, more consistent scoring, better document traceability, improved review evidence, faster identification of overdue actions, and a more reliable experience for risk owners and approvers.

Readers should replace the workflow volume, handling times, exception rate, maintenance effort, labour cost, software allocation, and implementation cost with their own figures.

Adding AI to the Automation

AI is optional and should be added only after required fields, scoring formulas, approvals, reminders, permissions, and failure recovery operate reliably.

Potential AI uses include summarizing long risk submissions, suggesting categories, identifying missing narrative elements, comparing similar descriptions, extracting obligations from unstructured documents, and creating management summaries.

AI should not calculate the final risk rating, accept exposure, approve a control, close a risk, determine a legal conclusion, or decide whether an action is complete. Those tasks are better handled through formulas, rules, evidence requirements, and human authorization.

The core automation already creates records, calculates scores, routes approvals, stores evidence, and sends reminders. AI adds value only when interpreting unstructured language.

The Recommended AI Enhancement

The recommended enhancement is an intake-quality assistant that reviews a draft risk statement and suggests a category, a clearer statement, and missing-information flags. It does not alter the human-selected score or send the record to approval.

• Trigger: A Draft risk has complete cause, event, and impact text, and the requester selects Request AI Review.
• AI input: Risk title, proposed category, cause, potential event, impact narrative, known controls, and permitted business area.
• System instruction: Act as a risk-writing assistant, not a decision maker.
• Expected output: Strict structured JSON containing suggestions and confidence.
• Validation: Category must match an allowed value, confidence must be between 0 and 1, and all required JSON properties must be present.
• Record update: Write results to separate Suggested fields, never to approved fields.
• Human review: The risk owner accepts, edits, or rejects each suggestion.
• Low confidence: Confidence below 0.75 creates no category recommendation and flags manual review.
• Prohibited data: Legal advice, privileged communications, credentials, personal data, detailed vulnerabilities, restricted customer information, and confidential contract text.
• Logging: Store prompt version, model identifier, timestamp, confidence, reviewer decision, and sanitized error status.
• Cost monitoring: Count requests and review the AI service invoice or usage report monthly.
• Failure behavior: Leave the record unchanged and return it to normal human review.

Reusable system instruction

You are an operational risk writing assistant. Your role is to improve clarity and identify missing information. You do not approve risks, determine final ratings, provide legal conclusions, or decide whether a risk is acceptable.

Use only the supplied information. Do not invent controls, incidents, financial amounts, legal requirements, probabilities, or business facts.

Return output that conforms exactly to the supplied JSON schema. Choose a suggested category only from the allowed category list. If the information is insufficient, use null for suggested_category and explain the missing information in missing_elements.

Treat all likelihood, impact, residual rating, approval, and closure decisions as human-controlled.

Reusable user prompt

Review the following draft operational risk.

Allowed categories:
Operational
Financial
Technology
Customer
Supplier
Legal & Compliance
People
Safety

Risk title:
{{RISK_TITLE}}

Proposed category:
{{PROPOSED_CATEGORY}}

Business area:
{{BUSINESS_AREA}}

Cause:
{{CAUSE}}

Potential event:
{{POTENTIAL_EVENT}}

Impact narrative:
{{IMPACT_NARRATIVE}}

Known controls:
{{KNOWN_CONTROLS}}

Tasks:
1. Determine whether the cause, event, and impact are distinguishable.
2. Suggest one allowed category only when supported by the text.
3. Draft a concise risk statement in this format: Because of [cause], [event] may occur, resulting in [impact].
4. Identify missing information without inventing facts.
5. Identify any ambiguous wording.
6. Return a confidence score from 0 to 1 for the suggested category.
7. Do not suggest likelihood, impact, residual scores, acceptance, approval, or closure.

Structured output schema

{
  "type": "object",
  "additionalProperties": false,
  "required": [
    "suggested_category",
    "category_confidence",
    "suggested_risk_statement",
    "missing_elements",
    "ambiguities",
    "requires_human_review"
  ],
  "properties": {
    "suggested_category": {
      "type": ["string", "null"],
      "enum": [
        "Operational",
        "Financial",
        "Technology",
        "Customer",
        "Supplier",
        "Legal & Compliance",
        "People",
        "Safety",
        null
      ]
    },
    "category_confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    },
    "suggested_risk_statement": {
      "type": "string",
      "maxLength": 1200
    },
    "missing_elements": {
      "type": "array",
      "items": {
        "type": "string",
        "maxLength": 250
      },
      "maxItems": 10
    },
    "ambiguities": {
      "type": "array",
      "items": {
        "type": "string",
        "maxLength": 250
      },
      "maxItems": 10
    },
    "requires_human_review": {
      "type": "boolean"
    }
  }
}

Configure the approved AI connector in Zapier to enforce structured output using the schema where supported. Map each response property to separate Airtable fields such as AI Suggested Category, AI Suggested Statement, AI Missing Elements, AI Confidence, AI Review Status, AI Prompt Version, and AI Run Date.

Add a filter after the AI response. Continue only when the response conforms to the schema and the category is either null or an allowed value. If confidence is below 0.75, clear the suggested category, retain the explanatory fields, and set AI Review Status to Low Confidence.

Test malformed output, prohibited data, empty inputs, low confidence, unavailable service, and inaccurate suggestions before activation. The fallback is the existing owner and compliance review process.

Benefits of the AI Enhancement

• Less time rewriting poorly structured risk statements
• More consistent separation of causes, events, and impacts
• Quicker identification of missing narrative elements
• More consistent category suggestions for reporting
• Faster reading of long or repetitive intake descriptions
• Better handling of unstructured text before formal review

These benefits are specific to language review. Clear ownership, automated scoring, document preparation, signatures, reminders, evidence storage, and status reporting come from the core rule-based automation rather than AI.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The AI estimate applies only to the 10 new or materially revised risk statements each month, not all 30 workflow events.

Expected AI-assisted time per eligible record:

3 minutes human review + 20% × 3 minutes correction + 5% × 8 minutes fallback = 4 minutes

Additional time recovered:

10 records × (8 – 4) minutes ÷ 60 = 0.67 hours per month

Additional labour value:

0.67 × $65 = approximately $43.55 per month

Net additional monthly value after the $12 AI allowance:

$43.55 – $12 = approximately $31.55 per month

In this scenario, the AI enhancement has modest direct financial value. Its stronger benefit is improved writing consistency and faster identification of incomplete submissions. It does not eliminate correction, service failures, or human review.

Testing Checklist

Use sample data and test identities before processing real risk information.

Ongoing Maintenance

The compliance manager is the primary business owner. The technical operations manager is the backup automation owner. Neither role should depend on undocumented personal knowledge.

Documentation should include data definitions, scoring rules, approval tiers, field mappings, template locations, connected identities, recovery procedures, current owners, and a dated change log.

When to Move to Dedicated Software

The Airtable, Zapier, DocuSign, and Google Drive implementation can remain appropriate while the process is understandable, supportable, and proportionate to risk. It should not be replaced solely because a dedicated platform exists.

Reassess the architecture when one or more of the following becomes material:

• Risk and control volume causes slow views, difficult navigation, or unreliable batch processing.
• The company requires advanced field-level or record-level permissions beyond the available configuration.
• Formal regulatory frameworks require mapped controls, tests, evidence, findings, and attestations.
• Multiple business units need separate registers with consolidated reporting.
• Complex approval routes produce excessive Zapier branching and maintenance.
• Exception rates increase because the process no longer fits standardized forms.
• Auditors require immutable history or formal control testing beyond the current records.
• Leadership needs advanced scenario analysis, quantitative risk modeling, or capital allocation.
• Mobile, offline, supplier portal, or customer-facing access becomes necessary.
• Integration with enterprise resource planning, identity, security, finance, or incident platforms becomes extensive.
• Connector changes or task volume create unacceptable reliability or operating cost.
• Security requirements demand dedicated key management, regional hosting controls, or more granular audit logs.
• The organization needs contractual vendor support for the complete risk-management application.

At that point, relevant categories may include governance, risk, and compliance platforms, enterprise risk-management systems, integrated audit tools, compliance-management platforms, or a governed custom application. The existing data model and workflow definitions can serve as migration requirements.

Implementation Checklist

• Confirm business requirements and risk-governance policy.
• Approve scoring definitions and rating bands.
• Select Airtable, Zapier, DocuSign, Google Drive, Google Docs, and notification tools.
• Create production and test accounts.
• Assign primary and backup system owners.
• Configure least-privilege permissions.
• Create shared inboxes and organization-controlled automation identities.
• Build Risks, Controls, Actions, Approvals, and Automation Log tables.
• Configure unique IDs, formulas, statuses, and linked relationships.
• Build the restricted intake form.
• Configure duplicate detection and incomplete-record handling.
• Create Google Drive folders and naming conventions.
• Create Google Docs approval templates.
• Create two-signer and three-signer DocuSign templates.
• Connect every tool through controlled OAuth accounts.
• Document all field mappings and returned identifiers.
• Build intake validation automation.
• Build approval-pack and envelope automation.
• Build DocuSign status synchronization.
• Build executed-document storage.
• Build action, renewal, and obligation reminders.
• Configure approval thresholds and human confirmation.
• Configure reminders, escalations, reassignment, decline, and void procedures.
• Create leadership, owner, exception, overdue, and automation-failure views.
• Apply privacy, retention, shared-link, credential, and audit controls.
• Test normal, exception, failure, retry, and unauthorized-access scenarios.
• Pilot with sample data and a limited risk group.
• Reconcile Airtable, Zapier, DocuSign, and Google Drive before launch.
• Document implementation cost and recurring software assumptions.
• Replace savings assumptions with actual workflow measurements.
• Add AI only after the core process is reliable.
• Keep scoring, acceptance, approval, exceptions, and closure human-controlled.
• Establish daily, weekly, monthly, quarterly, and annual maintenance tasks.
• Define measurable criteria for moving to dedicated risk-management software.