BMS: pricing rules, architecture & verification

BMS Pricing & Quoting Application — Technical Case Study

Microsoft Access · VBA · Business rules · Workflow automation

Real project in context

I independently designed and developed an Access application that combined four supplier catalogues, material and labour estimates, pricing review and quotation/BOQ output. A manager supplied the initial workflow framework and requirements. Typical quotation preparation fell from approximately 2–7 days to half a day, depending on complexity; this is a retrospective estimate.

Version scope: the Notion walkthrough primarily shows the simplified maintenance-pricing application. The pricing rules and interface below concern the expanded project-pricing version. Published code is independently reconstructed with synthetic inputs; the employer database and source are excluded.

The screenshot documents the interface, not a calculated quotation. A worked example appears in pricing requirements.

Technical evidence

What the expanded project-pricing workflow did

I proposed support for both an overall gross margin and category-specific margins. The interface instructed users to choose one method. The inspected implementation used the category result when available and otherwise the overall result. Discount followed margin pricing. See the requirements for exact inputs and fallback behaviour.

Material codes copied from a supplier library populated description and unit price. For applicable libraries, users manually confirmed the exchange rate through an Access form when opening the material library; displayed costs were in EUR.

Run the independent VBA example

1. Open Microsoft Access or Excel on Windows and press Alt+F11.
2. Import PricingDemo.bas and TestPricingDemo.bas through File → Import File. When pasting, omit the Attribute VB_Name lines.
3. Compile if available, then run RunPricingDemoTests and inspect the Immediate window (Ctrl+G).

The function accepts five synthetic costs, gross-margin rates and a discount. It does not implement forms, catalogue lookup, PDF output or email drafting. Test expectations and the recorded execution scope are in test scenarios.

Architecture & Data Flow — Conceptual Reconstruction

This diagram and entity model are independent abstractions, not an export of the original Access database. The exact original table schema, keys and cardinalities are not publicly supplied.

Functional flow

flowchart TD
    A[Project information] --> B[Material and labour estimates]
    C[Supplier price catalogue] --> B
    B --> D[Five independent subform summaries]
    D --> D1[TotalSubform aggregation]
    D1 --> E[Refresh action]
    P[Admin / Other / Panel costs] --> E
    E --> F[Update admin and project cost totals]
    F --> G[Calculated form controls]
    H[Five category margin rates] --> G
    I[Project-wide margin rate] --> G
    J[Discount] --> G
    G --> K{Category calculated result available?}
    K -->|Yes| L[Use category selling]
    K -->|No| M[Use project-wide selling]
    L --> N[Store or display Project Selling]
    M --> N
    N --> O[Quotation and BOQ output]

The original TotalSubform expression explicitly added five subform summary controls, each wrapped in Nz(). The original Refresh event assigned point totals, administration cost and ProjectCost before selecting between the two calculated selling controls. It used Me.Refresh at the end. The diagram does not represent Access's internal recalculation schedule or prove the update order is free of stale-value risks.

For the confirmed cost-dependency chain, see subform_aggregation.md. No row-by-row mapping from those five control values to the entities in the illustrative data model is asserted.

Illustrative relational model

erDiagram
    PROJECT ||--o{ MATERIAL_ESTIMATE : contains
    PROJECT ||--o{ LABOUR_ESTIMATE : contains
    PROJECT ||--o| PRICING_REVIEW : receives
    SUPPLIER ||--o{ CATALOG_ITEM : provides
    CATALOG_ITEM o|--o{ MATERIAL_ESTIMATE : referenced_by

    PROJECT {
      int project_id PK
      string reference
      string status
    }
    MATERIAL_ESTIMATE {
      int line_id PK
      int project_id FK
      int catalog_item_id FK
      decimal quantity
      decimal cost
    }
    LABOUR_ESTIMATE {
      int line_id PK
      int project_id FK
      decimal hours
      decimal cost
    }
    SUPPLIER {
      int supplier_id PK
      string supplier_name
    }
    CATALOG_ITEM {
      int catalog_item_id PK
      int supplier_id FK
      string product_code
      decimal unit_cost
    }
    PRICING_REVIEW {
      int review_id PK
      int project_id FK
      decimal project_margin
      decimal discount
      decimal calculated_selling
    }

Illustrative only: All entity names, foreign keys, data types and cardinalities are proposed generic labels. In particular, the actual pricing mode was not stored as a verified pricing_mode field; the provided form event selected a non-Null category result first.

Design boundaries

• Original reported application: supplier catalogues, Access relational records, forms/subforms, VBA actions, quotation/BOQ generation and Outlook email drafting.
• Reconstructed public code: a pure VBA calculation function on synthetic arrays. It does not read/write tables or implement Access form events.
• System limitation reported: double-clicking the original Refresh button could yield incorrect costs/selling; exact cause is unknown. See refresh investigation.
• Potential redesigns (not historical deliverables): explicit mode choice, input completeness guidance, calculation in one deterministic routine, guarded refresh events, and a front-end/back-end deployment architecture.

Requirements-to-Implementation Mapping (Retrospective)

Why two pricing routes?

Original request: one gross-margin rate on the entire quote.

Refinement: variable project complexity could justify distinct margins for Material, Labour, Administration, Other Costs and Panels. The manager agreed to support both pricing approaches in the more detailed review form.

Confirmed rules and implementation evidence

If a category rate is missing, the category result becomes unavailable. Selling can still use a valid project-wide result; otherwise it remains blank.

Calculation contract inferred from expressions

Project-wide gross margin:

S_project = ProjectCost / (1 - ProjectMargin) * (1 - Discount)

Category-specific gross margin:

S_category = (1 - Discount) * (Material/(1-MaterialMargin) + Labour/(1-LabourMargin) + Admin/(1-AdminMargin) + Other/(1-OtherMargin) + Panel/(1-PanelMargin))

Selection (pseudocode, not original VBA):

refresh point totals
refresh administration cost
refresh ProjectCost from calculated aggregate
if CategorySelling is Null:
    ProjectSelling = ProjectWideSelling
else:
    ProjectSelling = CategorySelling
refresh displayed record

ProjectCost in the original was calculated from Nz(TotalSubform) + Nz(TotalAdminCost) + Nz(TotalOtherCost) + Nz(PanelAllowance). The synthetic VBA demo instead sums the five cost categories for standalone verification. The inspected TotalSubform formula sums five distinct subform summary controls via Nz() (controller, BMS points/material and three labour-related summaries). Their individual formulas and runtime values remain unverified. See subform aggregation.

The original category expression used Nz() for Material, Labour and Administration, but not for Other Cost and Panel Allowance. Discount used Nz() in both routes. Rates were supplied via form controls (typically shown as percentages). Historical division-by-zero handling, field precision and rounding were not provided.

Retrospectively drafted acceptance scenarios

These are retrospective analysis artifacts. AC-07 is a target for a corrected implementation, not a claim of passing historical UAT.

Applied discount ≠ preserved gross margin

Example: cost 100, gross-margin rate 20%, discount 10% gives (100/0.8)*0.9 = 112.50. Achieved gross margin is (112.50-100)/112.50 = 11.11% (rounded). This was an intentional discount-after-pricing workflow, not evidence that the organisation required a post-discount 20% gross margin.

Implementation Notes, Evidence Boundaries & Choices

Original vs demonstration

The example accepts costs already expressed in EUR. In the application, users manually confirmed an exchange rate through an Access form for applicable supplier libraries. That catalogue process is outside the standalone function.

Design choices

I use Decimal intermediates to reduce binary midpoint rounding risk, distinguish explicit zero from missing input, and keep discount placement visible. Unused-route margins are not comprehensively validated. Verification evidence is centralised in test scenarios.

Possible future improvements

• Make the pricing mode explicit and reject simultaneous conflicting inputs.
• Show the specific missing fields rather than an unexplained blank selling amount.
• Calculate from stable input values in one routine before updating the form.
• Define rounding and discount policy with worked examples.
• Investigate the actual form dependency chain before changing event behaviour; see Refresh investigation.

Pricing Verification Scenarios — Independent Examples

Execution record: an earlier version of the standalone pricing demo passed 14 synthetic scenarios in Access. The current Decimal version and its 19-scenario harness have not been executed in Windows Access. The amounts below are independent arithmetic expectations. Neither version tests the original Access form or its Refresh defect.

Common synthetic inputs

Cost order: Material €100, Labour €200, Administration €50, Other €20 and Panel €80; total €450. Category margins: 20%, 25%, 10%, 5% and 15%. Rates are decimal fractions in VBA.

Automated demonstration test matrix

Independent arithmetic check

Overall: 450 / 0.8 × 0.9 = 506.25.

Category: (100/0.8 + 200/0.75 + 50/0.9 + 20/0.95 + 80/0.85) × 0.9 ≈ 506.1532508, rounded once to €506.15.

How to run

Import PricingDemo.bas and TestPricingDemo.bas as standard modules. Compile if available and run RunPricingDemoTests. The harness stops at a failed assertion or prints its pass message after every current assertion succeeds. Record the actual platform, date and output when running it.

T-14 checks repeatability of a pure function. The form regression requires the separate Refresh investigation protocol.

Subform-to-Project Cost Aggregation

Read the detailed notes

Confirmed dependency

The inspected TotalSubform expression added five subform summary controls, each wrapped in Nz(): controller, BMS points/material, panel labour, hardware/software labour and commissioning labour. The cost calculation then added administration, other costs and panel allowance. These labels describe the control names; the underlying formulas and one-to-one mapping to cost categories were not independently established.

View diagram source

flowchart TD
    A[Controller summary] --> T[Five-subform aggregate]
    B[BMS points / material summary] --> T
    C[Panel labour summary] --> T
    D[Hardware / software labour summary] --> T
    E[Commissioning labour summary] --> T
    T --> C1[Project cost calculation]
    F[Administration cost] --> C1
    G[Other costs] --> C1
    H[Panel allowance] --> C1
    C1 --> R[Refresh writes ProjectCost]
    R --> S[Calculated selling controls / route selection]

What Nz() establishes

In this numeric expression, a Null term contributes zero. This prevents Null propagation but does not prove that the underlying subtotal is genuinely zero or that a form reference is valid. If subtotals were temporarily Null, zero substitution could conceal incomplete inputs; no runtime trace has confirmed that mechanism.

Diagnostic checkpoints

Use distinguishable synthetic subtotals, such as 10, 20, 30, 40 and 50, plus separate administration, other and panel values. Capture the five source controls, TotalSubform, CalCost, ProjectCost and both selling expressions before the first Click, after it and after the second Click.

The first point of divergence would help distinguish aggregation, recalculation and persistence problems. The full protocol and observed symptoms are in Refresh investigation.

Refresh Double-Click Defect — Investigation Plan