CA ANZ Data Analytics and Insights cheat sheet

Data quality dimensions: completeness, accuracy, validity, consistency, timeliness, uniqueness, integrity - each is assessed separately and reported as a metric.

Completeness rate = (non-null values in field / total records) × 100; a 98% threshold is common for key financial fields, but thresholds are set by materiality, not convention.

Validity tests check format and domain membership (e.g., a date parses, a currency code appears in the ISO 4217 list); accuracy tests compare against an independent source of truth.

Referential integrity failure = a foreign key value with no matching primary key in the parent table; count orphans per relationship rather than in aggregate.

Duplicate detection: exact match on a composite key handles deterministic duplicates; fuzzy matching (Levenshtein, Jaro-Winkler, phonetic keys) handles near-duplicates such as name variants.

Structured data fits a predefined schema (ledger tables, CSV); semi-structured data carries tags but no rigid schema (JSON, XML); unstructured data has no schema (contracts, emails, images).

ETL extracts, transforms, then loads; ELT loads raw data first and transforms inside the target system - ELT preserves lineage and suits cloud warehouses.

A data dictionary documents field name, type, allowed values, owner, and derivation rule; a data lineage record traces each reported figure back to source system and transformation.

Descriptive analytics reports what happened; diagnostic explains why; predictive forecasts what will happen; prescriptive recommends an action - the four are cumulative, not alternatives.

Benford's Law: expected first-digit frequency for digit d is log10(1 + 1/d); digit 1 ≈ 30.1%, digit 2 ≈ 17.6%, digit 9 ≈ 4.6%. Applies to naturally occurring magnitudes spanning several orders.

Regression estimates the relationship between a dependent variable and one or more predictors; R² is the proportion of variance explained, not evidence of causation.

Classification predicts a categorical label (fraud / not fraud); clustering groups records by similarity without a pre-labelled target.

Sampling risk falls with sample size; a statistically valid sample requires a defined population, a random selection method, and a documented evaluation approach.

Outlier rules: a value beyond 1.5 × IQR from the quartiles is a mild outlier, beyond 3 × IQR is extreme; z-scores above |3| flag unusual observations in roughly normal data.

SQL joins: INNER returns matched rows only; LEFT returns all left rows plus matches; a join on a non-unique key multiplies rows and inflates totals.

Full-population testing removes sampling risk but shifts risk to data completeness and to the reliability of the fields tested.

Chart selection rule: comparison across categories → bar; trend over time → line; part-to-whole → stacked bar or treemap; distribution → histogram or box plot; correlation → scatter.

Truncating a bar chart's y-axis distorts perceived magnitude because bar length encodes value; line charts may use a non-zero baseline when the axis is labelled.

Dual-axis charts invite false correlation; use them only when units differ and both axes are explicitly labelled.

Data-ink ratio (Tufte): maximise ink devoted to data, remove gridlines, borders, and 3-D effects that carry no information.

Colour must not be the sole encoding of meaning - pair it with labels, patterns, or position for accessibility and for greyscale printing.

An insight statement names the finding, its magnitude, its population, and its implication; 'sales are up' is an observation, 'margin fell 2.1 points in the northern region' is an insight.

Aggregation can hide detail (Simpson's paradox): a trend across combined groups can reverse within every group, so report segment-level results alongside totals.

Annotate the chart with the source, period, population, and any exclusion applied, so the reader can reproduce the figure.

Audit risk model: Audit Risk = Inherent Risk × Control Risk × Detection Risk; analytics primarily reduces detection risk.

Analytics supports risk assessment, journal entry testing, segregation-of-duties review, three-way match, and full-population transaction testing.

Journal entry testing commonly targets entries posted at unusual times, round amounts, unusual user accounts, entries to seldom-used accounts, and post-closing entries.

Three-way match compares purchase order, goods receipt, and supplier invoice; exceptions are quantity or price mismatches, duplicates, or missing receipts.

Analytics results are audit evidence only if completeness and accuracy of the underlying data are tested and documented (IPE - information produced by the entity).

Exception-based testing requires a defined rule and a documented disposition for every exception: valid, invalid, or escalated - unexplained exceptions cannot be ignored.

Benford's Law and duplicate-payment tests are red-flag indicators; a deviation directs attention to a subpopulation, it is not itself proof of misstatement.

Documentation standard: record the data source, extraction date and parameters, reconciliation of record counts to the source, the rule logic, and the reviewer.

Variance analysis decomposes the gap between actual and budget into volume effect (quantity change × standard price) and price effect (price change × actual quantity).

Contribution margin = revenue − variable costs; it funds fixed costs before profit, so it drives short-term pricing and product-mix decisions.

Break-even units = fixed costs / contribution margin per unit; break-even revenue = fixed costs / contribution margin ratio.

Sensitivity analysis varies one input at a time to show the range of outcomes; scenario analysis varies several inputs coherently; simulation samples input distributions jointly.

A KPI needs a definition, formula, data source, owner, target, and review cadence; an undefined KPI is not comparable across periods.

Rolling forecasts replace the fixed annual budget horizon and reduce the stale-data problem of static budgets.

Segment reporting requires consistent allocation bases; changing an allocation method changes reported segment profit without any change in cash.

Predictive forecasting assumes the historical relationship persists; structural breaks (new contract, regulation, price shock) invalidate the model until refitted.

APES 110 fundamental principles: integrity, objectivity, professional competence and due care, confidentiality, professional behaviour.

Confidentiality persists after the engagement ends; client data used in analytics must be held only for the purpose and duration authorised.

Independence is impaired by a financial interest, an employment relationship, or providing a service that assumes management responsibility.

Privacy principles (e.g., Australian Privacy Principles) require collection for a stated purpose, notice, security safeguards, and limits on cross-border disclosure.

Algorithmic bias arises when training data under-represents a group or encodes historical discrimination; testing must include subgroup performance, not only aggregate accuracy.

Data minimisation: collect and retain only the fields required for the stated purpose; de-identification reduces but does not eliminate re-identification risk.

Accountability for an analytics-derived conclusion rests with the professional who signs it, even where the model was built by a third party.

A governance framework assigns data ownership, access controls, change logs, and an escalation path for quality failures.

RPA follows rule-based, deterministic steps on structured inputs; machine learning generalises from examples and suits judgement-like pattern tasks.

Generative AI and LLMs produce probabilistic text output; they require human verification and are unsuitable as an unreviewed source of authoritative figures.

Machine learning models require a training set, a validation set for tuning, and a held-out test set for unbiased performance reporting.

Process mining reconstructs actual workflows from system event logs and exposes bottlenecks, rework loops, and control deviations.

Continuous auditing uses automated, recurring tests against live data; continuous monitoring is management's own real-time control process.

Cloud analytics shifts controls to identity and access management, encryption at rest and in transit, and vendor assurance reporting.

Explainability techniques (feature importance, SHAP) attribute a model's output to inputs, supporting challenge and audit of model-driven decisions.

Model risk management covers model inventory, independent validation, performance monitoring, and documented retirement of superseded models.