Inventory value reconciliation

Contributing User
7 Posts
Contributing User
Australia
Contributing User

7Posts

0Kudos

0Solutions

Inventory value reconciliation

Hi, I have spent a great deal of time & numerous phonecalls to MYOB to try to understand why my inventory valuation report is out of balance & progressively getting worse. I do not post adjustments directly to the GL stock account (they are done via "adjust inventory" or "count inventory") & have checked all the link accounts which seem intact.

Staff at MYOB have run scripts on several occasions but this has not helped.

By way of background, raw material is purchased & then used to build the finished item. In the example below, there is a discrepancy between the Item Register & Item Register Detail report (the Detail Report being the correct one).

For some reason, on Oct 5 the opening balance of the current value (far right column) of the Item Register "changed" from 1080.00 to 1878.75, causing a reconciliation error of 798.75. This has also had the effect of valuing the closing stock at 4 times greater than it should be, which will distort the profit on future sales.

Qu1: what is the best way to fix this?

Qu2: why does it happen in the first place?

Item register detail.JPG
Item register.JPG
8 REPLIES 8
Ultimate Partner
4,697 Posts
Ultimate Partner
New Zealand
Ultimate Partner

4,697Posts

2,113Kudos

603Solutions

Re: Inventory value reconciliation

Hi @angela11 , the item register screen display will calculate the current value (final column) working backwards from the item's current on-hand value. I suspect that there may be transactions for this item that are dated after 5/10/20. If you enter a (much) later end date for the register display, does the opening balance correct itself? 

 

In any case, I would go by the register report. And the Inventory Value Reconciliation report can be run for any date, and will display all the items grouped by inventory (asset) account, with the comparable GL total, much like the Receivables & Payables Reconciliation reports. If there are no out of balance amounts, then your inventory reconciles to the GL. In that unlikely event, your next step would be to test-check some individual quantities and costs, because as you mentioned, incorrect costings would affect prior and/or future gross margins. 

 

If any of the asset account groups shows an out of balance, then try running the report at a future date - do the out of balance amounts zero out? If so, there may have been a timing difference. If not, are they the same? If so, there could be a one-off difference, which you may be able to find by running the report at various key month-end dates, such as the last end of year, then looking through the Find Transactions by Account window for that amount. 

 

If the out of balance amounts vary with every change in date, then pick an asset account with a large difference, and run the report as of one month ago, then at every day since. When you see a change in the out of balance amount, note the amount of the change, and use the account listing for that Asset account, for that day, and look for that amount. You may be able to explain why that particular variance has occurred.

 

I hope these suggestions will help you pin down the cause/s for your variances. Reply here with details if your search throws up any odd transactions. 


Regards, Mike (mike@datawise.co.nz)
DataWise Limited (www.datawise.co.nz), developers of:
DataWise Report Writer - Custom Reporting from MYOB programs
(Including AccountRight Classic/Live, and exo Payroll)

Ultimate Partner
4,697 Posts
Ultimate Partner
New Zealand
Ultimate Partner

4,697Posts

2,113Kudos

603Solutions

Re: Inventory value reconciliation

PS @angela11 ,  did you check the item GL account settings (income, cost of sales and inventory asset accounts), by exporting the items to a text file and review them in Excel? The list can be filtered there, to highlight any incorrect settings.


Regards, Mike (mike@datawise.co.nz)
DataWise Limited (www.datawise.co.nz), developers of:
DataWise Report Writer - Custom Reporting from MYOB programs
(Including AccountRight Classic/Live, and exo Payroll)

Contributing User
7 Posts
Contributing User
Australia
Contributing User

7Posts

0Kudos

0Solutions

Re: Inventory value reconciliation

Hi Mike,

Thank you for your prompt reply. Your suggestions are very useful & I've actually tried them all before. I've tried future dating the report (exact same discrepancy) & also have gone back & printed the report daily to pinpoint which item caused the reconciliation error & when as per the example above. 

Still don't know why it happens.

I've also exported the items as you suggested & they are all linked to the correct asset/income/COGS accounts.

All I can do for now is remove the incorrect stock & bring it back in at the correct value & then adjust the GL asset account to agree with the stock report, as my end goal is to have an accurate list of stock on hand that agrees to the GL.

Could timing be an issue? For example I post production weekly every Thursday after a physical stocktake of finished goods. That's the only time stock is "built" apart from some items which are built at time of invoice if there is no stock on hand on MYOB. Occasionally though, for some reason MYOB skips the "build" function & allows the stock to go negative.

 

 

Ultimate Partner
4,697 Posts
Ultimate Partner
New Zealand
Ultimate Partner

4,697Posts

2,113Kudos

603Solutions

Re: Inventory value reconciliation

Hi @angela11 , thanks for these details. 

 

I don't think timing is an issue. All the normal transaction types affecting inventory should balance as between the GL and the inventory, provided set-up is correct, as you have confirmed, and inventory asset accounts are not used in transactions where they should not be, for example in the 'Account' column of the inventory adjustments screen (because AccountRight uses those behind the scenes as the offset account for each line), or directly, for example in service purchases (because there is no corresponding inventory movement). 

 

If you are seeing occasional changes in behaviour, one suggestion is to clear the AccountRight cache (on all user's machines). Here's a page with all the folders listed:

https://community.myob.com/t5/Tips-and-Tricks/AccountRight-Log-Locations/td-p/582218

 

Can you tell me what the inventory value reconciliation reports shows for item CUBA on 5th October 2020, compared to the item register detail report up to that date? Do they agree or not? If they do, then no adjustment should be made, even if the item register (screen display) shows a different figure. 

 

Is your file in the cloud or held locally?

 

Do you use purchase orders and the Receive Item functions?

 

 


Regards, Mike (mike@datawise.co.nz)
DataWise Limited (www.datawise.co.nz), developers of:
DataWise Report Writer - Custom Reporting from MYOB programs
(Including AccountRight Classic/Live, and exo Payroll)

Contributing User
7 Posts
Contributing User
Australia
Contributing User

7Posts

0Kudos

0Solutions

Re: Inventory value reconciliation

Hi Mike,

The item CUBA has a value of $1057.50 on the Inventory Value Rec report compared to $258.75 on the Item Register detail report (258.75 is the correct value).

 

The file is in the cloud.

 

We use purchase orders but avoid using the receive function for part deliveries as it gets too complicated. We only receive items in when 100% delivered & invoiced.

 

 

 

 

Ultimate Partner
4,697 Posts
Ultimate Partner
New Zealand
Ultimate Partner

4,697Posts

2,113Kudos

603Solutions

Re: Inventory value reconciliation

Hi @angela11 , if the inventory value reconciliation report is incorrect, then I suspect that MYOB need to look at it, possibly a script needs to be run over the file again. 

 

The next time that MYOB appears to 'skip' the build, and stock goes negative, can you grab a copy of the diagnostic log and post it here? My earlier link has all the log files' locations. 


Regards, Mike (mike@datawise.co.nz)
DataWise Limited (www.datawise.co.nz), developers of:
DataWise Report Writer - Custom Reporting from MYOB programs
(Including AccountRight Classic/Live, and exo Payroll)

MYOB Moderator
3,056 Posts
MYOB Moderator
MYOB Moderator

3,056Posts

314Kudos

471Solutions

Re: Inventory value reconciliation

Hi @angela11 


Generally, the inventory value reconciliation report is incorrect or out of balance due to one of the reasons mentioned by @Mike_James. However, if you have checked all of them and none of those apply, please send us your serial number by a Private Message so we can get a copy of your file in and investigate what the issue is. 

Kind regards,
Komal

MYOB Community Support

Online Help| Forum Search| my.MYOB| Download Page

Did my answer help?

Mark it as a SolutionHelpful? Leave a to tell others

Contributing User
7 Posts
Contributing User
Australia
Contributing User

7Posts

0Kudos

0Solutions

Re: Inventory value reconciliation

Thank you Mike. Your assistance is much appreciated. I will send my details to MYOB so the matter can be examined further.

Didn't find your answer here?

Try using advanced search to find a post more easily Advanced Search
or
Get the conversation started and make a new post Start a Post