Excel zone

Tutorial: Sage and MSQuery #5 - Add nominal categories. By David Carter

We need to add two more items - the nominal categories - to the Analysisready.xls file we created in tutorial #3.

Click the link to download a copy of the nominal categories lookup table.

1. Contents of Sage_Lookup_Nominal_Categories.xls
This lookup table contains the Nominal Categories in the Sage demonstration company, Stationery and Computer Mart.

There are two worksheets – COA and PIVOT. Click onto PIVOT.

This is a pivot table. It shows the format of the P&L report we going to produce.

Now click onto the COA (chart of accounts) worksheet. The P&L layout in the pivot table has been calculated from this worksheet. You need to get your brain around how one gets turned into the other. The notes below should help.

Columns A (NOMCD) and B (NOMNAME) list the codes and names of all the nominal accounts (P&L only).

Columns C and D list the codes and names of the nominal categories. In fact, Sage stores only a category name, for example “Product Sales”, in column D. I’ve created a category number as well in column C, eg “40”)

Columns E and F list the top categories, eg “Sales”. Again, Sage only stores the value “Sales”, and I’ve created a number, “1”.

The reason for the numbers is to control the sort order. You want Sales to come top of the P&L, but S is near the end of the alphabet. By prefacing Sales with a 1, we ensure that it comes at the top.

Column G, NOMCAT, combines the values in columns C and D.

Similarly, column H, NOMTOP, combines the values in columns E and F.

It’s these last two fields that shall be pulling into the list of Sage transactions.

2. Use data from your own company or the demo company?
This set of categories fits the Sage demo company, Stationery and Computer Mart.

So, if your version of Analysisready.xls contains data from Stationery and Computer Mart, this lookup table will work fine. Continue to the next section.

On the other hand, if your copy of Analysisready.xls contains your own company data, you will have to make a lookup table for your own company in the same format. Instructions on how to do this are given in the Appendix at the end of the tutorial.

3. Insert the Lookup Table into Analysisready.xls
We will need to implant this lookup table into Analysisready.xls.

Make sure that both Sage_Lookup_Nominal_Categories and Analysisready are open.

Go into Sage_Lookup_Nominal_Categories, and into the worksheet COA.

Right click on the tab name COA at the bottom.

From the menu, choose Move or Copy – at the bottom, tick Create a Copy – at the top, To Book: Analysisready.xls. OK.

COA now appears in Analysisready.xls, next to datelookup, PIVOT and MASTER.

You can now close the Sage_Lookup_Nominal_Categories workbook.

4. Pull NOMCAT and NOMTOP into MASTER
In the Analysisready workbook, click onto the MASTER worksheet

To the right of AMOUNT, insert two empty columns (columns L and M).

In L1 type NOMCAT. Then click onto L2

From the menu, select: Insert–Function (or simply click the fx icon).

In Excel 2003 the Insert Function box appears.In the big “Select a Function” box, find VLOOKUP. If it is not there, you can find it in the category: Lookup and Reference.

[Excel 2000] The Paste Function box appears. Check to see that VLOOKUP is in the right hand list. (If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP)

Highlight VLOOKUP and click OK. The Vlookup box appears, starting with Lookup_Value.

Lookup_Value: Highlight the NOMCOD column by clicking on the I at the top.

Moving lines appear around the “NOMCOD” column and I:I appears in the Lookup_Value field.

Table_Array: Click the mouse onto the Table-Array field.

Click onto the COA worksheet.

Click on the A at the top of column A, then drag to include columns B to H.

Moving lines appear around columns A to H.

NOMCAT is in column G, ie number 7. Therefore:

Click onto the Col_Index¬Num field

Col_ Index_Num Type: 7

Range_Lookup Type: false, OK

You should see the Nominal Category appear in L2.

Copy down this formula to all rows.

In cell M1, type NOMTOP

Now pull the NOMTOP values into column M in the same way.

5. Add the new fields to the pivot table report
The new fields added in this and the last tutorial now make it possible to produce management reports

Click onto the PIVOT worksheet. Right click on it for menu. Refresh Data.

Right click again for menu. Select: Pivot Table Wizard. Layout.

The Layout screen now includes the fields MTHNAME, FIN_YR, NOMCAT and NOMTOP.

Re-arrange the layout as follows:

COLUMM = MTHNAME
ROWS = NOMTOP, NOMCAT, NOMNAME (NOMTOP at the top)
PAGE = DEPTNAME, FIN_YR

The DATA field remains unchanged as Sum of AMOUNT.

OK. Finish.

The pivot table is recalculated

6. Tidy up the pivot table
In B2, click on the down arrow next to FIN_YR. Choose either 05_06 or 06_07, whichever you prefer.

If you use Departments, you can choose a department by clicking the down arrow in B1. Otherwise leave the value as (All), ie the whole company.

When a pivot table is created, it displays all the detail. To reduce the detail, right click on the grey field button, NOMCAT, in B5. From the menu, select Group and Show Detail-Hide Detail.

The pivot table now collapses to nominal category level.

Tidy up the numbers by right-clicking for menu and choosing Field Settings–Number-Number.

Personally, I use: Decimal Places = 0; Use 1000 separator, YES; and -1234 in red.

7. A final word about the monthly figures
Hopefully, this report is meaningful to you. However, it’s important to raise one caveat about the monthly figures you see.

I’ve calculated the MTHNAME value for each transaction from the DATE value (see Tutorial #4, section 8, the Lookup Value box).

However, since the DATE field is typed in by the operator, you are entirely dependent on the operator never making a mistake. If ever a transaction is wrongly dated to a previous month, Sage will recalculate that previous months’ figures.

You could stop Sage moving the goalposts by generating MTHNAME from the DATENT (Date Entered) value, which is system-generated. However, then you can’t keep the books open after the end of the month.

This is why reviewers tend to get rather sniffy about “date driven” packages like Sage and QuickBooks. They aren’t really capable of producing monthly management accounts. Of course, if you were using a properly written accounts package like TAS Books, this problem would never arise...

One way to keep an eye on mis-dated transactions is to create a calculated field DIFF, in which you subtract DATE from DATENT. The result is the number of days difference between the two dates. Any difference over say 20 days should be checked.

END OF SESSION. Save and Close.

Appendix: How to make your own Nominal Categories Lookup table

Step 1: Export a Trial Balance from Sage
Start up Sage. Go into: Modules–Nominal Ledger. The screen lists all the Nominal Accounts, including those with zero value.

Then, from main menu: File–Send–Contents to MS Excel

Excel starts up, and the Trial Balance appears in columns A and B.

Delete any balances. All we want are the account code and description in columns A and B.

Step 2. Create column D (Nom Category) and column F (NomTopCat)
Go back into the Sage Nominal ledger screen, showing the list of nominal accounts. At top right it says: Layout–List.

Change List to Analyser. This shows the nominal categories. Unfortunately, I don’t think you can print them out. [You can also find the nominal categories in:
a) Settings–Configuration–Chart of Accounts-Add or Edit Range.
b) Modules–Company–COA–Edit]

Type the nominal categories and top categories into columns D and F.

Step 3. Create column C (NMCT)
In the Stationery and Computer Mart example, the first two digits of the account number represent a nominal category (column C in the lookup table).

If your own nominal codes also work this way, you can enter the NMCT column C by taking the nominal codes in column A and splitting them in two.

To do this, insert two blank columns C and D. Copy column A and paste it into column C. Highlight column C, and from main menu: Text to Columns–Fixed Width–insert the vertical line after two characters. Finish.

The 4 digit number splits into two columns. Delete the right hand column.

Step 4. Create column G (NOMCAT)
Create the NOMCAT column by combining columns C and D.

In G2, type the formula =C2&”-“&D2

Copy it down into all rows.

Step 5 Create column H (NOMTOP)
Create the NOMTOP column by combining columns E and F.

In H2, type the formula =E2&”-“&F2

Copy it down to all rows.

Step 6. Create a pivot table to test your codes
After you’ve entered all the codes in columns A to H, create a pivot table like that in Sage_lookup_nominal_categories.xls to see what your P&L will look like.

Previous tutorials in the Sage & MSQuery series

Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:

Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Number of comments: 1

jc, 25 August 2007 @ 11:14 AM

Believe one can extract Nominal code from the Sage CATEGORIES table using SQL and the BETWEEN command - which greatly simplifies the extraction

Look at this WHERE clause using BETWEEN

WHERE ......
((AUDIT_JOURNAL.NOMINAL_CODE) Between [CATEGORY].[LOW] And [CATEGORY].[HIGH])

Full SQL:

SELECT CATEGORY.CHART, CATEGORY.CATEGORY, CATEGORY.NAME, CATEGORY.LOW, CATEGORY.HIGH, AUDIT_JOURNAL.NOMINAL_CODE, AUDIT_JOURNAL.AMOUNT, AUDIT_JOURNAL.DELETED_FLAG, Year([DATE]) & Format(Month([DATE]),"00") AS YearMth, '200301' AS FyStart, ([YearMth]-[FyStart])+1 AS FinMth,
Format([DATE],"mmm") AS FinMthText
FROM CATEGORY, AUDIT_JOURNAL
WHERE (((AUDIT_JOURNAL.NOMINAL_CODE)>='4000' And
(AUDIT_JOURNAL.NOMINAL_CODE) Between [CATEGORY].[LOW] And
[CATEGORY].[HIGH]) AND ((AUDIT_JOURNAL.DELETED_FLAG)=1));

(also provides financial month calc from previous tutorial - substitute the date in bold/italics with your own Sage company FY start date)

You can add fields from the AUDIT_JOURNAL table as required or use this as the basis for a Cross-Tab query (Excel pivot equivalent)

This SQL works in Access and there is no reason why it should not work in MSQuery as well - after all its only a SQL query

The advantage with using SQL and BETWEEN is that the command is dynamic and will work under any circumstances even if the Chart of Accounts is changed by someone else without telling you. Whereas using an xls lookup means that one has to construct a specific unique lookup for each company which is open to Excel errors if the COA has changed