D552 Task 1 Accounting Data Presentation Example

Why Supplies Grew Nineteen Percent When Visits Grew Four: Preparing and Presenting Accounting Data for a Composite Six-Practice Dental Group

Student Name

School of Business, Western Governors University

D552: Data Analytics for Accountants I, Task 1

Course Instructor

Month Day, Year

Why Supplies Grew Nineteen Percent When Visits Grew Four: Preparing and Presenting Accounting Data for a Composite Six-Practice Dental Group

The Question

Riverbend Dental Partners, a composite group of six general dentistry practices in Ohio, spent $1.26 million on clinical supplies last year, up 19% from $1.06 million the year before, while patient visits rose only 4%. The chief financial officer asked the accounting team a specific question: what caused supply costs to rise faster than visits, and what can be done about it? Framing a narrow, answerable question first is the opening stage of the IMPACT cycle, the analytics model used in accounting that runs from identifying the question and mastering the data through performing the analysis, addressing and refining results, communicating insights and tracking outcomes (Richardson et al., 2023).

Data Sources

Three sources were needed. Accounts payable invoice lines for two years, 14,200 rows, exported from the accounting system with vendor, date, amount, general ledger account and practice. Monthly patient visits by practice from the practice management system. Item-level detail for the three largest suppliers, taken from their online order histories, including item numbers, quantities and unit prices. Accounting has always had data, but analytics now draws on larger and more varied sources that must be combined before they can answer a question (Vasarhelyi et al., 2015).

Extract

Each source was exported to a separate file without editing, and the original files were saved in a read-only folder so every later step could be traced back to them. Row counts and total amounts were recorded at extraction. The invoice lines totaled $2,325,598 across both years, $4,118 more than supplies expense in the general ledger for the same period; the difference was traced to rows the export had repeated, a problem the transform step would have to fix.

Transform

Most of the work happened here. Using Power Query, the team recorded every step so the cleaning can be rerun next quarter.

Vendor names were standardized: the same supplier appeared under four spellings, and a mapping table combined them.

General ledger accounts were mapped to five supply categories: consumables, impression materials, instruments, infection control and office supplies.

Practice names were replaced with a practice ID shared across all three sources, since the practice management system used different names from the accounting system.

Invoice dates were converted to months, and the visit data were reshaped so that each row held one practice and one month, a tidy layout, with one variable per column and one observation per row, that makes separate tables far easier to join and analyze (Wickham, 2014).

Exact duplicate rows created by the export were removed: 86 rows. Invoices that repeated the same vendor, item and quantity within seven days were flagged but kept, because they might be real duplicate orders rather than data errors.

Load

The cleaned tables were loaded into a Power BI data model: an invoice table, a visits table, a supplier item table and small tables for practices, categories and months, linked by practice ID and month. After loading, totals were checked again, and with the $4,118 of export duplicates removed they agreed to the general ledger to the dollar.

Analysis

Supply cost per visit rose from $19.40 to $22.20 overall, but the rise was uneven. Four practices stayed within 3% of their prior year. Eastgate and Millbrook rose 17% and 21%.

Comparing unit prices for the 25 most-ordered items showed why. In March, both practices began ordering from a new supplier after a sales visit, and they paid an average of 13% more than the group's primary supplier charged for the same items. The price difference added about $72,000.

The seven-day repeat flags showed a second cause. After three practices moved to new inventory software in June, automatic reorders fired twice for many items for about ten weeks. The supplier order histories confirmed that both orders shipped. The duplicate orders added about $48,000, much of it now sitting in storerooms.

The remaining increase of about $80,000 reflects the 4% growth in visits, roughly $42,000, and a shift toward more expensive impression materials for a new clear aligner service, roughly $38,000.

Checking the Analysis

Because the CFO will act on these numbers, the analysis was reviewed before it was presented. A second accountant who had not built the model reran the Power Query steps from the saved source files and reached the same totals. Twenty flagged repeat orders were traced to supplier packing slips to confirm that both shipments arrived, and ten were traced to the storerooms, where the surplus stock was found on the shelves. Unit price comparisons were limited to identical item numbers, so a cheaper substitute product was never compared with a premium one. Finally, the practice managers at Eastgate and Millbrook were asked why they changed suppliers; both said the new representative offered faster delivery, which the recommendations take into account.

Presenting the Results

The presentation to the CFO uses four visuals, each with a one-sentence caption stating what it shows. A bridge chart walks from last year's $1.06 million to this year's $1.26 million in four steps: visit growth, aligner materials, supplier prices and duplicate orders. A bar chart shows cost per visit by practice, with Eastgate and Millbrook highlighted. A table lists the ten items with the largest price differences between suppliers. A monthly line chart shows the duplicate order spike beginning in June and ending in August when the software settings were corrected.

Limits and Recommendations

The analysis has limits. Item-level data were available only for the three largest suppliers, which cover 78% of spending, and the split between visit growth and aligner materials is an estimate.

Recommendations: require all practices to order from the group's contracted suppliers unless the practice manager documents a reason; add a duplicate order check to the inventory software and a monthly report of repeat orders; use surplus stock before reordering; and refresh this dashboard quarterly, tracking cost per visit by practice as the outcome measure, which completes the final stage of the cycle.

Conclusion

The 19% rise in supply costs was not a mystery once the data were prepared: about $120,000 came from two avoidable problems, higher prices from an off-contract supplier and duplicate orders after a software change. Documented ETL steps made the answer traceable and repeatable, and a short presentation built around one question gave the CFO a clear basis for action.

References

Richardson, V. J., Teeter, R. A., & Terrell, K. L. (2023). Data analytics for accounting (3rd ed.). McGraw Hill.

Vasarhelyi, M. A., Kogan, A., & Tuttle, B. M. (2015). Big data in accounting: An overview. Accounting Horizons, 29(2), 381-396. https://doi.org/10.2308/acch-51071

Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23. https://doi.org/10.18637/jss.v059.i10