Comprehensive Case: Finding the Cash Behind the Profit

Case slides View slides Download PowerPoint

This case closes Part IV. It asks you to use the four Power BI chapters together on a question the audit committee has put to the controller: how a year with a profit of several million dollars ended with less cash than it began. You will build, profile, and validate a model of your own, as in Chapters 13 and 14, and measure balances, their changes, and how fast they turn with the DAX of Chapter 14. You will build pages that the committee can explore with the drill-through pages, tooltips, and parameters of Chapter 15, and you will test your own report before anyone relies on it, as the internal auditor tested the package in Chapter 16. The Part III case ended with a plan for the plant’s hours and the year-end adjustments it proposed for fiscal 2026. Those proposals are not posted to CharlesRiver.xlsx, so this case starts from the unchanged data. It ends with a recommendation on the cash that Charles River holds in its working capital.

The case works in a new file, Charles River Cash Case.pbix, built in Power BI Desktop from CharlesRiver.xlsx, so that the package of Chapters 13 to 15 and internal audit’s monitoring file stay as they are. The requirements use the techniques of the Part IV tutorials and of Exercises 8.2, 14.1, 15.1, 15.3, 15.4, and 16.1, so review any of those exercises you have not done before you start.

The Situation

It is June 2027, and the audit committee is reviewing the fiscal 2026 statements. Its members have asked a question that the income statement cannot answer. Charles River earned a net income of about four and a half million dollars in fiscal 2026, after a year in which its cash grew by several million, and yet it ended fiscal 2026 with less cash than it began with. The controller will answer at the committee’s next meeting, with a report its members can explore with her.

You are the staff accountant of Chapters 13 to 15. The controller asks you to build the report and to write the decision memo that goes with it. She wants to know four things:

where the cash went in fiscal 2026, and how that year differed from fiscal 2025;
which balances absorbed the cash, how quickly each turns back into cash, and how that has changed since 2024;
why the materials inventory grew, and how much cash a return to the company’s own inventory targets would release;
whether the balances behind the report are supported, and what the statements and the committee need to know about those that are not.

She adds two conditions. Internal audit will test the report as it tested the package in May, so the file must carry its own evidence: a Validation page, a Tests tab, and the date through which its data run. And some balances will look wrong. The memo should state what the records show, such as a balance that no document supports or a payment that the ledger does not record, rather than what anyone did, until evidence from outside the database settles the question.

The Data You Will Use

Create Charles River Cash Case.pbix in the same folder as CharlesRiver.xlsx, turn off the two loading options of Tutorial 13.1 (Auto date/time and Autodetect new relationships after data is loaded, under Current File), and import the Tables below with Home > Get data > Excel workbook, under the query names given:

GLEntry (T3_GLEntry), Account (T1_Account), and JournalEntry (T2_JournalEntry), for the ledger star of Tutorial 14.3 with its closing entries flagged, and the Date table of Tutorial 14.1;
Receipts, built from T36_GoodsReceiptLine with the receipt date from T35_GoodsReceipt and, through T34_PurchaseOrderLine, the requisition behind each line from T32_PurchaseRequisition: what was received into inventory, and why it was ordered;
Issues, built from T55_MaterialIssueLine with the issue date and the work order from T54_MaterialIssue: what production used;
Requisitions (T32_PurchaseRequisition), with the job title of each approver from T74_Employee;
Item (T44_Item), with each item’s TargetDaysSupply from T79_InventoryPolicy;
ARInvoices (T16_SalesInvoice), with each invoice’s open balance at the end of fiscal 2026 from T20_CashReceiptApplication and T23_CreditMemo, as in Exercise 16.1, and APInvoices (T37_PurchaseInvoice), with its open balance from T39_DisbursementPayment, as Exercise 8.2 found it in Excel;
DebtSchedule (T43_DebtScheduleLine), the scheduled payments of the company’s notes.

Follow the conventions of Part IV. Open T3_GLEntry with Transform Data and keep only the columns the model needs, but all of its rows. Clear Enable load on the queries that only feed others, relate each fact table to its dimensions and to Date, one-to-many in a single direction, and hide the keys. Keep the measures in a Key Measures table, each with a description that states its definition.

Watch out

The ledger holds rows that a working-capital report must treat apart. The year-end closes must be left out of every flow, as in Tutorial 14.3. The opening entry of January 1, 2024, brings in balances that no document in the extract supports, such as receivables without their invoices and inventories without their items. The missing schedules are evidence to request, not proof that the balances are wrong. Fiscal 2024 was the ledger’s first year, so a 2024 figure built on an average balance or on the change from the opening says little. Supplier payments are the only postings after fiscal 2026, so the last posting date in the ledger is not the date through which your report runs. Some journal entries move a balance from one account to another without any cash changing hands. And the inventory policies hold one row for each item and warehouse, so group them by item before you merge them, or each item with a policy at both warehouses will appear twice and ItemID will stop being unique.

Watch out

A ratio of a balance to a flow is only as good as its definition, and none is standard enough to go unstated. Receivables include sales tax and freight, so compare them with what was billed, not with revenue. Materials are used by production and finished goods are sold, so each has its own flow and its own target. Take each balance at the end of a quarter and the flow of the twelve months that end on that date, not a quarter’s flow. And GLEntry is large: let the Balance measure filter the Date table rather than iterate the ledger, and add no calculated columns to GLEntry.

Requirements

Requirement 1: Build and validate the case file (Chapters 13, 14, and 16).

Build the queries and the model. In Requisitions, add a column that names the channel behind each requisition from its justification: the supply plan, a work-order shortfall, or another request. Carry the channel into Receipts through the merge.
Profile the Tables the chapters did not load on the entire data set, with the column profiles of Chapter 13, and test the links the later requirements rely on: every receipt line to its order line and its requisition, and every issue line to its issue and its work order. Record what you find, but do not pursue isolated exceptions.
Start a Validation page that reproduces control totals you already trust and ties the new tables to the ledger: net income by fiscal year, total assets at each year-end with the Balance measure of Exercise 14.1, and the materials received and issued each year against the postings to account 1045. Put the queries behind them in a Tests tab in DAX query view.
Add a measure Data Through that returns the earliest of the last posting dates of the documents behind the working-capital accounts (the sales invoices, cash applications, supplier invoices and payments, goods receipts, material issues, and shipments), as Tutorial 16.3 did for its populations, and show it on an About page that states the report’s purpose, sources, and definitions. Like Tutorial 16.3’s card, it is a bound observed in the extract, not proof that every document up to that date is present. Explain why the last posting date in the ledger is the wrong answer, and why the payroll records, which end earlier, are reported apart.

Requirement 2: Explain where the cash went (Chapters 14 and 15; financial reporting).

Show the balance sheet at each fiscal year-end with the Balance measure, signed by account type as in Exercise 14.1, with the working capital, the current ratio, and the quick ratio, defined here as cash (1010) and receivables (1020) over current liabilities, which leaves out the inventories and the other current assets.
Add two columns to Account in Power Query. The first assigns each account, from its AccountSubType, to a step of a bridge from net income to the change in cash, with receivables, inventories, payables, and sales tax separated by account number; the second assigns each step to operating, investing, or financing activities. Write a measure for each step’s effect on cash from the year’s postings without the closing entries, not from the difference between two balances, which treat the closing entries differently at two dates. Use KEEPFILTERS wherever the measure filters a column that the visual groups by, as in Tutorial 15.3.
Chart fiscal 2026 as a waterfall with a year slicer, the steps in their order through a sort column, as the Steps table of Tutorial 15.1 was sorted, and compare fiscal 2025. Explain why fiscal 2024 cannot be read the same way.
From a matrix of the bridge, drill through from the investing and financing steps to their postings, as in Exercise 15.1, and find the entries that moved no cash. Restate the operating, investing, and financing sections as the indirect method requires. The standard on the statement of cash flows (FASB Accounting Standards Codification Topic 230) excludes investing and financing activities that involve no cash from the amounts in the statement and requires them to be disclosed separately, while the indirect method adjusts net income for noncash items, such as depreciation and the loss on a disposal, to reach operating cash flow.
Measure the cash the company holds net of the recorded sales tax payable, the tax billed on its invoices less the tax on credit memos, which is not the same as the tax collected. Find in DebtSchedule the principal of the notes due within a year, counting only the notes outstanding at the date measured. Explain what each means for the balance sheet and for the committee’s view of the company’s cash.

Requirement 3: Measure how fast working capital turns (Chapters 14 and 15; managerial accounting).

Write measures for days sales outstanding (receivables in days of billing), days inventory outstanding (inventories in days of cost of goods sold), days payables outstanding (payables in days of supplier invoices), and the cash conversion cycle (the first two less the third). Each divides a balance at the end of the period by the ledger flow of the twelve months that end on that date, times 365, and each is blank until the twelve months exist and after Data Through, as Tutorial 15.3 guarded its trailing measure.
Add days of supply for materials, against what production issued, and for finished goods, against what was shipped, so that each can be compared with the TargetDaysSupply of its items. The items’ targets differ, so for these company-level measures use the largest target of each class as a benchmark, label it as one, and list the targets behind it. A company-level comparison cannot show whether each item meets its own policy.
On one page, let a field parameter switch a line chart among the measures by quarter, as in Exercise 15.3, and add a report-page tooltip that shows the balance and the twelve-month flow behind each point. Add a matrix of all the measures at each quarter-end, with icons that compare the days of supply with those benchmarks.
Recompute days sales outstanding and days payables outstanding without the lines of the opening entry, and compare each measure at the end of 2024 with a version on the average balance of the year. Explain which version the committee should see, what 2024 can and cannot show, and which component moved the cycle.

Requirement 4: Trace the materials build and set a target for 2027 (Chapters 13 to 15; managerial accounting).

Chart the materials received and issued by month, with the receipts split by channel, and the build of fiscal 2026 by item group and item. Explain which channel and which items carry the build. The item chart shows receipts less issues during the year, not each item’s stock, because the opening materials carry no item detail.
Reference Requisitions in a query that keeps the work-order shortfall requisitions and groups them by their justification, which names one work order and one item, keeping the work order and the item in each group: the number of requisitions, their quantity and estimated value, and their first and last dates. Build the same text for each issue line from its work order and item with Text.From, as Tutorial 16.2 built a document number, group the issues by it, and merge the issued quantity into the groups. Compare what was requisitioned with what those work orders used, count the work order and item pairs requisitioned more than once, and show who approved the requisitions of each channel.
Add a what-if parameter for the target days of materials supply, as in Exercise 15.4, with the largest materials target as its default, a benchmark for the whole balance, and measures for the materials that the target allows at the current rate of issue, the excess above it, and the months of issues the excess represents. Test targets of 28, 60, and 90 days, and compare the excess with the year’s fall in cash. Every policy in the data ends with fiscal 2026, so the target for 2027 is the controller’s to approve.
State what the estimate assumes and leaves out, among them the finished goods and the requisitions approved but not yet ordered at year-end, and how quickly the cash could come back.

Requirement 5: Test the balances behind the report (Chapters 14 to 16; auditing).

In a matrix, break down by SourceDocumentType the balances at the end of fiscal 2026 of receivables (1020), finished goods and materials (1040 and 1045), payables (2010 and 2020), and sales tax payable (2050). The matrix only decomposes the ledger. Trace each part posted by a journal entry to its entry, and tell the opening balances that no subledger document in the extract supports from the reclassifications.
Reconcile receivables and payables to their open items at the end of fiscal 2026, and explain each difference. Compare what the ledger billed each year with the invoices dated in that year, and explain the differences with the cutoff findings of Chapters 6 and 12. Age the open payables, leaving out the invoices that the ledger has already moved to notes payable.
Test account 2050 Sales Tax Payable: what posts to it, what never does, and what evidence outside the database would show whether the tax was remitted.
Record each reconciling item and finding in a Findings table, entered with Enter data, with its account, amount, and disposition in Chapter 8’s terms, as Chapter 16 kept its dispositions. If you worked the Part III case, carry the adjustments it proposed into the table as proposals, without posting them to this file. Add tests to the Tests tab: each difference equals the items recorded for it, the bridge ends at the change in cash, and each working-capital measure of Requirement 3 is blank for the quarters after Data Through.

Requirement 6: Write the decision memo. Write a memorandum of no more than two pages to the controller, for her meeting with the audit committee, organized in the order of her questions:

Financial reporting: where the cash went, the restated sections with their noncash disclosure, and what the balance sheet needs: the sales tax, the notes due within a year, and the balances that no document in the extract supports.
Managerial accounting: which balances absorbed the cash and how fast each turns, what drove the materials build, and a 2027 target with its cash effect and its assumptions.
Auditing: the reconciliations and findings with their dispositions, the evidence outside the database that would settle each, the controls over requisitions that the build points to, and the controls the report itself needs before the committee relies on it every quarter, including what must be updated by hand for a later quarter, since a refresh alone does not settle an earlier finding.

Close the memo with your recommendation.

Deliverable. A decision memorandum of no more than two pages, with an appendix of at most three pages of exhibits, each large enough to read and each naming the report page that holds its complete schedule: the cash bridge for fiscal 2025 and 2026, the restated cash flow sections, the working-capital measures by quarter, the materials build by channel with the 2027 target, and the findings with their dispositions. Submit Charles River Cash Case.pbix as well, with its About page, Validation page, Tests tab, and Data Through card.

What a Strong Submission Includes

A file that another person could refresh and check: named queries, the closing entries flagged, control totals that agree, a Tests tab that passes, and the date through which the data run on the page the reader sees.
A cash bridge that ends exactly at the change in cash, with the entries that moved no cash found and presented as the standard requires.
Working-capital measures with stated definitions, computed on full twelve-month windows, and honest about what fiscal 2024 and the unsupported balances do to them.
A materials build traced to the documents behind the receipts, and a 2027 target with its cash effect and its assumptions.
Receivables and payables reconciled to their open items, the materials flows tied to the ledger, every difference explained by recorded reconciling items, and each balance without support recorded with a disposition worded as a finding.
A memo written for the controller and the committee: concise, organized by perspective, careful with its words, and ending with a recommendation she can act on.

Connecting the Dots

Much of this case reconciled one view of the company with another: profit with cash, the ledger with its subledgers, the purchases made with the plan they were meant to follow, and a report with its own tests. Each Part of this book has done that work with one tool, the workbook, the query, and the report. Part V offers three capstone cases that draw on every tool of the book.