In the tutorial 'Create a P & L from a monthly balances with a pivot table' we imported a set of monthly balances into Excel and used a pivot table to convert them into a Profit and Loss report. Frequently, however, the balances you need are not stored on-line in the accounts package. In this case you will have to export transactions into Excel and calculate your balances with a pivot table.

In this tutorial we'll import 2,000 transactions, use pivot tables to generate some basic reports such as trial balance, debtors control summary, bank reconciliation, daybook analysis. Then we'll print 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.

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. 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 [1] 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 [2] file containing the Excel instructions, and the SAGEPIVOT.CSV [3] 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 Richard Norton [4] at AccountingWEB and we will email you copies of the data files by return.

Introduction to the tutorial

The tutorial should take you about 60-90 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'.

This tutorial was developed and tested on Excel 97 and Excel 2000. There may be minor differences from Excel 2003. Notes in square brackets [] indicate where 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).

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

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

A. 3 Moving arouind the file
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 Two-stage process ' Audit, then Financial Reports
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. Therefore, 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
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 Reconcile 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 Check 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