Excel Tip: Audit & Financial Reporting Using Pivot Tables

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter. The tutorials were originally developed for a UK audience, but the techniques work equally well for US needs.

Be sure to check out Carter's other pivot table tutorials, including:



Tutorial 4: Audit & Financial Reporting

In this follow-up to his first Sage Line 50 tutorial David Carter audits 2,000 accounting records (trial balance, debtors control summary, bank reconciliation, daybook analysis) and prints the financial reports (balance sheet, profit and loss, departmental profit and loss, customer sales summaries) – all within a fraction of the time it would take by conventional methods.

Says David: “This is the crowning piece in the series, and if there was just one tutorial I would like an accountant to see in order to understand the sheer power of Excel and pivot tables, this would be it. We’ve extracted the data from Sage using the SagePivot program, and it has come over “pivot-table ready” for immediate analysis, so we’ve been able to put Excel straight to work. The results are pretty amazing.”

Finally, David illustrates the one really dangerous design fault in Excel and requests you all to send an email to Bill Gates asking him to sort it out.

Now read on!

This “Audit and Financial Reporting” tutorial shows you how to use Excel pivot tables to quickly identify errors in a set of several thousand accounting records, then print financial and departmental profitability reports – all within a fraction of the time the same tasks would take using conventional methods.

The tutorial is built around two files. The SAGEPIVOT.CSV file holds 2,052 records taken from a set of demonstration data supplied by Sage with version 6 of their Line 50 accounts package. This data has been output from Sage by the “SagePivot” data extraction program. In addition, there is a Word file which tells you how to analyse the records using Excel.

Please start by printing out your instructions for downloading the files from AccountingWEB. They come in a two-page Word file. When they appear on your screen, select File – Print to print them off.
Double click now for these STARTUP instructions.

Using the instructions in Startup, now return to AccountingWEB and download the two files which you will be using in the tutorial. They are the WORD file containing the Excel instructions, and the SAGEPIVOT.CSV file containing the sample data.

In our experience a small proportion of users will encounter difficulty downloading the files. If you have any problems please email Rob Benson at AccountingWEB and we will email you copies of the data files by return.

INTRODUCTION TO THE AUDIT & FINANCIAL REPORTING TUTORIAL

This tutorial is the promised Part Two of the first Sage Line 50 tutorial, which was released on 2nd February 2000.

It differs from the first tutorial in two ways. Firstly, it uses a much fuller set of Demonstration data supplied by Sage with version 6 of Line 50. (from the Sage menu: – Open – Demonstration company). There are 2,052 records covering 12 months from 31/12/1998 to 31/12/1999. This demonstration data has been amended slightly, the main change being to assume that the company’s financial year runs from July to June.

The second difference is that, instead of exporting the data from Sage via one of Sage’s standard reports, we have used a specially written program to generate the data file. This program, called “SAGEPIVOT”, is designed to extract the data from Sage and collate it in a format ideal for analysis by Excel and pivot tables (more details about SAGEPIVOT at the end of these instructions).

To some extent, therefore, the first Sage tutorial was “Before” while this second one is “After”. The first tutorial spent a lot of time getting the raw data into a fit state to analyse. In this tutorial that task has already been done for you. You are therefore free immediately to start analysing the data and to deploy all the extraordinary analytical power that Excel has to offer.

If this is the first time you have looked at pivot tables, consider first working your way through the “Budgeting with Excel pivot tables” tutorial (available in the Expert Guides section). This is designed to familiarise the beginner with the basic workings of pivot tables. The current tutorial is more advanced, in that it is a lot longer (21 pages against 8), and also assumes some previous experience.

The tutorial should take you about 40 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard.

You will make extensive use of both left and right mouse buttons. When I say “right click” I mean click the right mouse button. When I say “click” on its own, I mean “click the left mouse button”.

You can run this tutorial on any version of Excel from Excel Five (released in 1994) onwards, that is, on Excel 5, on Excel 7 (for Windows 95), on Excel 97 or on Excel 2000. The tutorial was developed and tested on Excel 97. Notes in square brackets [] indicate where other versions of Excel differ. If you are not sure which version you have, find out now by starting up Excel. At the right of the main menu click on Help, then About Microsoft Excel. The version is at the top of the screen.

Before starting you need to be sure that the date formats on your PC are correctly set for your part of the world. Otherwise the pivot table may not be able to group dates correctly. Click on: Start - Settings - Control Panel - Regional Settings. If you are based in the United Kingdom they should read English (United Kingdom) or English (British).

Ideally, the instructions in this tutorial should be sufficiently clear for you to be able to work your way through to the end. If you find any of the text unclear, please read it a second time. If after a second reading it is still unclear, please email me a note of it at david.carter@moose.co.uk. If any section is regularly causing difficulty, it is no problem to revise it.

If you find that the instructions seem OK, but at some point what’s on your screen doesn’t seem to tally with what the tutorial says, try clicking several times on Excel’s Undo icon (the left curving arrow in the icon bar at the top). This will cancel your previous steps. But if the error does not immediately come to light, it may be better not to try to correct any errors but to go back and start over again. This will probably be quicker in the long run and, in any case, it is not a bad idea to go through the tutorial twice. Second time around you will find that a lot of things fall into place that didn’t really register the first time because you were just concentrating on following the instructions.

A. 1. IMPORT THE “SAGEPIVOT” CSV FILE INTO EXCEL

Start up Excel. File - Open

Go to the folder that contains SAGEPIVOT.CSV You can’t see SAGEPIVOT.CSV

Change Files of Type: to All files SAGEPIVOT appears. Open it.

The data appears. It is sorted in nominal account (NLAC – the first column) order

Click on the “1” brick to the left of Row 1. The top row is highlighted

Click the B icon. The column headings in Row 1 become bold.

Highlight the whole sheet by clicking onto the top LH brick above 1 and left of A. Then:

Double click between column headings A and B to adjust all columns to best fit.

Click on the “2” brick to the left of Row 2. The whole row is highlighted

Freeze the column headings by selecting from the main menu: Window - Freeze Panes

Now highlight the AMOUNT column by clicking the “E” column heading. Then, from the menu:

Format – Cells – Number - Dec Places 2 - 1000 separator? YES - –1234.10 in red at the bottom

Centre these columns: NLAC, PD, TRAN, DEPT, NLTYP, ACTYP, SGGPNO, SGTPNO, SPN

Left justify these columns: REFNO, ACCTANAL1, ACCTANAL2. ACCTANAL3, ACCTNO

Click back onto cell A2.

Finally, select: File - Save as: SAGEPIVOT

Save as Type: change to Excel Workbook

Note: the original SAGEPIVOT.CSV file remains unchanged. If you want to do the tutorial a second time, you still have SAGEPIVOT.CSV on your disk in its original form.

A. 2. CONTENTS OF THE DATABASE

Excel can read CSV files directly, so the SAGEPIV.CSV file opened straight away. The SagePivot program has assembled 32 fields in all for each record. In a spreadsheet the records are displayed underneath each other, one per row (known as “List view”). This makes it easy to see multiple records at once, but difficult to see lengthy records like these because you can’t display all the fields on the screen. Therefore try displaying the records in traditional data entry “Form view” as follows:

Click anywhere on the worksheet Select from the menu: Data - Form

One record appears in “Form” view. At the top right it says record 1 out of 2052.

Click on Find Next twice. Record 3 out of 2052 appears.

Looking at this record (“McNally Machine”), basically the first 11 fields up to DEPT are the details entered whenever you type in a transaction. The fields after that are analysis fields which will come in useful when summarising the data. The fields are:

NLAC = the Nominal Account Code
NL_SHORT = the first 15 letters of the Nominal Account Description (NLNAME)
ACCT_SHORT = the first 15 letters of the Supplier or Customer’s Name (ACCTNAME)
DETAILS = the narrative typed in by the bookkeeper
AMOUNT = the Amount of the transaction
REFNO = the Reference Number of the transaction, i.e. Invoice no, cheque no etc
**PD = the period in the company’s financial year
NO = the transaction reference number. This is a unique sequential number automatically allocated to the transaction by Sage.
TRAN = the Type of transaction. SI = Sales Invoice, JD = Journal Debit etc
DATE = the Date of the transaction (i.e the date typed in by the bookkeeper).
DEPT = the Department code (if any) typed in by the bookkeeper
DEPTNAME = the Department Name
**FINYR = the company’s Financial Year
***BKPAGE = the Bank statement Page number (see section B 10)
TC = the Tax code, i.e. 1 = 17.5% VAT, 0 = zero rate VAT etc
*DATENT = the date the transaction was entered (i.e the POSTING DATE)
BALANCE = the current Balance on the nominal account
NLTYP = the type of Nominal account (Profit and Loss PL or Balance Sheet BS)
ACTYP = another nominal account type (Bank, Control or Posting account)
DEL = transaction Deleted? 1 = Yes 0 = No
*SAGEGROUP = a nominal group (eg Administrative Expenses)
*SGGPNO = nominal group number (purely for sorting purposes)
*SAGETOPGROUP = a nominal TopGroup (eg Overheads, Fixed Assets)
*SGTPNO = the Topgroup number (purely for sorting puroses)
NLNAME = the full Nominal Account Description
COA = the chart of accounts containing SAGEGROUP and SAGETOPGROUP
ACCTANAL1 = Analysis Code 1 in the Customer or Supplier record
ACCTANAL2 = Analysis Code 2 ” “ “ “
ACCTANAL3 = Analysis Code 3 “ “ “ “
ACCTNO = the supplier or customer account code
**SPN = “S” for Sales, “P” for Purchases, “N” for Nominal
ACCTNAME = the full Name of the Supplier or Customer

[Note for SagePivot users: Items marked * only come over via SagePivot if you are using version 6 of Line 50. If you are using version 5 they come over as blank fields. There are inconsistencies in the Sage ODBC drivers. Items marked ** are not present in release 1 of SagePivot, but are planned for release 2. Item marked *** (BKPAGE – the bank statement page) is not available in Sage.but ought to be (see section B. 10 for further discussion]

Finally, select Close to return to the usual spreadsheet “List” view.

A. 3 MOVING AROUND THE FILE

Click on A2. Now go to the bottom of the file as follows:

Find the second “Ctrl” key at the bottom right of the keyboard under the Enter key. Hold it down with the thumb of your right hand.

Now tap Down arrow with the middle finger of your right hand. You go to the bottom row, 2053.

Get into the habit of doing Ctrl+Home, Ctrl+Down Arrow, Ctrl+Page Down etc etc with the right hand only, not both hands.

Now return to the top of the file, cell A2. (hold down right Ctrl key - press Up Arrow - let go the Ctrl key - Down Arrow once)

A.4 THE TWO-STAGE PROCESS – AUDITING AND FINANCIAL REPORTING

Producing a set of accounts is a two-stage process. Firstly, you must check the transactions to identify and correct any errors (the “audit” stage). Then you go onto stage two and extract the financial reports such as the Profit and Loss report and Balance Sheet.

Normally, therefore, you would export the data from Sage twice. On the first copy of the data you would do the audit checks, find the errors, then correct them in Sage. Then, knowing that Sage was now accurate, you would export the data from it a second time and from this second copy you would produce the financial reports.

It is not practical to do this in the tutorial. Theerfore, when we find an error we will correct it in Excel and keep going. Be aware, though, that in a real case you would make the corrections in Sage, not in Excel, then re-export the data into Excel a second time.

In the tutorial that follows, the sections numbered with a B are concerned with the audit stage. The sections numbered with a C are concerned with the second stage, producing the financial reports. Each section is self-contained, so if you were short of time or wanted only to see the Financial Reporting side, it would be possible now to go straight to section C.1 Producing The Balance Sheet.

PART ONE - AUDITING

B. 1 CHECKING THE CONTENTS OF THE “DATE” COLUMN

Always make a point of checking that any DATE column contains valid dates. (A single blank or invalid date value will prevent the Group function from grouping dates into months and you will get the error message – “Cannot Group That Selection”)

Click on cell J10 in the DATE column. Click the AZ icon. The database is sorted into DATE order.

Look at the first date in row 1. It is 31/12/98. That looks OK.

Now Ctrl-Down Arrow to go to the bottom row 2053. There are two dates of 27/09/09!

As a rule of thumb, assume that one date per thousand will be keyed in with the wrong year. Such errors are easily picked up since they will appear at the very beginning or very end of the sorted file.

In real life you would correct these dates and any other errors in Sage Line 50 itself via the: File – Maintenance – Corrections menu. However, for this exercise we’ll correct the dates in Excel.

Change 27/09/09 to 27/09/99 in both cases.

In addition, highlight cells M2050 and M2051 in the FINYR column.

Copy 99_00 into cells M2052 and M2053 below.

Return to cell A2 via Ctrl+Home.

Now sort the database into its original order. Select: Data - Sort – Sort by NLAC - Then By: DATE

B. 2 REPRODUCE THE TRIAL BALANCE

Our initial task is to reproduce the Sage Trial Balance report. This will prove that all the transactions have come over correctly from Sage into Excel. We’ll do this via a pivot table.

Click anywhere on the sheet, then from the menu select:

Data - Pivot Table Report [Excel 2000 = Pivot Table and Chart Report, Excel 5 = Pivot Table]

The Pivot Table Wizard, Step 1 of 4 screen appears. Click on: Next [Excel 2000 – Step 1 of 3]

Step 2 of 4 The Range box should say: $A$1:$AF$2053.

[Check the range carefully. The first half should always be $A$1. The second half should show the last row you noted down – 2053. If the number is higher, say 2054, it means the pivot table includes a blank record and the Group command will fail when you try to group the Date field into months.

Also sometimes the Range box is blank or says “Database”. In this case, click the mouse pointer inside the box and erase the contents. Now click onto cell A1. Type a colon. Hold down Ctrl and press the “End” key. This should display the range SagePivot!$A$1:$AF$2053.]

Click on Next.

Step 3 of 4: The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet [note: if you are using Excel 2000, you must first click on Layout]

[if instead you get the message “A pivot table field name is not valid…….”, then either one of the column headings is missing, or your Range includes a blank column at the far right (which has no heading, obviously). Cancel, then check Row 1 and the far right column of the Range].

Drag and drop these headings as follows:

NLTYP into the ROW area
NLAC into the ROW area below NLTYP
NL_SHORT into the ROW area below NLAC
BALANCE into the ROW area below NL_SHORT

AMOUNT into the DATA area.

[When dropped into the DATA area, AMOUNT should now say “Sum of AMOUNT”. If it says “Count of AMOUNT”, double click on Count of AMOUNT. A box appears. Change “Count” to “Sum”]

Now in the ROW area, double click on NLAC The Pivot Table Field box appears

In the Subtotals area on the left, change from Automatic to None. OK

Also change Subtotals from Automatic to None in the NL_SHORT and BALANCE fields.

(As a general rule, assume that you will always have to set ROW fields to “No Subtotals”)

Now click on Finish. [Excel 2000, OK - Finish] A pivot table is generated

B. 3 FORMAT THE PIVOT TABLE

First, we’ll tidy up the pivot table. Right click on any number within the “Total” column E.

A menu appears. It has “Format Cells” either at the top or in the middle.

From the menu select: Field. [Excel 2000 = Field Settings, Excel 5 or 7 = Pivot Table Field]

The “Pivot Table Field” dialogue box appears. From the list of options on the right, select: Number

The “Format Cells” box appears. From the “Category” list, select: Number Then:

Decimal Places = 2 - Use 1000 separator? YES - click on the -1234.10 in red at the bottom of the list - OK - OK.

The pivot table now shows all figures to 2 decimal places and credits are in red with minuses.

Now left click on NLAC in cell B2. The column is highlighted. [Excel 2000, see below]

[Excel 2000 - note that cell references in Excel 2000 are usually two rows lower than in Excel 97.
Move the mouse pointer above NLAC in cell B4 until you get a black down arrow, then left click. The column is highlighted].

Centre the nominal account codes by clicking the Centre icon.

[Note: If you can’t get the highlighting or the black down arrow to work, you may have the Selection feature switched off. To switch it on: Right click for menu - Select - Enable Selection]

Now in the same way, highlight the BALANCE column. Then from the menu:

Format - Cells - Number - Decimal Places = 2 - 1000 separator? YES -1234.10 in red OK - OK.

Widen column C, NL_SHORT

Click on the “5” brick to the left of Row 5. [Excel 2000 – the “7” brick] The row is highlighted

Select: Window - Freeze Panes

B. 4 RECONCILE THE TRIAL BALANCE

You must first check that the totals calculated by the pivot table agree with the balances held on Sage:

Click onto cell F3 next to 50,000 [Excel 2000 = cell F5. Cell references are two rows lower].

From the main menu, select: Insert - Function.

In the box there are two lists. From the right hand list highlight IF and click OK.

Type the following: Logical test : d3=e3 [Excel 2000 d5 = e5]
Value if true: ok
Value if false: error OK “ok” appears in F3.

Click onto OK in F3. Place the mouse pointer on the RH bottom corner of F3 until it becomes a black cross. Double click the mouse. The formula is copied down to the bottom of the worksheet.

All the values in column F are “OK”, except the BS and PL subtotals which each add up to 46,906.26.

The data has been imported correctly and you can move to the next stage.

[Note that this really not quite correct. On the PL accounts the BALANCE should really equal the total of the transactions for this financial year only. But it wasn’t possible to adapt the Sage demonstration data to do this]

Now delete column F containing the “OK”’s. (Highlight column F. Press Delete key)

Right click for menu - Wizard - drop BALANCE out of the ROW area. Finish.

Finally, right click on “Sheet1” at the bottom of this worksheet. Rename this sheet TB

B. 5 SHOW THE TRIAL BALANCE BY MONTH

With the data balances now proven, we’ll split them out by month.

Right click on the pivot table for the menu. Select: Wizard [Excel 5 or 7 – Pivot Table]

Move NLTYP from the ROW area to the PAGE area.

Drop FINYR into the COLUMN area. (it’s at the top of the fourth column of field names)

Drop PD into the COLUMN area to the right of FINYR. Finish

The pivot table is recalculated, showing year 98_99, periods 06_Dec to 12_Jun, then year 99_00, periods 01_Jul to 06_Dec. (Remember, your financial year starts in July).

Note 1: At this point, let’s illustrate Excel’s Undo and Redo features, which are very useful if you make a mistake, particularly with pivot tables:

Click on the Undo icon (arrow curving left). The Trial Balance re-appears. Now click it again.

Now click on the Redo icon (arrow next to it curving right). Click it again. The periods return

Should you enter a wrong instruction in this tutorial, use the Undo icon to cancel the mistake.

Note 2. Also, note the value of the NL_SHORT field. Being 15 characters in length as opposed to 35 characters for the full NL_NAME in column Z, it means you can fit more columns onto the screen.

We only want to see figures from this financial year. Therefore: [Excel 5, Excel 7 or Excel 97]

Double right click on the grey FINYR field button in C3. The menu appears. Select: Field

In the Hide Items box at the bottom left, highlight 98_99 and OK.

The year and months for 98_99 disappear. Year 99_00 July to December remain. .

[Excel 2000] On the grey FINYR field button in C3 there is a down arrow. Click on it.
Remove the tick against 98_99. OK
The year and months for 98_99 disappear. Year 99_00 July to December remain. .

At present we have two sets of grand totals in columns I and J. We only need one.

Double right click on FINYR in C3. Select: Field [or Field Settings]

Change Subtotals from Automatic to None. OK One set of subtotals is removed.

B. 6 SHADE COLUMN HEADINGS (Excel 97 & Excel 2000 only)

We’ll smarten up the monthly column headings, as follows:

[Excel 97] Left click on the PD field button in cell D3. All the months are highlighted
or
[Excel 2000] Move the mouse pointer above PD in D3. Move it gradually downwards until it turns into a thick vertical down arrow. Left click the mouse. All the months are highlighted.

[And then for both] Click on the yellow “Fill Color” icon in the bottom row of icons, second from right (the one that looks as if it’s pouring a can of paint).

Click on the Centre icon. Then, click on the B icon to make them bold.

Click anywhere to remove the highlighting. The months are centred, bold and shaded yellow.

B. 7 DRILL DOWN ON SUSPICIOUS BALANCES

You now want to check the monthly figures, particularly of the P & L accounts.

Click on the Down arrow in B1 , next to NLTYP. From the list select: PL.

We see the P&L accounts for the last 6 months, July to December.

Reviewing the monthly figures, the Gross Wages figure of 16,200 for July in C16 seems to be doubled up. Also the Rent for September in E21 looks understated.

Double click on the 16,200.00 in cell C16 – Gross Wages. A drill down Sheet2 is generated.

Wages for June do seem to have been posted twice, once in journal 621, once in journal 664.

Click back onto the TB worksheet.

Now double click on the –50 in E21 for September Rent. Another drill down sheet is generated.

There are 4 entries for September. Something seems to have gone wrong with the Prepayments.

Click back onto the TB worksheet.

Delete the two drill down sheets you have just created. (right click on “sheet2” for menu, Delete, OK)

After deleting the second drilldown sheet you are returned to the TB worksheet.

B. 8 ANALYSE DATA BY TRANSACTION TYPE

An important feature to note is that, by using TRAN as a Page field you can get Excel to instantly calculate the monthly nominal totals for individual types of transaction such as purchase invoices or petty cash payments. This helps to check that they have been correctly analysed to the nominal.

Right click on the TB pivot table for the menu. Select: Wizard [Excel 5 or 7 – Pivot Table]

Drop TRAN into the PAGE area under NLTYP. Finish.

The pivot table stays unchanged, but TRAN has appeared as a Page field in cell A2.

Click on Down arrow in B1 next to NLTYP. Change PL to All. The B/Sheet accounts reappear

Click onto the Down arrow in B2 next to TRAN. A list of transaction types appears.

Scroll down the list and select PI (Purchase Invoices)

The pivot table now shows the monthly nominal analysis totals for purchase invoices only.

An obvious error appears – £200 for Sales S.E. in September should not be on a purchase invoice.

Double click on the 200 in cell E9. A drill down sheet appears. An invoice from Wallace Office Equipment for “services” has been analysed to a sales account. Obviously this is wrong. We’ll correct it.

Click on the SAGEPIVOT worksheet. Click on cell C10 in the ACCT_SHORT column.

Click the AZ icon. The database is sorted into Account Name order, starting with ABS Garages.

Go down to the group of Wallace Office Equipment transactions, starting at row 977.

Find row 997. The invoice is analysed to 4003, Sales South East. Normally it would be 5000, Materials Purchased, as in row 998 below.

As stated earlier, you would normally correct the error in Sage, then rerun the SagePivot program with clean source data. But to sustain the tutorial we’ll correct it now in Excel.

Change the nominal analysis to 5000, Materials Purch, as follows:

Copy cells A998 and B998 onto A997 and B997.

Also copy cells U998 to Z998 onto U997 to Z997.

Go back into the TB worksheet. Right click for menu: Refresh Data. The 200 disappears

Delete the drilldown worksheet you just created for this transaction (sheet4?) .

Back in the TB worksheet, click onto the Down arrow in B2 next to TRAN (currently it says PI).

Go to the top of the list and select (All). The complete Trial Balance reappears.

B. 9 RECONCILING DEBTORS CONTROL TO THE AGED DEBTORS REPORT

A key task when auditing is to confirm that the sum of the customer account balances on the Aged Debtors report in the Sales Ledger agrees with the balance on Debtors Control in the Nominal Ledger.

Suppose that in this case they disagree. The total value on the Aged Debtors report is £32,214.32, whereas on Debtors Control it is £31,589.32, a discrepancy of £625. Analysing the Debtors Control account with a pivot table will usually highlight the cause of the problem.

We’ll create a Control account summary for account 1100, Debtors Control. The quickest way to do this is to make a copy of the TB pivot table, as follows:

Right click on TB at the bottom of the worksheet. Select: Move or Copy.

The Move or Copy box appears. Tick the Create a Copy box at bottom left. OK

A new sheet, TB(2) is created. Right click again for menu and Rename it Debtors.

Right click on pivot table for menu. Select: Wizard. The ROW-COLUMN-DATA screen appears.

Drag and drop FINYR and PD out of the COLUMN area.

Move TRAN from the PAGE area into the COLUMN area.

Remove NLTYP from the PAGE area. Move NLAC into the PAGE area.

Drag and drop DATE above NL_SHORT in the ROW area. Finish.

The pivot table is recalculated. It looks a bit disorganised. Don’t worry.

First, click on the Down arrow next to NLAC in B2. From the list of nominal accounts, select 1100.

Debtors Control appears throughout column B. However, the DATE field is subtotalling.

Double left click on DATE in cell A5. The Pivot Table field box appears.

Change Subtotals from Automatic to None. OK

Now double right click on DATE in A5. Select: Group and Outline – Group

In the Grouping box select Months and Years (underneath Quarters) . OK

We have a monthly control summary for the Debtors Control account showing, from right to left, total Receipts, Invoices, Discounts, Credit Notes, Payments on Account and Journals.

Shade yellow, centre and embolden the column headings ( JC, JD, SA etc). (See section B6 if you can’t remember how to do this).

The culprit is in D17 among the journals. In November someone posted a nominal journal directly to the Debtors Control account. Double click on the -625 Journal Credit in D17 to see the details.

The drill down worksheet shows that this was a Rent Prepayment, journal no. 931. It was mistakenly analysed to 1100, Debtors Control account, when it should have been to 1103, Prepayments account.

As before, we will make the correction in Excel. Click onto the SAGEPIVOT worksheet.

Click on cell F10 in the “RefNo” column. Then click on the AZ icon.

Scroll down to row 1648, which contains journal 931, in the “PREPAY” group of transactions.

In journal 931 change 1100 Debtors to 1103 Prepayments.

Now click back onto the Debtors worksheet. Right click for menu. Refresh Data

The -625 disappears from November JC’s. The grand total in K19 corrects itself to 32,214.32, which agrees with the total of the Sales ledger balances.

Delete the drill down worksheet created for the Rent Prepayment

B. 10 DETECTING BANK RECONCILIATION ERRORS

Another key audit task is to reconcile the bank balance to the balance on the bank statement. Often errors have been made in earlier bank reconciliations, i.e. items have been “ticked” which should not have been, and vice versa. Before the bank will reconcile these errors must be detected and removed.

You can use a pivot table to detect bank reconciliation errors, as long as the bank reconciliation program has recorded a bank statement reference (for example, the page number of the statement).
Unfortunately, in Sage when you tick a payment during bank reconciliation, there is no provision to store the statement page number or any other reference. In this tutorial, however, I’ve assumed that Sage does allow you to record the bank statement page number when reconciling and in the BKPAGE column N of SAGEPIVOT I’ve invented bank statement page numbers for most Bank transactions.

Using a pivot table you can quickly now sort the bank account into the same page sequence as the bank statement. Any errors can be detected by comparing the running balance at the end of the page on the bank statement with the running balance at the end of the page on the pivot table. Where they start to vary, that page contains an error.

Recreate the bank statement as follows:

Right click on the bottom of the Debtors worksheet. Select: Move or Copy.

The Move or Copy box appears. Tick the Create a Copy box at bottom left. OK

A new sheet, Debtors(2) is created. Rename it Bank

First, click on the Down arrow in B2 next to NLAC. From the list of accounts, select 1200.

Bank Current Ac appears throughout column C.

Right click for menu. Select: Wizard. The ROW-COLUMN-DATA screen appears.

Drag and drop TRAN out of the COLUMN area.

Remove Years and DATE from the ROW area.

Drag and drop BKPAGE (i.e. “Bank Page”) under NL_SHORT in the ROW area. Finish

The pivot table is recalculated, showing the value of each Bank Statement page, pages 242 to 254.

To the right of “Total”, type into cell D5 the word Balance

In cell D6, opposite 1076.84, type the formula = C6 1076.84 appears in D6

In D7 type the formula = D6+C7 -4226.92 appears in D7.

Copy the formula in D7 down to D19 We have re-created the statement running balances.

The balances in column D should equal the balances at the end of each bank statement page. Thus the balance at the end of the most recent bank statement, page 254, should have been 31,658.71.
Where the running balance in column D starts to diverge from that on the bank statements, that page will contain the reconciliation error.

-18.551.04 in C19 has no statement page against it. It is the value of uncleared cheques and receipts.

Double click on –18,551.04.

A drill down sheet is generated. This is your list of unpresented payments and receipts.

Delete the drilldown sheet. You are returned to the TB worksheet.

Adding back the unpresented cheques (-18,551.04) to the statement balance (31,658.71) gives you the Cashbook balance in the accounts (13,107.67).

Reconciling to the bank statement by conventional methods can take hours. With pivot tables you can reduce this to minutes. But your accounts package must allow you to record a statement reference when it asks you to tick a payment or receipt at reconciliation time!

B. 11 CHECKING THE PURCHASE DAYBOOK VIA AUTOFILTER

In addition to pivot tables, Excel’s SORT and AUTOFILTER enable you to search through thousands of transactions very quickly. They are particularly handy for scanning daybooks and audit trails.

Click onto the main SAGEPIVOT database. Ctrl+Home to go to the top row.

At present the data as exported from Sage is in nominal account code (NLAC) order. Suppose you wanted to look at the transactions in the order they were entered. To do this, simply:

Click on cell H10, in the “NO” column. Then click the AZ icon.

The data is now sorted in “NO”, i.e. Transaction Number, order.

Suppose you now want to look particularly at purchase invoices. Select: Data - Filter - Autofilter.

Black down arrows appear against each column heading.

[It may happen that the screen has now jumped to the rightmost columns in the database, AA, AB, AC etc. This is an irritating fault. To correct it, Ctrl + Home to return to cell A2].

Click on the black down arrow in I1, the “TRAN” column. A list of transaction types appears.

From the list, select: PI (purchase invoices). Only the PI type transactions are now shown.

We want to look at the Purchase Daybook for the months of this financial year. To do this:

Click on the down arrow in cell M1, the FINYR column. Select 99_00.

Ctrl+Home to get back to cell A2.

Now click on the down arrow in cell G1, the PD column. A list of the periods in year 99_00 appears.

Click on 01_Jul. Autofilter shows only the July purchase invoices.

Click back onto the down arrow in G1. This time select: 03_Sep

The September invoices are listed. Note that the screen displays the nominal account name and the supplier name, both of which are omitted from the Sage purchase daybooks.

Note also that the down arrows in G1 PD and in I1, TRAN are shaded blue. This means that filters are being applied to these columns.

Finally, from the menu: Data - Filter - Show All. All transactions are redisplayed.

B. 12 ADDING THE “SHOW ALL” ICON TO THE ICON BAR

Excel comes with the most commonly used icons pre-set in the icon bar at the top of the screen. However, there are some other useful ones buried away. One of these is the “Show All” icon. It saves you having to click Data – Filter – Show All every time you want to switch off the filters. We will bring it into the icon bar and insert it to the right of the yellow “Fill Color” icon:

From the menu select: View - Toolbars - Customize (at the bottom). Click the Commands tab.

From the list on the left, click on Data In the list on the right Show All is displayed fifth down.

Drag and drop Show All so that it is placed between the Fill Color and big A icons.

Close the Customize box.

B. 13 LISTING ALL THE TRANSACTIONS FOR ONE ACCOUNT

Certain nominal accounts, e.g. Mispostings or Suspense, need to be checked carefully. Therefore:

The Autofilter down arrows should still be visible. Ctrl+Home to get to A2.

Click on the down arrow in cell B1 next to NL_SHORT. A list of nominal accounts appears.

From the list select: Mispostings Acc All transactions analysed to Mispostings a/c appear

Click on the B1 down arrow again. This time take a look at the Adjustments account..

Similarly, you might want to look at all the postings to one supplier account.

First, click on Show All in the icon bar. All the records are redisplayed.

Now click on the down arrow in C1, the ACCT_SHORT column. A list of names appears.

Select McNally Machine. All transactions involving McNally Machinery appear.

B. 14 DISPLAYING A LIST OF JOURNALS

You wish to see a list of all the nominal journals posted this financial year.

Click the Show All icon to display all records. Then in column M select: FINYR = 99_00.

Ctrl+Home to go to A2. Click on down arrow in I1 TRAN. The list of transaction types appears.

To see a journal in full, you will need to see both sides, i.e. both the JC’s and the JD’s.

To do this, select: (Custom…) The Custom Autofilter box appears.

In the top LH box, click down arrow and change equals to begins with

In the top RH box, type: J then click OK. All the JD and JC transactions are displayed.

Note that June salaries appear to have been entered twice. We saw this earlier in section B 7.

B. 15 FINDING A CHEQUE IN THE BANK ACCOUNT

Suppose that you have a cheque on your bank statement for £4,193.58 and are not sure whether it was ever entered on Sage. AUTOFILTER enables you to instantly to trace a single transaction:

Click on the Show All icon to redisplay all transactions.

Now click on the down arrow in cell B1, NL_SHORT. A list of nominal accounts appears.

Select: Bank Current Ac. Autofilter filters out everything except Bank transactions.

Now click on the down arrow in cell E1, the AMOUNT column.

A list appears of all the values Excel has found. From the list select: –4,193.58

Excel has found the entry. It was transaction no. 768 in Sage, but no cheque was recorded in REFNO.

Now select: Data - Filter - Autofilter. Down arrows disappear and all transactions reappear.

This concludes the section on Auditing. Now it is time to produce the financial reports.

If you are starting to tire and your concentration is going, now is a good time to take a break. Save the file, then come back in 20 minutes or so and restart at this point. You need to be fresh.

PART TWO – PRODUCING FINANCIAL REPORTS

C. 1 PRODUCING THE BALANCE SHEET

Click onto the main SAGEPIVOT worksheet. Click anywhere onto the data. From the menu, select:

Data - Pivot Table Report [Excel 2000 = Pivot Table and Chart Report, Excel 5 = Pivot Table]

The Pivot Table Wizard, Step 1 of 4 screen appears. Click on Next

Step 2 of 4 The Range box should say: $A$1:$AF$2053.

[Check the range carefully. The first half should always be $A$1. The second half should show the last row you noted down – 2053. If the number is higher, say 2054, it means the pivot table includes a blank record and the Group command will fail when you try to group the Date field into months.

Also sometimes the Range box is blank or says “Database”. In this case, click the mouse pointer in the box and erase the contents. Now click onto cell A1. Type a colon. Hold down Ctrl and press the “End” key. This should display the range $A$1:$AF$2053.]

Click on Next. A message appears: “You will use less memory if ……..” Select No

Step 3 of 4: The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet.

Drag and drop these headings:

SGTPNO into the ROW area
SGTOPGROUP into the ROW area below it
SGGPNO into the ROW area below it
SAGEGROUP into the ROW area below it
NLAC into the ROW area below it
NL_SHORT into the ROW area below it.

NLTYP into the PAGE area
DEPTNAME into the PAGE area below it

AMOUNT into the DATA area

[When dropped into the DATA area, AMOUNT should now say “Sum of AMOUNT”. If it says “Count of AMOUNT”, double click on Count of AMOUNT. A box appears. Change “Count” to “Sum”]

Finally, select Finish. The pivot table is calculated.

[If instead you get the error message “Microsoft Excel cannot make this change because there are too many Row or Column items….” your machine has insufficient memory to store all the ROW fields.
You will have to reduce them. Start by knocking out NLAC or NLNAME and see if that works.]

C. 2 FORMAT THE PIVOT TABLE

Widen columns B and D.

You have subtotals on all the row fields, but only want them on the first field, SGTPNO.

To remove subtotals from the other fields, first double left click on SAGETOPGROUP in B5.

The Pivot Table Field box appears. Change Subtotals from Automatic to None. OK

In the same way, remove subtotals from SGGPNO, SAGEGROUP, and NLAC.

Right click anywhere in the Total column G. From menu select: Field [or Field Settings].

Select: Number - Number - Decimal Places = 0 - Use 1000 separator? YES - -1234 in red OK OK

Shade the “1 Total”, “2 Total ” subtotal lines as follows: (Excel 5/7 users skip to section C 3)

[Excel 97]
Click on “1 Total” in cell A13 . All the sub-total lines are highlighted
or
[Excel 2000]
Move the mouse pointer to the grey “13” brick opposite “1 Total” in cell A13.. Move it gradually to the right until it assumes the shape of a thick horizontal black arrow. Then left click the mouse. All the sub-total lines are highlighted

To add shading, click on the Down arrow in the yellow “Fill Color” icon. From the palette of colours, select Light Turquoise or Light Green in the bottom row.

Click anywhere to remove the highlighting. The “Total” lines are all shaded with colour.

Often you may find that you cannot get all the columns of a spreadsheet onto the screen. However, you can bring the rightmost columns into view by hiding other columns.

As an example, we’ll hide the SGGPNO field in column C:

Click on the top of column C to highlight the column.

Then select: Format - Column - Hide. Column C is hidden and with it SGGPNO.

(Both SGTPNO and SGGPNO exist solely to sort the accounts into the correct order. They don’t have to be displayed. But we can’t delete SGGPNO, as the SAGEGROUP column would then sort itself into alphabetical order and we would lose the correct sort sequence.)

Centre the NLAC column.

Click on the “7” brick to the left of cell A7.

Select: Window - Freeze Panes

Click on the down arrow in cell B1. Select BS. The Balance Sheet is displayed.

Click on Sheet7 at the bottom of this worksheet. Rename the worksheet BSHEET.

C.3 PRODUCING THE PROFIT & LOSS REPORT

Next we’ll produce a Profit and Loss report. Make a copy of the Balance Sheet as follows:

Right click on BSHEET at the bottom. From the menu select: Move or Copy

The Move or Copy box appears. Tick the Create a Copy box at bottom left. OK

A new sheet, BSHEET(2) is created. Rename it PL

Click on the down arrow in cell B1. Select PL. The Profit and Loss accounts are displayed.

We would like to see the Profit and Loss report broken down by month.

Right click for menu. Select: Wizard. The ROW-COLUMN-DATA screen appears.

Drag and drop FINYR into the COLUMN area. Then drop PD to its right. Finish.

The pivot table is recalculated, showing year and periods across the top.

We only want to see figures from this financial year. Therefore: [Excel 5, Excel 7 or Excel 97]

Double right click on the grey FINYR field button in G4. The menu appears. Select: Field

In the Hide Items box at the bottom left, highlight 98_99 and OK.

The year and months for 98_99 disappear. Year 99_00 July to December remain. .

[Excel 2000] On the grey FINYR field button in G4 there is a down arrow. Click on it.
Remove the tick against 98_99. OK
The year and months for 98_99 disappear. Year 99_00 July to December remain. .

You may be unable to see all the columns. Narrow columns A to F as much as you can.

The FINYR field is subtotalling (see column M). To remove the subtotal:

Double left click on FINYR. Change Subtotals to None. OK Column M disappears.

We don’t really need to see the NLAC column. Click on the “E” column heading.

Right click for menu. Select: Hide [Note how the right click menu if you click outside the pivot table is different from the right click menu if you click inside the pivot table]

Highlight the monthly column headings, as follows: (Excel 97 and Excel 2000 only)

[Excel 97] Left click on the PD field button in cell H4. All the months are highlighted
or
[Excel 2000] Move the mouse pointer above the PD field button in H4. Move it gradually down until it turns into a thick vertical down arrow. Left click the mouse. All the months are highlighted.

[And then for both] Click on the down arrow by the “Fill Color” icon in the bottom row of icons, second from right (the one that looks as if it’s pouring a can of paint). Select yellow from the palette.

Click on the Centre icon. Then, click on the B icon to make them bold.

Click anywhere to remove the highlighting. The months are centred, bold and shaded yellow.

C. 4 PRODUCING DEPARTMENTAL PROFITABILITY REPORTS

One great advantage of pivot tables is that it is so easy to produce departmental reports. So:

Click on the down arrow in cell B2. A list of departments appears, Sales dept, Accounts dept etc.

Only a few records in the Sage demonstration data have been analysed by department, but they are sufficient to illustrate the general idea.

From the list, select Sales. A miniature Profit and Loss report for the Sales department is displayed.

Click back onto the down arrow in cell B2 once more. This time select: Purchasing

Click onto the B2 down arrow again, and from the bottom select (blank)

A Profitability report for Head Office (i.e no department code) is displayed

Finally, click back onto the B2 down arrow. Select (All). The company totals are redisplayed.

The PAGE field is an immensely powerful tool for analysing company performance by cost centre, department, branch, division, job etc.

C. 5 SUMMARISING THE PROFIT & LOSS REPORT AT DIFFERENT LEVELS

The full company Profit and Loss report extends to row 37, beyond the bottom of the screen.

We’ll summarise the P & L at top level. Double right click on SGTPNO in A6.

From the menu, select: Group and Outline - Hide Detail

The P & L is summarised at the Sales, Purchases, Direct Expenses and Overheads level.

You can drill down on any section to see further detail. Double click on the “1” in A7.

The individual sales accounts appear. Click anywhere to remove any highlighting.

To summarise the Sales again, double click on the space below the “1”, e.g. on cell A8 or A9.

Now look at the detail of the other three sections 2, 3 and 4.

Make sure that the pivot table is summarised once again into totals for 1, 2, 3 and 4.

Now this time we’ll display at SAGEGROUP level. Double right click on SAGEGROUP in D6

From the menu select: Group and Outline – Show Detail

The P & L is expanded to its lowest (NL_SHORT) level

Double right click again on SAGEGROUP. Then select: Group and Outline – Hide Detail.

NL_SHORT goes blank and this time the pivot table is summarised at SAGEGROUP level.

With pivot tables you can choose to display your financial reports at varying levels of detail dependent on the reader.

C. 6 PRODUCING CUSTOMER AND SUPPLIER REPORTS

You can also produce useful sales or purchases analysis reports with pivot tables.

First take a copy of the PL pivot table, as follows:

Right click on PL at the bottom of the worksheet. Select: Move or Copy.

The Move or Copy box appears. Tick the Create a Copy box at bottom left. OK

A new sheet, PL(2) is created. Rename it: CustSupp

Before anything, we need to redisplay any hidden columns.

Highlight the whole sheet by clicking onto the top LH brick above 1 and left of A. Then:

Select: Format – Column - Unhide. The hidden columns re-appear.

Click anywhere to remove highlighting. Then right click on the pivot table for menu.

Select: Wizard. The ROW-COLUMN-DATA screen appears.

Remove DEPTNAME from the PAGE area.

Drag and drop ACCT_SHORT into the PAGE area below NLTYP.

Drag and drop SPN into the PAGE area below it. (SPN, “Sales, Purchase, Nominal”, is at the far right of the list of fields)

Remove all fields from the ROW area.

Drag and drop ACCTNAME into the ROW area (it’s the farthest field to the right)
REFNO into the ROW area below it
DETAILS into the ROW area below it
TRAN into the ROW area below it

Finish. The pivot table is recalculated. Adjust the width of columns A, B and C.

All the row fields are subtotalling. Double left click on ACCTNAME in A7.

The Pivot table field box appears. Change Subtotals from Automatic to None. OK

Do the same for REFNO and DETAILS.

Left justify REFNO (by clicking on REFNO in B7, NOT the column heading B).

Re-adjust the widths of columns A to D.

We’ll summarise turnover by month first. Double right click on ACCTNAME in A7

From the menu select: Group and Outline - Hide Detail

The monthly turnover for each account is displayed.

To show purchases only, click on the down arrow next to SPN in B3. From the list, select P

Only the Supplier accounts are displayed.

Now click on the down arrow in B3 again. Select S Customer accounts are displayed.

To see the sales in detail, double right click on ACCTNAME in A7.

Select: Group and Outline – Show Detail. The individual detail lines appear.

Now click on the Down arrow next to ACCT_SHORT in B2.

From the list of names, select Morley Solicitors. The individual items sold to Morley are displayed.

This ends the financial reporting section and the tutorial. Save your work. Relax.

WHERE DO YOU GO FROM HERE?
My first budgeting tutorial in December began with the claim that “for the management accountant, Excel pivot tables represent the most important development in IT since the original invention of the spreadsheet.” Whether your interest lies in budgeting, sales analysis, or in financial reporting, I hope this series of tutorials has made a strong case.

I also made the point that pivot tables are not new: they have been available since Excel 5 was released in 1994. Almost unbelievably, pretty well everything you have done in this tutorial you could have done at any time over the last six years.

So why have pivot tables made so little headway? The problem lies with the accounting package vendors. Each vendor has to write a program that will output the data from the files in a format specially suitable for analysis in pivot tables. This is what we have done for Sage Line 50 with SAGEPIVOT and it is what you have to get your own package supplier to do.

If you discuss this with the suppliers they will all claim that their package links to Excel. It probably does, but the true test is: can you press a button in the accounts package to output the nominal ledger transaction files to Excel, then start up Excel, load up the output file, and start applying pivot tables straight away? If you have to spend lots of time tidying the data up once it’s in Excel, obviously it is not pivot-table ready.

IF YOUR ACCOUNTS PACKAGE IS SAGE LINE 50.
If you run Sage Line 50, versions 5 or 6, your next action is simple. Buy a copy of SAGEPIVOT, a bargain at a mere £50. You can download it from AccountingWEB, in the Expert Guides section, and try it out for 21 days without charge. Alternatively, send an email to davidc@minerva-cs.co.uk. (If, by the way, you tried this program soon after it appeared on AccountingWEB and had problems, it is probably because for the first 4 days it was accompanied by the wrong Word file and contained no Excel instructions. Please download the Word and Summary files once again.)

IF YOUR ACCOUNTS PACKAGE IS NOT SAGE LINE 50
At time of writing (March 2000) I know of only two accounting vendors that are making serious efforts to integrate pivot tables with their packages. One is AccountView, a Dutch company new to me but with an impressive pan-European mid-range product (London office: tel: 0171-477-6524). In the UK they aim particularly at the time recording/project accounting market. The other is Open Accounts, who specialise in mid-range corporate accounting systems. Open Accounts are also linking their package to Microsoft’s OLAP SQL Server in order to handle the large volumes associated with a bigger company, since Excel has a limit of 65,000 records. (Open Accounts: tel: 01327-301900).

Other vendors have various offerings. Some offer screen-based reporting facilities similar to pivot tables, which are designed to deliver regular reports to managers throughout the company. There is definitely a need for these, but they tend to be a bit inflexible and require a semi-programmer to set
them up. You lose that marvellous facility with Excel to be able to just drop the data into a pivot table and start analysing.

So, ask your package supplier to make available a “flat” pivot-table ready data file containing all your nominal ledger transaction records. In Appendix One following I’ve given a list of fields which I suggest each record should contain, but it will vary from package to package. Also, make a copy of the list on page 3 of this tutorial of the fields output by the SAGEPIVOT program and use that as an example.

Remember, pivot tables will become easy to use only when customers start insisting on pivot-table ready data files from their package suppliers! It’s up to you!

ONE LAST REQUEST
I hope you have enjoyed this tutorial and the others. Before you sign off, can I ask you to do me a favour in return? It relates to the one serious fault in Excel, which we need Microsoft to put right as a matter of urgency. The fault relates to sorting with the AZ icon.

The fault remains uncorrected in all versions, even Excel 2000. If you are not aware of it, it is easy to corrupt your database beyond repair without even realising what has happened.

Go back into the SAGEPIVOT worksheet. Go to cell A2. At the moment it should be sorted in transaction number (“NO”) order - column H.

Suppose we want to sort it into account code (NLAC) order, column A. We’ll do this now in two ways, the first way safe, the second unsafe.

SORT ONE (SAFE)
Click on cell A10 in the NLAC column. Then click the AZ icon.

The database is sorted into nominal code order. Check this by looking at NL_SHORT in column B next to NLAC. Whenever NLAC has changed, NL_SHORT has changed too.

Now undo the sort via: Edit - Undo Sort (or left curving arrow icon).

The database reverts to NO (column H) order. Click any where to remove the highlighting.

SORT TWO (UNSAFE)
This time, click on the “A” at the top of the NLAC column. The column is highlighted.

Position the mouse pointer over the AZ icon (don’t click yet).

Now, while concentrating on column B, NL_SHORT, click on the AZ icon.

Column B has not changed!

Think about this. Excel has sorted the NLAC column only and left the remaining columns as they were. All the records in the database have been corrupted.

Now undo this sort via: Edit - Undo Sort or via left curving arrow. The corruption is reversed.

Click anywhere to remove the highlighting on column A.

This is a Grade A fault and quite lethal. One day you will need to do an AZ Sort and will click on the column heading without thinking. You won’t realise what has happened until later, and if in the intervening time you have saved the workbook, your database is corrupted beyond repair.

There are two ways to tackle this problem. The first is to train your subconscious mind always to click on a particular row, say Row 10, before clicking the AZ icon.

The second is to send an email to Mr Bill Gates of Microsoft along the lines of: “I think your Excel pivot tables are great, but clicking a column heading and AZ icon to do a Sort and finding that I’ve ruined my whole database, that’s not so great. Can you stop Excel doing this, please?”

Please do this NOW. His email address is askbill@microsoft.com Over the years I have raised this problem several times with Microsoft, but to no effect. Who knows? If a thousand British accountants all complain politely about it to Mr Gates at the same time, maybe we’ll get some action.

DAVID CARTER, March 15th 2000.

Based in Hemel Hempstead, Herts, David Carter is an independent consultant who installs accounting and order processing systems into small and medium size businesses. He also writes
reviews of accounting and ERP packages for the computer press. Since 1996 he has been using Excel pivot tables in areas such as financial and management accounting, budgetting, job costing, sales analysis, order book analysis, and production control. He would be pleased to have any feedback from you on this tutorial and may be contacted on david.carter@moose.co.uk

APPENDIX ONE - SUGGESTED FIELDS FOR A “PIVOT-TABLE READY” EXPORT FILE

Basically, this is a flat file containing all nominal ledger (in the USA, “general ledger”) transaction records held on the system. Fields to include in each record are:

Transaction Type (invoice, payment etc),
Reference Numbers (invoice no, your ref, etc),
Transaction Number (any internally generated sequential transaction number)

Posting Date,
Document Date,
Financial Year number
Period Number,
Invoice Line number,
Narrative,
Value (debits = positive, credits = negative)

Supplier/Customer Code,
Supplier/Customer Name,

Nominal Account Code,
Nominal Account Description,
Nominal Group
Nominal Account Type (eg P and L, or Balance Sheet),

Cost Centre Code
Cost Centre Description

Department Code
Department Description

Divisional Code
Divisional Description

Project Code
Project Description

Note 1: these are for guidance only. Precise field names will differ from package to package. For an example, see the list in page 3 of this tutorial of the fields extracted by the SAGEPIVOT program.

Note 2: Filters should be available on the Nominal Account Type, Financial Year number and Period number fields in order to restrict the number of transactions exported (Excel will handle a maximum of 65,000 records).

Note 3. The data export file should contain field names in the first row. These field names should reflect the length of the data held in the field. For example, the value in any “Invoice Line Number” field is likely to be 3 digits maximum. Therefore make the field name “Ln” rather than “Invoice Line Number”. Again, look at the list of fields and fieldnames in page 3 for examples.

David Carter, March 2000.

View more helpful tips!

You may like these other stories...

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Already a member? log in here.

Upcoming CPE Webinars

Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.