Accounting / Toolkit
Excel Lab — Journal to Trial Balance
Turn ten journal lines into a traceable trial balance. Practise SUMIFS, IF and ABS, then investigate what a balanced total can still conceal.
Return to your library resultsHow to use this toolkit.
Workflow
- Open Trial balance and review the expected totals.
- Edit the amber cells on Journal. The seven account names form the permitted chart of accounts.
- Read the line and voucher reviews, then reconcile the trial balance.
- Work through the two exercises below and restore the example.
Assumptions & limitations
Original synthetic October 2026 data, IDR, with zero opening balances. The journal accepts 100 lines, rows 8–107, and seven accounts. Extend all relevant ranges when adding accounts or lines. Formulas are editable and are not protected. This lab illustrates aggregation; it does not prescribe recognition, tax or reporting policy.
Example scenario
Ten lines in five balanced vouchers. Journal debits and credits each total IDR 163,000,000. Closing trial balance debits and credits each total IDR 148,000,000.
| Account | Debit balance (IDR) | Credit balance (IDR) |
|---|---|---|
| Cash | 112,000,000 | 0 |
| Inventory | 21,000,000 | 0 |
| Accounts payable | 0 | 30,000,000 |
| Capital | 0 | 100,000,000 |
| Sales | 0 | 18,000,000 |
| Cost of sales | 9,000,000 | 0 |
| Rent expense | 6,000,000 | 0 |
Sample data from the downloadable file; values are illustrative.
## The question behind the spreadsheet
How do individual business events become an explainable account balance? This lab follows a small fictional business through an owner contribution, a credit purchase, a cash sale, inventory consumption and rent paid. Every amount is illustrative.
You will aggregate journal lines, separate debit and credit closing balances and investigate errors that an overall balance alone will miss. If debits and credits are new to you, review the transaction and journal concept in the Accounting map first.
## Follow the data
Journal contains a unique line ID, a voucher ID, an account, an event description, debit and credit amounts. A voucher can contain several lines. Enter a numeric zero on the unused side, rather than leaving the amount missing. Use the account dropdown to keep names consistent.
Trial balance groups those lines into seven accounts. There are no opening balances in this exercise. Cash receives IDR 100 million of capital and IDR 18 million from a sale, then pays IDR 6 million of rent. Its closing debit balance is IDR 112 million.
## Build the formula
In Trial balance B8, the worked formula is:
=SUMIFS('Journal'!$E$8:$E$107,'Journal'!$C$8:$C$107,A8)
The first range contains debit amounts. The second contains account names. A8 supplies the account to match. Dollar signs keep the source ranges fixed when copying down, while A8 changes to the next account. The credit formula uses Journal column F with the same matching rule.
Closing debit uses =IF(B8>C8,B8-C8,0). Closing credit reverses that comparison. This presents the net amount on one side, without erasing the underlying activity.
The voucher review compares debits and credits for the same voucher. ABS makes a positive or negative difference comparable with the IDR 0.01 tolerance. Line reviews also flag duplicate line IDs, unknown accounts, missing fields, negative amounts or entries on both sides.
## Interpret and check
The source journal has IDR 163 million on each side. The closing trial balance has IDR 148 million on each side. These totals differ because account activity is netted to closing balances. All six reconciliation counts or differences should be zero in the supplied example.
A zero global difference can coexist with a wrong account, an omitted balanced voucher or two offsetting errors. A trial balance supports a reconciliation; source evidence and accounting judgment remain separate work.
## Practise, then investigate
1. On Journal row 12, reduce the Cash debit from 18,000,000 to 17,000,000. Expect a negative IDR 1 million journal difference and a voucher warning. Restore the original amount.
2. Change that account to Unknown. Expect IDR 18 million of unmapped debit and a line warning. Restore Cash.
3. Add two new lines for a hypothetical accrual. Use unique line IDs and one common voucher ID. Explain the business event, reporting period and evidence before choosing the accounts.
## Self-check
Why can a trial balance remain balanced after an expense is posted to Inventory? Both sides still agree, but the classification and resulting profit may be wrong. Identify which source record and policy would resolve the treatment.
## Continue through the pillar
Return to Accounting Apply to explore reconciliation and working records. Then open Connect and explain how a classification error changes the finance interpretation of performance.
Put the perspective to work.
Use these questions to connect the resource to your own business context.
What should this transaction or balance represent?
Which recognition, measurement or cutoff decision is involved?
What records support the amount and its treatment?
What judgment or reconciliation should be documented?
References & context.
Use the framework and jurisdiction applicable to your work. Original examples and templates do not imply endorsement by the referenced organisations.
A question, correction or missing source?
The resource link will be included in your message form.