Transaction Processing and Financial Statements

Introduction

Accounting applications transform source documents into reliable financial statements. In a spreadsheet environment, this transformation must be more than a collection of formulas. It should be a controlled processing system in which transactions are authorized, validated, classified, posted, reconciled and reported through an auditable sequence.

Excel is particularly useful for small organizations, accounting simulations, analytical schedules and prototypes. However, spreadsheets do not automatically provide the access controls, referential integrity or posting discipline of an enterprise accounting system. Effective design therefore requires explicit validation rules, protected formulas, control totals, reconciliations and review procedures.

The accounting model remains based on the fundamental equation:

For every journal entry, total debits must equal total credits:

These equations provide both conceptual foundations and practical control tests.

Learning Objectives

After studying this chapter, students should be able to:

1. Design a controlled transaction-processing workbook.
2. Validate and post journal entries to ledger accounts.
3. Build subsidiary schedules for receivables, payables and inventory.
4. calculate payroll, depreciation and bank reconciliation adjustments.
5. Produce and test an adjusted trial balance.
6. Prepare an income statement, statement of changes in equity, balance sheet and cash flow statement.
7. Apply spreadsheet governance, audit-trail and ethical principles.
8. Evaluate the limitations of Excel-based accounting applications.

1. The Accounting Processing Cycle

Transaction processing converts economic events into accounting information. The major stages are:

1. Capture: Record information from invoices, receipts, timesheets, bank records and other evidence.
2. Validate: Test dates, amounts, account codes, authorization and document uniqueness.
3. Classify: Assign accounts, departments, tax codes, customers or suppliers.
4. Journalize: Represent each event as balanced debit and credit lines.
5. Post: Aggregate journal lines into general and subsidiary ledgers.
6. Adjust: Recognize accruals, prepayments, depreciation and other period-end amounts.
7. Reconcile: Compare independent records and investigate differences.
8. Report: Generate the trial balance and financial statements.
9. Close and archive: Lock the period, preserve evidence and carry balances forward.

Figure: A controlled transaction-processing cycle from source evidence to period closure.

The cycle is not strictly linear. Review may identify errors requiring correction at an earlier stage. Corrections should normally be made through authorized adjusting or reversing entries rather than by overwriting original records.

2. Workbook Architecture and Data Design

A robust workbook separates data, processing and output. A practical structure is:

Excel Tables should be used instead of uncontrolled ranges. Tables automatically extend formulas, filters and structured references when records are added. Each table requires a primary identifier, such as EntryID, CustomerID, AssetID or ItemID.

2.1 Chart of accounts

The chart of accounts is the classification backbone of the workbook. Each account should contain at least:

• a unique account code;
• an account name;
• its normal balance;
• its financial statement category;
• an active/inactive indicator; and
• optional department, tax or cash-flow mappings.

Account names should not be typed independently into every transaction. Instead, the account code should be selected through data validation and the name returned using XLOOKUP:

=XLOOKUP([@AccountCode],Accounts[Code],Accounts[Name],"INVALID")

An invalid code must be treated as an exception rather than silently omitted.

3. Controlled Journal Processing

A line-level journal may contain the columns Date, EntryID, LineNo, AccountCode, Debit, Credit, Description and SourceDocument.

3.1 Row validation

Debit and credit values must be numeric and non-negative. A valid posting line has a positive amount on exactly one side. Assuming columns E and F contain numeric debit and credit values, including zero, the following control rejects blanks, text, negative amounts, zero-value lines and lines with amounts on both sides:

=IF(OR(A2="",B2="",C2="",D2="",NOT(AND(ISNUMBER(E2),ISNUMBER(F2))),E2<0,F2<0,AND(E2=0,F2=0),AND(E2>0,F2>0)),"ERROR","OK")

Thus, a negative debit or credit can never be labelled OK.

3.2 Entry-level balance control

Journal balance must be tested by EntryID, not merely by row. For a currency rounded to two decimal places:

=IF(ABS(SUMIFS($E:$E,$B:$B,B2)-SUMIFS($F:$F,$B:$B,B2))<0.005,"BALANCED","UNBALANCED")

The tolerance of 0.005 currency units is appropriate only when amounts are recorded to two decimal places. A zero-tolerance test may be used where all entries are stored in exact cents.

3.3 Duplicate controls

A valid multi-line entry repeats both its EntryID and usually its source-document number. Consequently, applying COUNTIF to document numbers in the line-level journal would incorrectly flag normal debit and credit lines as duplicates.

Two different controls are needed:

1. Duplicate line control: EntryID and LineNo should be unique together.

=IF(COUNTIFS($B:$B,B2,$C:$C,C2)>1,"DUPLICATE LINE","OK")

1. Duplicate source-document control: test an EntryHeaders table containing one row per entry, rather than the line table. A business key may combine document type, counterparty and document number:

=IF(COUNTIFS(EntryHeaders[DocumentType],[@DocumentType],EntryHeaders[CounterpartyID],[@CounterpartyID],EntryHeaders[DocumentNo],[@DocumentNo])>1,"REVIEW","OK")

This control identifies the same supplier invoice entered as two different journal entries while permitting the document number to appear on all lines belonging to one valid entry. A REVIEW result is preferable to automatic deletion because document-number reuse may occasionally be legitimate.

3.4 Posting to the general ledger

For account code in cell A2, total debits and credits may be calculated as:

=SUMIFS(JournalLines[Debit],JournalLines[AccountCode],A2)

=SUMIFS(JournalLines[Credit],JournalLines[AccountCode],A2)

The signed balance under a debit-positive convention is:

where B_a is the closing signed balance of account a, O_a is its opening signed balance, D_a is total debits and C_a is total credits. Credit-balance accounts therefore normally appear as negative values under this convention. A separate normal-balance field can present liabilities, equity and revenue as positive report amounts.

4. Receivables and Payables

4.1 Accounts receivable

The receivables subsidiary ledger records invoices, credit notes, receipts and write-offs for each customer. Its total must reconcile to the general-ledger receivables control account:

An ageing schedule classifies outstanding invoices by the number of days past due. If the reporting date is stored in Setup!B2, days past due may be calculated as:

=MAX(0,Setup!$B$2-[@DueDate])

Nested IFS or a lookup table can assign categories such as current, 1–30 days, 31–60 days and over 60 days. Expected credit losses should not be calculated mechanically without considering historical defaults, current conditions and supportable forecasts.

4.2 Accounts payable

The payables schedule supports cash planning and prevents late or duplicate payment. Typical controls include:

• approved supplier master records;
• three-way matching of purchase order, receiving record and invoice;
• duplicate invoice testing at header level;
• due-date and discount monitoring;
• segregation between invoice entry and payment authorization; and
• reconciliation to supplier statements and the payables control account.

A formula such as =[@InvoiceDate]+[@CreditDays] calculates a contractual due date where credit days are calendar days.

5. Inventory Processing and Valuation

A perpetual inventory system records every receipt and issue. For each item:

Inventory records must also preserve the cost-flow assumption. Under the moving weighted-average method, the average cost immediately after a purchase is:

This formula applies when the denominator is positive and all costs use the same currency and unit of measure.

Worked example: moving weighted average

A business has 100 units costing $10 each and purchases 60 units at $13 each.

If 120 units are issued, cost of goods sold is 120 × 11.125 = $1,335. The remaining 40 units are valued at 40 × 11.125 = $445. The control check is $1,335 + $445 = $1,780.

Physical counts remain necessary because spreadsheet records cannot independently detect theft, damage or unrecorded movement. Differences should be investigated and approved before adjustment.

6. Payroll Applications

Payroll integrates personnel data, time records, tax rules and payment information. A simplified calculation is:

Employer payroll taxes or pension contributions are additional employer expenses and do not reduce employee net pay unless the law or contract identifies them as employee deductions.

Suppose gross wages are $40,000, employee income-tax withholding is $6,000 and employee pension deductions are $2,000. Net pay is:

The journal entry debits wages expense for $40,000, credits tax and pension payables for $8,000, and credits cash or wages payable for $32,000. If employer payroll tax is $4,000, a separate entry debits payroll-tax expense and credits payroll-tax payable by $4,000.

Payroll controls include authorized employee master changes, independent review of bank details, reasonableness tests, duplicate bank-account reports, reconciliation to attendance records and protection of confidential personal information.

7. Fixed Assets and Depreciation

Depreciation allocates an asset’s depreciable amount over its useful life; it is not a process for updating the asset to market value. Under straight-line depreciation:

These formulas assume cost is at least residual value, useful life is positive and no impairment or revaluation changes the estimates.

7.1 A bounded period-depreciation formula

A reliable schedule must stop depreciation at the earlier of the end of useful life and the disposal date. It must also calculate historical periods correctly: an asset currently marked as disposed may still require depreciation for periods before its actual disposal date.

The following daily straight-line policy defines:

• B2: in-service date;
• C2: cost;
• D2: residual value;
• E2: useful life in years;
• F2: disposal date, blank if not disposed;
• H1: reporting-period start date; and
• H2: reporting-period end date.

The disposal date is treated as the first date on which the asset is no longer used, so it is excluded from depreciation. Useful life must be stated in whole or otherwise valid month increments for EDATE.

=LET(Start,$B2,Cost,$C2,Residual,$D2,LifeMonths,$E212,LifeEnd,EDATE(Start,LifeMonths),StopDate,MIN(LifeEnd,IF($F2="",DATE(9999,12,31),$F2)),PeriodStart,H$1,PeriodEnd,H$2+1,OverlapDays,MAX(0,MIN(PeriodEnd,StopDate)-MAX(PeriodStart,Start)),Base,MAX(0,Cost-Residual),IF(OR(Start="",LifeMonths<=0,Cost<Residual),NA(),BaseOverlapDays/(LifeEnd-Start)))

The numerator counts service days falling within both the reporting period and the permitted depreciation interval. The denominator is the total number of calendar days from the in-service date to the scheduled life-end date. Consequently:

• depreciation is zero before the in-service date;
• depreciation stops when useful life ends;
• depreciation stops on disposal;
• historical periods before disposal remain correctly charged; and
• cumulative scheduled depreciation cannot exceed cost minus residual value.

This is a defined daily allocation policy. Organizations using monthly, half-year or tax conventions should implement those policies explicitly rather than treating this formula as universally applicable.

Worked example

An asset costs $26,000, has a $2,000 residual value and a four-year useful life. Its depreciable amount is $24,000 and full-year depreciation under a conventional annual straight-line calculation is:

If the asset remains in use for all four years, accumulated depreciation is limited to $24,000 and carrying amount is limited to $2,000. If it is disposed of earlier, depreciation ceases at the policy-defined disposal date and any gain or loss is calculated separately from disposal proceeds and carrying amount.

8. Bank Reconciliation

A bank reconciliation explains the difference between the bank statement balance and the cash-ledger balance. Timing differences adjust the bank side; transactions known by the bank but missing from the books adjust the book side.

Worked reconciliation

The bank statement reports $33,300. Deposits in transit are $4,200 and outstanding checks are $5,600:

The cash ledger reports $31,450. The bank statement reveals interest income of $120, an electronic customer receipt of $500, a bank fee of $70 and an NSF customer payment of $100:

The two adjusted balances agree. Journal entries are required for the book-side items but not for deposits in transit or outstanding checks, because those timing items are already recorded in the ledger.

Useful spreadsheet columns include transaction date, reference, amount, match status and matched statement line. Conditional formatting can highlight old unmatched items, but it should not replace investigation.

9. Trial Balance and Adjustments

A trial balance lists ledger balances and verifies that total debits equal total credits. It does not prove that transactions were authorized, classified correctly or recorded in the correct period.

Typical adjusting entries include:

• accrued expenses and revenue;
• prepaid-expense consumption;
• depreciation;
• inventory adjustments;
• expected credit losses; and
• corrections of identified errors.

A structured trial balance commonly contains unadjusted debit and credit columns, adjustment columns, and adjusted debit and credit columns. The control equation is:

A small unexplained difference must not be forced into a suspense account merely to produce apparent agreement.

10. Preparing Core Financial Statements

Accounts should be mapped to statements through controlled categories rather than hard-coded row references. For example, SUMIFS can aggregate adjusted balances by statement heading.

10.1 Income statement

For a trading entity:

10.2 Statement of changes in equity

For companies, separate columns may be required for share capital, retained earnings and other reserves.

10.3 Balance sheet

The balance sheet must satisfy the accounting equation after incorporating current-period profit and distributions.

10.4 Statement of cash flows

Cash flows are classified as operating, investing and financing activities under the applicable reporting framework. The statement must reconcile opening and closing cash:

Classification requirements may differ between accounting standards, particularly for interest and dividends. The workbook should document the policy used.

11. Illustrative Case: Northstar Traders

This is a fictional case created for instructional purposes.

Background and problem

Northstar Traders maintains journals, customer balances and inventory in separate files. Management experiences duplicate supplier invoices, unexplained bank differences and delayed financial statements. The company needs a controlled Excel application for its year-end process.

Approach

The accounting team creates linked tables for entry headers, journal lines, customers, suppliers, inventory, payroll, fixed assets and bank reconciliation. Account codes are validated against the chart of accounts. Duplicate supplier invoices are tested once per entry header using supplier, document type and invoice number. Journal lines are checked for non-negative values, unique line numbers and balanced entries.

After posting and period-end adjustments, the following adjusted trial balance is produced:

Analysis and statements

Gross profit is $80,000 − $48,000 = $32,000. Operating and interest expenses total $14,000 + $5,000 + $3,000 + $2,000 + $1,000 = $25,000. Therefore, profit is $7,000.

With no contributions or dividends during the period, closing retained earnings are:

The balance sheet reports net assets as follows:

Suppose cash records also show operating cash inflow of $12,000, investing cash outflow of $5,000 and no financing cash flow. With opening cash of $25,000, closing cash is:

This agrees with the adjusted trial balance and balance sheet.

Outcome and learning points

Northstar can now trace statement figures to account balances and individual journal lines. The case demonstrates that dependable reporting requires several independent agreements: journal debits equal credits, subsidiary ledgers equal control accounts, adjusted bank balances agree, the trial balance balances, cash flow reconciles, and assets equal liabilities plus equity. Agreement does not eliminate the need to test authorization, valuation and cutoff.

12. Governance, Ethics and Spreadsheet Risk

Spreadsheet errors may arise from overwritten formulas, hidden rows, incorrect references, outdated rates, unauthorized changes and copied values. Recommended controls include:

• separate preparer and reviewer responsibilities;
• locked formula cells and controlled input areas;
• version naming and approved storage locations;
• change logs and period-end snapshots;
• visible exception reports;
• backups and restoration testing;
• documented accounting policies;
• restricted access to payroll and banking information; and
• retention of source evidence.

Ethical accounting requires faithful representation rather than manipulation to meet profit targets or borrowing conditions. Users should not suppress exceptions, backdate unsupported entries or alter formulas to obtain desired outcomes.

Excel is not automatically compliant with financial-reporting, tax, labor or privacy regulation. Compliance depends on applicable jurisdiction, policies, evidence and review.

13. Technology, Automation and Contemporary Developments

Power Query can import and standardize bank statements, invoices and system extracts. PivotTables can summarize ledger activity, while dynamic arrays can produce exception lists. Office Scripts or macros may automate repetitive steps, but code should be documented, tested and access-controlled.

Artificial intelligence can assist with invoice data extraction, account suggestions, anomaly detection and narrative explanations. Nevertheless, AI output may be inaccurate, biased or unsupported. Material classifications and journal entries require human approval, and confidential data should not be submitted to unauthorized AI services.

As transaction volume and complexity grow, organizations should migrate from spreadsheets to databases or enterprise systems with role-based access, automated audit trails, concurrency controls and formal period locking. Excel remains valuable as an analytical and reconciliation layer.

Chapter Summary

Controlled transaction processing links source documents, journal entries, ledgers, subsidiary schedules, reconciliations and financial statements. Reliable Excel applications separate data from reports, validate master data, reject negative or incomplete journal lines, balance entries by identifier and test duplicate documents at the entry-header level. Receivables, payables, inventory, payroll and fixed-asset schedules must reconcile to general-ledger control accounts.

Depreciation formulas must respect in-service dates, useful-life limits and actual disposal dates without corrupting historical-period calculations. Bank reconciliations distinguish timing differences from items requiring journal entries. The adjusted trial balance supports preparation of the income statement, statement of changes in equity, balance sheet and statement of cash flows. Protection, review, audit trails, ethical conduct and appropriate technology are essential because a numerically balanced workbook may still contain substantive accounting errors.

Key Terms

Accounting cycle: The sequence from transaction capture to reporting and period closure.

Adjusted trial balance: Account balances after period-end adjustments have been posted.

Audit trail: Evidence linking reported amounts to transactions, approvals and source documents.

Bank reconciliation: A comparison explaining differences between bank and ledger cash balances.

Chart of accounts: The controlled list of accounts used to classify transactions.

Control account: A general-ledger account supported by a detailed subsidiary ledger.

Cutoff: Recording transactions in the correct accounting period.

Depreciable amount: Asset cost less residual value.

Duplicate-document control: A test for repeated source documents at the transaction-header level.

Entry header: A record containing information applying to an entire journal entry.

General ledger: The complete set of accounts to which journal entries are posted.

Inventory valuation: Assignment of monetary cost to inventory issued and held.

Journal entry: A balanced record of debit and credit effects of a transaction.

Moving weighted average: Inventory method that recalculates average unit cost after receipts.

Reconciliation: Comparison of related records and investigation of differences.

Residual value: Estimated amount recoverable from an asset at the end of its useful life.

Subsidiary ledger: Detailed records supporting a general-ledger control account.

Three-way match: Comparison of purchase order, receipt evidence and supplier invoice.

Review Questions

Short Answer Questions

1. Why should transaction inputs, calculations and financial reports be separated in a workbook?
2. What conditions must a valid journal line satisfy?
3. Why is a repeated document number not necessarily an error in a line-level journal?
4. Which bank-reconciliation items require journal entries?
5. What is the purpose of a subsidiary-ledger reconciliation?

Long Answer Questions

1. Explain the complete transaction-processing cycle and the controls required at each stage.
2. Design a workbook architecture for journals, ledgers, receivables, payables and financial reporting.
3. Discuss the accounting and spreadsheet controls required for a payroll application.
4. Explain how an adjusted trial balance is converted into the four core financial statements.
5. Critically evaluate Excel as an accounting platform compared with a database or enterprise system.

Analytical / Application Questions

1. Design journal row, entry-balance and duplicate-document controls for a business in which every invoice produces at least three journal lines.
2. An asset is disposed of before the end of its useful life. Explain how a period-depreciation model should treat periods before disposal, the disposal period and later periods.
3. A trial balance agrees, but receivables do not reconcile to customer balances and several old bank items remain unmatched. Assess the risks and propose an investigation plan.

Multiple Choice Questions with Answers

Question 1

Which workbook design most effectively supports control and auditability?

A. Store inputs, formulas and reports in one unrestricted worksheet

B. Separate source data, processing schedules, control reports and financial statements

C. Replace account codes with manually typed account names

D. Hide all validation formulas from preparers

Correct Answer: B. Separate source data, processing schedules, control reports and financial statements

Question 2

A journal row contains a debit of −$500 and a credit of $0. How should the row control classify it?

A. OK because only one side contains a non-zero value

B. OK if the complete journal entry balances

C. Error because journal amounts must be non-negative

D. Error only if the account has a credit normal balance

Correct Answer: C. Error because journal amounts must be non-negative

Question 3

Why should a simple COUNTIF on document number not be used as the duplicate control in a line-level journal?

A. Document numbers can contain letters

B. A valid multi-line entry normally repeats its document number

C. COUNTIF cannot examine text values

D. Document numbers never need duplicate testing

Correct Answer: B. A valid multi-line entry normally repeats its document number

Question 4

Which key should be unique for every line within a journal?

A. Account code only

B. Document number only

C. EntryID combined with LineNo

D. Transaction date combined with debit amount

Correct Answer: C. EntryID combined with LineNo

Question 5

Inventory consists of 100 units at $10 each and a receipt of 60 units at $13 each. Under moving weighted average, what is the new unit cost?

A. $10.00

B. $11.125

C. $11.50

D. $13.00

Correct Answer: B. $11.125

Question 6

Gross wages are $40,000, employee tax is $6,000 and employee pension deductions are $2,000. What is net pay?

A. $32,000

B. $34,000

C. $38,000

D. $48,000

Correct Answer: A. $32,000

Question 7

An asset costs $26,000, has a $2,000 residual value and a four-year useful life. What is full-year straight-line depreciation?

A. $5,500

B. $6,000

C. $6,500

D. $7,000

Correct Answer: B. $6,000

Question 8

Which depreciation control correctly handles an asset that is currently disposed of?

A. Set depreciation to zero for every historical and current period

B. Continue depreciation until cost is fully written off

C. Use the actual disposal date so periods before disposal can still receive depreciation

D. Ignore the disposal date until the original useful life ends

Correct Answer: C. Use the actual disposal date so periods before disposal can still receive depreciation

Question 9

A bank statement balance is $33,300, deposits in transit are $4,200 and outstanding checks are $5,600. What is the adjusted bank balance?

A. $31,900

B. $32,700

C. $34,700

D. $43,100

Correct Answer: A. $31,900

Question 10

Which statement about a balanced trial balance is correct?

A. It proves that every transaction was authorized

B. It proves that every account classification is correct

C. It confirms debit and credit equality but does not detect every accounting error

D. It eliminates the need for subsidiary-ledger reconciliation

Correct Answer: C. It confirms debit and credit equality but does not detect every accounting error