Promotion Accuracy Tooling — System Guide

Complete reference: every tab, every column, every formula, every control — and the reasoning behind each one.

1 · What this system does

This documentation describes a promotion accuracy system built as an interview demonstration for House of Dank. It takes a promotional request — a deal sheet — and validates it against product data, live inventory, signed vendor agreements, margin policy and a compliance rulebook before it is allowed to reach a customer-facing menu.

All data is fictional. Every product, brand, vendor, price and inventory figure in this system was invented for demonstration purposes. It contains no House of Dank data and is not a House of Dank system. The logic, however, is real and is documented here in full.

The system has three components, all of which share one dataset and one set of validation rules:

Component	Format	Purpose
Promotion Accuracy QA Tool	Google Sheet / Excel workbook	The validation engine. Sixteen automated checks per promotion, plus a leadership report.
Menu & Promotion Manager	Web application	The operational surface — catalogue, promotion builder, channel consistency, approvals, campaigns.
Discount Calculator	Single-purpose web page	Fast pricing answers against every guardrail.

The workbook and the web application implement the same sixteen checks independently — one in Excel formulas, one in JavaScript. Both were run against the same twenty sample promotions and produce identical results: seven approved, two held for review, eleven blocked, twenty-two errors, eight warnings, and $6,479 of calculated exposure. That agreement is the point: two independent implementations of the same rules reaching the same answer is evidence the rules are unambiguous.

2 · The operating context

Three systems carry a cannabis retailer's menu, and they are not equals.

System	Role	Consequence
Dutchie	E-commerce and point of sale. The system of record.	What the register actually rings. If a promotion is not built here, it does not exist.
Weedmaps	Third-party marketplace. A mirror.	Where customers browse before visiting. Drifts out of sync unless actively reconciled.
Leafly	Third-party marketplace. A mirror.	Same as above. Drift here is what a customer sees, and what they complain about.

A deal sheet is the request that starts everything: a submission from buying, merchandising or marketing asking for a product to be discounted, at a stated price, at named stores, over named dates. It arrives as a human document and is trusted by default in most retail operations. This system does not trust it.

Vendor co-op funding is the other half of the problem. Vendors frequently subsidise discounts on their own products, but that subsidy is governed by a signed agreement with two hard limits: a maximum discount percentage, and a validity window. Exceed either and the vendor does not reimburse — the retailer absorbs the difference, usually without noticing until reconciliation.

3 · End-to-end workflow

The process the system implements, from request to live menu:

Submission. A team submits a deal sheet in one standard format. Every field the validation needs is mandatory, so an incomplete request is rejected at the door rather than three days later.
Automatic validation. Sixteen checks run the moment a row is entered. No human triage step, and no opportunity for a request to sit unexamined.
Triage by status. Anything with a hard error is BLOCKED. Anything with warnings only is NEEDS REVIEW and requires a human decision. Clean requests are APPROVED.
Return with reason. Blocked requests go back to the submitter with a plain-English explanation and a named owner for the fix — not a rejection code.
Build. Approved promotions are built in Dutchie, then mirrored to Weedmaps and Leafly.
Parity confirmation. A promotion is only considered live when all three channels agree.
Report. A weekly accuracy report is generated from the validation data itself, so reporting is a by-product of the process rather than a separate task that can drift from reality.
Fix the source. The errors-by-check-type breakdown identifies which rule keeps failing, so the intake process is corrected rather than the same defect being re-caught every week.

4 · Two conventions that make everything else work

4.1 Offer-level pricing

Every price in the system describes the whole offer, not a single unit. A buy-one-get-one on a $44.99 cartridge is recorded with a regular price of $89.98 and a promotional price of $67.49, alongside a Units in Offer value of 2.

This is why a single set of formulas can validate percentage discounts, dollar discounts, fixed price points, BOGOs and multi-buy bundles without branching into separate logic for each. Every promotion type reduces to one comparable effective discount percentage. Separate logic per promotion type is where validation systems typically develop gaps.

4.2 The status model

Each of the sixteen checks returns one of four values:

Result	Meaning
PASS	The rule is satisfied.
WARN	Not a violation, but a risk requiring a human decision — tight inventory, a pending asset, a short build window.
FAIL	A hard violation. Blocks the build.
N/A	The rule does not apply — for example vendor checks on a house-funded promotion.

Those roll up into a single row status: any failure produces BLOCKED; warnings without failures produce NEEDS REVIEW; a clean row produces APPROVED. There is no partial-approval state, because a promotion is either safe to publish or it is not.

5 · Reference data — the definition of “correct”

Everything the engine does is a comparison against the Sample Data tab. Nothing else in the workbook contains an opinion; this tab holds all of them. In production these five tables would be a Dutchie product export, an inventory report, the signed vendor agreements, internal pricing policy, and the compliance rulebook.

5.1 SKU & Inventory Master — rows 8–31

Col	Field	What it is and why it is needed
A	SKU ID	The key every check joins on.
B	Product Name	Compared against the submitted name in check C3.
C	Brand	Compared against the vendor agreement's brand in check C8. Citing a valid agreement for the wrong brand is a common error.
D	Category	Drives the discount cap and margin floor lookups. Always taken from here, never from the deal sheet.
E	Unit Cost	Without this, margin cannot be calculated at all — only discount percentage, which says nothing about profitability.
F	Reg Price	The live system price. Check C4 exists purely to catch deal sheets that disagree with it.
G	On Hand	Units available. Drives the inventory coverage check.
H	Days in Stock	Ageing. 90+ days marks a clearance candidate.
I	Weekly Velocity	Baseline units sold per week. Combined with on-hand this produces weeks of supply, and with promo length it produces projected demand.
J	Weeks of Supply	Formula. On hand ÷ weekly velocity.
K	Inventory Signal	Formula. Classifies the SKU as aged, overstocked, low stock or healthy.

The two formulas on this tab:

=IFERROR(ROUND($G8/$I8,1),"")

=IF($G8="","",IF($H8>=90,"AGED "&$H8&" days - clearance candidate",IF($J8>=12,"OVERSTOCK - "&ROUND($J8,0)&" wks supply",IF($J8<=2,"LOW STOCK - "&ROUND($J8,1)&" wks supply","Healthy - "&ROUND($J8,0)&" wks supply"))))

The signal formula tests in a deliberate order: ageing first, then overstock, then low stock. A product sitting for 96 days is reported as aged even though it is also overstocked, because age is the more actionable fact — it is the reason to run a promotion at all.

How to explain it: “This column is why I know what to promote. Promotional effort should go to inventory that needs help — aged and overstocked stock — not to products that already sell. And the low-stock flag is the opposite signal: do not advertise something you cannot fulfil.”

5.2 The other four tables

Table	Rows	Contents	Feeds
Store Master	35–42	Seven locations plus “ALL”, with the channels each publishes to.	Check C12
Vendor Agreements	47–54	Agreement ID, brand, funding %, maximum discount, valid-from and valid-to.	Checks C8, C9 and the funding-at-risk calculation
Category Rules	59–66	Maximum discount and minimum gross margin per category.	Checks C6, C7 and the compliant floor price
Compliance Rules	70–77	Eight written policy rules, each naming the check that enforces it.	Checks C10, C14, C15, C16

Why the compliance table names its enforcing check. A rulebook nobody enforces is decoration. Listing the check beside each rule makes the mapping auditable — anyone can confirm that every written rule is actually implemented, and that no check exists without a policy behind it.

6 · Deal Sheet Intake — every column

The submission format. Columns A–AA are filled in by the submitting team; columns AB–AC are returned automatically by the validation engine, so the submitter sees the verdict and the reason without asking anyone. Row shading mirrors the status.

Col	Field	Purpose
A	Deal ID	Unique identifier for the submission. Used to trace the promo from deal sheet through build, QA and reporting.
B	Submitted By	Team and person. Drives the accuracy-by-team table in the Weekly Accuracy Report.
C	Date Submitted	Used two ways: to measure build lead time (Check C10) and to decide which of two conflicting promos was first (Check C11).
D	Promo Name	Guest-facing name. Scanned by Check C16 for prohibited wording such as 'free', 'medical', 'cure' or 'guarantee'.
E	Promo Type	% Off / $ Off / Price Point / BOGO / Bundle. Determines how the expected promo price is recalculated in Check C5.
F	Discount Value	% Off, BOGO, Bundle -> decimal (0.25 = 25%). $ Off -> dollars off the offer. Price Point -> the target price.
G	Units in Offer	How many units the offer covers. 1 for a single item, 2 for BOGO, 4 for a 4-pack bundle. This is what lets one formula validate every promo type.
H	Applies To	SKU, Brand or Category level targeting.
I	SKU ID	Looked up against the SKU / Inventory Master. Drives Checks C2, C3, C4, C7 and C13.
J	Product Name (as submitted)	Compared character-for-character to the SKU master in Check C3. This is where copy-paste errors surface.
K	Category (as submitted)	Compared to the SKU master. Note the caps in C6/C7 always use the SYSTEM category, never this one, so a mistyped category cannot dodge a cap.
L	Brand (as submitted)	Compared to the SKU master and to the vendor agreement brand in Check C8.
M	Store Code(s)	Comma-separated. 'ALL' means every location. Every code is validated against the Store Master in Check C12.
N	Start Date	Validated in C10 (lead time, ordering) and C11 (overlap with an earlier promo on the same SKU).
O	End Date	Required. An open-ended promo is a compliance violation under rule CR-05 and fails Check C10.
P	Reg Price (as submitted)	The offer-level regular price. Compared to system reg price x units in Check C4.
Q	Promo Price (as submitted)	The offer-level promo price. Recalculated independently in Check C5 and stress-tested against every cap and floor.
R	Vendor Funded	Yes triggers the vendor agreement checks C8 and C9. No skips them.
S	Vendor Agreement ID	Must exist, must belong to the SKU's brand, and must cover the entire promo window.
T	Stacking Allowed	Defaults to No under rule CR-03. Yes requires the words 'stacking approved' in the Notes column or Check C16 fails.
U	Limit Per Customer	Required under rule CR-02 and capped at 10 units.
V	Dutchie Built	Dutchie is the system of record. Not built = hard fail on Check C14.
W	Weedmaps Updated	Third-party menu mirror. Pending = warning on Check C14.
X	Leafly Updated	Third-party menu mirror. Pending = warning on Check C14.
Y	Menu Image Updated	Pending = warning on Check C15.
Z	Compliance Copy Approved	Not approved = hard fail on Check C15. Nothing goes live without approved copy.
AA	Notes	Free text. Also scanned for the phrase 'stacking approved' by Check C16.
AB	QA STATUS auto	Returned automatically from the Validation Engine.
AC	ISSUES FOUND auto	Plain-English list of every failed and warned check, returned automatically.

The three “as submitted” columns. Product name, category and brand are captured exactly as the submitter typed them and deliberately not looked up. That is the entire point — they exist so they can be compared against the master record. If the form auto-filled them, check C3 would have nothing to catch and copy-paste errors would pass silently.

How to explain it: “One format, every team. That consistency is not bureaucracy — it is what makes automated validation possible at all. You cannot write a rule against a free-form email.”

7 · The Validation Engine

Twenty rows, one per promotion. Row 9 corresponds to Intake row 7, and that two-row offset holds all the way down — every formula on this tab reaches back into Intake at ROW()−2.

The tab is organised into four bands, left to right, in order of decreasing urgency:

Band	Columns	Contents
Submission	A–F	Deal ID, name, SKU, stores, dates. Columns A and B are frozen so they stay visible when scrolling right.
QA Result	G–L	Status, error and warning counts, plain-English issue detail, assigned owner, inventory signal.
The sixteen checks	M–AB	One column per rule. Each header carries a hover note describing the rule it enforces.
Working	AC–BA	Every intermediate calculation and every value resolved from reference data. This is the audit trail.

Why the working is visible rather than hidden. A validation tool that produces a verdict without showing its reasoning is impossible to trust or to debug. Anyone challenged on a number can scroll right and see precisely how it was derived.

7.1 The KPI strip (rows 4–5)

Six live figures across the top: deals submitted, approved first pass, needs review, blocked, first-pass accuracy, and exposure prevented. All six are formulas — nothing on this tab is typed by hand.

='Deal Sheet Intake'!A7

The KPI cells themselves use straightforward counts, for example first-pass accuracy:

=IFERROR(COUNTIF($G$9:$G$28,"APPROVED")/COUNTA($A$9:$A$28),0)

7.2 The calculation columns (AC–AL)

These run before the checks conceptually — every check reads from them.

AC Expected Reg (Offer)

System regular price × units in offer.

=IFERROR(AQ9*'Deal Sheet Intake'!G7,"")

Normalises every promotion to the level of the whole offer. A BOGO on a $44.99 cartridge has an offer-level regular price of $89.98, not $44.99. Without this normalisation, BOGO and single-item promotions would need separate validation logic.

AD Expected Promo Price

Recalculated from the promotion type and discount value.

=IFERROR(ROUND(IF('Deal Sheet Intake'!E7="$ Off",AC9-'Deal Sheet Intake'!F7,IF('Deal Sheet Intake'!E7="Price Point",'Deal Sheet Intake'!F7,AC9*(1-'Deal Sheet Intake'!F7))),2),"")

Branches on type: $ Off subtracts the value from the offer regular price; Price Point takes the value as the price directly; everything else (% Off, BOGO, Bundle) multiplies by (1 − discount). Rounded to two decimals so it can be compared to a real currency value.

AE Submitted Promo Price

The price as entered on the deal sheet.

='Deal Sheet Intake'!Q7

Held separately from the recalculated value so the two can be compared. The engine never overwrites what was submitted — it reports the difference.

AF Effective Discount %

1 − submitted price ÷ expected regular price.

=IFERROR(1-AE9/AC9,"")

The single number that makes every promotion type comparable. A $5-off, a 25%-off and a BOGO all reduce to one percentage, which is what the category and vendor caps are expressed in. Note it uses the submitted price, so a mispriced promotion is measured on what would actually ring, not on what was intended.

AG Promo Gross Margin %

(submitted price − unit cost × units) ÷ submitted price.

=IFERROR((AE9-AP9*'Deal Sheet Intake'!G7)/AE9,"")

Gross margin at the promotional price. Drives check C7.

AH Compliant Floor Price

The highest of three separate floors.

=IFERROR(MAX(AC9*(1-AU9),(AP9*'Deal Sheet Intake'!G7)/(1-AV9),IF(AND('Deal Sheet Intake'!R7="Yes",ISNUMBER(AX9)),AC9*(1-AX9),0)),"")

Takes MAX of the category-cap price, the margin-floor price and the vendor-cap price. Each represents a different constraint, and the binding one is whichever is highest — the lowest price that satisfies all three simultaneously. This single figure answers the question buyers actually ask: how low can this legally and profitably go?

AI Promo Days

Inclusive day count of the promotional window.

=IF(OR('Deal Sheet Intake'!N7="",'Deal Sheet Intake'!O7=""),"",'Deal Sheet Intake'!O7-'Deal Sheet Intake'!N7+1)

End date minus start date plus one, so a single-day promotion counts as one day rather than zero. Returns blank if either date is missing, which cascades cleanly into the checks that depend on it.

AJ Projected Unit Sales

weekly velocity ÷ 7 × promo days × 1.5.

=IF(OR(AI9="",AT9=""),"",ROUND(AT9/7*AI9*1.5,0))

Prorates the baseline weekly sales rate across the promotional window and applies a 1.5 promotional lift factor. Used by both the inventory check and the financial exposure calculations.

AK Price Variance Exposure

Per-unit gap between the compliant price and the submitted price, multiplied by projected units.

=IFERROR(ROUND(ABS(MAX(AH9,AD9)-AE9)/'Deal Sheet Intake'!G7*AJ9,0),0)

Uses ABS deliberately, so it captures error in both directions. A promotion priced below what policy permits gives away margin; one priced above what was advertised overcharges guests. Both are failures of accuracy and both belong in the number.

AL Vendor Funding at Risk

Discount value × projected units × the cited agreement's funding percentage.

=IFERROR(IF(AND('Deal Sheet Intake'!R7="Yes",T9="FAIL",ISNUMBER(BA9)),ROUND((AC9-AE9)/'Deal Sheet Intake'!G7*AJ9*BA9,0),0),0)

Calculated only when check C8 has failed and the promotion is marked vendor funded. It represents co-op reimbursement the deal sheet assumed but the signed agreement does not actually cover — money the business would otherwise absorb without realising.

7.3 The sixteen checks

Each check is documented below with its purpose, the exact rule, the formula as it appears in the workbook, and the reasoning behind how it is written. Every formula shown is the real one, extracted directly from the delivered file.

C1 Required Fields col M

Confirms every mandatory field on the deal sheet is populated before any other check is attempted.

Rule	Fails if any of these are blank: promotion name, promotion type, discount value, units in offer, SKU, store list, start date, end date, regular price, promo price, or per-customer limit.

=IF('Deal Sheet Intake'!A7="","",IF(('Deal Sheet Intake'!D7="")+('Deal Sheet Intake'!E7="")+('Deal Sheet Intake'!F7="")+('Deal Sheet Intake'!G7="")+('Deal Sheet Intake'!I7="")+('Deal Sheet Intake'!M7="")+('Deal Sheet Intake'!N7="")+('Deal Sheet Intake'!O7="")+('Deal Sheet Intake'!P7="")+('Deal Sheet Intake'!Q7="")+('Deal Sheet Intake'!U7="")>0,"FAIL","PASS"))

Why it is written this way. The blank-test is written as a sum of comparisons — (A="")+(B="")+…>0 — rather than nested ORs. In Excel a TRUE evaluates to 1, so adding them gives a count of missing fields, and any total above zero means something is missing. It reads more clearly than an eleven-deep OR and is easier to extend when a new mandatory field is added.

C2 SKU Valid col N

Confirms the SKU exists in the catalogue master.

Rule	Fails when the submitted SKU has no match in the SKU / Inventory Master.

=IF('Deal Sheet Intake'!A7="","",IF(ISNUMBER(MATCH('Deal Sheet Intake'!I7,'Sample Data'!$A$8:$A$31,0)),"PASS","FAIL"))

Why it is written this way. Uses ISNUMBER(MATCH(...,0)) rather than a lookup. MATCH with a third argument of 0 forces an exact match and returns a row number if found, or an error if not. Wrapping it in ISNUMBER converts that into a clean TRUE/FALSE without needing IFERROR. Every subsequent check depends on this one, which is why checks C3 onward return N/A when it fails — reporting eleven consequential failures for one bad SKU would bury the actual cause.

C3 Product / Brand / Category Match col O

Compares the product name, brand and category as typed on the deal sheet against the catalogue master.

Rule	Fails when any of the three differ from the master record for that SKU.

=IF(N9<>"PASS","N/A",IF(AND(TRIM(LOWER('Deal Sheet Intake'!J7))=TRIM(LOWER(AM9)),TRIM(LOWER('Deal Sheet Intake'!L7))=TRIM(LOWER(AN9)),TRIM(LOWER('Deal Sheet Intake'!K7))=TRIM(LOWER(AO9))),"PASS","FAIL"))

Why it is written this way. Both sides are wrapped in TRIM(LOWER(...)) so that stray spaces and capitalisation do not produce false failures — the check is looking for a genuinely different product, not a different way of typing the same one. This is the check that catches a row copy-pasted from the line above and only partially edited.

C4 Reg Price Match col P

Confirms the regular price on the deal sheet matches the live system price.

Rule	Fails when the submitted regular price differs from system price × units in offer by more than one cent.

=IF(N9<>"PASS","N/A",IF(ABS('Deal Sheet Intake'!P7-AC9)<=0.01,"PASS","FAIL"))

Why it is written this way. The comparison is ABS(submitted − expected) <= 0.01 rather than a direct equality test. Currency held as a floating-point number can differ in the last decimal place even when two values are notionally identical, so an exact = would produce spurious failures. A one-cent tolerance is the smallest meaningful unit of currency, so nothing real is missed.

C5 Promo Price Math col Q

Independently recalculates the promotional price from the stated discount and compares it to what was submitted.

Rule	Fails when the submitted promo price differs from the recalculated price by more than roughly one cent.

=IF(OR(N9<>"PASS",AD9=""),"N/A",IF(ABS(AE9-AD9)<=0.011,"PASS","FAIL"))

Why it is written this way. This is the check that most justifies the tool. It never trusts the arithmetic on the deal sheet; it redoes it from the regular price and the discount, then compares. The tolerance is 0.011 rather than 0.01 to absorb the half-cent that appears when a price such as $89.98 × 0.75 lands on $67.485 and must be rounded.

C6 Category Discount Cap col R

Confirms the effective discount is within the maximum permitted for that product category.

Rule	Fails above the cap. Warns within one percentage point of it.

=IF(OR(AF9="",AU9=""),"N/A",IF(AF9>AU9+0.002,"FAIL",IF(AF9>=AU9-0.01,"WARN","PASS")))

Why it is written this way. The cap is looked up using the system category from the catalogue master, never the category typed on the deal sheet. If it used the submitted value, someone could type "Flower" on a concentrate and inherit a more generous cap. Deriving the rule from the system of record rather than from user input is the single most important design decision in the engine.

C7 Margin Floor col S

Confirms gross margin at the promotional price clears the category minimum.

Rule	Fails below the floor. Warns within three percentage points of it.

=IF(OR(AG9="",AV9=""),"N/A",IF(AG9<AV9-0.002,"FAIL",IF(AG9<=AV9+0.03,"WARN","PASS")))

Why it is written this way. Margin is calculated as (promo price − unit cost × units) ÷ promo price. Without unit cost in the reference data this check is impossible, which is why cost is carried in the catalogue master. A promotion can sit comfortably inside its discount cap and still be unprofitable — a 30% discount on a product with a thin cost basis is a very different proposition to 30% on one with a wide one.

C8 Vendor Agreement Coverage col T

Confirms the cited vendor agreement exists, belongs to the product's brand, and covers the entire promotional window.

Rule	Fails if the agreement is missing, unknown, registered to a different brand, or if the promotion starts before the agreement begins or ends after it expires. Returns N/A when the promotion is not vendor funded.

=IF('Deal Sheet Intake'!R7<>"Yes","N/A",IF('Deal Sheet Intake'!S7="","FAIL",IF(ISNUMBER(MATCH('Deal Sheet Intake'!S7,'Sample Data'!$A$47:$A$54,0))=FALSE,"FAIL",IF(TRIM(LOWER(AW9))<>TRIM(LOWER(AN9)),"FAIL",IF(OR('Deal Sheet Intake'!N7<AY9,'Deal Sheet Intake'!O7>AZ9),"FAIL","PASS")))))

Why it is written this way. Three separate conditions are tested in sequence inside nested IFs, so the first genuine problem is the one reported. The brand comparison matters as much as the dates: citing a valid agreement belonging to the wrong vendor is a common and expensive error, because the co-op claim is then made against a vendor who never agreed to fund that product.

C9 Vendor Discount Cap col U

Confirms the discount is within the ceiling set by the vendor agreement.

Rule	Fails above the contractual maximum. Returns N/A when the promotion is not vendor funded or no valid agreement resolves.

=IF('Deal Sheet Intake'!R7<>"Yes","N/A",IF(OR(AX9="",AF9=""),"N/A",IF(AF9>AX9+0.002,"FAIL","PASS")))

Why it is written this way. A small tolerance of 0.002 is applied. Because prices are rounded to the cent, a promotion priced to exactly the cap can compute to a fraction of a percentage point above it — 25.007% against a 25% ceiling — which is a rounding artefact, not a breach. Without the tolerance, correctly-priced promotions would fail.

C10 Date Logic col V

Validates the promotional window and the time available to build it.

Rule	Fails when either date is missing, when the end date precedes the start date, or when the window exceeds 45 days. Warns when there are fewer than three days between submission and start.

=IF('Deal Sheet Intake'!A7="","",IF(OR('Deal Sheet Intake'!N7="",'Deal Sheet Intake'!O7=""),"FAIL",IF('Deal Sheet Intake'!O7<'Deal Sheet Intake'!N7,"FAIL",IF(AI9>45,"FAIL",IF('Deal Sheet Intake'!N7-'Deal Sheet Intake'!C7<3,"WARN","PASS")))))

Why it is written this way. The lead-time warning is measured from the submission date, not from today. Measuring from today would make every historical record appear urgent the moment it aged. An absent end date is treated as a hard failure rather than an omission, because an open-ended promotion has no defined cost.

C11 Promo Conflict col W

Detects a second promotion running on the same product, in overlapping stores, over overlapping dates.

Rule	Fails when an earlier-submitted promotion overlaps on SKU, dates and stores.

=IF('Deal Sheet Intake'!A7="","",IF(SUMPRODUCT(('Deal Sheet Intake'!$I$7:$I$26='Deal Sheet Intake'!I7)*('Deal Sheet Intake'!$A$7:$A$26<>'Deal Sheet Intake'!A7)*('Deal Sheet Intake'!$N$7:$N$26<='Deal Sheet Intake'!O7)*('Deal Sheet Intake'!$O$7:$O$26>='Deal Sheet Intake'!N7)*('Deal Sheet Intake'!$C$7:$C$26<'Deal Sheet Intake'!C7)*((('Deal Sheet Intake'!$M$7:$M$26='Deal Sheet Intake'!M7)+(LEFT('Deal Sheet Intake'!$M$7:$M$26,3)="ALL")+(LEFT('Deal Sheet Intake'!M7,3)="ALL"))>0))>0,"FAIL","PASS"))

Why it is written this way. Written as a single SUMPRODUCT multiplying five conditions together. Each condition produces an array of TRUE/FALSE across all rows; multiplying them keeps only rows where every condition holds, and summing counts them. Critically, the fifth condition requires the other promotion to have been submitted earlier, so only the later submission is flagged. Without it both promotions would fail and neither could proceed. Store overlap treats "ALL" on either side as matching everything.

C12 Store Codes col X

Confirms every store code in the list exists in the store master.

Rule	Fails when any code in the comma-separated list is unknown, or when more than four codes are supplied.

=IF('Deal Sheet Intake'!M7="","FAIL",IF(AND(LEN('Deal Sheet Intake'!M7)-LEN(SUBSTITUTE('Deal Sheet Intake'!M7,",",""))+1<=4,IF(LEN('Deal Sheet Intake'!M7)-LEN(SUBSTITUTE('Deal Sheet Intake'!M7,",",""))+1>=1,ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE('Deal Sheet Intake'!M7,",",REPT(" ",200)),1,200)),'Sample Data'!$A$35:$A$42,0)),TRUE),IF(LEN('Deal Sheet Intake'!M7)-LEN(SUBSTITUTE('Deal Sheet Intake'!M7,",",""))+1>=2,ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE('Deal Sheet Intake'!M7,",",REPT(" ",200)),201,200)),'Sample Data'!$A$35:$A$42,0)),TRUE),IF(LEN('Deal Sheet Intake'!M7)-LEN(SUBSTITUTE('Deal Sheet Intake'!M7,",",""))+1>=3,ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE('Deal Sheet Intake'!M7,",",REPT(" ",200)),401,200)),'Sample Data'!$A$35:$A$42,0)),TRUE),IF(LEN('Deal Sheet Intake'!M7)-LEN(SUBSTITUTE('Deal Sheet Intake'!M7,",",""))+1>=4,ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE('Deal Sheet Intake'!M7,",",REPT(" ",200)),601,200)),'Sample Data'!$A$35:$A$42,0)),TRUE)),"PASS","FAIL"))

Why it is written this way. The list is split by replacing each comma with 200 spaces, then reading fixed 200-character windows and trimming — a standard technique for splitting text without a dedicated split function. Each token is then matched exactly against the store master. An earlier version searched for each master code as a substring, which was shorter but would have accepted a code that merely contained another valid code. Exact token matching removes that class of error entirely.

C13 Inventory Coverage col Y

Compares units on hand against the demand the promotion is likely to generate.

Rule	Fails below 50% coverage. Warns below 100%.

=IF(OR(AI9="",AR9="",AT9=""),"N/A",IF(AR9/(AT9/7*AI9*1.5)<0.5,"FAIL",IF(AR9/(AT9/7*AI9*1.5)<1,"WARN","PASS")))

Why it is written this way. Projected demand is weekly velocity ÷ 7 × promotional days × 1.5. The 1.5 is a promotional lift assumption — a discounted product sells faster than its baseline rate. It is deliberately a single visible constant rather than a hidden model, so it can be argued about and tuned against real sell-through data.

C14 Channel Parity col Z

Confirms the promotion is live and consistent across all three menu channels.

Rule	Fails if it is not built in Dutchie. Warns if Weedmaps or Leafly is still pending.

=IF('Deal Sheet Intake'!A7="","",IF('Deal Sheet Intake'!V7<>"Yes","FAIL",IF(OR('Deal Sheet Intake'!W7<>"Yes",'Deal Sheet Intake'!X7<>"Yes"),"WARN","PASS")))

Why it is written this way. The asymmetry is deliberate. Dutchie is the system of record — if it is not built there, the promotion does not exist and nothing else matters. Weedmaps and Leafly are mirrors; a lag is a real problem but a recoverable one, so it warns rather than blocks.

C15 Menu Asset & Copy col AA

Confirms the creative and legal prerequisites are met.

Rule	Fails when compliance copy is not approved. Warns when the menu image is still pending.

=IF('Deal Sheet Intake'!A7="","",IF('Deal Sheet Intake'!Z7<>"Yes","FAIL",IF('Deal Sheet Intake'!Y7<>"Yes","WARN","PASS")))

Why it is written this way. Approved copy is a regulatory gate and therefore blocking. A missing image degrades the listing but does not create legal exposure, so it warns. Separating the two prevents a cosmetic gap from blocking a compliant promotion.

C16 Compliance Rules col AB

Enforces the promotion policy rulebook.

Rule	Fails on prohibited wording in the promotion name, on a missing or non-numeric purchase limit, on a limit above 10, or when stacking is enabled without written approval in the notes.

=IF('Deal Sheet Intake'!A7="","",IF(OR(ISNUMBER(SEARCH("free",'Deal Sheet Intake'!D7)),ISNUMBER(SEARCH("cure",'Deal Sheet Intake'!D7)),ISNUMBER(SEARCH("medical",'Deal Sheet Intake'!D7)),ISNUMBER(SEARCH("guarantee",'Deal Sheet Intake'!D7))),"FAIL",IF(OR('Deal Sheet Intake'!U7="",NOT(ISNUMBER('Deal Sheet Intake'!U7)),'Deal Sheet Intake'!U7>10),"FAIL",IF(AND('Deal Sheet Intake'!T7="Yes",NOT(ISNUMBER(SEARCH("stacking approved",'Deal Sheet Intake'!AA7)))),"FAIL","PASS"))))

Why it is written this way. Prohibited words are detected with ISNUMBER(SEARCH(word, name)). SEARCH returns an error when the term is absent, and ISNUMBER converts that to FALSE without propagating the error — so no IFERROR wrapper is needed. Stacking requires the literal phrase "stacking approved" in the notes field, which forces a deliberate, auditable act rather than an unexplained toggle.

7.4 The roll-up columns (G–L)

G STATUS

BLOCKED if any check failed; NEEDS REVIEW if warnings only; APPROVED if entirely clean.

H Errors

COUNTIF of "FAIL" across the sixteen check columns.

I Warnings

COUNTIF of "WARN" across the sixteen check columns.

J Issue Detail

Plain-English description of every failure and warning, assembled by concatenating sixteen conditional strings. This is the text that goes back to the submitting team verbatim — the engine explains itself rather than emitting a code to be looked up.

K Owner / Next Action

Routes the fix by failure type: vendor problems to Buying, pricing problems to Marketing Operations and Pricing, supply problems to Inventory. A defect without a named owner does not get fixed.

L Inventory Signal

Pulled from the catalogue master. Flags the SKU as aged, overstocked, low stock or healthy, so the reviewer can see whether the promotion is even aimed at the right product.

Status and the two counts:

=IF('Deal Sheet Intake'!A7="","",IF(H9>0,"BLOCKED",IF(I9>0,"NEEDS REVIEW","APPROVED")))

=COUNTIF($M9:$AB9,"FAIL")

The Issue Detail column concatenates sixteen conditional strings with &. It deliberately avoids TEXTJOIN, which is unavailable in older Excel versions and requires an _xlfn. prefix when written programmatically. Plain concatenation works everywhere, in every version, in Google Sheets and in Excel alike — and it is trivially readable by whoever maintains it next.

How to explain it: “I never eyeball a deal sheet. Sixteen checks run on every row, and the ones that fail tell me exactly what is wrong and who owns the fix. The plain-English column is what I paste straight back to the submitter — they get a reason, not a rejection code.”

8 · The Weekly Accuracy Report

Seven sections, every figure a live formula reading from the Validation Engine. Nothing is typed, so the report cannot drift from the data it describes — which is the whole reason it exists in the same file rather than being rebuilt by hand each week.

§	Section	What it contains
1	Executive Summary	Three sentences assembled from formulas — volume, first-pass accuracy, exposure prevented, and the dominant failure mode.
2	Key Metrics	Ten metrics against targets, with automatic ON TARGET / BELOW TARGET flags.
3	Errors by Check Type	Per-check error and warning counts with a priority rating. The most operationally useful table in the workbook.
4	Financial Exposure Prevented	The two dollar figures, with the calculation method written beside each.
5	Accuracy by Submitting Team	Volume, clean rate and an error-type breakdown per team.
6	Root Causes & Actions	Four specific process changes — not observations.
7	Appendix	Every deal, its status and its issues.

The executive summary is built by concatenating text with TEXT()-formatted formula results:

="Of "&TEXT(COUNTA('Validation Engine'!$A$9:$A$28),"0")&" promotions submitted this week, "&TEXT(COUNTIF('Validation Engine'!$G$9:$G$28,"APPROVED"),"0")&" cleared all sixteen automated checks on the first pass — "&TEXT(IFERROR(COUNTIF('Validation Engine'!$G$9:$G$28,"APPROVED")/COUNTA('Validation Engine'!$A$9:$A$28),0),"0.0%")&" first-pass accuracy. "&TEXT(COUNTIF('Validation Engine'!$G$9:$G$28,"BLOCKED"),"0")&" were blocked before they could reach a live menu and "&TEXT(COUNTIF('Validation Engine'!$G$9:$G$28,"NEEDS REVIEW"),"0")&" are held pending a human decision." …

Section 3 counts each check column independently, for example errors on check C8:

=COUNTIF('Validation Engine'!$T$9:$T$28,"FAIL")

Section 5 uses COUNTIFS across two sheets simultaneously — matching the submitting team on Intake against the status on the Engine. This works because both ranges are the same height and aligned row for row, which is exactly why the two-row offset is held consistently.

=COUNTIFS('Deal Sheet Intake'!$B$7:$B$26,$A52,'Validation Engine'!$G$9:$G$28,"APPROVED")

How to explain it: “This is what leadership gets on Friday. It writes itself off the engine, so the numbers are never hand-typed and never stale. And section three is the one I actually act on — it tells me which rule keeps failing, so I fix the intake instead of re-catching the same error every week.”

9 · The web application

Seven sections in the left navigation. The application re-implements the same sixteen checks in JavaScript and reads the same dataset, which is why its figures match the workbook exactly.

9.1 Overview

Six live tiles, a “needs attention today” table listing every promotion that cannot go live as submitted with its first failure and assigned owner, and an inventory watch-list of aged, overstocked and low-stock SKUs with a recommended action for each.

9.2 Product Catalog

All twenty-four SKUs with search and two filters (category, inventory signal). The store selector in the header changes what the table shows: store-specific price overrides appear in bold, products not carried at that location show as “not carried”, and a badge explains the override.

Control	Effect
Search box	Matches product name, brand or SKU as you type.
Category filter	Narrows to one category.
Inventory signal filter	Low stock / aged & overstocked / healthy.
Store selector (header)	Applies that location's price and availability overrides.
Promo tag column	Shows any non-blocked promotion currently attached to the SKU.

9.3 Promotion Builder

The core screen. A full promotion form on the left; a live price preview and three guardrail meters on the right; Test Mode below.

Control	What it does
Promotion name	Scanned by check C16 for prohibited wording.
Product	Selecting a SKU pulls cost, price, category, inventory and the brand's agreement.
Promotion type	Changes how the discount value is interpreted; the field label and helper text update accordingly.
Units in offer	1 for single item, 2 for BOGO, 4 for a four-pack. Drives offer-level pricing.
Discount value	Decimal for % Off / BOGO / Bundle; dollars for $ Off; target price for Price Point.
Locations	“All locations” disables the individual store pills; selecting any store clears “All”.
Dates	Drive promo length, lead time, conflict detection and projected demand.
Vendor funded / agreement	Enables checks C8 and C9 and the vendor guardrail meter.
Limit / stacking	Enforced by check C16.
Five status checkboxes	Dutchie, Weedmaps, Leafly, menu image, compliance copy — drive checks C14 and C15.

The live preview recalculates on every keystroke and shows the guest price, the struck-through regular price, the saving, effective discount, gross margin, the lowest compliant price, promo length, projected units and inventory on hand.

The three guardrail meters each draw a bar against a fixed marker representing the limit. Green inside, amber close, red over. The margin meter is inverted — for margin, higher is safer.

Button	Behaviour
Run Test Mode	Executes all sixteen checks and lists each with a plain-English result. Once run, it re-runs automatically on every subsequent edit.
Publish promotion	Disabled whenever errors > 0. Label changes to “Publish blocked” or “Publish with warnings”. Publishing is simulated — nothing is sent.
Break it on purpose	Sets the discount to 45% and renames the promotion so it contains a prohibited word, producing three errors at once. Click again to restore.

How to explain it: “The preview is the part buyers care about, and the meters are the part finance cares about. Test Mode is the gate — publish stays disabled until it comes back clean. It will not let me ship a promotion that breaks a vendor's contract.”

9.4 Third-Party Menu Consistency

Two tables. The first lists only the discrepancies — SKU, channel, what is wrong, and a Push from Dutchie button that copies the authoritative values over the drifted channel and removes the issue. The second is the full three-channel comparison, where any cell disagreeing with Dutchie is highlighted.

Seven discrepancies are seeded across five types: a stale price, a missing listing, an expired promotional tag still displaying, a miscategorised product, and a missing menu image.

9.5 QA & Approvals

A five-lane board — Submitted, In QA, Changes Requested, Approved, Live. Lane assignment is derived from the validation result, not set manually: a blocked promotion sits in Changes Requested, a warning sits in In QA, and a clean promotion is Approved or Live depending on channel completion. Clicking any card opens its full sixteen-point record with the owner and exposure prevented.

The board reflects validation; it never overrides it. There is no way to drag a failing promotion into Approved, because the lane is computed rather than stored.

9.6 Campaign Preview

An editable SMS field with a live phone mock-up, character and segment counting (160 characters for one segment, 153 per segment beyond that), and a five-point compliance check that re-runs as you type: prohibited wording, opt-out language, a stated purchase limit, offer details present, and message length.

The email mock-up is assembled only from promotions that passed QA — which is the structural point: a blocked promotion cannot reach a campaign, so the price in the email is guaranteed to match the register.

9.7 Workflow

The Monday.com board design: six groups, twenty-eight columns, six automations, eight saved views and a leadership dashboard. Presented as a build specification rather than a live instance, and labelled as such at the top of the page.

The columns worth knowing: Promo Window as a timeline column is how overlapping promotions become visible rather than inferred; Agreement Expiry surfaces the single most common failure at submission time; Owner / Next Action ensures no defect exists without someone accountable; and Exposure Prevented rolls the QA function up into a number leadership already cares about.

The automations are the substance — status changes notify the submitter automatically, an agreement fourteen days from expiry warns Buying before anyone writes against it, and a promotion whose start date arrives unapproved blocks itself.

10 · The Discount Calculator

A single-purpose page for answering pricing questions immediately. Load a product or enter values manually; every output recalculates as you type.

Field	Purpose
Load a product	Fills cost, price, category and the brand's vendor agreement from the catalogue.
Regular price / Unit cost	Per unit. Multiplied by units in offer internally.
Category	Applies that category's discount cap and margin floor.
Units in offer	Switches the whole calculation to offer level.
Promotion type / value	% Off, $ Off or Price Point. The label and helper text change with the type.
Vendor agreement	Optional. Applies the contractual ceiling and shows the reimbursement value.

The output gives the guest price, saving, effective discount, gross margin, gross profit per offer, and — the most useful figure — the lowest compliant price, with the three competing floors shown beneath it so it is clear which constraint is binding.

How to explain it: “If someone asks me on the spot how low we can go on a product, this answers it in about five seconds and tells me which constraint is the one actually stopping us — the category cap, the margin floor, or the vendor's contract.”

11 · Formula design decisions

Several choices in the workbook are deliberate and worth being able to defend.

11.1 INDEX/MATCH rather than VLOOKUP or XLOOKUP

Every lookup uses INDEX(range, MATCH(value, range, 0)). VLOOKUP breaks when columns are inserted, because it depends on a counted column offset. XLOOKUP is cleaner but is unavailable in older Excel and behaves inconsistently when a file is written programmatically. INDEX/MATCH works identically in every version of Excel, in Google Sheets, and in LibreOffice — which matters when the file will be opened by people whose software you do not control.

11.2 Tolerances instead of exact equality

Currency comparisons use a tolerance — one cent for prices, 0.002 for percentages — rather than =. Floating-point arithmetic means two notionally identical prices can differ in the last decimal place, and a promotion priced exactly at a 25% cap can compute to 25.007%. Without tolerances the engine would report failures that are arithmetic artefacts rather than real problems, and an alerting system that cries wolf gets ignored.

11.3 The rounding trap

Excel's ROUND rounds halves away from zero. JavaScript's toFixed(2) does not always — (67.485).toFixed(2) returns 67.48, because 67.485 is stored in binary as very slightly less than 67.485.

When the web application was first built it used toFixed, and its BOGO prices came out one cent below the workbook's. On a promotion demonstration about pricing accuracy, the tool was itself displaying a guest price one cent below what the register would ring. It was corrected with an explicit round-half-away-from-zero helper:

function r2(n){ n = Number(n)||0; return Math.round(n*100 + (n>=0 ? 1e-6 : -1e-6)) / 100; }

The epsilon nudges the value just past the midpoint before rounding, reproducing Excel's behaviour exactly. After the fix both implementations agree to the cent across all twenty promotions.

This is worth mentioning if asked about attention to detail. It is a one-cent discrepancy that most people would never notice — and it is exactly the class of error the whole system exists to prevent.

11.4 Exact token matching for store codes

Check C12 originally searched for each master store code as a substring of the submitted list. That was shorter, but it would accept a code that merely contained a valid code. It was rewritten to split the list on commas and match each token exactly. The formula is longer; the failure mode is gone.

11.5 Only the later promotion is flagged

The conflict check requires the competing promotion to have been submitted earlier. Without that condition, two overlapping promotions would each detect the other, both would fail, and neither could proceed. With it, the first approved promotion holds and only the second requires attention — which is also how the business would resolve it.

11.6 Rules derived from the system of record

Caps and floors are looked up using the system category, never the category typed on the deal sheet. If validation trusted user input to select the rule that judges that input, the rule could be bypassed by mistyping a field. This is the most important principle in the whole engine.

12 · Glossary

Term	Meaning
Deal sheet	A promotional request submitted by buying, merchandising or marketing.
Dutchie	Cannabis e-commerce and point-of-sale platform. The system of record.
Weedmaps / Leafly	Third-party cannabis marketplaces carrying mirrored menus.
Vendor co-op	Vendor subsidy of a discount, governed by a signed agreement with a discount ceiling and a validity window.
Effective discount	The discount expressed as one percentage of the offer's regular price, regardless of promotion type.
Offer level	Pricing that describes the entire offer rather than a single unit.
Compliant floor price	The lowest price satisfying the category cap, the margin floor and the vendor ceiling simultaneously.
First-pass accuracy	Share of promotions clearing all checks with no rework. The headline health metric.
Channel parity	All three menus showing the same product data at the same time.
Days in stock	How long a product has been held. 90+ days marks a clearance candidate.
Weeks of supply	Units on hand divided by weekly sales velocity.
Promotional lift	The assumed uplift in sales rate during a promotion. Set to 1.5× here.

13 · The figures this system produces

Promotions submitted	20
Approved on first pass	7
Held for review	2
Blocked before launch	11
First-pass accuracy	35.0%
Hard errors caught	22
Warnings raised	8
Price variance prevented	$3,493
Vendor co-op at risk	$2,986
Total exposure prevented	$6,479
SKUs in catalogue	24
SKUs in sync across all three channels	17 of 24
Menu discrepancies	7
Most common failure	C8 Vendor Agreement Coverage (4)

Verified identically by the Excel implementation and the JavaScript implementation.

↑