Formula and audit layer

Calculations

The model exposes robust, explainable Excel formulas instead of opaque calculations. The live values below respond to the same dashboard filters used by the visual layer.

Filtered revenue

$55,906

=SUM(tbl_Clean[Revenue])

Filtered profit

$23,198

=SUM(tbl_Clean[Profit])

Profit margin

41.5%

=IFERROR(Profit/Revenue,0)

Quantity sold

137

=SUM(tbl_Clean[Quantity])

Formula library

Core formulas prepared for the Calculations worksheet.

Revenue

=SUM(tbl_Clean[Revenue])

Dynamic top-line KPI from the clean Excel Table.

Profit margin

=IFERROR(SUM(tbl_Clean[Profit])/SUM(tbl_Clean[Revenue]),0)

Protects the KPI card from divide-by-zero errors.

Category revenue

=SUMIFS(tbl_Clean[Revenue],tbl_Clean[Category],A2)

Feeds category performance tables and charts.

Customer count

=COUNTA(UNIQUE(tbl_Clean[Customer]))

Returns dynamic unique customers without manual deduplication.

Best product

=XLOOKUP(MAX(G2:G20),G2:G20,F2:F20)

Returns the highest-revenue product from a helper summary.

Ranked list

=SORT(FILTER(A2:G200,G2:G200>0),7,-1)

Builds an analysis-ready, nonblank ranked output.

Lookup alternative

=INDEX(Product[Category],MATCH([@Product],Product[Product],0))

Interview-ready fallback when XLOOKUP is unavailable.

KPI reconciliation

The filtered live KPI outputs reconcile from the exact same analytical rows.

Category	Revenue	Margin
Home & Office	$29,233	45.4%
Electronics	$21,681	34.3%
Sports	$4,992	49.6%

The model uses named table columns rather than fragile cell addresses, so formulas expand safely as new valid rows are added.