From trial balance to management report

1. 1. Take the trial balance as numbers, not a picture

   Export it with ledger names, their groups and closing balances — Excel or CSV where you can, because a spreadsheet keeps figures as numbers. Re-keying from a printout is where most errors in a monthly report are born.

2. 2. Check it balances before anything else

   Total debits must equal total credits. If they do not, the problem is in the books, and no amount of work in the report will fix it.

3. 3. Map every ledger to one report head

   Revenue, direct costs, employee cost, other operating expenses, finance costs, depreciation, and on the balance sheet side receivables, inventory, cash, payables, borrowings and equity. The ledger's group is the best first guess; the accountant's judgement is the final one.

4. 4. Sum the heads and fix the signs

   In a trial balance credits are usually negative. A report shows revenue as a positive number, so each head needs a presentation sign as well as a total.

5. 5. Prove the report ties back

   The mapped heads must add back to the trial balance total, nothing may be left unmapped, and the balance sheet must balance with the period's profit included.

6. 6. Keep the mapping for next month

   The mapping is the expensive part and it barely changes. Record it, reuse it, and review only the ledgers that are new.

The mapping above is the template. Keep it as its own sheet, one row per ledger, with four columns:

• Ledger, spelled exactly as the export spells it, because that is what every formula matches on.
• Group, from the accounting system, as the default for a ledger nobody has looked at yet.
• Report head, the line of the P&L or balance sheet the ledger feeds.
• Note, why an unusual ledger went where it did, so next month’s preparer does not undo it.

Balances do not belong on the mapping sheet; they change every month and the mapping should not. Keep the trial balance on a sheet of its own and let the statements read both.

With the trial balance on one sheet and the mapping on another, add a column to the trial balance that looks up each ledger’s report head — XLOOKUP, or INDEX and MATCH — and every line of the statements becomes a single SUMIFS: the sum of the balance column where the head equals that line. Multiply credit heads by minus one to present them as positive.

Then add the two checks that keep it honest: a count of trial balance rows whose lookup returned nothing, which must be zero, and the balance sheet difference, which must be zero too. Turning an uploaded trial balance into the statements without the spreadsheet is on trial balance to financial statements.

Every TallyPrime ledger sits under a group, chosen in the ledger’s Under field when it is created from Gateway of Tally > Create > Ledger. TallyPrime provides 28 predefined groups — 15 primary groups and 13 subgroups — such as Sales Accounts, Direct Expenses, Indirect Expenses and Sundry Debtors, and a company can add its own beneath them.

That group is most of a mapping already, which is why the example above shows it: Sales Accounts becomes revenue, Sundry Debtors becomes trade receivables. What the group cannot do is split Indirect Expenses into employee cost, rent and finance costs, or catch a ledger created under the wrong group. Export the trial balance ledger-wise so the group and the ledger name both arrive, and map by ledger with the group as the default. Magic MIS reads Tally’s group structure the same way, and keeps the mapping for the months after.

Completeness. The sum of every report head equals the trial balance total, and no ledger with a balance is unmapped. A report can look perfect and still leave out a ledger someone created last week.

The balance sheet balances. Assets equal liabilities plus equity once the year-to-date profit is included. If it is off by exactly the profit, the profit was left out; if by twice a balance, a ledger was mapped with the wrong sign.

Months add up to the year. The monthly P&L figures sum to the year-to-date trial balance. When they do not, a prior month was reopened and posted to after it was reported.

Traceability. Anyone should be able to pick a number in the report and see the ledgers behind it. That is what turns a question in a review meeting from an afternoon of searching into a click.