Technical case study · 02

BMS Pricing & Quoting

An internal Access/VBA application that turned fragmented spreadsheets and manual estimates into a guided quotation workflow.

Microsoft AccessVBARelational data modelBusiness rulesValidation

Real application, reconstructed code. I developed the workplace tool. The published VBA example is independent and synthetic; it does not contain the employer’s production source, quotation records or supplier price libraries.

From requirements to implementation

I designed the application around work details, material and labour estimation, pricing review and quotation/BOQ output. During design, I questioned whether one overall margin could meet the needs of projects with different cost structures. After manager discussion, the more detailed version supported both project-wide and five category-specific gross margins.

EstimateSupplier lookups · materials · labour

ReviewGross margins · discount · refresh

OutputQuotation / BOQ · PDF · email draft

The screen below illustrates the expanded Pricing Review configuration; the simpler application walkthrough on Notion does not show every advanced feature.

Anonymised Microsoft Access pricing review form showing overall margin and five separate category margin inputs

Anonymised view · extended Pricing Review form

Pricing logic and calculation precedence

The observed design used calculated form controls and VBA to write updated cost totals and select a selling amount. Five cost components—Material, Labour, Administration, Other Costs and Panel—could receive separate margins. When the category calculation returned a value, it took priority; otherwise the process fell back to the single project-wide margin.

Pricing rule	Calculation
Project-wide gross margin	`S = C / (1 − m) × (1 − d)`
Category-specific	`S = (1 − d) × Σ(Cᵢ / (1 − mᵢ))`
Fallback	Category result when available; otherwise project-wide result

Discount is applied after gross-margin pricing; this can lower the achieved margin. Category calculations require all five margin fields and explicit zero entries for absent Other / Panel costs.

Curated excerpt from the independent VBA demo

If categoryReady Then
    ' Calculate five category selling amounts, then discount.
    For i = 0 To CATEGORY_COUNT - 1
        rate = ParseMargin(categoryMargins(LBound(categoryMargins) + i))
        categorySelling = categorySelling + numericCosts(i) / (1# - rate)
    Next i
    CalculateSellingPrice = RoundHalfUpCents(categorySelling * (1# - discountRate))
    Exit Function
End If

' Fallback to project-wide margin if category calculation is unavailable.
If Not IsBlankInput(projectMargin) Then
    rate = ParseMargin(projectMargin)
    CalculateSellingPrice = RoundHalfUpCents(totalCost / (1# - rate) * (1# - discountRate))
Else
    CalculateSellingPrice = Null
End If

This code is from my reconstructed, stateless demonstration—not the employer’s form event handler.

Validation and engineering trade-offs

I compiled and ran the synthetic VBA implementation in Microsoft Access. The standalone test harness passed 14 of 14 scenarios across five consecutive runs, covering standard calculations, missing inputs, fallback logic, discounts and invalid values.

Unresolved historical defect. Repeatedly double-clicking Refresh in the original form could produce incorrect totals. I traced an aggregation dependency through five subform controls and documented possible causes; the root cause was not confirmed or fixed. The standalone code does not reproduce Access form event timing.

Other lessons include the risks of shared Access files and the case for controlled supplier catalogue imports. These are documented as engineering trade-offs, not claims of production fixes.