Achieving balance: tips, tricks to preparing balance sheets and income statements from a trial balance.

• Author: Beacham, Cliff
• Position: SpreadsheetTips

The longest trial balance I worked with contained more than 100,000 accounts. While this might seem excessive, I have worked with general ledgers with upwards of 8,000 accounts.

But we're not here to discuss whether this is appropriate or not-just how to deal with these enormous monsters.

It's no secret that many companies use Excel to prepare their financial statements, and it has been my experience that the level of efficiency in that process could do with sonic improving. Let me explain.

Excel allows you to add cells and cell ranges by referring to them as "addresses." This is a powerful technique, but error prone and time consuming. Before 1 go on. 1 should explain what the wrong way is:

=Al +SUM(A3:A8)+SUM(A12:A18)+A22 Why is this wrong? The answer is that when you are summing a complex or extensive range, there is a danger of omission or double counting. I remember a CFO who asked me to find his errors and would not let. rue do it my way. This I did--and fairly quickly but when I said I could show him a better way to do it, he answered. "I haven't got time right now, but I will get back to you." Of course, that never happened. The same thing occurred in the 1990s. It was a budget director who was going to make the time, but never did in my 10-month engagement

Figure 1 Cash on hand 1,234 Bank 1 234,789 Bank 2 53,456 Savings account 11,456 CD 432,000 Money Market 5,533,000 Payroll account 2,020 Figure 2 Cash 302,955 Short term deposits 5,965,000 Figure 3 Cash 302,955 Short term deposits Formula=SUM(D7:D10)+D13 Short term deposits 5,965,000 Formula=SUM(D11:D12) The Way Forward

The technique (or lack thereof) is displayed in figures 1 and 2. Figure 1 is an extract from a trial balance and Figure 2 shows the applicable report. However, the formulas behind the report are shown in Figure 3.

Notice the formulas that produce the totals consist of addresses that add the cells in a fairly incoherent. form. The good news is that we can easily improve this technique.

First, obtain the trial balance. Ensure it's at the correct date and that the general ledger is closed. A client recently asked me to investigate a case of the changing numbers. I discovered that they were not closing their general ledger and journal entries were still posted to the past periods.

Second, design your FinStats. This article is covering the income statement. and balance sheet. Using the trial balance will ensure that the bottom line of the income statement will equal the...