Share this content
4

Tutorial: Sage Line 50 and Pivot Tables [2000]

Tutorial: Sage Line 50 and Pivot Tables [2000]

Didn't find your answer?

David Carter's latest tutorial unleashes the power of pivot tables on ubiquitous software package Sage. By taking raw data from Sage Line 50 and turning it into the more flexible pivot table format, users can produce consistently high quality reports and in a fraction of the time.

Contents

  • 1. Background to the Tutorial
  • 2. Export the Nominal Activity Report
  • 3. Print off a Trial Balance Report
  • 4. Contents of the Database
  • 5. Moving Around the File
  • 6. Tidy Up the Data
  • 7. Paste Values in Value2

    1. Background to the Tutorial

  • In BUDGDEMO and SALANAL the data was pre-processed for you and you were able to start analysing it straight away. Unfortunately, however, in the real world it's not like this. Before you can even start analysing, there may be a lot of work to be done in getting the raw data out of your accounts package in the right format. (If you have tried using pivot tables on your own company's data, you may already have found this out.)

    2. Export the Sage Nominal Activity report as a CSV file
    Start up Sage. You need to output the contents of Sage's nominal transaction file.

    From the menu with icons, select: Nominal. Then select: Reports. A list of reports appears.

    The standard nominal transaction report in Sage is 'Nominal Activity' (NOMACT_SRT). The tutorial is built around this report. However, 'Nominal Activity (with A/C ref)' (NLACTIV_SRT) is available on some Sage versions. If you have this report, use it, since it is 'pivot-table ready' and after keying in the column headings you can go to section B.8 and start reproducing the Trial Balance straight away. Similarly, if you analyse by department in Sage, 'Nominal Departmental Analysis (Detailed)' is also pivot-table ready.

    3. Print off a Trial Balance Report
    Your next task is to print out a Trial Balance report from Sage. You will use this report as a control. When the pivot table in Excel matches the Sage Trial Balance you can be sure that the data has been brought over correctly. Only then should you move on to the next stage.

    From the lower menu with icons, select: Financials. Then: Trial

    The 'Criteria' box should offer the year-end as default. OK to accept the default.

    4. Contents of the Database
    Excel can read CSV files directly, so the NOMACTIV file opened straight away. (If you remember, in the Sales Analysis tutorial you had to use the Text Import wizard to load the TXT file).

    Let's take a look at the data that's come over. These are the principal fields recorded on the Nominal Transaction file in Sage. They are:

    NLAC = the Nominal Account Code
    NLNAME = the Nominal Account Description
    BALANCE = the current Balance on the nominal account
    NO = the transaction reference number. This is a

    5. Moving Around the File
    Scroll down through the first 100 or so records to see the sort of data the file contains. Then:

    Go to the bottom of the file: (tip: keyboard shortcut = hold down Ctrl key, then press Down arrow)

    There are 518 rows (i.e. 518 nominal transaction records)

    Return to the top of the file, cell A2. (keyboard shortcut = hold down Ctrl key, then press Up Arrow. Let go your Ctrl key. Down arrow once)

    6. Tidy Up the Data
    Sage has done a good job in bringing over most of the data you need. However, there is a bit of tidying up to do.

    First, there is a problem with the VALUE (K) column. The amounts are all positives, whereas we need them to be positive only for debits, and negative for credits. We have to create another column where the values are expressed this way. We'll do this in column N. Therefore:

    First highlight column N by clicking the N at the top, then select: Format ' Column ' Width ' 11

    7. Paste Values in Value2
    The VALUE2 column may contain a series of formulae. We need to convert them to numbers.

    Highlight the VALUE2 column by clicking on the N at the top.

    From the main menu, select: Edit ' Copy Moving lines appear around the column

    From the menu: Edit ' Paste Special ' Values - OK

    Press the Escape key at top left to remove the moving lines

    Click on N10 in the VALUE2 column to remove the highlighting.

    Note that cell N10 now contains '93.00 not a formula, and so does the rest of column N.

    Finally, we'll move VALUE2 to the left of the VALUE column, so that it is easy to see on the screen.

    Click on K10, then: Insert ' Columns. Highlight the VALUE2 column, then: Edit - Cut

    Click on the top of the blank K column. Then: Edit ' Paste. VALUE2 is copied into column K.

    B. 6 TIDY UP DATA (2) - FILL IN EMPTY DATE FIELDS

    There is a second problem with the data as exported by Sage. This is that where there are nominal accounts with no transactions (e.g. Freehold Property, Leasehold Property, at the top of the screen) Sage has brought over a zero account balance but no date.

    One of the most useful features of pivot tables is the 'Group' command. This groups date fields by day, week, month etc. and you will be using it shortly. However, it is a bit temperamental, and if just one record out of thousands in your database has an incorrectly formatted date or no date at all, it will refuse to work and simply say 'Cannot Group That Selection'. Therefore, whenever you import a database, always make a point of checking that the date columns are filled with valid dates before you start running any pivot tables.

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

    Go to Row 436. In F436 enter the date: 28/02/1997 Now copy 28/02/1997 down to row 518.

    [tip: type 28/02/1997 into F437 as well. Highlight both cells. Black cross on RH bottom of F437, then double click. If you highlight one cell, Excel will increment the dates to 01/03/97, 02/03/97 etc]

    Similarly, go to cell I436 in the 'DEP' column. In I436 type: 0 (zero)

    Fill I436 down to I518 with zeroes.

    Go to the top of the spreadsheet. [Ctrl+Home]

    Now to sort the database back into NLAC/DATE order. From the menu select:

    Data - Sort ' Sort by NLAC - Then by: DATE. OK

    The database reappears in the original order. Freehold Property and Leasehold Property in rows 2 and 3 now have dates.

    The data is now ready to be analysed in an Excel pivot table.

    B. 7 CHECKING THE DATA VIA SORT AND AUTOFILTER

    After bringing over data, you may wish to audit and reconcile it before producing the final accounts. Apart from pivot tables themselves, Excel's SORT and AUTOFILTER commands offer phenomenal facilities for analysing thousands of transactions very quickly. To give a couple of examples:

    EXAMPLE 1
    At present the database as exported from Sage is in nominal account code (NLAC) order. Suppose you wanted to look at the original source transactions. To this, simply:

    Click on cell D10, in the 'NO' column. Then click the AZ icon.

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

    And suppose you wanted to look particularly at purchase invoices. Select: Data - Filter - Autofilter.

    Black down arrows appear against each column heading.

    Click on the black down arrow in E1, the 'TYP' column. A list of transaction types appears.
    From the list, select: PI (purchase invoices). Only the PI type transactions are now shown.

    The earliest items are opening balances and not very interesting. Go to the bottom of the list.

    The last 2 purchase invoices were reference numbers 674 and 435. For each one, three lines of double entry are shown, the debits offsetting the credits.

    EXAMPLE 2
    Reconciling the Bank account often takes up a large amount of time. Suppose that you have a cheque on your bank statement for £2,138 and are not sure whether or not it was ever entered on Sage. AUTOFILTER enables you instantly to trace a single transaction within many thousands of records:

    At the top of column E, the down arrow is blue. Click on it and select (All).

    The filter is removed and all the data reappears.

    (You may need to use Ctrl+Home to get to the top of the worksheet).

    Now click on the down arrow in cell A1, NLAC. A list of nominal codes appears.

    Scroll down to account 1200 (this is the account for Bank). Highlight it and click.

    Autofilter filters out everything except Bank transactions.

    Now click on the down arrow in cell K1, the VALUE2 heading.

    A list appears of all the values Excel has found. From the list select: '2138.00

    Excel has found the entry. It was transaction no. 189 in Sage, with no cheque number.

    Close down Autofilter via: Data - Filter - Autofilter. The transactions reappear.

    B. 8 REPRODUCE THE TRIAL BALANCE

    Our initial task is to reproduce the Sage Trial Balance report (the last two pages of this document). If we can do this, it will prove that all the transactions have come over correctly from Sage into Excel. We'll do this via a pivot table.

    When creating a pivot table, always first check that all Date fields are OK. The Group command cannot group transactions by month if there is even one faulty value in a Date column. Therefore:

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

    Confirm that the dates in Rows 2 and 3 look OK. (They do.)

    Now go the end of the file [Ctrl+Down Arrow]. Confirm that the dates in the bottom rows are OK.

    (They aren't. The last few transactions are dated August. But for this exercise, accept them.)

    Also, note down the number of the last row. It is 518. Now go back to Row 1 [Ctrl+Home]

    We'll build up the pivot table step by step. From the main menu at the top of the screen, 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:$N$518.

    [Check the range carefully. The first half should always be $A$1. The second half should show the last row you noted down ' 518. If the number is higher, say 519, it means the pivot table includes a blank record and the Group command will fail. Also, earlier versions of Excel don't always pick up the range automatically. If the Range box is blank or says 'Database', then: make the box blank; click the pointer inside it; click onto cell A1; type a colon; hold down Ctrl and press the 'End' key]

    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:

    NLAC into the ROW area.
    VALUE2 into the DATA area.

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

    Now click on Finish. [Excel 2000, OK - Finish]

    In column A is a list of Nominal Codes. In column B is a list of 'Totals'.

    Excel has taken the 518 transaction records in the NOMACTIV worksheet and generated a pivot table that summarises them by nominal code

    Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of NOMACTIV. Probably it is named Sheet1.

    Click back onto the NOMACTIV worksheet to confirm that your transactions are still there.

    Now right click back to the new sheet containing the pivot table. Rename this worksheet PIVOT.

    B. 9 FORMAT THE NUMBERS

    The numbers in the 'Total' column B need to be tidied up.

    First, right click on any number within the 'Total' column.

    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.

    [Excel 5 ' from the list select: #,##0.00;[Red]-#,#00.00]

    B. 10 ADD THE NOMINAL ACCOUNT NAME

    In addition to the nominal account code, we need the account name as well:

    Right click on the pivot table. The menu appears. Select: Wizard. [Excel 5 or 7 = Pivot Table]

    The COLUMN-ROW-DATA box re-appears. [Excel 2000, select Layout first]

    Drop NLNAME below NLAC in the ROW area. Then Finish.

    The nominal description is displayed on the pivot table, but the screen is not very clear as the NLAC Nominal Code is totalling. These totals need to be removed:

    Double right click on the grey NLAC field button in A2. The menu appears

    [Note to Excel 2000 users. Cell references within pivot tables are usually two rows lower in Excel 2000 compared with other Excel versions. Cell A2 therefore usually signifies cell A4, and so on].

    Select: Field. [or Field Settings, or Pivot Table Field]

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

    That looks better. Now left click on NLAC in cell A2. The column is highlighted.
    [Note 1. Excel 2000, move pointer above NLAC until you get a black down arrow, then left click]

    [Note 2. 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]

    Centre the nominal account codes by clicking the Centre icon.

    Click on cell A4, two lines below the grey NLAC field button. [Excel 2000, click on A6]

    Select: Window - Freeze Panes

    Now go down to the bottom row (Row 148). The Grand Total is zero, as it should be in a Trial Balance.

    Check the account balances in the pivot table against your printed Trial Balance.

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

    B. 11. ADD 'BALANCE' IN THE PIVOT TABLE

    In fact, strictly speaking you didn't need a copy of the Trial Balance report, since Sage obligingly have brought over the account balance in the BALANCE field.

    Right click on the pivot to bring up the menu. Select: Wizard

    Drop BALANCE into the ROW field under NLNAME. Then:

    In the ROW area, double click on NLNAME. Change Subtotals from Automatic to None. - OK.

    Finish. The pivot table is recalculated and a 'Balance' column is added.

    The balance is positive, whether debit or credit. Check it against the Total column as follows:

    Click on cell E3. From menu, select: Insert - Function.

    In the box there are two lists. From the right hand list, double-click on: IF

    Type the following: Logical test : d3<0
    Value if true: c3+d3
    Value if false: c3-d3 OK The value 0 appears in E3.

    Click onto E3. Place the mouse pointer on the RH bottom corner of E3 until it becomes a black cross. Double click the mouse. The formula is copied down to the bottom of the worksheet.
    If you have any problems, hold the mouse button down and drag the cross to the bottom of the column.

    All the values in column E are zero, proving that the calculated Total equals BALANCE.

    Now delete column E.

    Also remove BALANCE. Right click for menu - Wizard - drop BALANCE off the table.

    B. 12. GROUP THE PIVOT TABLE MONTH BY MONTH

    We'll break down the account balances by month, using the Group command.

    Go back to the top of the pivot table. Right click within the pivot table to bring up the menu.

    Select: Wizard [or Pivot Table]. The COLUMN-ROW-DATA box re-appears.

    Drag and drop DATE into the COLUMN area. Finish.

    The pivot table is recalculated. Across the top there is now a large number of dates.

    Now double right click on the grey DATE field button in cell C1. The menu appears.

    Select: Group & Outline - Group. The 'Grouping' box appears.

    [if instead you get a 'Cannot Group That Selection' message, see paragraph below]

    Select both Months and Years (at the bottom of the list under Quarters). OK.

    The pivot table summarises the balances by month.

    [Error message - 'Cannot Group That Selection' You should not get this error message during the tutorial. However, if you ever do, the following notes may be helpful'''The Group command is invaluable, but a bit temperamental. I get more trouble trying to group date fields than all other problems put together. And if it does fail, the only feedback you get is 'Cannot Group This Selection'. There are two lik

    Replies (4)

    Please login or register to join the discussion.

    AccountingWEB logo
    By Accounting WEB
    24th Aug 2000 12:56

    Nice idea, but a Macro is quicker
    There is a macro available that will input a Sage Trial Balance (& TB by Date), Nominal Ledger & add company detail to the Nomina Ledger (if required). It will also tidy the text and remove zero balances.

    To get a copy E-Mail me at [email protected]
    (PS a departmental TB & P&L generator for sage V4 and V5 is also available)

    Thanks (0)
    avatar
    By AnonymousUser
    18th Feb 2000 17:23

    SAGE L50 is fairly good -but now is more powerful using Pivot Ta
    The web-based tutorials that David Carter produced were excellent. Easy to follow and now I have a powerful tool to use for the accounts I produce for my company.

    I wish SAGE had mentioned the uses that the CSV data had when imported into Excel.

    Thanks (0)
    avatar
    By AnonymousUser
    18th Feb 2000 17:23

    SAGE L50 is fairly good -but now is more powerful using Pivot Ta
    The web-based tutorials that David Carter produced were excellent. Easy to follow and now I have a powerful tool to use for the accounts I produce for my company.

    I wish SAGE had mentioned the uses that the CSV data had when imported into Excel.

    Thanks (0)
    avatar
    By AnonymousUser
    14th Feb 2000 13:30

    Debit and credit columns
    In the tutorial, when dealing with the conversion of separate debit and credit columns into one column, David suggests sorting the columns and replacing the blanks with 0s. Quicker to select the debit and credit columns and then do a find and replace. Find a space and replace it with a 0 (zero) seems to do the trick!

    Thanks (0)
    Share this content