Summarizing Business Transactions in Excel 2010: From the Journals to the Balance Sheet

This chapter is from the book 

Understanding Ledgers

Just as you can choose which special journals to maintain for your business, you can choose which ledgers to maintain. If you kept only one ledger with detailed information about all accounts, it would lose some of its value as a summary document.

Therefore, it's normal to establish some subsidiary ledgers that contain detailed information from the journals. You can then keep information about specific sales and specific purchases in the subsidiary ledgers, and you can transfer their summary totals to a general ledger. Because of the frequency of transactions involving sales and purchases, many businesses keep information about these transactions in an accounts receivable subsidiary ledger and an accounts payable subsidiary ledger.

Creating the General Ledger

By keeping detailed information from the journals in these subsidiary ledgers, it's easier to check the status of your individual accounts with both creditors and customers. At the same time, you can keep the general ledger from becoming cluttered with detailed information about individual customers who owe you money, and about individual creditors who expect to be paid. See Figure 4.6, which displays a general ledger's asset and liability accounts.

Every dollar entry in this ledger refers directly to either an entry in a journal or a total of the transactions in a journal. For example, the formula in cell D8 is as follows:

='Cash Receipts Journal'!$E$9

Figure 4.4 shows the cash receipts journal. Notice that the value of $6,000 in cell D8 of the general ledger in Figure 4.6 is the single entry in the cash receipts journal that represents the note accepted by Bell Books in partial payment for the third floor of its building. In contrast, the formula in cell D4 of Figure 4.6 is as follows:

=SUM('Cash Receipts Journal'!CRJDebitsCash)

Here, the name CRJDebitsCash is a dynamic range name that refers to this:

=OFFSET('Cash Receipts Journal'!TransactionDate,0,2)

This dynamic range does not include the sum in cell C15 in Figure 4.4. That's because the range is based on TransactionDate, which extends only through A13. OFFSET's optional Rows argument is not supplied, so the row count is taken from TransactionDate.

This example illustrates how a ledger entry summarizes all the transactions in a given category that appear individually in a journal.

So debits to the general ledger's cash account are based on the cash receipts journal. In contrast, credits to the general ledger's cash account are based on the cash payments journal. Figure 4.5 shows the cash payments journal, and column D contains the cash payments made during the month, totaling $11,419.30 (cell D15). The outflow of cash is represented in the general ledger by credits to the cash account. The formula in cell E5 of Figure 4.6 is as follows:

=SUM('Cash Payments Journal'!CPJCreditsCash)

Here, the name CPJCreditsCash is a dynamic range name scoped to the Cash Payments Journal worksheet that refers to this:

=OFFSET('Cash Payments Journal'!TransactionDate,0,3)

Consider the accounts receivable classification in Figure 4.6. It contains three amounts: $42.00, $2,411.10, and $1,505.10. The $42.00 value represents the return of merchandise from a customer and is taken from the general journal.

This formula returns the $2,411.10 value:

=SUM('Sales Journal'!SJDebitsCash)

The value is the sum of all sales in the sales journal, which by definition are noncash transactions. A range in the sales journal is named SJDebitsCash and refers to cells E2:E8 of that worksheet, shown in Figure 4.2. The range contains noncash sales that are recorded as debits to accounts receivable. (The dynamic range name SJDebitsCash was discussed in this chapter's section on dynamic range names in journals.)

This formula returns the $1,505.10 value in cell E13 of Figure 4.6:

=SUM('Cash Receipts Journal'!CRJCreditsAR)

This refers to the total of the credits in accounts receivable, from the Credits section of the cash receipts journal (see Figure 4.4).

In this way, the activity in the accounts receivable account is summarized for the month. New credit sales are totaled in cell D12, and receipts for credit sales are totaled in cell E13, both in the general ledger. You might find it useful to open the Financial Reports.xlsm file and view each of its entries to determine its source in the journals. If you do, you will find that every value for June in the general ledger refers (either directly, or indirectly via the SUM function) to an entry in a journal.

Figure 4.7 shows the Revenue and Expense section of the general ledger.

The entries in the general ledger's Revenue and Expense section follow the pattern established in its Assets and Liabilities section. That is, each entry comes from a journal and is either a specific journal entry or the total of several journal entries that belong to the same account.

The values in cells D60 and E60 of Figure 4.7, $210,698.10, show that the accounts are in balance. As noted previously, the double-entry method is meant to ensure that the total of the debits for a given period equals the total of the credits for the same period. Each transaction is a debit to one account and a credit to another.

Cells D60 and E60 total the debit and credit account balances in the general ledger. They are equal, which is evidence that the accounts are in balance. However, just because the two totals are equal doesn't necessarily mean that all entries are accurate. For example, compensating errors might exist. But if the two amounts were unequal, it would demonstrate that at least one error existed somewhere.

Creating Subsidiary Ledgers

Bell Books uses two subsidiary ledgers: accounts receivable and accounts payable. The purpose of these ledgers is to keep tabs on accounts that customers have with Bell Books and that Bell Books has with its suppliers. Figure 4.8 shows the accounts receivable ledger.

Neither the accounts receivable ledger nor the accounts payable ledger links to the general ledger. Each subsidiary ledger repeats some of the information in the general ledger; specifically, the accounts receivable ledger provides details about the individual accounts for Bell Books' credit customers.

Two worksheet buttons appear on the accounts receivable ledger. One is labeled Post from Sales Journal. That button contains VBA code that posts information about noncash sales from the sales journal to the accounts receivable ledger. Clicking the button causes the VBA code to run. The button labeled Post from Cash Receipts Journal also contains associated VBA code that posts information from the cash receipts journal to the accounts receivable ledger. The cash receipts represent payments for credit sales that were recorded in the sales journal.

The accounts receivable ledger also contains a pivot table that provides the current balance for each of the accounts receivable.

In Figure 4.8, cell D9 contains the value $326.67. That value represents the payment received from Fred Howell on June 8 that was entered into the cash receipts journal (refer to cell I8 of Figure 4.4). The value $326.67 is transferred to the general ledger (refer to cell E13 of Figure 4.6) as part of the total of the credits to accounts receivable from the cash receipts journal.

This example illustrates that amounts posted to subsidiary ledgers must be included in two ledgers: both the subsidiary ledger and the general ledger. Doing so ensures that the account's amount, as shown in the general ledger, equals the amount shown in the subsidiary ledger.

Automating the Posting Process

You can put buttons in various places in an Excel workbook; for example, you can put buttons on worksheets and user forms such as dialog boxes. By associating the button with VBA code, you can cause Excel to take the actions you want it to when the button is clicked. Here's how to create a button on a worksheet using Excel 2007 or Excel 2010:

1. Click the Developer tab and then click Insert in the Controls group.

   TIP

   If you don't see the Developer tab, establish it by clicking the File tab, choosing Excel Options, and clicking Customize Ribbon.

2. Click the Command Button icon in the ActiveX Controls section. Move the mouse pointer over the worksheet.
3. Holding down the left mouse button, drag across and down on the worksheet to indicate where you want the button to appear. Then release the mouse button.
4. The Command button is established on the worksheet. Right-click the Command button to invoke a shortcut menu. One of its items is View Code. When you are ready to provide the VBA code that is to run when the button is clicked, select the View Code item.
5. The button has a default label, CommandButton1 if it's the first Command button on the worksheet. You can change the label in various ways, including this one: Right-click the button and select CommandButton Object, Edit from the shortcut menu. When you move your mouse pointer over the button's label, the pointer changes to an I-bar. Hold down the left mouse button and drag across the label to highlight it. Then type whatever text you want to appear on the button.
6. Deselect the button by clicking any worksheet cell.

The Post from Sales Journal button shown in Figure 4.8 is linked to the VBA procedure named PostFromSalesToAR in Listing 4.1, which actually performs the posting.

Listing 4.1. VBA Procedure PostFromSalesToAR

Option Explicit
Option Base 1

Sub PostFromSalesToAR()
Dim SalesDate As Range, Acct As Range, Posted As Range, SalesAmount As Range
Dim ThisTransaction As Integer, NextEntryRow As Long


With Sheets("Sales Journal")
   .Activate
   Set SalesDate = .Range("TransactionDate")
   Set Acct = .Range("Account")
   Set Posted = .Range("Posted")
   Set SalesAmount = .Range("SJDebitsCash")
End With
NextEntryRow = ThisWorkbook.Sheets("Accts Receivable Ledger") _
    .Range("TransactionDate").Rows.Count

For ThisTransaction = 1 To Acct.Rows.Count
    If Posted(ThisTransaction) <> Chr(252) Then
        With ThisWorkbook.Sheets("Accts Receivable Ledger")
            .Range("TransactionDate").Offset(NextEntryRow, 0).Resize(1, 1) = _
                SalesDate(ThisTransaction)
            .Range("AccountNames").Offset(NextEntryRow, 0).Resize(1, 1) = _
                Acct(ThisTransaction)
            .Range("Purchases").Offset(NextEntryRow, 0).Resize(1, 1) = _
                SalesAmount(ThisTransaction)
        End With
        Posted(ThisTransaction).FormulaR1C1 = "=CHAR(252)"
        NextEntryRow = NextEntryRow + 1
    End If
Next ThisTransaction


With ThisWorkbook.Sheets("Accts Receivable Ledger")
   .Activate
   .PivotTables("ARSummary").PivotCache.Refresh
End With
End Sub

The first two lines of code in Listing 4.1 set two general options. Option Explicit requires you to explicitly declare all variables that you use in the code. If this option is not set, new variables could be declared implicitly on the fly—simply typing a variable name would create it. Because this approach makes the code much more difficult to trace and debug, it's wise to use Option Explicit and to explicitly declare each variable. (See the discussion of the Dim statements later in this section.)

The second option, Option Base 1, requires that the first element of all VBA arrays be element number one. Omitting this option causes Excel to treat the first element of an array as element number zero. Suppose that the first element of MyArray was Fred Howell. Using Option Base 1, you would refer to this value as MyArray(1). Without Option Base 1, you would refer to it as MyArray(0). If you prefer to start counting at one instead of at zero, use Option Base 1 in your VBA code.

The next statement in Listing 4.1, Sub PostFromSalesToAR( ), classifies and names the procedure. VBA has two types of procedures: functions and subroutines. The keyword Sub identifies the procedure as a subroutine. VBA procedures always have a set of parentheses following the name. If the parentheses enclose a variable name or names, those variables are being passed as arguments to the subroutine. (The topic of arguments is lengthy and complicated. For now, just be aware that arguments help provide values of variables to the procedures that use them.) In this case, no variables are being passed to the subroutine.

The next two statements are Dim (short for dimension) statements. Using Option Explicit requires the code to explicitly declare all variables. The Dim statements declare the existence and types of several variables. The variable named ThisTransaction, for example, is declared as type Integer. This means that the variable cannot take on a numeric value that has a fractional component, such as 3.1416, and cannot take on a text value such as "Ellen".

The four variables that are declared as type Range will later refer specifically to worksheet ranges. After the Dim statement declares the variables, the Set statements assign them to ranges. In this case, the variables are simply conveniences: The code can refer to the variable instead of to the name of the range in the worksheet where it exists and to the workbook that contains the worksheet.

Consider the next statement in Listing 4.1:

With Sheets("Sales Journal")

The statement starts what's called a With block. That's a set of statements that pertain to the object named in the With statement. In this case, the next five statements all have to do with the sheet named Sales Journal. It's a sort of shorthand; instead of repeating the name of the worksheet in each of the five statements, the code encloses them inside a With . . . End With structure.

Here's the first statement in the With block:

.Activate

This statement causes Excel to make the sheet named Sales Journal the active worksheet. The Sales Journal worksheet, of course, contains the sales transactions that will be posted. Without the With block, the statement would have had to be this:

Sheets("Sales Journal").Activate

This probably doesn't seem like much of a savings, but the code can do the same thing with the following four statements, which assign the four Range variables to named ranges:

 Set SalesDate = .Range("TransactionDate")
 Set Acct = .Range("Account")
 Set Posted = .Range("Posted")
 Set SalesAmount = .Range("SJDebitsCash")
End With

These statements cause the variable SalesDate to stand in for the range named TransactionDate in the sales journal, Acct to stand in for the range named Account, and so on. Then the end of the With block is indicated.

When the range variables have been set, the code determines the next available row in the AcctsReceivable sheet to post a transaction. It does so by counting the number of rows that are presently in the AcctsReceivable range:

NextEntryRow = ThisWorkbook.Sheets("Accts Receivable Ledger") _
    .Range("TransactionDate").Rows.Count

NextEntryRow will be used as an offset, so we can be sure not to overwrite what is at present the final row of data on the Accts Receivable Ledgers worksheet.

The statement names three objects that are separated by dots. In VBA, this is termed dot notation and denotes a hierarchy: An object that follows the dot belongs to an object that precedes the dot. This was implicit in the With structure just discussed.

The statement refers to a range named TransactionDate. That range belongs to a sheet named Accts Receivable Ledger. In turn, that sheet belongs to the ThisWorkbook object—that is, the workbook containing the VBA code that's executing.

The TransactionDate range has a Rows property, which returns a collection of the rows that belong to the TransactionDate range. That collection of rows itself has properties, one of which is the Count property. The Count property returns the number of objects in a collection. As used here, it returns the number of rows in the TransactionDate range found on the AcctsReceivable sheet that is in the workbook containing the VBA code.

The number of rows in the range is assigned to the variable NextEntryRow. That variable is used later in the code as an argument to the Offset function, and it determines which row the next transaction will be posted to.

Now the meat of the procedure begins. Consider this statement in Listing 4.1:

For ThisTransaction = 1 To Acct.Rows.Count

It starts a loop that executes once for each row in the range named Account, which is represented by the Acct variable. The approach is similar to that used to find the next entry row: Acct.Rows.Count returns the number of rows in the range represented by the Acct variable. The loop steps through the sales journal row by row, looking for transactions that should be posted to the accounts receivable ledger.

The For loop starts at 1 and ends at Acct.Rows.Count; therefore, it executes as many times as there are rows in that range. The Acct variable represents the range named Account on the Sales Journal worksheet. So the loop executes once for each row in that range.

The first statement inside the loop is as follows:

If Posted(ThisTransaction) <> Chr(252) Then

This statement causes the statements that follow it to execute if its condition is satisfied, but to be skipped otherwise. The condition is that a particular value does not equal the ANSI character associated with the number 252. (Using the Wingdings font, that's a check mark.) The particular value that's tested is the element in the Posted range that corresponds to the current value of ThisTransaction. The Posted variable refers to the range named Posted in the sales journal. Notice how it's declared in the second line of the procedure.

In brief, the first time that the loop executes, ThisTransaction has a value of 1. Excel examines the first value in Posted to see if it equals Chr(252): that is, to see if it appears on the worksheet as a check mark. If that value is not a check mark, subsequent statements are executed because the transaction has not yet been posted; otherwise, the transaction has already been posted and the subsequent statements (up to End If) are skipped.

On the other hand, if the current transaction has not yet been posted—if the result of Chr(252) is not found in the current element of the Posted range—the statements that follow the If should be executed, up to the End If statement. Executing those statements results in the posting of the transaction to accounts receivable.

More on Dot Notation

Next, the code begins another With block:

With ThisWorkbook.Sheets("Accts Receivable Ledger")

Inside the With blocks are references to objects (such as a worksheet), methods (such as a worksheet's Activate method), and properties (such as a cell's Font property). Some of the references are preceded by a dot (for example, .Range), with nothing immediately preceding that dot. Anything with nothing but a dot preceding it is taken to belong to whatever object is named in the With statement. In this case, all objects, methods, and properties referred to inside the With block belong to the sheet named Accts Receivable Ledger, which, in turn, belongs to ThisWorkbook.

Inside the block, several statements use ranges belonging to the Accts Receivable Ledger sheet. If the With block weren't used, the code would need to repeatedly qualify those ranges by referring to the sheet where it's found, as well as the workbook where the sheet is found. But because the With block is used, the code needs to refer to that sheet only once: in the With statement.

Each statement inside this With block accomplishes some task in the posting of a transaction from the sales journal to the accounts receivable ledger. For example, consider the first statement inside the With block:

.Range("TransactionDate").Offset(NextEntryRow, 0).Resize(1, 1) = _
    SalesDate(ThisTransaction)

Notice the following aspects of the statement:

• Because of the With statement, the fragment .Range("TransactionDate") is taken to belong to the sheet named AcctsReceivable in ThisWorkbook.
• The Offset fragment describes a range that is offset from the TransactionDate range by NextEntryRow rows and by zero columns. The first time through the loop, NextEntryRow equals the number of rows in the TransactionDate range. (Refer to the assignment statement that precedes the For loop.) So the offset to the TransactionDate range refers to one row below it and in the same column.
• The Resize fragment temporarily defines the size of the range as containing one row and one column: thus, one cell.
• This cell is set equal to the value in the Sales Journal sheet, referred to by the variable SalesDate, for the current value of ThisTransaction, which is the transaction being posted. That is, the date of the current transaction is placed in the TransactionDate range in the Accts Receivable Ledger worksheet.

This process is repeated in the remainder of the With block so that the account name and purchase amount are also posted.

The End With statement terminates the With block. Then a check mark is placed in the Posted range of the Sales worksheet by means of this statement:

Posted(ThisTransaction).FormulaR1C1 = "=CHAR(252)"

The variable that controls the offset to the ranges on the Accts Receivable Ledger worksheet is incremented. Therefore, the next time the loop executes, the sales date, account, and amount are placed one row farther down:

NextEntryRow = NextEntryRow + 1

Then the If block is terminated with an End If. (This marks the end of the statements that are executed if a transaction in the sales journal has not yet been posted.) The final statement in the loop is this:

Next ThisTransaction

Control returns to the beginning of the loop if the value of ThisTransaction does not yet exceed Accts.Rows.Count. When the loop has executed once for each row in the range named Account in the Sales worksheet, the loop ends.

After the final instance of the loop has completed, another With block is started for the ledger sheet. The Accts Receivable Ledger sheet is activated, and its pivot table is refreshed. The purpose of refreshing the data in the pivot table is to cause the table to incorporate the transactions that have just been posted to the ledger:

With ThisWorkbook.Sheets("AcctsReceivable")
   .Activate
   .PivotTables("ARSummary").PivotCache.Refresh
End With

Using Pivot Tables to Summarize Individual Accounts Receivable

The purpose of the pivot table is to summarize the current status of all the individual accounts receivable. Figure 4.8 shows the data posted from the sales journal and the cash receipts journal. Notice, for example, that Fred Howell has made two purchases, one for $326.67 and one for $165.00, and accounts receivable has been debited for those two transactions. Howell has made payment for the first transaction but not for the second. And the pivot table shows that Howell still owes $165.00 for the second transaction.

This is managed by creating a calculated field named Total inside the pivot table, using that field as the pivot table's data field, and using Account as the row field. More specifically, the following steps are taken. (You need to take them only once, when you first add the pivot table to the worksheet.)

1. Click the Formulas tab and then select Define Name from the Defined Names group.
2. Type DataRange in the Name box. In the Refers To box, type =OFFSET(TransactionDate,0,0,ROWS(TransactionDate),5) and click OK.
3. Select cell F1. Click the Insert tab and select PivotTable from the Tables group.
4. The Create PivotTable dialog box appears. In its Table/Range box, type DataRange. Make sure that the Location box contains a reference to cell F1. Click OK.
5. A pivot table schematic appears on the worksheet, the pivot table field list appears, and the Ribbon displays the pivot table options groups. Drag Account from the field list to the Drop Row Fields Here area on the worksheet.
6. On the Ribbon, click Formulas in the Tools group and then click Calculated Field in the drop-down menu.
7. In the Name box, type Total.
8. In the formula box, type = Debit - Credit and click OK.
9. If the new, calculated Total field does not automatically appear in the pivot table's data area, drag it there from the Field list.
10. Close the Field list by clicking its Close box.

The result of this maneuvering is a pivot table with one row for each account. Associated with each account in the table is a calculated field, named Total in this example. That field expresses the difference between an account's debits and its credits, thus summarizing the information in the Purchases and Payments ranges on the AcctsReceivable worksheet.

Pivot tables do not respond immediately to changes in their underlying data sources, regardless of whether the source is a worksheet range or an external data source. In this respect, pivot tables are different from worksheet formulas, defined names, and charted data series, which do recalculate immediately when their source changes.

If a change occurs to a pivot table's data source, it's necessary to refresh the pivot table, and that's the purpose of this VBA statement:

.PivotTables("ARSummary").PivotCache.Refresh

After the pivot table has been refreshed, the subroutine ends with the End Sub statement.

You can write this VBA code in slightly more efficient ways. For example, you could create more variables that refer to worksheet ranges in the Accts Receivable Ledger worksheet as well as in the Sales worksheet. However, the structure was chosen to illustrate a variety of VBA capabilities, including the With and If statements, the Offset and Resize methods, and the automated redefinition of range addresses.

These VBA subroutines and their associated buttons are replicated for the accounts payable ledger (see Figure 4.9).

The main difference in the VBA code that posts to the accounts payable ledger is that the code accesses the purchases journal instead of the sales journal. The code that manages postings from the cash payments and cash receipts journals is also similar to the code for the purchases and sales journals; the principal difference consists of which journals are accessed.