Excel Tip: Pivot Tables
David Carter's series of PivotTable tutorials on our AccountingWEB-UK site have become an Internet legend. The first tutorial, Budgeting with PivotTables, has been accessed more than 9,600 times and was highlighted as a particularly valuable resource by the Internet Scout Report on business and finance.
This page gives you an overview of what is possible with this relatively unsung feature within Microsoft's Excel spreadsheet program. Each tutorial (listed below) comprises an Excel file containing the source data, together with a Word file giving step by step instructions on how to create the PivotTable. If you are new to PivotTables, David suggests starting with either the Modelling or the first Budgeting tutorial.
Budgeting with PivotTables
Shows how to set up monthly budgets for several departments within a company, then use a PivotTable to calculate the overall company total budget. "Drill down" on the monthly figures to check doubtful balances. Learn how to revise individual departmental budgets, then use the Refresh Data command to recalculate the overall company totals.
Budgeting with PivotTables - Part Two
Follows on from the first Budgeting tutorial. Shows how to add the actuals to the budgets each month as they occur, then use PivotTables to produce monthly profitability reports comparing Actual versus Budget, and Variance.
Modelling and "What If?" Analysis with PivotTables
In this exercise you are a magazine publisher who is forecasting future income streams dependent upon variables such as the number of new subscribers anticipated per month, number of pages per issue, estimated advertising rate per page, etc etc. It shows how, by using a PivotTable for the underlying structure, you can build a model that is reliable, that is properly documented and whose structure can easily be changed at a later date.
Analyze Sales Data with PivotTables
In this exercise you import into Excel five thousand sales invoice item records taken from an accounting/ERP package. Then use PivotTables to analyse sales and gross profit any way you want - sales by customer by month, sales by product, sales by sales executive, gross profit by sales exec, rank your top selling products by value, etc etc.
Audit and Financial Reporting with PivotTables
Takes two thousand transactions from an accounting package and shows how to use PivotTables to check for any errors - eg transactions analysed to the wrong nominal account, why Debtors Control doesn't agree with the sum of customer balances, etc. After proving that the data has been correctly analysed, use a PivotTable to generate the Trial Balance, Profit and Loss report and Balance Sheet.
Sage Line 50 and PivotTables
Many users will wish to use PivotTables to analyze data in their own accounts or ERP package. But the data files in such packages are designed for transaction processing rather than analysis. Based around the popular Sage Line 50 accounts package, this tutorial shows you what's involved in taking raw data from an accounts package, and modifying it preparatory to analysis in a PivotTable.
SagePivot - Extract "Pivot-ready" Data from your Accounts Package
For Sage Line 50 users ONLY. Shows how a specialist data extraction program can assemble the data from multiple data files into the one large "flat" file that is necessary for data analysis via PivotTables.
IT consultant David Carter has been installing accounting and stock control systems for 10 years. He discovered PivotTables in Excel 95 and has helped a range of clients exploit them for a range of applications over the past five years. An expert on mid-range enterprise resource planning systems, he trained with IBM and has written on business systems for Financial Director magazine and the Financial Times. He can be reached by email at firstname.lastname@example.org.