ACC 430 Module 2 Data Preparation Assignment Example

Before the Margin Analysis: Cleaning and Documenting 1.26 Million Invoice Lines

[Student Name]

Southern New Hampshire University

ACC 430: Data Analytics for Financial Professionals

Module Two Assignment

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

Before the Margin Analysis: Cleaning and Documenting 1.26 Million Invoice Lines

Introduction

The margin analysis requested by the CFO needs two years of invoice-line detail: what was sold, to whom, at what price and at what cost. That detail lives in the distributor's ERP system, with customer and product information in separate master files. Before any analysis, the data must be complete, consistent and reconciled to the company's books. Richardson et al. (2021) place this mastering of data at the heart of the analytics cycle, because errors introduced here pass silently into every chart and conclusion. This assignment documents the extraction, profiling, cleaning and reconciliation.

Extraction

The IT manager ran a query for all invoice lines dated within the last 24 months, returning 1,262,400 rows with invoice number, line number, date, customer ID, product ID, quantity, unit of measure, unit price, extended price and standard cost. The customer master supplied segment codes, such as school, health care, office and restaurant, and the product master supplied categories. The extract was saved untouched, and all cleaning was performed on a copy through a documented query so the steps can be rerun. A hash value of the original file was recorded, so anyone can confirm later that the starting point was not changed.

Profiling and Problems Found

Table 1. Data Quality Issues and Rules

The duplicates were the largest problem. During the ERP migration 14 months ago, two weeks of invoices were loaded twice, and the duplicate lines inflate revenue in those weeks. Removing exact duplicates on invoice and line number eliminated them, and a check of those two weeks against the bank's deposit totals confirmed the corrected figures. The unit of measure problem was smaller but would have distorted price analysis badly: a case of 1,000 nitrile gloves recorded as one glove makes the unit price look a thousand times too high.

Decisions That Needed Business Approval

Two fixes involved judgment rather than mechanics. Assigning segments to 208 uncoded customers required knowing who they were, so the analyst proposed codes from billing addresses and the sales manager approved them. Deciding whether to keep returns was an analytical choice: removing them would make margins look better than they are, so they were kept and flagged. Brown-Liburd et al. (2015) caution that large data sets tempt analysts to filter aggressively to reduce noise, and that such filtering can introduce bias into the judgments made from the data. Recording who approved each judgment call addresses that risk.

Reconciliation

Table 2. Revenue Reconciliation to the General Ledger (millions of dollars)

The remaining $41,000 difference traces to freight charges billed on invoices but recorded in a separate ledger account, which is acceptable for a product margin analysis and documented in the log so that no later reader mistakes it for an error. Cao et al. (2015) note that the value of large-scale analytics in financial work depends on whether the data can be reconciled to the financial records the analysis is meant to explain, and this reconciliation provides that link.

Validating the Joins

Joining invoice lines to the customer and product masters is where data most often disappear silently. A line whose customer ID does not exist in the master would be dropped by an inner join and its revenue would vanish from the analysis. To prevent that, the joins were run as left joins and unmatched lines counted: 312 lines referenced six customer IDs deleted from the master after accounts closed, and 95 lines referenced discontinued product IDs. Both groups were restored from archived master files rather than dropped, and the log records that step. After the joins, the line count and revenue total were rechecked against Table 2 and matched.

What Cleaning Changed in the Story

Cleaning was not cosmetic. Before duplicates were removed, the two weeks loaded twice showed a spike in revenue that would have looked like a successful promotion. Before units were standardized, gloves appeared to sell at absurd prices, which would have pulled average margins for the health care segment up by almost two points. And before missing segments were coded, about $5.6 million of revenue sat in an unknown category, much of it small restaurants, the very segment the CFO suspects is unprofitable. Each fix moved the eventual answer closer to the truth, which is why the preparation deserves as much care as the analysis.

The Data Quality Log

Every rule is recorded in a data quality log with the issue, the rule, the number of rows affected, the effect on revenue and cost, the date and the approver, in the order the rules were applied. The log, the original extract and the cleaning query together allow anyone to reproduce the cleaned data set or challenge a choice.

Conclusion

The cleaned data set contains 1,223,300 invoice lines whose revenue reconciles to the general ledger within $41,000, with duplicates removed, customers grouped correctly, returns retained and flagged, segments assigned with approval and units standardized. The margin analysis can now proceed on data that match the books. When the next month's invoices are added, the same query will apply the same rules, and the reconciliation will be rerun before any figure is reported, so the cleaning becomes a routine control rather than a one-time project that slowly goes stale.

References

Brown-Liburd, H., Issa, H., & Lombardi, D. (2015). Behavioral implications of big data's impact on audit judgment and decision making and future research directions. Accounting Horizons, 29(2), 451-468. https://doi.org/10.2308/acch-51023

Cao, M., Chychyla, R., & Stewart, T. (2015). Big data analytics in financial statement audits. Accounting Horizons, 29(2), 423-429. https://doi.org/10.2308/acch-51068

Richardson, V. J., Teeter, R. A., & Terrell, K. L. (2021). Data analytics for accounting (2nd ed.). McGraw Hill.