Building a Trial Balance using Excel

Spreadsheet Home Page

Financial Accounting Home Page

The SUMIF function has been with us in spreadsheeting terms for a long time now. This page will reveal it, however, to have a level of usefulness that may have remained an mystery until now! This page demonstrates how a simple built in function can help us to turn our data into information in the twinkling of an eye! You can use our new file Derive a Trial Balance using a Pivot Table in Excel in conjunction with this page: the link is on the left

This table shows the before and after of our situation: from raw data to information

	A	B	C	D	E	F	G
1	Transaction data	Value			Trading and Profit and Loss Account	£	£
2	Sales	2084			Sales		44746
3	Sales	2284		less	Cost of Sales
4	Sales	2292			Opening Stock	1000
5	Closing Stock	1200		plus	Purchases	35993
6	Purchases	1074				36993
7	Purchases	1062		less	Closing Stock	1200
8	Purchases	1018			Cost of Sales		35793
9	Opening Stock	1000			Gross Profit c/d		8953
10	vehicle license and tax	250
11	telephone	276		plus	rental income	233
12	wages and salaries	199			dividends received	218	451
13	wages and salaries	195			Gross Income		9404
14	wages and salaries	213		less	Expenses
15	rental income	233
16	telephone	265			wages and salaries	1867
17	telephone	229			Telephone	2072
18	Petrol	84			Stationery	190
19	Petrol	88			Postage	184
20	Petrol	84			Advertising	575
21	advertising	68			Petrol	796
22	advertising	65			vehicle license and tax	250	5934
23	advertising	66
24	stationery	23			Net profit		3470
25	stationery	21
26	stationery	19
27	Postage	19
28	Postage	22
29	Postage	20

Note: There are many more transactions: these are just examples

How do we do this?

The SUMIF function has the following characteristics:

=SUMIF(range,criteria,[sum_range])

We use this SUMIF function to add together all values that conform to a criterion. For example, from the table above, we used SUMIF to add together all the sales values, all of the purchase values and so on.

What we do is instruct Excel, for example, to look in a range and find every case where the word "sale" occurs; once we have done that, find the value of each incidence of "sale" and add them all together. Let's use the table above to demonstrate what this means.

Imagine the table above begins in Cell A1, we apply the SUMIF function to find the total value of Sales as follows.

In cell G2 we enter

=SUMIF(A2:A122,"Sales",B2:B122)

Note: the complete list of transactions extends down to row 122, even though we have only shown them down to row 22 in the table above).

Once we press , this function will look down Column A, find every case where Sales has been entered and then look in column B to find the values that go with Sales in column A and then adds them all together … then it places that value in cell G2 … simple!

We do the same for every element of the Profit & Loss Account:

Opening stock
Purchases
Closing stock
…

Here are the SUMIF statements for the first Opening stock and Purchases

For the Opening Stock, enter this in cell F4 =SUMIF(A2:A122,"Opening Stock",B2:B122) For the Purchases, enter this in cell F5 =SUMIF(A2:A122,"Purchases",B2:B122)

For you to do

Now you can generate the SUMIF functions you need to complete the Profit & Loss Account layout.

If you would like a copy of my spreadsheet to help you to check this, just write to me at the email address you can find at the bottom of this page.

Making the Example More Flexible

Take a look at the following:

=SUMIF(A:A,"Sales",B:B)

This function allows us do exactly what we have done before but this time, we can add to the list of transactions in Columns A and B without needing to change the function. If we didn't make this change and wanted, say to add eight more transactions, we would need to enter

=SUMIF(A2:A130,"Sales",B2:B130)

Then if we wanted to add another ten transactions, we would have to change this function, AND all others to

=SUMIF(A2:A140,"Sales",B2:B140)

Similarly,

=SUMIF(A:A,"Opening Stock",B:B)
=SUMIF(A:A,"Purchases",B:B)

Extending the Model

We can extend this model in at least two ways:

Generate a balance sheet and many other statements and tabulations

Use random numbers to generate new sets of transactions

Balance Sheet: whilst it isn't shown here, anyone who needs to and knows the layout needed, can use the ideas shown in this example to generate data that can be used to draft a balance sheet in exactly the same was as we have done for the profit & loss account.

Random Numbers: we can use random numbers to help us to generate new questions … we are thinking here of teachers, trainers and examiners who might need to generate a number of similar questions: this is how this part works and by the way, our own spreadsheet uses this function too.

The beauty of using this approach is that each time we press the key, Excel uses the RANDBETWEEN function to generate a whole new batch of random numbers and therefore a whole new question AND solution. Be careful, though, to make sure you keep the right question with the right solution … think about how this might be done! (The answer's in my spreadsheet file if you're not sure!)

In cell A3 we have =RANDBETWEEN(1800,2300)

This means that Excel will generate a random number from 1800 to 2300 inclusive when of is pressed.

All Sales transactions have this function in column B

All Purchase transactions have =RANDBETWEEN(900,1100)
All Wages and Salaries transactions have =RANDBETWEEN(180,230)

and so on; but note that items such as Opening Stock and Closing Stock only need one entry in a simple example like this and you could enter a value for them rather than a RENDBETWEEN value but make sure to change this value if you make your RANDBETWEEN values much larger than we have seen here otherwise the stock values will be unrealistic.

In fact, we can set these values at any level: sales could be from 100 to 105 or 100000 to 5000000; purchases could be from 3000 to 4500 or from 675000 to 1000000

Choose the values you wish: for young students or for people new to accounting, keep the numbers small!

Vital Information

We need to know that =RANDBETWEEN does not work automatically, although it might be installed on your computer. If it is not installed you will face the #NAME! error when you try to use it.

In order to use =RANDBETWEEN, do the following:

Tools
Add-Ins
Analysis ToolPak … click to put a tick next to this
OK

It should work now although some of you may be asked to install the CD with the Excel software on it.

Note: there is also the Analysis ToolPak - VBA option in the Add-Ins selection … you don't need to tick this.

Conclusions

The SUMIF function can be a very useful function and we have demonstrated here one of the ways in which we can use it, to help us to manipulate data in such a way that we turn data into information.

This page has also combined SUMIF with RANDBETWEEN in a way that teachers, trainers and examiners might appreciate: by helping them to generate many versions of the same question.

What the Spreadsheet Might Look Like Now

In full, then, the top part of our spreadsheet looks like this:

	A	B	C	D	E	F	G
1	Transaction data	Value			Trading and Profit and Loss Account	£	£
2	Sales	=RANDBETWEEN(1800,2300)			Sales		=SUMIF(A:A,”Sales”,B:B)
3	Sales	=RANDBETWEEN(1800,2300)		less	Cost of Sales
4	Sales	=RANDBETWEEN(1800,2300)			Opening Stock	=SUMIF(A:A,” Opening Stock”,B:B)
5	Closing Stock	=RANDBETWEEN(1200,1250)		plus	Purchases	=SUMIF(A:A,” Purchases”,B:B)
6	Purchases	=RANDBETWEEN(900,1100)				=F4+F5
7	Purchases	=RANDBETWEEN(900,1100)		less	Closing Stock	=SUMIF(A:A,” Closing Stock”,B:B)
8	Purchases	=RANDBETWEEN(900,1100)			Cost of Sales		=F6-F7
9	Opening Stock	=RANDBETWEEN(900,1100)			Gross Profit c/d		=G2-G8

Duncan Williamson
14 July 2002 updated 27 June 2003