Not sure if the topic accurately describes this, so apologies if it doesn't.
I have a few discrepancies on the trial balance totals compared to the nominal code totals for the year to January 2015. Some I know won't match (eg DLA) but the sales figure is hopelessly out. I've gone back to 2013 on the nominal report and it was correct, but 2014 shows me 83k in sales against 146k cleared down on the year end.
Similar story for some other nominal codes, the trial balance just doesn't look right at all.
I've just checked the 2014 year end back up aand the sales figure is 83k so I'm at a loss as to what's gone wrong, I rolled they year forward in July once everything had been completed but the date is correct 31st January 2014.
__________________
John
Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
I have an Excel spreadsheet that I've used to troubleshoot these sorts of problems before. It extracts the nominal ledger totals and the transactions, and then compares the totals from both sources. If you'd like a copy send me a message together with what version of Sage you're using so I can set it up to use your ODBC driver to get the data.
Hi John there might be an error in the report.Export your nominal transactions on Excel and then you can check the accuracy of transactions through data filter process. Rubina anjum E: info@pyramidpointlimited.co.uk
The nominal report for 2014 is showing sales 83k with 146k cleared down once the year was rolled forward. That in turn has affected the trial balance for 2015 when sales were 95k against 35k on the balance sheet.
Other items on the TB are also wrong. Hopefully when I've got Ian's excel sheet I can identify the problem.
__________________
John
Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
Sounds like someone didn't check the TB after doing the year end journal.
If you run the opening balance report after doing the year end everything in the P&L should be zero.
If you have a faulty COA or an error this can happen, you may be able to just make a correcting journal now.
__________________
For just about anything Sage :- switch to renting, pay-as-you-go sage support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me.
If you run the opening balance report after doing the year end everything in the P&L should be zero.
How do i do this please, I've googled it but the results say about entering opening balances at the start.
If I run a TB brought forward I can see the entries that shouldn't be there but I don't know how they got there. I've run a report using Ian's Onion software and everything looks fine.
As an example Sales to Jan 2014 were 83k but 146k has been cleared down after rolling the year forward, how would this happen? I've checked the previous year and it shows sales of 83k with 83k cleared down.
Sales to Jan 2015 were 95k but the TB shows this as £33k, and the difference is showing up in the bf tb. Ian's software correctly shows the sales made in both years. The sage pl report shows correctly at 95k for 2015
Not the best way to spend Easter Sunday
__________________
John
Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
Mysterious but it must be something to do with transaction dates.
Do I understand correctly? - When you say cleared down - was £83k cleared on your year end report, but when you run a sales report now it shows b/f £146k? Or in the sales nominal is the year end close figure different from your actual year end repot.
Sorry if this seems obvious and you have already checked, but could the transactions in question have been posted with a wrong (prior) year date? Perhaps posted sometime after the year end was run? Are the VAT and Debtors control also out?
In the newer versions of Sage there is a drillable TB (and P&L and BS) so you would be able to see exactly which transactions make up the balance in your B/F TB which are great for tracing errors. Which version are you using?
As an example Sales to Jan 2014 were 83k but 146k has been cleared down after rolling the year forward, how would this happen?
A difference of £63K.
I've checked the previous year and it shows sales of 83k with 83k cleared down.
Sales to Jan 2015 were 95k but the TB shows this as £33k,
A difference of £62K the other way.
and the difference is showing up in the bf tb.
Based on what I'm seeing above, that makes sense - it's showing up in the brought forward TB because it's something to do with what happened one year ago; that £63K and £62K are almost certainly the same amount (subject to some genuine late transactions). Whatever happened to get between £83K and £146K is affecting your brought forward figures, throwing up as a difference between £95K and £33K the other way.
The most likely problem last year is that additional transactions hit the system between producing the report showing £83K (and taking the backup) and the actual process of closing the year down; that would explain the amount being cleared down differing from the amount in the P&L.
The flip side to that is what's causing the problem this year (and appearing in the brought forward figures) - and I would guess that whatever was done to increase the turnover last year was reversed in this year's figures, but dated as though they were in last year (perhaps even the same date as the transactions they are reversing).
A bit like a provision for stock/WIP for instance, where you might debit stock/WIP and credit cost of sales on [YEAR-END-DATE], and reverse that on [YEAR-END-DATE+1], but in this case the +1 has been forgotten (and it's not stock/WIP, since that shouldn't affect sales!)
But it does sound to me like some cock up over adjustments posted before the year end routine, and reversed after.
Scan through your transactions and find the transaction numbers of the journals Sage posts when it carries out the year end procedure. Let's say they are transaction numbers 5000 to 5073. Now run a transaction history, asking Sage to show you all transactions over 5073, but up to last year's year end date.
In other words, transactions posted after the year end was closed down, but dated in last year.
Hopefully, that'll produce some interesting results.
[Edits: Why do my line breaks keep disappearing?]
-- Edited by VinceH on Monday 6th of April 2015 10:44:02 AM
-- Edited by VinceH on Monday 6th of April 2015 10:44:47 AM
-- Edited by VinceH on Monday 6th of April 2015 10:52:09 AM
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
But it does sound to me like some cock up over adjustments posted before the year end routine, and reversed after.
Scan through your transactions and find the transaction numbers of the journals Sage posts when it carries out the year end procedure. Let's say they are transaction numbers 5000 to 5073. Now run a transaction history, asking Sage to show you all transactions over 5073, but up to last year's year end date.
In other words, transactions posted after the year end was closed down, but dated in last year.
Hopefully, that'll produce some interesting results.
Alas no, and I am surprised because it would have been the most likely explanation. Attached is the report and I can see straight away these are just errors from the client. I've also attached the nominal code 4000 that shows the ledger year end plus an excel sheet showing all transactions from the beginning, with a running total. As you can see 2012-3 was cleared correctly but 2013-14 wasn't. Both highlighted in yellow.
You've maybe got it sorted by now but, if not, I'd be happy to try to interpret the contents of the workbook I sent you if you were happy to share it with me via DropBox. Just let me know and I'll send you a DropBox share invite.
Mysterious but it must be something to do with transaction dates. Do I understand correctly? - When you say cleared down - was £83k cleared on your year end report, but when you run a sales report now it shows b/f £146k? Or in the sales nominal is the year end close figure different from your actual year end repot. Sorry if this seems obvious and you have already checked, but could the transactions in question have been posted with a wrong (prior) year date? Perhaps posted sometime after the year end was run? Are the VAT and Debtors control also out? In the newer versions of Sage there is a drillable TB (and P&L and BS) so you would be able to see exactly which transactions make up the balance in your B/F TB which are great for tracing errors. Which version are you using?
Hope I haven't hopelessly misunderstood.
Anne
Thanks Anne, I'm not the world's best at explaining but hopefully my reply to Vince will make it clearer. The client is using Sage 50 v 12
__________________
John
Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
Looking at your spreadsheet: were there really no transactions to 4000 in Dec-2013 and January 2014?
If you change the transaction based query in the workbook I sent you to include deleted transactions does it reveal that transactions must have been deleted after the roll-forward?
No there wasn't, the Company ran into difficulties in October 2013 and they mothballed it until they got another contract.
I was going to email you today anyway because I didn't know what to do with the worksheet, it came up with some figures for 2009 and 2010 and I didn't know what to do to link it with the client's sage. I guess it's just dummy figures because I opened it on my computer as well and it showed the same figures, and I don't have sage.
I also downloaded the onion reporting software you suggested and I have to say, that is an excellent bit of kit, although everything looked ok from a transactional pov. Your tb pivot shows sales 83k for 2013-14 and 96k for 2014-15, which is correct.
__________________
John
Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.
As Ian points out, there's a notable gap in transactions in that spreadsheet approaching the year-end, with the last one being dated 29th November 2013, with the next transaction being the year-end journals. That gap* combined with the balance at that point differing from the year end journal strongly suggests deleted transactions - or perhaps a data corruption (and subsequent repair) which resulted in the same thing.
* Although looking at the dates and transactions, the (possibly) missing data may not only be for the last couple of months. For example, May 2013 appears to show only one transaction of £212.50
Edit: I see you've replied to Ian on the subject of deletions as I typed my own comment. Hmm. :/ In that case, I'd ask them if they had any problems with the data.
-- Edited by VinceH on Monday 6th of April 2015 03:57:03 PM
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
You are correct. The figures in the workbook I sent are from the Sage Demo Company.
I've emailed you about how to connect the queries to your data and refresh them. The most important part of the process is identifying the directory your company data sits in. Something like C:\ProgramData\Sage\Accounts\2006\Company.000\ACCDATA or C:\Program Files\Sage 2006\Accounts\Company.000\ACCDATA I'd expect.
Regards,
-- Edited by Onion4Sage on Tuesday 7th of April 2015 09:46:11 AM
