Reporting Challenge 1: Produce a P&L from a TB with 15,000 accounts

If your accounts package makes use of multi-segment nominal codes, you can find your nominal codes breeding like rabbits. A reader asks David Carter for help.

Anyone who reads the AccountingWEB software Lab Tests will know that I don't like accounts packages that use multi-segment nominal codes.

The problem with multi-segment codes
At its simplest a nominal code is one of the accounts that appears in the P&L or Balance sheet - Rent, Marketing etc.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Why MSAccess

Darwood | | Permalink

Why do you need MS Access?
Can't Crystal report on your accounting software directly?

A possible solution

Anonymous | | Permalink

Faced with a similar problem in the past, I decided that there was no easy way out, so I split the cost centres into separate accounting and then consolidated to produce management reports - the advantage being that the information for each cost centre is always readily available

Chart of Accounts

jrnewton | | Permalink

This is a comment on system design and not a solution to the problem.

Multi-segment codes are a pain when it means replicating the nominal code for every cost centre/department/division.

One system I have used - Movex Financials from Intentia - uses a different approach. The full nominal code can have up to 7 elements which are defined independently. So if you have 100 nominal codes that is all you have no matter how many cost centres and other analysis codes you need.

Normally the first element is used for the nominal code proper and you are then free to define which of the other elements are used - all, none or element 2 and 4 or whatever. It's up to you. This chart of accounts sits underneath a divisional structure so you can have as many divisions as you need also.

An expense code for purchases may not need a cost centre but you may want to combine it with say, element 3 which holds a product code. Overhead codes would need a cost centre but not the product code. A balance sheet nominal may not need any further sub-analysis.

This is much more flexible for reporting than permanently fixing each element together in a long unwieldy code which is then multiplied for each cost centre/division. Using the report writer supplied with the system it was very easy to produce all types of accounting reports.

Access

mathewr | | Permalink

Whichever method you choose, you are still faced with your client's inconsistent NL codes. Can these be corrected? If the data is in SQL format, this should not be a problem, but is not a job for the faint-hearted.

I would strongly advise getting to grips with MS Access; it will avoid the revision of data ranges etc and should provide more ways to slice and dice the numbers. Furthermore, the reports are easy to tweak to suit different circumstances. I should think that 80% of my reports are now done through MS Access.

I would also recommend Crystal Reports as an easy-to-use alternative, but beware - it is easy to construct inaccurate reports if the figures in the database are not fully understood.

Good luck!

reporting problem

AnonymousUser | | Permalink

I would data/text to colums first to split the code into its various components.

then either pivot tables or access.

however, it is probably worth ensuring that all possible codes are set up to make sure you get consistent pivot tables.

The reports could then lookup the tables.

carnmores's picture

Monarch

carnmores | | Permalink

is an excellent data miner you might like to have a look

in excel

davidcottam | | Permalink

In one sheet create a generic chart of accounts based on digits 5-7.
This can be mapped to generic p+L.in another sheet using =sumif().

Enter company code and company department, each in one cell, then concatenate these with ledger account, creating new variable column. In the chart of accounts.

Drop full tb in another sheet, create a new column of tb codes with subcode stripped off (=left()).

Collect numbers from tb into chart of accounts using =vlookup()

So this will produced one departments tb and hence p+L.

Create a macro to run through all companies / departments which puts each company code and company department in turn into the designate cells, as above, and prints or whatever.

Note convert the chart numbers or part numbers into labels at ALL stages.

Re wrong format numbers – another problem.

Crystal Reports

Darwood | | Permalink

I am sure you would be best using crystal reports to do this.

carnmores's picture

Hi David

carnmores | | Permalink

this sounds like an absolute nightmare and thats just the coding!

i think i agree about pivot tables, though sorted subtotals reports might be an easier option.

I suspect that Access might be the best answer but i am useless with it. If i got off this site i could usefully spend my time learning about it.

which reminds me are Aw eb still flogging Simon Hirst's productivity kit , has it been updated or isnt that necessary and is it amazing?

listerramjet's picture

an excel solution

listerramjet | | Permalink

there is a source and a target. the source is a 15000 line trial balance, and the target is the management accounts.

BASIC SOLUTION
the first step is to create a mapping table, which links source to target. This will be 15000 lines long, in two columns. column 1 has the source codes, and column 2 the target codes (could use the management account line descriptions, or could create codes for each management account line).

then use a vlookup in an extra column against the trial balance, to get the target codes against each line of the trial balance source.

then use sumif in the target report to aggregate the trial balance figures.

DEALING WITH MULTI DEPARTMENTS
The report can still use sum if but split the target code into management account line reference and department. put the management account bit of the code in the rows, and the department bit in the colums (use the first row for this. Then use string concatenation in the sumifs to put the account and department together.

AND PIVOTS IF YOU LIKE
but if you prefer pivots then can use a pivot on the tb rather than create the sumif based report

ADMINISTRATION
requires some additional effort when new codes are added to either the source or target.

AND ACCESS IF YOU LIKE IT
the method can be applied in Access - it gives a more elegant, and easier to maintain - solution, but it works fine in EXCEL if the row limit is not exceeded.

John Stokdyk's picture

ProductivITy tips & tutorials

John Stokdyk | | Permalink

Nicholas,

Thanks for reviving memories of Simon Hurst's ProductivITy Kit. It's still here - one of the many hidden treasures tucked away on AccountingWEB.

You can buy the six-module tutorial as a standalone edition for £60 or get the single database introductory module as part of the Office ProductivITy Service for £150. The service includes esupport from The Knowledge Base and I suspect that if you fired in a lot of questions about MS Access, you would probably get quite a lot of the material in reply.

While the general principles may hold, I'm not certain about the specific versions of Access and Excel that the tutorials address - it's nearly three years old now. However, we're big fans of Simon's on AccountingWEB and have been talking to him recently about contributing to our new ExcelZone. Some of the things that hes posted (free to air) in recent weeks explore the subject of MS Access (and Excel), including:

  • Linking to Access tutorial
  • A pain-free introduction to MS Access (based on ProductivITy Kit material)
  • Don't be frightened: Learn to live with databases

    Hope these items whet your appetite for databases. It looks like you'll be having a thrilling summer!

    John Stokdyk
    Technology editor
    AccountingWEB.co.uk

  • carnmores's picture

    LOL

    carnmores | | Permalink

    thanks John it wont be boring, mind you i am sure that other contributors will be pleased to know that i am off to greece for 3 wks in 3 wks, now where did i bury that pda!

    listerramjet's picture

    why MS Access?

    listerramjet | | Permalink

    actually a very good tool to bring together multiple sources of data, and the technique described to produce data for the report is a very common technique. Personally I would question the benefit of Crystal Reports. MS Access has some very good report presentation tools, as does Excel.

    Ms Access

    Darwood | | Permalink

    I can see the need for MS Access if you are drawing from multiple sources otherwise I think you are adding an unnecessary layer. Some people might prefer a dynamic report but it does depend upon the requirements which we don't have in the scenario described by David Carter. He would ahve to consider whether MS Access is a necessary layer in his scenario as it is in yours.

    In any case I wouldn't put Excel anywhere near this solution unless there was an explicit customer requirement. Perhaps if the customer said he wanted to get it into a spreadsheet in the end. I would still question why he wants to do that. There is a lot of crossover these days between products but in the scenario described by David Carter we are looking primarily at a reporting solution. That in my opinion calls for a reporting tool and Crystal is the way to go. If you want to manipulate the data first ir pull together from different sources by all means chuck MS Access in the mix too. I haven't done much with it but the reporting facilities might fit the bill but I would be very surprised if they reach the standard of Crystal.

    There is a side issue of some data cleansing which is where Access or Excel might come in handy.

    listerramjet's picture

    reporting tools

    listerramjet | | Permalink

    Hi Darren

    I think you should reconsider your views of Excel. There are numerous documents on this web site that demonstrate some of the powerful ways in which Excel can be used to report data. Pivot tables are a favourite current topic, but don't forget autofilter and charts. Also to remember is that there is a large installed user base, excellent support and development community, and a large supplier committed to product development.

    Perhaps you are mixing up the reporting aspect of Excel with its spreadsheet functionality? But there is a world of difference between Excel as a spreadsheet tool and Excel as a reporting tool.

    Crystal might be an "industry standard" database reporting tool, but it has its own limitations. As a reporting tool it is at a similar level to some of the tools in Access, but a big plus for Access is its integration with Excel (and other parts of the Office products).

    Excel

    Darwood | | Permalink

    I will do that. I am primarily a developer so my Excel exposure is admitedly very limited. I have however seen numerous examples of people trying to push a product (particularly Excel) beyond it's designed uses. The end result usually ends up messy and unreliable. No doubt we have all seen systems where you need to turn round twice and kick the cat before pressing F3 to get the routine to run. But the guy who designed it thinks it works fine because he knows all of the foibles. Documented procedures or user guides? Never! Standard user interface? Hardly! That is before I even get into validation, lookup tables, referential integrity, multiuser access etc.
    Try and separate an accountant from Excel is like taking a dummy out of a baby's mouth. The tears are heartbreaking. What is the expression? To a guy with a hammer everything looks like a nail?
    I see many examples of companies running their entire financial operation on spreadsheets and the whole thing just about hangs together. But I would always recommend a proper accounts package be used; but then that is another discussion.
    So my antiExcel bias aside I will look at it as a reporting tool again with an open mind sometime.

    Oracle functionality

    David Carter | | Permalink

    Thanks Richard.
    I don't know Oracle that well. I do know that Oracle and others make it very easy to create new combinations "on the fly".

    But are you saying that, when you create a new cost centre in Oracle, the chart of accounts does not actually increase in size?

    Any chance of giving a worked example?

    listerramjet's picture

    re Oracle

    listerramjet | | Permalink

    Does it allow the administrator to disallow certain combinations? e.g. I might have a TV advertising account code, which I want to restrict to the marketing cost centres.

    Nice

    Darwood | | Permalink

    Which of course is how a decent system should handle this issue. The 'other' way highlights the problems of using 'packed' data fields where you try and pack more than 1 variable into a single field.

    Monarch - more than excellent

    Anonymous | | Permalink

    Nicholas Myles has mentioned Monarch earlier without elaborating. Monarch is a fantastic product and I think all accountants should have it in their toolkits. See www.datawatch.com for details. (I have no commercial interest to declare being merely a satisfied user.) It is of particular benefit to those working with legacy systems that produce large, detailed reports and where it is difficult to have new, more appropriate reports written.

    If the client does not want to spend a lot of time on Access or Crystal Reports, he could learn Monarch and set up a model to deal with this type of requirement in a few hours. It would enable him to extract the departments or sections that he requires; sub-total/summarise them; export lists and summaries to Excel; automate the process etc.

    Monarch will also work with almost any software package so the skill is portable.

    Check it out, it might be worth your while!