An operational workbook executes a repeatable process — generating outputs, tracking status, or automating communication. This standard defines the structure, documentation, and output rules that all DDL operational workbooks must follow.
| Analytical Workbook | Operational Workbook |
|---|---|
| Answers a question | Executes a process |
Decision test: Which failure hurts more? Wrong output → operational. Wrong conclusion → analytical.
All operational workbooks follow a 3-layer structure with unidirectional data flow.
| Layer | Purpose |
|---|---|
INPUT | Structured data entry. Validated. Never free-form. |
LOGIC | Process rules, formulas, status derivation. |
OUTPUT | Generated content ready for use or export. |
STAGING: For workbooks receiving external data requiring validation before it qualifies as INPUT. Raw imports, CSV dumps, or multi-source merges land in STAGING.
CONTROL: Parameter cells, run instructions, and configuration values that govern the process.
| Allowed in OUTPUT | Violation in OUTPUT |
|---|---|
=INPUT!A3 — value passthrough | =INPUT!D3 * 1.1 — arithmetic |
TEXT(), UPPER(), TRIM() — formatting | =IF(INPUT!C3>0,…) — conditional logic |
Display references are allowed. Anything that changes the underlying value belongs in LOGIC.
The INPUT tab is the only place operators enter data. Every input column must have data validation. No free-form text fields — use dropdown lists, date pickers, or number constraints everywhere possible.
INPUT columns must be named and typed. A column that accepts “any value“ is an uncontrolled input. Document every column in the CONTROL tab header with: column name, data type, allowed values, and what breaks if invalid data enters.
LOGIC reads exclusively from INPUT (or STAGING → INPUT). LOGIC never references OUTPUT. Every column in LOGIC derives a single value type — status, formatted output, flag, or calculation.
=IFS( INPUT[@Status]=“Exception“, “Exception“, INPUT[@SentDate]<>““, “Sent“, INPUT[@ReadyFlag]=“Yes“, “Ready“, TRUE, “Draft“ )
Exception: operator sets with required rationale.
=“Dear “ & InputTab[@Name] & “,“ & CHAR(10) & “Your commission for “ & InputTab[@Period] & “ is “ & TEXT(InputTab[@Amount], “$#,##0.00“) & “.“
Every formula in LOGIC that encodes a business rule must have a comment explaining the rule — not the syntax.
Every LOGIC formula that could produce a standard Excel error must be wrapped in IFERROR. The fallback must be a visible, human-readable string:
“DATA MISSING“ “REVIEW REQUIRED“ “ERROR — SEE INPUT“
Blank cells and zero values are not acceptable error fallbacks in operational workbooks — they hide failures that propagate silently to OUTPUT.
OUTPUT tabs contain generated content only. No manual edits. If generated content is wrong: fix INPUT or LOGIC → verify OUTPUT regenerates correctly → copy and send.
When time constraints make fix-and-regenerate infeasible, a manual correction to OUTPUT is allowed only when:
1. The correction is logged in the CONTROL tab exception log (record ID, field edited, original value, edited value, reason, date).
2. The record status is updated to Exception.
3. The root cause is fixed in INPUT or LOGIC before the next run.
Every record displays a current status reflecting the complete process lifecycle:
Backward transitions (e.g., Sent → Ready) are allowed but must be logged in the exception log with reason.
Generated outputs must be fully formed (no placeholders), validated against INPUT, and identifiable by Record ID. “Copy and send“ is the standard — not “copy, edit, then send.“
All values that govern process behavior reside on the CONTROL tab as named parameter cells. Naming: sel_<ParameterName>. Every parameter must have adjacent documentation explaining what it controls, valid values, and behavior when changed.
| Column | Purpose |
|---|---|
| Date | When the exception occurred |
| RecordID | Which record was affected |
| FieldEdited | Which field was manually changed |
| OriginalValue | What it was before the edit |
| EditedValue | What it was changed to |
| Reason | Why the exception was necessary |
| Operator | Who made the change |
Append-only. Entries are never deleted.
Every operational workbook must include a CONTROL tab header with: workbook name and version, process it supports, owner, date last updated, last verified date, and brief description of inputs, outputs, and how to run it.
Run instructions must be step-by-step and verified as current before every process cycle that includes a structural change. A stale Last Verified date on a recently changed process is a visible signal that documentation discipline has failed.
| Object | Convention | Example |
|---|---|---|
| Input table | tbl_Input<Process> | tbl_InputReview |
| Output table | tbl_Output<Process> | tbl_OutputEmails |
| Logic table | tbl_Logic<Process> | tbl_LogicStatus |
| Parameter cell | sel_<Name> | sel_ReviewPeriod |
| Record ID | <Process>-0000 | REVIEW-0001 |
| Layer | Tab Color |
|---|---|
| INPUT | Light Blue |
| LOGIC | Light Gray |
| OUTPUT | Light Green |
| CONTROL | Light Yellow |
| STAGING | Light Orange |
Status fields must use conditional formatting: Draft (gray), Ready (blue), Sent (green), Complete (dark green), Exception (red). Use formula-based CF rules for all status columns — not the built-in “highlight cells“ shortcut.