The numbers would have to run in line with Sage coding, as I couldn't cope with remembering anything else! LOL! As a quick example, the current coding would go something like this...
First 4 numbers are the general description
5th number shows the site - 0 is for combined (needing apportionment) 1 for Manchester, 2 for London
6th and 7th number - 01-99 which allows us to further breakdown of the site transactions, into sub category
Each code would need number and name in it - cos no one is that good at remembering! The number really just keeps it in order I suppose, as we run down the NL/PL. I wouldn't be able to put a manager element in, as two managers can need the same code in their report.
4000s = income codes
4000-4099299 = donations analysis (40000XX = combined, and nature of donation) (40001XX is Manchester and nature) (40002XX is London, and nature)
4100-4199299 is grants analysis
4500-4599299 is income resources for charitable activities
4600-4699299 is activities for generating funds analysis
Should they open a 3rd site, its easy to insert codes with a 5th number of 3
5000s = wages, ers nic and staff travel, pensions etc
5000101 -5000199 is Manchester gross wages analysed by staff member - so can have up to 99 staff
5000201 - 5000299 is London gross wages
5001101 - 5001199 is Manchester employers NI - again broken down per staff member
5001201-5001299 is London employers NI
5002101-5002199 is Manchester pensions by staff member
5002201-5002299 is London pensions by staff member
It carries on to analyse personnel and recruitment costs, which are analyse by site, then by type category, so 50071 is Personnel Costs - Manchester, and 01 would be CRB checks, 02 - advertising for staff and so on
6000s = direct costs
The have 13 different direct cost analysis, split down in different ways - as an example, 6000 is "publications costs" 60011 means its Manchester and the further 01 is a named publication, 02 another named publication costs
Another - 6100 is staff travel. 61001 means its Manchester . 6100101 is Staff Travel - Manchester - Staff members name. We also have mileage on its own, accommodation on its own - all analysed per staff member.
7000s = support costs
7700 - Computer and IT costs
77001 Computer and IT costs - Manchester
7700101 Computer and IT costs - Manchester - software & licences
7700102 Computer and IT costs - Manchester - support contracts
7700103 Computer and IT costs - Manchester - peripherals
And it goes on, and the same is done for Oxford, just using a 5th digit of 2
This is the format each code has "Description - Site - Sub-description"
Michelle,
What a fantastic insight into an inventive way to use Sage! My mind is buzzing with questions.
As I understand it, the main coding constraints within which Sage users normally work are:
Nominal Code 8 digits
Nominal Code Description 60 characters
Department Code 3 digits
Department Name 60 characters
I appreciate that there are custom defined codes available for sales ledger, purchase ledger and stock related items (as well as Fund and SOFA analyses) but Ill ignore these for the purposes of this post. Our departmental reporting template customers often comment that they find that the department code is less well catered for in Sage reporting outputs than the nominal code, so an approach that embeds a code for 'site' in the nominal code is very interesting. Because it is relatively less accessible in Sage, I'll largely ignore the use of the department code for this post too.
What youve done in your example is to encapsulate three logical elements within the one nominal code, with a digit to spare. I came across an Oracle Financials system in the early 1990s that combined 4 logical elements into a single segment (Oracles name for a single logical element of an account code combination). Oracle catered for up to 30 segments in an account code combination, so it was highly unusual that the organisation had decided to squeeze four logical elements into one segment! I mention it because a consequence of so doing was that a lot of standard reports became virtually unusable as a result (too much detail) and the organisation had to develop a lot of bespoke reports that used the substring function in SQL to separate out the four individual elements so that reports could aggregate by element. I presume your approach doesn't create the same carnage for reporting?
When I said youd encapsulated three logical elements within the one nominal code, with a digit to spare, in fact, it is even more sophisticated than that. Your sub analysis can be of up to 100 different items for any individual nominal code. That is to say that, when dealing with Gross Wages (5000) for Manchester (1), the sub-analysis code 01 may represent Joe Bloggs whereas, when dealing with Personnel Costs (5007) for Manchester (1), the sub analysis code 01 may represent CRB checks. Fantastic flexibility, but Im really curious about a number of things:
Do you use the Nominal Ledger upload template to create all the additional code variants you need, or do you do it all at initial company set-up with a custom chart of your own?
You mention elsewhere "creating bespoke PLs that only account for some codes, not all". Can you elaborate on what you're doing a bit more?
How do you use the Nominal Code Description of 60 characters? Do you use a special separator to demarcate the three elements of the description? (e.g. 5007101 - Personnel Costs¦Man¦CRB Checks) Do you find 60 characters is often restricting?
Do you need to add a filter into your reports in Report Designer, for example, to allow you to filter by the fifth digit of the code?
Have you built custom reports in Report Designer to take advantage of the extra information? Care to share details?
Do you use 4 and 5 digit codes where the additional analyses are not relevant, or do you pad with zeros to seven characters where this is the case?
Your approach opens up the possibility of more easily implementing crosstab type coding to support tabular notes to the accounts (Tangible Fixed Assets, Provisions etc) directly from the nominal ledger coding. Consider tangible assets. If the first four digits represent the asset type, the last two digits could be:
00 Opening Cost or Valuation
01 Additions
02 Disposals etc
10 Opening Accum Deprec
11 Deprec charge
12 Deprec disposed etc
I know that youve said that you only use Departmental reporting occasionally, but it strikes me that it could come into play if reporting needs were more demanding. Mind you, if you had more than 9 sites youd probably just re-implement with codes 5 and 6 for site and 7 and 8 for sub-analysis. Perhaps if you needed a second sub-analysis for some reason?
Im thinking that a structure like 5007101 - Personnel Costs¦Man¦CRB Checks would allow our Onion software or its complementary templates to record three separate additional codes in the underlying PivotTables to enhance the reporting flexibility. Theyd be:
Account: 5007 Personnel Costs
Site: 1 Man
Sub-analysis: 01 CRB Checks
Anorak that I am, the possibilities are exciting me. I think it would be a really useful option to add into the existing mix. Now I just need to figure out how to implement the functionality in a couple of clicks!
Thank you so much for sharing your approach. Id love to hear more (Im sure there are plenty of implications I havent yet grasped, or perhaps Ive misunderstood how youre implementing). If anyone else does innovative things with their Sage coding it would be really interesting to hear details on here if you have the time to write.
Regards,
-- Edited by Onion4Sage on Friday 9th of October 2015 01:03:59 PM
Now then, if you are going to make more money out of my fabulous-ness, I expect a cut!
Hahah!!
Awww thank you! Im blushing! So nice for someone to recognise my efforts... I am going to disappoint you a little and say - I just did what seemed to make sense! No real techy brain behind it, I don't even understand half of what you just said!!
The NL list is crazy long, but, as far as possible, I've set up default codes for customers and suppliers, and have memorisations set up for some journals.
You can copy the default PL, and then take out all the codes you don't need, to create a report that shows one manager just the income and/or expenses they want to see. It will always tell you that you have a partial COA but as long as you are dilligent to add any new codes created, it seems to work fine. Doing it this way means a code can be in every managers report, if it needs to be. Departments couldn't make that happen.
I can also do the Site PLs, which should add together to match the main PL.
What would be great, is if you could create blank PLs, and when setting up a code, or editing an existing one - you had the option to tick which PL report you wanted the codes to be shown on... so you set up a code and in defaults you have a list of the available PLs, and you just put a tick next to the ones you want it to appear on... That would save you having to keep messing about with the chart of accounts
You can copy the default PL, and then take out all the codes you don't need, to create a report that shows one manager just the income and/or expenses they want to see. It will always tell you that you have a partial COA but as long as you are dilligent to add any new codes created, it seems to work fine. Doing it this way means a code can be in every managers report, if it needs to be. Departments couldn't make that happen.
Michelle,
That's some tough requirement! Some figures dependent on manager, some not. The bespoke P&L COAs act as a sort of detailed multiple item filter to control the output content of standard reports? You have a different P&L COA for each manager? Have I understood correctly?
I'd been thinking more of editing standard reports to include manager filters and, maybe, adding further memorandum lines to which the filters didn't apply if other lines were needed. I think I'd prefer your way as it avoids the dreaded Report Designer (but it renders the report totals meaningless, does it not?). I just need to overcome my ingrained aversion to seeing [PARTIAL] on any COA definition.
Yes, bespoke PL's simply pick up the codes the manager requires. And each manager has his own template. I suppose many people are put off by the idea of having an impartial CoA. One does have to be diligent though. I ask the client to keep a list of all new codes created between my visits, so that I can update everything when I next go in.
I don't have that many bespoke reports so far, but all information is set up so that something CAN be created, IF it becomes necessary. I tried to create the system so it had flexibility as things change - and even now, I realise there are some things I could have done differently, as they were created for today's situation, and didn't take into account what might happen down the line.. but you live and learn! At the moment, we have
Combined PL - which should get apportioned off to NIL (these are holding accounts, really - so M/c is 1: London is 2: and Combined is 0. These codes don't get used that much.)
Manchester PL - picks up all xxxx1 codes
London PL - picks up all the xxxx2 codes
These 3 should equal the default PL - if they don't, we know one has a missing code on the COA.
Then I have a PL specifically for the NHS contract (which has to pick up items already included on the site PLs, hence why departments wasn't enough.
There's a staff costs template, which is output as the monthly PL breakdown - this is generally used in conjunction with another spreadsheet, which analyses the staffing costs into project, for the accountant, who does a project income/expenditure note on the account.
There's also a PL that picks up the support costs which need apportionment..
The staff member that hunts down funding also has her own PL, because she only needs certain figures when applying for grants.
Its still a work in progress. The main manager has been off quite a bit, so we haven't been able to move forward with some things. He said he wants a PL, that will also have a section for fixed assets at the bottom, so he can review whats been spent... and I am thinking of using the taxation section of the COA for that. I've done that many times before to show dividends to directors (as cash drawn gets put to the DLA, so that the accountant can decide on actual dividends... so I just ask the PL to include the cash drawn code where the taxation section is. It just for information purposes and comes under the profit figure, so is quite useful.
That approach to nominal coding is similar to one I employed from around 1994 for a company (actually a registered charity, providing care homes) I worked for, after a merger with another, similar organisation to form a new, much bigger one.
If memory serves, what I did was use 6 digit codes, along the lines of ddgccc, where:
'dd' represented the 'department' with 00 being head office, and the care homes starting at 10
'g' was the coding group
'ccc' was the actual nominal code within that group.
I can't remember why I chose that approach with the new organisation rather than use proper departments - but I do remember discussing the CEO and ruling out departments, going away to think about it, and coming up with the above as a solution.
Reporting was actually done via spreadsheets, with data exported from Sage and run through something I wrote. (In fact, quite a lot of inputting was done using the exact reverse of that - starting out in spreadsheets, processed by another function in my program, then imported into Sage.)
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
VinceH wrote:I can't remember why I chose that approach with the new organisation rather than use proper departments
I suspect that like myself you've had some considerable exposure to database design vince and with most database packages the use of the primary key rather than foreign keys / secondary indexing has performance advantages with large databases so that mentality becomes ingrained.
This sort of composit key arrangement has been around for a long time. Even evident in old heirarchic database structures such as IMS DB/DC. Indeed, in 1979 I was reading it in database structures created in the 1960's.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
