Skip to main content

7. Explain Profit, Cash, and the Year-End Close

The controller is preparing the year-end management review. Management wants an explanation of performance, cash movement, and obligations. A collection of reports is not enough: you must reconcile the periods, trace selected source evidence, and decide which unresolved matters deserve attention first. Predict why closing revenue and expense accounts should not itself create or consume cash.

Worked scope. Fourth-quarter 2026 activity and December 31 balances. Opening balances include all recorded history before October 1, including the opening journal and earlier years. The fixed-asset extension follows asset 18 across its 2026 lifecycle. Exclude the working file's later 2027 supplier payments from year-end balances.

Choose Excel or SQL. The site retains your selection between lessons and cases.

Preparation: 6. Find the Accounting Entries, 11. Follow an Estimate into Settlement, 12. Turn Records into a Conclusion, Case 1: customer account review (recommended), Case 3: supplier obligations (recommended). Use the Excel setup or SQL setup for your chosen tool.

Ready to begin:

  • Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
  • Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
  • Retain full ledger history for openings and distinguish pre-close performance, cash movements, balance snapshots, and complete closing vouchers.

Key Data Sources​

Each source below has a distinct row meaning. Import only the fields and populations requested in each stage. Working tables default to the source name plus Data unless a stage assigns a more specific name.

SourcePublished Excel TableFirst working name
GLEntryT3_GLEntryGLEntryData
AccountT1_AccountAccountData
JournalEntryT2_JournalEntryJournalEntryData
GoodsReceiptT35_GoodsReceiptGoodsReceiptData
GoodsReceiptLineT36_GoodsReceiptLineGoodsReceiptLineData
PurchaseInvoiceT37_PurchaseInvoicePurchaseInvoiceData
PurchaseInvoiceLineT38_PurchaseInvoiceLinePurchaseInvoiceLineData
FixedAssetT40_FixedAssetFixedAssetData
FixedAssetEventT41_FixedAssetEventFixedAssetEventData
DebtAgreementT42_DebtAgreementDebtAgreementData
DebtScheduleLineT43_DebtScheduleLineDebtScheduleLineData
Import, merge, group, and retain evidence

Use Microsoft 365 desktop and a separate analysis workbook. Keep the released workbook unchanged. The table above names the exported Excel Tables, which differ from worksheet names. For first-time setup, follow Set Up Excel.

  1. Import: On Windows, choose Data → Get Data → From File → From Workbook. On Mac, choose Data → Get Data (Power Query) → Excel workbook. Select the published workbook, choose the named table in Navigator, then Transform Data. Rename the query in Query Settings. Set identifiers to Whole Number, dates to Date, and amounts/hours to Decimal Number. Keep reference numbers as text. Apply the stage’s filters before loading large tables.
  2. Merge. In the Power Query editor choose Home → Merge Queries → Merge Queries as New. Select the two queries and matching key columns. For multiple keys, select them in the same order with Ctrl on Windows or Command on Mac. Use the join type specified in the stage. Expand only the required fields using the button on the merged column. Clear Use original column name as prefix when a stage uses bare column names, and rename summary columns exactly as specified. Compare row counts before and after expansion. A lookup requires a unique key, while a one-to-many relationship needs a merge or a separate aggregate.
  3. Group: Choose Home → Group By. Use Advanced for multiple keys or totals. Enter each output name, operation (Sum or Count Rows), and source column explicitly. Group each detail source before joining independent totals. Right-click a query and choose Reference when building a new summary from its existing filters.
  4. Load. Load the final query as a table on a new worksheet. On Windows, Close & Load To can leave staging queries as connection-only. On Mac, use the editor’s Close & Load for the final result. Select the loaded table and set its Table Name to the name used in the formulas. Keep source queries in the editor so filters and relationships remain inspectable. Microsoft documents the Mac editor and loading workflow and matching multiple merge keys.
  5. Summarize. Select a working table, choose Insert → PivotTable, and place the specified fields in Rows and Values. In Value Field Settings, count identifiers and sum amounts. Ordinary SUM includes filtered-out rows. Use SUBTOTAL(109,table[column]) for a visible sum or SUMIFS for an explicit population. Refresh queries and PivotTables after changing a filter.
  6. Retain. Save each session’s workbook and review notes. Record the source file, cutoff, key fields, row meaning, and checks. SQL learners save each supplied statement and its result from DB Browser’s Execute SQL tab. Run one statement at a time using the cases' read-only queries. Follow Save the Result Not Just the Query to retain and verify the exported rows.

Copy one formula at a time. For a block labeled Excel, create the named column and paste into its first data cell, or use the named worksheet cell. If a table formula does not fill down automatically, copy it through the remaining data rows. Paste a block labeled Power Query into Add Column → Custom Column in the query editor without a leading equals sign. Resolve No match results before using a lookup in another calculation.

The examples use English function names and comma separators. Regional Excel installations may use translated names or semicolons with the same fields and criteria. Investigate a blank relationship or date before converting it to zero.

Session 1 — Establish opening, movement, and closing balances​

Create Case07.xlsx or Case07.sql. This case begins with the complete ledger, then summarizes before loading results. Keep one definition of the quarter and one sign convention.

How do source events reach the management review?

Reading guide: Arrows show steps in the investigation. Confirm record keys, dates, and scope before interpreting a result.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words
  • Operational documents and finance journals produce ledger entries.
  • The ledger supplies period activity and cumulative account balances.
  • Statement definitions select and classify those amounts for interpretation.

Conceptual teaching diagram. Arrows show steps in the investigation. Confirm record keys, dates, and scope before interpreting a result.

1.1. Build openings from the full history​

Predict before you run: What would be missing if you imported only October–December?

Named inputs: LedgerHistory ← T3_GLEntry and AccountData ← Account (import here).

Produced outputs: LedgerHistory: full history through December 31, 2026; retained ungrouped in Power Query, Rollforward: one row per account; only summary loaded.

  1. Import T3_GLEntry into Power Query as LedgerHistory and import Account as AccountData.

  2. Keep PostingDate, AccountID, Debit, Credit, SourceDocumentType, SourceDocumentID, VoucherNumber, GLEntryID, and Description; the optional asset trace needs Description.

  3. Set PostingDate to Date and monetary columns to Decimal Number.

  4. Filter only the upper boundary: PostingDate on or before December 31, 2026.

  5. Do not add an October lower boundary.

  6. In Power Query choose Add Column → Custom Column and add DebitNet using the expression below; Power Query expressions do not start with an equals sign:

    Power Query — LedgerHistory / DebitNet
    [Debit]-[Credit]

  7. Add OpeningDebitNet:

    Power Query — LedgerHistory / OpeningDebitNet
    if [PostingDate] < #date(2026,10,1) then [DebitNet] else 0

  8. Add QuarterDebitNet:

    Power Query — LedgerHistory / QuarterDebitNet
    if [PostingDate] >= #date(2026,10,1) then [DebitNet] else 0

  9. Reference this query as Rollforward.

  10. Group by AccountID, summing OpeningDebitNet, QuarterDebitNet, and DebitNet (name the last ClosingDebitNet).

  11. Merge AccountData on AccountID and expand AccountNumber, AccountName, NormalBalance.

  12. Sort AccountNumber and load only this summary as Rollforward.

  13. Keep LedgerHistory as connection-only on Windows; on Mac retain it in the editor and load the grouped result, without loading raw ledger rows to a sheet.

Checkpoint. 71 accounts have postings. The source has 732,310 rows before October 1 and 83,085 in the quarter. Cash opens at 9,185,795.46, changes by −743,539.66, and closes at 8,442,255.80.

A cumulative balance needs prior history. Filtering the source to the quarter would make the opening balance zero and turn the quarter’s movement into a false ending balance.

If your result differs

Check the Applied Steps list for an accidental lower-date filter. Numeric source dates must be converted to Date before comparison.

Keep for the next stage: Save Rollforward, source date boundaries, and row-count evidence.

1.2. Check the rollforward and signs​

Predict before you run: Why is a normal credit-balance liability negative in this schedule?

Named inputs: Rollforward (Session 1.1).

Produced outputs: Rollforward: arithmetic Check and NormalClosing display columns, Review: three ledger balance checks.

  1. In Rollforward add Check:

    Excel — Rollforward[Check]
    =ROUND([@OpeningDebitNet]+[@QuarterDebitNet]-[@ClosingDebitNet],2)

  2. On Review label B2 Opening ledger balance, B3 Quarter ledger movement, and B4 Closing ledger balance.

  3. Enter each total and confirm it is within one cent of zero:

    Excel — Review!B2
    =SUM(Rollforward[OpeningDebitNet])
    Excel — Review!B3
    =SUM(Rollforward[QuarterDebitNet])
    Excel — Review!B4
    =SUM(Rollforward[ClosingDebitNet])

  4. Keep the debit-net columns unchanged.

  5. For a management display add NormalClosing:

    Excel — Rollforward[NormalClosing]
    =IF([@NormalBalance]="Credit",-[@ClosingDebitNet],[@ClosingDebitNet])

  6. Filter to account numbers 1020, 2010, 2020, 2040, and 2060 to inspect the control accounts.

Checkpoint. Closing debit-net values include AR 4,798,804.32; AP −6,237,466.66; GRNI −1,020,992.06; accrued expenses −480,552.36; customer deposits −3,413.46. All 71 opening-plus-movement checks are zero to cents.

A negative debit-net AP balance is the expected credit balance. Changing signs selectively inside the underlying rollforward would destroy the accounting identity.

If your result differs

Do not compare a credit-normal report balance to the raw debit-net schedule without reversing its sign.

Keep for the next stage: Retain the sign definition and selected control-account display.

Session stopping point

Save the 71-account schedule and its checks. You now have a reliable period boundary for the remaining sessions.

Session 2 — Explain profit and cash differences​

Calculate profit before closing entries, then reconcile it to the cash movement through changes in other accounts. This arithmetic bridge is broader than an operating-cash subtotal.

Which history belongs in each column?

Reading guide: Opening balance plus in-scope movement equals closing balance. State the cutoff and treatment of closing entries.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words
  • Opening: all postings before October 1, 2026.
  • Movement: October 1–December 31, with close treatment stated.
  • Closing: opening plus movement through December 31.

Selected released evidence and teaching explanation. Opening balance plus in-scope movement equals closing balance. State the cutoff and treatment of closing entries.

2.1. Calculate pre-close performance and cash activity​

Predict before you run: Would including the annual closing entry make December’s operating result meaningful?

Named inputs: LedgerHistory and AccountData (Session 1.1), JournalEntryData ← JournalEntry (import here; full history retained).

Produced outputs: LedgerPrepared: full-history detail with account and conditional journal fields, QuarterLedger: all account types in Q4, excluding close; retained ungrouped, ProfitDetail: pre-close P&L detail, ProfitSummary: three monthly rows, CashActivity: Q4 cash by source type.

  1. Import JournalEntry as JournalEntryData and retain its full history.

  2. Reference LedgerHistory as LedgerPrepared; merge AccountData and expand AccountNumber, AccountName, AccountType, and AccountSubType.

  3. Keep this full-history detail ungrouped.

  4. In Power Query add JournalKey with Add Column → Custom Column:

    Power Query — ProfitDetail / JournalKey
    if [SourceDocumentType] = "JournalEntry" then [SourceDocumentID] else null

  5. Left merge JournalEntryData on JournalKey = JournalEntryID and expand EntryType; do not merge every source ID to journals.

  6. Reference LedgerPrepared as QuarterLedger; filter PostingDate October 1–December 31, 2026, and exclude EntryType beginning Year-End Close while keeping null EntryType.

  7. Preserve QuarterLedger ungrouped, with all account types.

  8. Reference QuarterLedger as ProfitDetail and filter AccountType Revenue or Expense and AccountSubType not Header.

  9. Add a Custom Column named ProfitAmount:

    Power Query — ProfitDetail / ProfitAmount
    [Credit]-[Debit]

  10. Reference ProfitDetail as ProfitSummary.

  11. Derive Month using Transform → Date → Month → Start of Month and group by Month summing ProfitAmount into a column named ProfitAmount.

  12. Load the three-row summary as ProfitSummary.

  13. For CashActivity, reference QuarterLedger, filter AccountNumber 1010, group by SourceDocumentType summing Debit, Credit, and DebitNet into columns with those same names.

  14. Load as CashActivity.

Checkpoint. Pre-close profit: October 255,812.14; November 405,751.40; December 502,693.70; quarter 1,164,257.24. Cash falls 743,539.66. Customer cash receipts add 7,816,054.56; supplier payments use 5,912,239.65.

Profit and cash are different measures of the same business period. Customer receipts and supplier payments may settle invoices or obligations from earlier periods. The annual close must not erase the quarter’s performance measure.

If your result differs

Preserve null journal matches for operational events. Filtering null EntryType out would remove much of the ledger.

Keep for the next stage: Save monthly Profit and CashActivity with the close exclusion documented.

2.2. Construct the complete arithmetic bridge​

Predict before you run: Which other account changes explain the difference?

Named inputs: QuarterLedger, ProfitSummary, and CashActivity (Session 2.1).

Produced outputs: BridgeEffects: nonzero Q4 noncash/non-P&L effects, Review: profit-to-cash reconciliation and chart.

  1. Reference the retained QuarterLedger as BridgeEffects; it already has the conditional journal join and close exclusion, with all account types still present.

  2. Keep AccountNumber not 1010 and AccountType other than Revenue or Expense.

  3. In Power Query add BridgeEffect with Add Column → Custom Column:

    Power Query — Bridge effects / BridgeEffect
    [Credit]-[Debit]

  4. Group by AccountNumber and AccountName, summing BridgeEffect into a column with the same name.

  5. Add NonzeroEffect with the expression below and filter it to true:

    Power Query — Bridge effects / NonzeroEffect
    Number.Round(Number.Abs([BridgeEffect]),2) > 0

  6. Load as BridgeEffects.

  7. On Review label B8 Quarter pre-close profit and enter:

    Excel — Review!B8
    =SUM(ProfitSummary[ProfitAmount])

  8. Place the 19 effect rows beneath the profit explanation.

  9. Label B9 Calculated cash movement and enter:

    Excel — Review!B9
    =SUM(B8,BridgeEffects[BridgeEffect])

  10. Label B10 Ledger cash movement and enter:

    Excel — Review!B10
    =SUM(CashActivity[DebitNet])

  11. Label B11 Bridge difference and enter:

    Excel — Review!B11
    =ROUND(B9-B10,2)

  12. Chart the signed effects using a column chart, clearly separating starting profit and calculated cash movement from individual effects.

Checkpoint. Nineteen nonzero account effects sum to −1,907,796.90. Added to profit of 1,164,257.24, they reconcile to −743,539.66 cash movement. Inventory and WIP increases contribute −2,872,614.48; the AP increase contributes +411,484.70.

This bridge explains the accounting difference. It does not mean every inventory increase was paid in cash or every liability increase is an operating cash inflow. The published cash-flow statement applies its own operating, investing, and financing classifications.

If your result differs

Do not use post-close retained-earnings movement as an additional profit effect: that would count annual profit again.

Keep for the next stage: Save the zero-difference bridge and identify the largest three effects.

2.3. Trace one liability movement to source documents​

Predict before you run: Can the large GRNI entries be explained by a receipt and a later invoice?

Named inputs: LedgerPrepared (full-history detail from Session 2.1), PurchaseInvoiceData ← PurchaseInvoice and PurchaseInvoiceLineData ← PurchaseInvoiceLine (import here), GoodsReceiptData ← GoodsReceipt and GoodsReceiptLineData ← GoodsReceiptLine (import here).

Produced outputs: LiabilityTrace: selected December 2026 2020 ledger rows, ReceiptInvoiceTrace: invoice 39692 / receipt support.

  1. Reference LedgerPrepared as LiabilityTrace and filter AccountNumber 2020 and December 1–31, 2026.

  2. In Power Query add a Custom Column named AbsoluteAmount:

    Power Query — Selected liability trace / AbsoluteAmount
    Number.Abs([DebitNet])

  3. Sort AbsoluteAmount descending then GLEntryID and keep the first five rows.

  4. Import PurchaseInvoice as PurchaseInvoiceData, PurchaseInvoiceLine as PurchaseInvoiceLineData, GoodsReceipt as GoodsReceiptData, and GoodsReceiptLine as GoodsReceiptLineData.

  5. Follow GoodsReceipt 27267 and PurchaseInvoice 39692 into these headers and lines.

  6. Reference PurchaseInvoiceLineData as ReceiptInvoiceTrace, filter PurchaseInvoiceID 39692, merge GoodsReceiptLineData on GoodsReceiptLineID, then GoodsReceiptData on GoodsReceiptID.

Checkpoint. Receipt 27267 records 34,472.46 on December 25; invoice 39692 clears the same GRNI amount December 31. The line relationship identifies the receipt match. The supplier invoice header is dated December 30 while its GRNI ledger posting is December 31; retain both dates rather than silently substituting one.

The matching amounts and explicit link support a transition from GRNI into AP, not payment. That distinction helps explain why a liability balance can change even without immediate cash movement.

If your result differs

Matching amounts alone are insufficient. Require the GoodsReceiptLineID relationship.

Keep for the next stage: Save the two-document trace and identify the next table needed to investigate payment.

Session stopping point

Save the profit, cash, full bridge, and one source trace. Draft a plain-language explanation of why profit and cash moved differently.

Session 3 — Separate annual close from quarterly performance​

The close transfers a year’s profit into equity. Its scope is the entire year, even though its posting date falls in the fourth quarter.

What does the annual closing sequence do?

Reading guide: Read document dates and accounting effects separately. The timeline does not create additional database relationships.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words
  • Journal 850 closes annual revenue and expense balances into income summary.
  • Journal 851 transfers 4,503,611.24 from income summary to retained earnings.
  • Quarterly performance remains the pre-close activity, not the transfer to equity.

Selected released evidence and teaching explanation. Read document dates and accounting effects separately. The timeline does not create additional database relationships.

3.1. Inspect both closing journals​

Predict before you run: Should the equity transfer equal fourth-quarter profit or full-year profit?

Named inputs: JournalEntryData and LedgerPrepared (full-history queries from Session 2.1).

Produced outputs: ClosingJournals: two December 31, 2026 close headers, ClosingJournalDetail: retained full selected vouchers, ClosingJournalSummary: journal/account amounts, FullYearPerformance: retained 2026 P&L detail; AnnualPreClose and AnnualPostClose: separate annual-profit summaries.

  1. Reference the raw JournalEntryData as ClosingJournals and filter PostingDate December 31, 2026 and EntryType beginning Year-End Close.

  2. Reference LedgerPrepared as ClosingJournalDetail, filter SourceDocumentType JournalEntry, and Inner merge ClosingJournals on SourceDocumentID = JournalEntryID.

  3. Keep all account lines; AccountNumber and DebitNet are already present from LedgerPrepared.

  4. Expand JournalEntryID from ClosingJournals and keep the existing EntryType; retain SourceDocumentType and SourceDocumentID as the source pair.

  5. Reference ClosingJournalDetail as ClosingJournalSummary and group by JournalEntryID, EntryType, AccountNumber, summing DebitNet.

  6. Separately reference LedgerPrepared as FullYearPerformance and filter PostingDate January 1–December 31, 2026, without a quarter restriction.

  7. Add ProfitAmount using the expression below:

    Power Query — Full-year performance / ProfitAmount
    [Credit]-[Debit]

  8. Filter AccountType Revenue or Expense and AccountSubType not Header.

  9. Add a custom column ReviewYear with the constant 2026.

  10. Preserve FullYearPerformance and create two references: AnnualPreClose excludes EntryType beginning Year-End Close while keeping null EntryType; AnnualPostClose keeps closing entries.

  11. In each reference Group By ReviewYear and sum ProfitAmount as AnnualProfit.

  12. Keep both one-row results for comparison.

Checkpoint. Journals 850 and 851 transfer annual pre-close profit of 4,503,611.24. The annual post-close revenue/expense net is zero. This is different from fourth-quarter profit of 1,164,257.24.

A zero post-close P&L balance confirms the transfer for the selected definition without implying that the business earned no profit. Do not add the annual transfer to the quarter’s operating result.

If your result differs

An annual close cannot be checked against a quarter-only P&L. Keep annual and quarterly schedules distinctly labeled.

Keep for the next stage: Save the closing sequence and annual comparison.

Session stopping point

Save the annual close workpaper. Explain why the retained-earnings movement uses a different period from the management performance discussion.

Session 4 — Reconcile reports and advise management​

Use published reports as an independent presentation check after building the analysis. Align their definitions and periods before comparing amounts.

How should management read the quarter?

Reading guide: These measures are compared side by side. Their different definitions and grains do not form a transaction sequence.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words
  • Pre-close profit is 1,164,257.24.
  • Other account effects explain why cash movement is −743,539.66.
  • Cash closes at 8,442,255.80; outstanding obligations require separate follow-up.

Selected released evidence and teaching explanation. These measures are compared side by side. Their different definitions and grains do not form a transaction sequence.

4.1. Match the published statement definitions​

Predict before you run: Which report rows can be summed across months, and which are ending balances?

Named inputs: Existing Monthly Income Statement report download, Existing Monthly Indirect Cash Flow report download.

Produced outputs: StatementComparison: Q4 2026 income/cash movements and October/December cash endpoints.

  1. From Reports and Analytics open Monthly Income Statement and Monthly Indirect Cash Flow in the Statements and Cash group.

  2. Download their existing Excel or CSV files.

  3. Filter FiscalYear 2026 and FiscalPeriod 10,11,12.

  4. For income select only the Net Income subtotal.

  5. For cash select Net Change in Cash; sum the three monthly movements.

  6. Read Beginning Cash from October only and Ending Cash from December only.

  7. Retain those filters and the report title on Review.

  8. Do not sum monthly balance snapshots.

Checkpoint. Published Net Income agrees with the three monthly profit values. Cash movements are −44,374.25, −203,901.73, and −495,263.68, summing to −743,539.66. October beginning cash and December ending cash agree with the rollforward.

Matching totals support the reconciliation on these definitions. They do not prove every underlying transaction is error-free. The source trace and unresolved items remain part of the review.

If your result differs

Choose actual reports, not the pro forma reports. Do not add both subtotal and detail rows.

Keep for the next stage: Save a compact report tie-out table with difference columns.

4.2. Write the management explanation​

Predict before you run: Which matters deserve action first rather than another report?

Named inputs: Review rollforward, profit/cash bridge, close checks, and StatementComparison (earlier stages).

Produced outputs: Review: management explanation and unresolved-items table.

  1. Create a one-page Review with quarter profit, cash movement, opening/closing cash, and the largest bridge effects.

  2. Add an unresolved-items table with Evidence, Interpretation, MissingEvidence, Owner, and NextAction.

  3. Write a 600–800 word management memo explaining performance, cash, obligations, and the annual close.

  4. Distinguish confirmed arithmetic, a supported source explanation, and a management hypothesis.

Checkpoint. The worked explanation emphasizes inventory accumulation and the timing of supplier and other obligations, while preserving positive pre-close profit and falling cash as compatible facts.

Recommend reviewing the drivers of inventory growth and the payment outlook rather than treating profit as available cash. State what additional aging, inventory, or operating evidence is needed before making a liquidity or purchasing decision.

If your result differs

Do not present a selected document trace as proof of every control-account balance.

Keep for the next stage: Save the memo, bridge, report comparison, and source workpaper.

Session stopping point

The completed review should stand on its own: definitions, reproducible schedules, source evidence, limitations, and an action priority.

Prepare the Decision​

The quarter produces positive pre-close profit while cash falls. The complete ledger bridge explains that difference and the selected receipt/invoice trace illustrates a liability transition. The annual closing entries transfer full-year profit to retained earnings without creating cash. Management can now discuss inventory and obligation timing using reconciled evidence, while recognizing that an arithmetic tie-out alone does not establish transaction validity.

Deliverable: A full-history balance rollforward, profit/cash bridge, selected source reconciliation, and management review memo. Include the source file, selection dates, source IDs, measure definitions, checks, evidence limitations, and the next action. A clearly ordered SQL result table can support the conclusion, with a chart added if useful.

Independent Challenge​

Apply the framework to fourth-quarter 2025 and December 31, 2025. Build openings from earlier history, exclude annual close from period performance, trace a material bridge item, and explain which management priority changes.

Planning hint

Do not reuse the worked opening balances. Reconstruct all earlier history through September 30, 2025 and use the 2025 closing journals. Select an asset lifecycle that actually occurs in the relevant records if you extend the case.

Assessment criteria: Assess opening history, signs, close exclusions, complete cash bridge, matched report definitions, source support, and priorities that reflect uncertainty. Your instructor evaluates the method, evidence, and reasoning. Model answers for this challenge remain in private course materials.

Worked extension — Trace a financed asset​

Asset 18 is the Finishing Booth Upgrade and Air Handling Retrofit. Its 280,000 cost enters the 2026 records before the fourth quarter. Follow acquisition, financing, depreciation, and debt payments without treating a note-financed acquisition as immediate cash expenditure.

How does a financed asset affect cost and cash?

Reading guide: Asset acquisition and financing lead to separate depreciation and debt-service evidence; separate principal from interest.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words
  • Receipt 15616 records the asset; invoice 21692 creates AP.
  • Journal 445 replaces AP with a 280,000 note.
  • Depreciation records asset use; debt payments separate principal and interest.

Selected released evidence and teaching explanation. Asset acquisition and financing lead to separate depreciation and debt-service evidence; separate principal from interest.

Named inputs: FixedAssetData ← FixedAsset and FixedAssetEventData ← FixedAssetEvent (import here; use selected references), DebtScheduleLineData ← DebtScheduleLine (import here; raw query retained), LedgerHistory (full-history query from Session 1.1).

Produced outputs: AssetReview: asset 18 and depreciation formula, AssetLedgerExcerpt: selected source rows, with partial voucher labeled, DebtReview: agreement 2 payment schedule and Q4 principal/interest check.

  1. Import FixedAsset as FixedAssetData, FixedAssetEvent as FixedAssetEventData, and DebtScheduleLine as DebtScheduleLineData; preserve the raw queries.

  2. Reference FixedAssetData and filter FixedAssetID 18; filter a separate FixedAssetEventData reference to that same ID.

  3. Reference DebtScheduleLineData and filter DebtAgreementID 2 and PaymentDate through December 31, 2026.

  4. Load the selected asset as AssetReview and add MonthlyDepreciation:

    Excel — AssetReview[MonthlyDepreciation]
    =ROUND(([@OriginalCost]-[@ResidualValue])/[@UsefulLifeMonths],2)

  5. This asset begins depreciation in the month after its January in-service date.

  6. Reference LedgerHistory as AssetLedgerExcerpt and filter document pairs GoodsReceipt/15616, PurchaseInvoice/21692, JournalEntry/445, and the two JournalEntry/685 rows whose Description names this asset.

  7. Keep the last two as an excerpt of the larger depreciation voucher.

  8. Reference the selected debt schedule as DebtReview and load it.

  9. Filter PaymentDate to October–December 2026.

  10. On Review label B20 Principal, B21 Interest, B22 Payment, and B23 Payment difference.

  11. Enter each formula:

    Excel — Review!B20
    =SUBTOTAL(109,DebtReview[PrincipalAmount])
    Excel — Review!B21
    =SUBTOTAL(109,DebtReview[InterestAmount])
    Excel — Review!B22
    =SUBTOTAL(109,DebtReview[PaymentAmount])
    Excel — Review!B23
    =ROUND(B20+B21-B22,2)

Worked check. Asset cost is 280,000 and monthly depreciation 2,333.33. December’s asset-specific depreciation debits manufacturing cost clearing, not cash. The note-financing entry has no cash line. Q4 debt-2 payments total 16,396.26: principal 12,500.72 and interest 3,895.54. Principal reduces debt, while interest has a separate expense and cash classification. The description-based depreciation excerpt is not a unique formal asset-to-ledger key.

Further queries and earlier case topics

These canonical queries extend the investigation. Their populations, dates, and measures may differ from the worked case, so read their definitions before comparing results. These optional references extend the complete guided routes above. Earlier specialist topics consolidated into this case remain accessible through this reference sequence.

Choose a reference after completing the guided investigation and verify its scope before comparing totals.

  1. Trial Balance by Period

    Optional extension; compare this query’s scope with the guided case.

  2. Control Account Reconciliation

    Optional extension; compare this query’s scope with the guided case.

  3. Journal and Close Cycle Review

    Optional extension; compare this query’s scope with the guided case.

  4. Retained Earnings and Close Entry Impact

    Optional extension; compare this query’s scope with the guided case.

  5. Annual Income to Equity Bridge

    Optional extension; compare this query’s scope with the guided case.

  6. Post Close Profit and Loss Leakage Review

    Optional extension; compare this query’s scope with the guided case.

  7. Annual Net Revenue Bridge

    Optional extension; compare this query’s scope with the guided case.

  8. Invoice Revenue Cutoff Exception Summary

    Optional extension; compare this query’s scope with the guided case.

  9. Invoice Revenue Cutoff Exception Trace

    Optional extension; compare this query’s scope with the guided case.

  10. Fixed Asset Rollforward by Behavior Group

    Optional extension; compare this query’s scope with the guided case.

  11. Capex Acquisitions Financing and Disposals

    Optional extension; compare this query’s scope with the guided case.

  12. Debt Amortization and Cash Impact

    Optional extension; compare this query’s scope with the guided case.

  13. Manufacturing Cost Component Bridge

    Optional extension; compare this query’s scope with the guided case.

  14. Cash Flow Statement Indirect Monthly

    Optional extension; compare this query’s scope with the guided case.

  15. Working Capital Bridge by Month

    Optional extension; compare this query’s scope with the guided case.

  16. AR Aging Open Invoices

    Optional extension; compare this query’s scope with the guided case.

  17. Customer Deposits and Unapplied Cash Aging

    Optional extension; compare this query’s scope with the guided case.

  18. AP Aging Open Invoices

    Optional extension; compare this query’s scope with the guided case.

  19. Cash Conversion Timing Review

    Optional extension; compare this query’s scope with the guided case.

  20. Payroll Liability Rollforward

    Optional extension; compare this query’s scope with the guided case.

  21. Payroll Cash Payments and Remittances

    Optional extension; compare this query’s scope with the guided case.

  22. Accrued Expense Rollforward

    Optional extension; compare this query’s scope with the guided case.

  23. Accrued vs Invoiced vs Paid Timing

    Optional extension; compare this query’s scope with the guided case.

  24. Sales Commission Payable Rollforward

    Optional extension; compare this query’s scope with the guided case.

  25. Budget vs Actual Working Capital and Cash Bridge

    Optional extension; compare this query’s scope with the guided case.

  26. Customer Deposits and Unapplied Cash Exception Review

    Optional extension; compare this query’s scope with the guided case.

  27. Over Under Accrual Review

    Optional extension; compare this query’s scope with the guided case.

Next Steps​

Continue to Investigate Exceptions and Recommend Controls. Keep your completed evidence and use the Query Library for a separate extension.

Notice a mistake?Report an error on this page