Summary
In this hands-on accounting analytics case, students will use Power BI to analyze two years of sales and receivables data for a growing company. Students will extract, transform, and load the data, establish a relational data model, and create visuals to explore changes in discounts, returns, and write-offs as well as analyze collection performance. The case culminates in students analyzing the adequacy of the allowance for doubtful accounts and communicating their findings and recommendations to management.
Content
This case provides a comprehensive, hands-on exercise in analyzing issues relating to a company's revenue cycle and has been designed for use in data analytics, audit, and financial accounting courses. Students assume the role of an analyst for a rapidly growing building-supplies distributor that experienced a 17% increase in gross revenue but nearly a 40% increase in accounts receivable following the introduction of a more aggressive sales compensation plan.
The case develops skills across the analytics process. Students use Power Query to import, clean, transform, append, and integrate seven data files and then construct and evaluate the resulting Power BI data model. They connect these technical tasks to accounting by preparing journal entries, considering the appropriate timing of revenue recognition, and determining how sales, cash receipts, discounts, returns, and write-offs affect the financial statements. Students then verify revenue and accounts receivable balances against provided general ledger figures before moving into more open-ended analysis.
The analytical portion asks students to build visualizations and measures that investigate discounts, returns, write-offs, accounts receivable aging, and days outstanding, first at the company level and then by territory and salesperson. This progression encourages students to move beyond simply constructing visualizations to identifying anomalous patterns, drilling into their causes, and exercising judgment about which findings warrant management attention. Students ultimately assess whether the company's 6.5% allowance for uncollectible accounts remains adequate and prepare a memo summarizing their findings, recommendations, and suggested next steps.
The case also incorporates generative AI as a tool rather than as a substitute for judgment. Students evaluate an example LLM prompt and use an LLM to help generate DAX and M code needed for selected calculations, including year-end accounts receivable and aging analyses. The materials include the detailed case narrative and requirements, financial-statement benchmark amounts, a comprehensive data dictionary describing the customer, salesperson, territory, sales invoice, and A/R credit datasets, a journal-entry exercise, and an example LLM prompt for generating DAX code.
The case also provides opportunities for optional audit-related extensions, allowing instructors of various courses to tailor the assignment to course learning objectives. The underlying transactional data can support additional analyses of customer credit limits, revenue cutoff, sales incentives, unusual transaction timing, salesperson behavior, and other revenue-cycle risks. Instructors can use these extensions to emphasize financial accounting, auditing, accounting information systems, or more advanced data analytics while retaining the same core dataset.
Because the case combines ETL, data modeling, visualization, accounting knowledge, generative AI, and managerial judgment in a single realistic revenue-cycle setting, faculty can use it to reinforce technical analytics skills while keeping the analysis firmly grounded in accounting decisions.
