Nominal Records/Reports incorrect

[lorraineroberts]

When we run nominal reports or look in the nominal records section of the nominal ledger tab some of the nominal accounts have duplicated information in them. However, the information is correct when we look in the nominal activity section of the nominal ledger tab. This is not a problem for our current 2006 financial year, but for our 2005 closed off financial year.

SAGE line50 version 10

[brucedenney]

It is unclear as to what you are describing.

I guess the "nominal ledger tab" is the nominal ledger module (Modules>Nominal Ledger)

I know what the activity section is, but that seems to be alright.

But other than that I am lost. Which reports are wrong and what information is duplicated?

The previous year bit suggests that you could be looking at brought forward transactions, only these are not duplicated, they are reversed so as to enable the transaction to be zeroed out for the new financial year but be retained on the system for VAT, Bank Rec or Customer / Supplier matching.

Is there any common factor in the duplications?

Can you give an example?

[lorraineroberts]

Sorry! OK I'll try again.

An example: I have a nominal code 4220. In 2005 the total income was £900.00; £200 in March, £300 in September, and £400 in December 2005.

The next nominal code is 4225. That has income of £200.00, all received in December 2005.

If I go to Nominal>Activity and specify the date range Jan to December 2005, the above information for each nominal code is detailed correctly. I assume from this that the audit trail (source) is correct.

However, If I go to Nominal>Reports>Nominal Details reports and run a report for the above two nominal codes, the prior year (2005) information given for 4225 is an exact duplication of the nominal code 4220, month for month, and of course in total.

Also, if I go to Nominal>Record and request the record for nominal code 4225 the information is again a duplicated version of 4220.

In both cases, the current year information is correct (i.e. not duplicated).

The nominal codes this is happening to are quite random, except that it is always two or three codes that are next to each other in the nominal listing that are duplicated.

I'm not looking at brought forward transactions/ledger year end balances which are unrelated.

Hope the above makes more sense.....

[brucedenney]

When is your year end?

Did you clear the audit trail at year end or have you left all last years transactions in situ?

Which one of the "Nominal>Reports>Nominal Details Report" are you running?

To clarify your issue is that transactions (in last year) on account 4225 appear to have the same monthly total values as account 4220.

Please run the following report for both accounts (for one of the duplicated months) and see in what way the transactions on the two accounts are different.

Nominal>Reports>Nominal Activity Reports>Nominal Activity

Is it possible that "someone" had split the sales 50:50 between the two sales accounts and that they were posted equally and hence now appear equal?

[lorraineroberts]

Year end: 31 December.

We have cleared the audit trail.

I ran the nominal details reports>nominal record values "nomvals.srt" report.

You are correct in your clarification.

As requested I ran the specified nominal activity "nomact.srt" report for both accounts. The information in that report appears to be correct for each nominal code. Therefore, given that the information in the nominal>record section is incorrect, I would assume that this is where the trail is failing - in the nominal record part of SAGE?

No its not possible that someone has split the sales etc etc. Definately not.

What do you think?

[brucedenney]

To be absolutly clear the values shown on the nomact.str report are correct and different to the values shown on the nomvals.srt report which are incorrect.

[lorraineroberts]

Yes. The values are different.

[brucedenney]

It sounds like the "prior year" values on the nominal accounts have been displaced by one nominal account.

It could also have affected all the other prior years and budget as well.

Can you open the archive you created at year end and run the nomvals.srt report and just verify that the actuals in 2005 are misplaced by one nominal code to be prior year values in 2006. Have a look and check out the positions on budgets and the other prior years as well.

Did you import / export nominal codes at some point? maybe around year end, possibly to do budgets or something? This would/could explain how they came to get misaligned? If you haven't then we haven't any explanation and it could be something else and it could happen again which is a bit of a worry.

Post back when you have checked things out, if it is just one nominal code out and once we know if the other prior years and budgets have been misaligned as well, I can do you some instructions on how to fix it.

[lorraineroberts]

I have checked out the 2005 archive etc etc and there are no errors in the data there. This does mean that I should be able to run the reports I need for the 2005 financial year which is great! Thanks for that suggestion.

However, it doesn;t solve the problem with the current company data. I have looked in the current company data and note that the 2004 values also appear to be duplicated. I hadn't checked these before you suggested it.

In terms of things that could have caused this....

I have added certain nominal ledger codes since the year end, but not all the codes I have added have caused these problems.

I have amended the names of some existing nominal codes.

I have gone into report maker at various stages and amended (but not saved) reports to produce results in a format I like.

The only other thing I have done is manually input budget details to the nominal>record part of SAGE for the current year. I did this by going into each nominal record and typing in the monthly budget amounts.

The problems have occurred since I did this manual input. We had a similar problem before with the current year data. However that was sorted by running check data and fixing the problems. (Obviously we have tried that again, but no errors are coming up).

In terms of displacement, just to clarify..... the problem is not consistent across all nominal codes. eg 4525, 4530 and 4531 are duplicates of 4531. Then 4534 is a duplicate of 4535 (the next nominal codes in the list). But then 4536 onwards is correct until much later down the nominal listing, 6240 and 6245 etc etc.

[brucedenney]

This is going to be a bit more complicated than I thought.

Rather than trying to give you step by step instructions, I am going to have to explain the principle, the tools and what you need to do.

What I am going to suggest is not without risk.You could make a real mess of things so BACKUP first.

The general principle is that the nominal data that appears to be wrong can be imported into line 50, so we can fix the numbers externally using the good data form the archive and then, using the import facility, load it all back in again to the current year.

What we will create is a spreadsheet, you can use open office, excel or whatever you are familiar with.

The sheet will contain the nominal code, its description and then sets of twelve columns for budgets and then the prior year(s).

So you will have a lot of columns and a lot of numbers and it could get very mixed up.

Rather than type all the numbers in ourselves we will cheat, we will get the numbers out of Sage alter them and then put them back.

(Actually we will get the number out of Sage a couple of times, from the current year data to get the budgets and from the previous year archive to get the good data for previous years).

We can combine these to create a "good" set of data for the current year and then import it back into Sage.

If you are happy with this and you understand what I am talking about then go for it, if you don't then you will have to pay us to do it for you or get someone else to help (Sage tech support?)

You need to decide with these files which one will be the master, are you going to use the archive data and add in the budgets and new accounts from this year or use this years data and add in the information from last year.

I would be inclined to use this years data and the vlookup function in excel to find the corresponding correct data in the archive. (you could cut and paste very carefully if you wanted.

First we dump the data out of the current year sage nominal ledger by printing the report Ni_ledg2.srt Reports>Nominal Details>Nominal Record CSV Extended. This is a handy format report because it create a file that is in the right format for a nominal import, so all we have to do is fix the data and we have it made. When you print the report print it to file and choose the csv file type, the file it creates you can open in your spreadsheet program "as" a spreadsheet.

It is worth nothing the field orders they are listed here
http://sage.support.makingithappen.co.u ... nomrec.htm

Version 11 and later is much more complicated they go back 5 years!

This will become our master file that we will import back into sage later.

We need to know the good data for the prior year so in the archive run the nomvals.srt report to file and set it to csv.

With both these files open in your spreadsheet program you can cut past or lookup current information from the previous year to correct the prior year information in the current year.

Once you have the corrections done, save the file (Note that excel has some quirks with csv format see below
http://sage.support.makingithappen.co.u ... oblems.htm

Now import the "fixed" data back into Sage with File>Import

[lorraineroberts]

Thanks.

I thought I might have to import data from the archives. I should be able to do it, so it shouldn't be a problem.

Assume I'm ok if you don't hear from me otherwise!!

Thanks again.