Duncan Williamson: Cost Data and Performance Reports costdata.html

Introduction

The purpose of this page is to demonstrate how we can derive a Trial Balance from a list of bookkeeping transactions by using a series of in built techniques. This page can be read in conjunction with the page we prepared earlier: Using the SUMIF Function to Prepare a Profit & Loss Account ... the link is on the left.

This page will benefit anyone who has to attempt to model at least part of a bookkeeping system simply by using a spreadsheet and anyone who has generated a list of bookkeeping transactions and needs to analyse them in readiness for preparing the profit and loss account and balance sheet.

The list of transactions shown in this page is not extensive but the model we describe is flexible enough that with only relatively minor changes it could be made to deal with a list as large as your computer will accept.

Bookkeeping Transactions

Here are the transactions we generated for this exercise. All we did was to put this table into a spreadsheet, starting in cell A4 with the "details" title.

date details amount Dr account name Dr account number Cr account name Cr account number

37622 cash deposited by owner 10000 cash 2 capital 1

37622 purchase of goods 1491 stocks of goods for resale 11 cash 2

37623 sale of goods 1578 cash 2 sales 10

37626 wages 500 wages 13 cash 2

37628 sale of goods 1469 M Hanson 7 sales 10

37628 purchase of goods 1171 stocks of goods for resale 11 J Smith 6

37631 electricity 100 electricity 4 cash 2

37631 telephone 50 telephone 12 cash 2

37631 postage 15 postage 8 cash 2

37634 rent of building 750 rent of building 9 cash 2

37634 sale of goods 1601 cash 2 sales 10

37636 purchase of computer 1000 stocks of goods for resale 11 I Jack 5

37638 wages 350 wages 13 cash 2

37640 sale of goods 1702 D Edwards 3 sales 10

37643 purchase of software 450 stocks of goods for resale 11 J Smith 6

37645 wages 375 wages 13 cash 2

37647 postage 5 postage 8 cash 2

37649 purchase of goods 1175 stocks of goods for resale 11 cash 2

37649 electricity 15 electricity 4 cash 2

37650 sale of goods 1612 cash 2 sales 10

37652 purchase of goods 1433 stocks of goods for resale 11 cash 2

37652 wages 350 wages 13 cash 2

LOOKUP Table

As a matter of interest, we have set up a LOOKUP table to help us to identify the account code numbers. This is not an especially complicated thing to do once you are familiar with LOOKUP functions ... if you download a copy of the spreadsheet that accompanies this page you will see what we have done: click here to get the spreadsheet.

Generate a Pivot Table

All we do now is to generate a Pivot Table from the transactions, as follows:

put the cursor anywhere inside the table of transactions and select

Data
Pivot Table and Pivot Chart Report

Now follow the Wizard by putting (drag and drop) the Dr account number and Dr account name in the ROW area of the Pivot Table Layout

Put the Cr account number and Cr account name in the COLUMN area of the Pivot Table Layout

Finally put the amount in the DATA area of the Layout and make sure that you have set the argument for it to SUM so that the label for it reads Sum of account.

Your Layout dialogue box should look like this now:

Now click OK and then Finish and you will have a matrix in your spreadsheet file that looks like this:

What this matrix does for us is that it adds together all related transactions and tells us the total cash received and paid, the amount spent on postage, the capital investment and so on. We are almost at the stage of having prepared our Trial Balance now!

That Pivot Table starts in A1 of the Sheet called pivot and TB and here is the first stage in taking the Trial Balance from it: this table starts in cell I17 of the same sheet. Here is the table and then the formulae that the table uses:

First Draft Trial Balance from the Pivot Table

Before we explain the meaning of #N/A, let's just take a look at the LOOKUP formulae we have used here first.

The Formulae Underlying the First Draft Trial Balance

We have used the VLOOKUP and the HLOOKUP functions in this first draft TB but notice that there is a small difference between the VLOOKUP function in column J versus the VLOOKUP and HLOOKUP functions we have put into columns K and L. The difference relates to the final entry in the function "...,0". Read Excel's Help files for LOOKUP functions but briefly what this 0 (or it might be 1) does is to tell Excel whether the list we are working from is sorted into alphabetical or numerical order or not.This is what the dialogue box tells us if we use the Function Wizard to help us with this part of the function ... this 0 value relates to the Range_lookup:

#N/A

Now the #N/A problem. What this error message means is that Excel has done what you asked it to but has found nothing relating to what you asked it to find! Literally, #N/A means it couldn't find what you wanted: at least you know it hasn't entered the wrong values!

Take a look at what has happened and you will see that Excel has very cleverly understood what we have done and what we wanted to do. So in the first row of the table it has prepared it has found that the capital account has no debit balance/entry but it does have a credit balance. When you work through this yourself you might be shocked that Excel has been able to distinguish between the debit and credit balances but it can!

You also need to understand and work with the difference between the VLOOKUP and HLOOKUP functions: VLOOKUP for the Debits and HLOOKUP for the Credits. It all makes sense once you worked through it at least once!

Getting Rid of the #N/A Problem

The #N/A problem is easy to solve and we can say at this stage that if you are already an experienced Excel user you may already have realised that you can combine several aspects of the work we are doing. For example, the #N/A problem can be dealt with in one fell swoop: we could have resolved the issue in the previous table by combining what we did there with what we are about to do.

Still, don't worry, since we are not dealing with massive spreadsheet files, not combining our work will not cause us massive problems. Here is the finished Trial Balance and following that we show the formulae underlying this final table.

The formulae we need to turn the first draft of the TB into the final draft are here:

Conclusions

That's it! That's all there is to preparing a Trial Balance from a set of bookkeeping transactions. We have used some relatively complex functions and ideas in this discussion but by reviewing this page and Excel's Help pages there is nothing that we cannot do to deal with our transactions!

Duncan Williamson
27 June 2003