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

Kashflow logo
Share this content

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. But supposing you need to analyse to cost centres and departments for your management accounts?

One solution favoured by package designers is to add these cost centres and departments to the nominal code and combine them into one 'multi-segment' account code. Segment 1 (digits 1 to 10) might contain the nominal code proper, segment 2...

Please Login or Register to read the full article

The full article is available to registered 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.


Please login or register to join the discussion.

By Darwood
25th Jul 2005 10:57

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

Thanks (0)
By Anonymous
21st Jul 2005 12:34

A possible solution
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

Thanks (0)
21st Jul 2005 14:12

Chart of Accounts
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.

Thanks (0)
By mathewr
21st Jul 2005 11:09

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!

Thanks (0)
21st Jul 2005 10:33

reporting problem
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.

Thanks (0)
21st Jul 2005 16:46

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

Thanks (0)
21st Jul 2005 16:49

in excel
In one sheet create a generic chart of accounts based on digits 5-7.
This can be mapped to generic 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.

Thanks (0)
By Darwood
21st Jul 2005 10:12

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

Thanks (0)
19th Jul 2005 23:36

Hi David
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?

Thanks (0)
20th Jul 2005 09:20

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

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.

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.

but if you prefer pivots then can use a pivot on the tb rather than create the sumif based report

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

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.

Thanks (0)
20th Jul 2005 09:46

ProductivITy tips & tutorials

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

Thanks (0)
20th Jul 2005 10:46

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!

Thanks (0)
25th Jul 2005 16:03

why MS Access?
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.

Thanks (0)
By Darwood
25th Jul 2005 17:00

Ms Access
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.

Thanks (0)
26th Jul 2005 09:05

reporting tools
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).

Thanks (0)
By Darwood
27th Jul 2005 14:32

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.

Thanks (0)
28th Jul 2005 22:08

Oracle functionality
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?

Thanks (0)
29th Jul 2005 13:19

re Oracle
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.

Thanks (0)
By Darwood
01st Aug 2005 10:32

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.

Thanks (0)
By Anonymous
02nd Aug 2005 13:55

Monarch - more than excellent
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 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!

Thanks (0)