Regular readers will know that I am a huge fan of Pivot Tables in Excel and I thought it might be useful to start an occasional series highlighting some of their uses. I thought I would start with an accountant's staple - Cash Book Analysis.
If you are not yet familiar with Pivot Tables, it will almost certainly help to view our free video on the subject before you read the rest of this article.
Typically, accountants use Excel to automate a traditional cash book by mimicking its manual equivalent with the added benefit of the totals being automatically calculated. A slightly different approach, using the power of Pivot Tables, reduces the data input and significantly increases the reporting flexibility.
Instead of replicating the net balance of each cash transaction in the column associated with its category (as in a manual cash book), simply have a column that contains the name of that category (this can be a drop-down list using data validation) and include the net value once.
By dragging this field into the column area of the pivot table, with the net amount as data, you replicate the traditional approach - but can choose to just show totals rather than the detailed transactions (if you wish). Using the "=month()" formula, you can have a column alongside the data that strips the month number from the date. If you use this as a the Page field in the Pivot Table you can provide monthly summaries.
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.
About Glen Feechan
Chartered Accountant with extensive experience in management reporting, Microsoft Excel and business process improvement.
Also runs a spreadsheet development business at http://www.needaspreadsheet.com.
Provides consulting on the above specialisms as well as providing cost savings to clients at no cost.
Editor and chief contributor to Not Just Numbers Ezine - The ezine for those who know it's not just about the numbers.
Cost Saving for SMEs, Overhead reduction, Excel, Management Reporting, Process Improvement, PISO.