Banking, Bookkeeping & Excel - in practice, lesson 2. Excel skill level: Focussed beginner Accounts level: novice
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)
Published:
Browse All Articles > Banking, Bookkeeping & Excel - in practice, lesson 2. Excel skill level: Focussed beginner Accounts level: novice
By an accountant
This is an f.a.q. to go with my concurrent bookkeeping articles.
This will work for anyone anywhere,
First principles of program design are first to perfect the manual process.
This article defines the tools I choose to use from our modern tool box.
APPLIED TECHNIQUES
Pivot table reporting - dynamic length - single column internal import codes from external LOOKUP()
Pivot tables are a book subject in their own right so suffice it to say for our purposes because they are dynamic in length we have to include ALL data to be reported in the data set. Pivot tables cannot concatenate text columns to dynamically create comma separated value output, which ultimately still has to be done by hand at the end on a fixed copy of the data. Fixed copy means a copy of the pivot table copied separately so it is no longer dynamic so we can further work on it for final export to other systems for tax and statutory accounts production.
tables DSUM() layout - fixed length - external codes from table - multi columns
I have not decided yet whether to publish this technique. The thing is it may now be obsolete so I remain reflective about the question. Their advantage is because data tables are fixed in length they can be included as part of a wider spreadsheet solution, whereas pivot tables do not lend themselves to such easy inclusion. You can add data columns to the end of the table and calculate on from that, whereas with pivot tables you can't. This affects coding for onward transmission to accounts production systems.
DSUM() Bug warning
Beware that this formula checks only the first FOUR characters of the criteria. It has been this way for many many years. I have no idea why it has not been fixed (I gave up reporting it years ago).
If using pivot table reports you do NOT need this formula.
I used DSUM for decades, so I am not saying do not use it, just be aware of its limitation or you will lose much hair. For the curious: its usefulness lies in making the criterion cell the input cell of a data table.
IF(CODE="Open", just add this transaction,add this transaction to the balance above)
This checks if the CODE column entry is the text OPEN and if it is adds only the value from the AMOUNT column (i.e. the opening balance), if it is anything else it adds the BALANCE from the previous transaction (the row above) to the value in the AMOUNT column to obtain the current new BALANCE. This separates SOURCES of transactions from each other, so bank accounts and credit card transactions lists do not become mixed together.
The closing balance MUST always be zero because the final transaction is always the closing balance typed into the AMOUNT column as a transaction, with its sign reversed; as such deducting the typed in closing balance from the calculated closing balance, so it has to be zero if all the transaction values have been correctly entered. If not zero then it shows the amount of the error as the value in the calculated BALANCE column. Remember never have two identical column headings.
SUMIF() for cumulative balance
I myself am new to this technique from a question I asked. Here is how I see it working:
![SUMIF Screenshot]()
and the file itself
SUMIF-Balance-methods.xlsx
All my trial balances and bank and credit card accounts total to zero, by including the final total/BALANCE as a negative transaction. This means anything not zero has an error which is easy to see.
For completeness cross cast check that across = down
Traditional methods use analysis columns across the page. I no longer use these because we can use the computer to produce such layouts as reports without our having to do any of the laborious work. This is where we see the impact of computer techniques on manual methods: where we used to extend everything by hand, we can now let the machines do it. I question the sanity of anyone still using extended analysis, likewise anyone who continues to enter sub totals in lists. All we need these days is the basic annotated list. The rise of the machines allows everything else to be automated and in mere seconds. This is how I am able to spend so much time with my clients instead of number crunching.
Hard typed balance columns v. calculated BALANCE columns
Where our source data has balances included it is useful to include a BALANCEx column that is the balances entered as hard type. This means any errors against the calculated BALANCE column are very easy to find, an extra column subtracting one from the other instantly shows where the errors are.
We are working with money. That means we never go beyond two places of decimal and any potential results beyond 2d MUST be rounded to two places. Failure to do this will ripple through all your results introducing errors. This is a short point, but VERY important.
Any division must be rounded (which includes multiplication by decimals).
This text is explaining WHAT to do with the SORT features, NOT how to learn them.
Multiple SORT columns. Original - Reverse (backward csv's) - New data (input) - Final - For
headers removal.
These rather cryptic descriptions describe the following separate sometimes additional columns for use as sorting/ordering (Index type).
Where data is imported in the wrong (usually reverse) order, use a SORT column to fix it, do NOT use the date column, because multiple items on the same date will not sort reliably.
Where you want certain datatypes in different places use decimals .001 .002 and thousands
1001 1002 3001 3002 etc in a SORT column.
Where you add data (transactions) on the end as a way to add data, add the sort numbers and make sure you are not duplicating existing numbers, or you will have to start over because there is no way to undo this error if the SOURCE column is the same.
Original sort order is critical. This is 1 2 3 4 5 etc series of numbers against the transactions in the order they were originally entered/imported. The reason it is critical is because this is the (audit trail) link back to the source documents such as bank and credit card statements or cash receipts or sales tax records and so on. You MUST preserve this original order; which is to say the ability to always view the transactions in the same order as the documents of "prime entry", otherwise it is like setting fire to your filing system. To put it mildly. Thankfully it is rare these days, but when I lose the original order I have to delete the file and start over again. As such a constantly incrementing filename 01 02 03 etc with frequent incremental saves is a data saver: see (8) below for more about this.
Use as many CODE columns as you like, you can analyses and sub analyse to your heart's content. Add new CODE columns by inserting a column and copying into it an existing code column's contents and start from there, whether more detailed or less detailed. Make sure no two columns have identical descriptions, so CODE1 CODE2 etc, but always keep your CODE column intended to be used for the final output headed CODE. Then we have a standard.
It can be argued that here lies the power of this method, practically instant flexible reporting.
These are columns mostly added after basic data entry is complete and often based on calculated formulae, so take very little time to create and facilitate reporting, just like fields in a database.
These below allow cross tab reporting by month and down/across by sales tax return periods, tax years and accounting years. We could add a business name column if we wanted to report across multiple business entities, for example where someone runs both a company and a self employment: both these interacting with their private finances. This approach leaves nothing for the tax authorities to examine for unpleasant surprises - a gap most small businesses leave wide open.
Multiple DATE columns - Std 12/12/2012 -
Pivots '201201 Vat '1209 Accyear 2012 Tax Year 2011
B/S - P&L - Business/Private Alimony/Children/Home/Occupancy etc
These are key words illustrating how to get pivot tables to report in very useful ways. Insert columns and enter such words which Excel can then use for reporting.
Increment filenames with 01 02 03 etc, for more details see this article
Regards,
Anthony
Chartered Accountant
United Kingdom
This is an f.a.q. to go with my concurrent bookkeeping articles.
1. Why this subject?
Previously I have carefully avoided these details, so now is the time. You need to know what you need to know. You can definitely learn as you go along in practice, and this article is my attempt to offer some perspective about what I see as the lack of any need for complex Excel specific skills. This is not to say we do not use some very powerful Excel capabilities.2. Who is this for?
Readers of my two initial articles on this subject written for the self employed/owner manager as an introduction and a primer (or 101) .3. Where? Country:
This will work for anyone anywhere,
MOTIVATION
I think that anyone interested in developing their Excel bookkeeping skills especially after reading my first two articles on this subject, is due a summary of what might be characterised as the syllabus; the Excel skills applied in achieving the results. These skills are a melange of Excel functions and accounting control methods we have used since long before computers were around. I used to do all this by hand for we had no (PCs) computers at all, they had not been invented in the 70s. I am linking the traditional ways with modern methods (pun intended if you are a programmer). We did eventually move to electronic adding machines from the mechanical ones, yet all recording was handwritten, reports being copy typed with mechanical typewriters and we used to call out and read over the handwritten text against the typed versions and initial and date the carbon copy ("CC", the real thing) as correct.First principles of program design are first to perfect the manual process.
This article defines the tools I choose to use from our modern tool box.
APPLIED TECHNIQUES
1. Data entry
Debits & Credits - fear not! You do NOT need to understand these. Even so you can be comforted they are the basis of all this work. Perhaps I will write an article about these just for interest. NOT to be confused with whether an entry is "plus" or "minus".2. Reporting
Pivot table reporting - dynamic length - single column internal import codes from external LOOKUP()
Pivot tables are a book subject in their own right so suffice it to say for our purposes because they are dynamic in length we have to include ALL data to be reported in the data set. Pivot tables cannot concatenate text columns to dynamically create comma separated value output, which ultimately still has to be done by hand at the end on a fixed copy of the data. Fixed copy means a copy of the pivot table copied separately so it is no longer dynamic so we can further work on it for final export to other systems for tax and statutory accounts production.
tables DSUM() layout - fixed length - external codes from table - multi columns
I have not decided yet whether to publish this technique. The thing is it may now be obsolete so I remain reflective about the question. Their advantage is because data tables are fixed in length they can be included as part of a wider spreadsheet solution, whereas pivot tables do not lend themselves to such easy inclusion. You can add data columns to the end of the table and calculate on from that, whereas with pivot tables you can't. This affects coding for onward transmission to accounts production systems.
3. Summing
DSUM() Bug warning
Beware that this formula checks only the first FOUR characters of the criteria. It has been this way for many many years. I have no idea why it has not been fixed (I gave up reporting it years ago).
If using pivot table reports you do NOT need this formula.
I used DSUM for decades, so I am not saying do not use it, just be aware of its limitation or you will lose much hair. For the curious: its usefulness lies in making the criterion cell the input cell of a data table.
IF(CODE="Open", just add this transaction,add this transaction to the balance above)
This checks if the CODE column entry is the text OPEN and if it is adds only the value from the AMOUNT column (i.e. the opening balance), if it is anything else it adds the BALANCE from the previous transaction (the row above) to the value in the AMOUNT column to obtain the current new BALANCE. This separates SOURCES of transactions from each other, so bank accounts and credit card transactions lists do not become mixed together.
The closing balance MUST always be zero because the final transaction is always the closing balance typed into the AMOUNT column as a transaction, with its sign reversed; as such deducting the typed in closing balance from the calculated closing balance, so it has to be zero if all the transaction values have been correctly entered. If not zero then it shows the amount of the error as the value in the calculated BALANCE column. Remember never have two identical column headings.
SUMIF() for cumulative balance
I myself am new to this technique from a question I asked. Here is how I see it working:
and the file itself
SUMIF-Balance-methods.xlsx
4. (Business) Controls - error trapping
Zero base accounting, the beauty of nothing.All my trial balances and bank and credit card accounts total to zero, by including the final total/BALANCE as a negative transaction. This means anything not zero has an error which is easy to see.
For completeness cross cast check that across = down
Traditional methods use analysis columns across the page. I no longer use these because we can use the computer to produce such layouts as reports without our having to do any of the laborious work. This is where we see the impact of computer techniques on manual methods: where we used to extend everything by hand, we can now let the machines do it. I question the sanity of anyone still using extended analysis, likewise anyone who continues to enter sub totals in lists. All we need these days is the basic annotated list. The rise of the machines allows everything else to be automated and in mere seconds. This is how I am able to spend so much time with my clients instead of number crunching.
Hard typed balance columns v. calculated BALANCE columns
Where our source data has balances included it is useful to include a BALANCEx column that is the balances entered as hard type. This means any errors against the calculated BALANCE column are very easy to find, an extra column subtracting one from the other instantly shows where the errors are.
5. Rounding
ROUNDingWe are working with money. That means we never go beyond two places of decimal and any potential results beyond 2d MUST be rounded to two places. Failure to do this will ripple through all your results introducing errors. This is a short point, but VERY important.
Any division must be rounded (which includes multiplication by decimals).
6. Analysis
Multiple CODE/analysis columnsThis text is explaining WHAT to do with the SORT features, NOT how to learn them.
Multiple SORT columns. Original - Reverse (backward csv's) - New data (input) - Final - For
headers removal.
These rather cryptic descriptions describe the following separate sometimes additional columns for use as sorting/ordering (Index type).
Where data is imported in the wrong (usually reverse) order, use a SORT column to fix it, do NOT use the date column, because multiple items on the same date will not sort reliably.
Where you want certain datatypes in different places use decimals .001 .002 and thousands
1001 1002 3001 3002 etc in a SORT column.
Where you add data (transactions) on the end as a way to add data, add the sort numbers and make sure you are not duplicating existing numbers, or you will have to start over because there is no way to undo this error if the SOURCE column is the same.
Original sort order is critical. This is 1 2 3 4 5 etc series of numbers against the transactions in the order they were originally entered/imported. The reason it is critical is because this is the (audit trail) link back to the source documents such as bank and credit card statements or cash receipts or sales tax records and so on. You MUST preserve this original order; which is to say the ability to always view the transactions in the same order as the documents of "prime entry", otherwise it is like setting fire to your filing system. To put it mildly. Thankfully it is rare these days, but when I lose the original order I have to delete the file and start over again. As such a constantly incrementing filename 01 02 03 etc with frequent incremental saves is a data saver: see (8) below for more about this.
Use as many CODE columns as you like, you can analyses and sub analyse to your heart's content. Add new CODE columns by inserting a column and copying into it an existing code column's contents and start from there, whether more detailed or less detailed. Make sure no two columns have identical descriptions, so CODE1 CODE2 etc, but always keep your CODE column intended to be used for the final output headed CODE. Then we have a standard.
7. Helper or Analysis columns
It can be argued that here lies the power of this method, practically instant flexible reporting.
These are columns mostly added after basic data entry is complete and often based on calculated formulae, so take very little time to create and facilitate reporting, just like fields in a database.
These below allow cross tab reporting by month and down/across by sales tax return periods, tax years and accounting years. We could add a business name column if we wanted to report across multiple business entities, for example where someone runs both a company and a self employment: both these interacting with their private finances. This approach leaves nothing for the tax authorities to examine for unpleasant surprises - a gap most small businesses leave wide open.
Multiple DATE columns - Std 12/12/2012 -
Pivots '201201 Vat '1209 Accyear 2012 Tax Year 2011
B/S - P&L - Business/Private Alimony/Children/Home/Occu
These are key words illustrating how to get pivot tables to report in very useful ways. Insert columns and enter such words which Excel can then use for reporting.
8. File saving protocol
Increment filenames with 01 02 03 etc, for more details see this article
Regards,
Anthony
Chartered Accountant
United Kingdom
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
"Yes" vote above.