Value of Inventory Items Backdating

[annbrinck]

Morning

We are having an issue with our Inventory Value Reconcilation (IVR).

When finalising our September reports, we noticed that the IVR report was out from previous months reported and changes had been back dated to the beginning of Jan this year.

We have done some checking through back ups and the last time the report was balanced and correct was 6th Sept. Some COS and Finished Goods codes are different from what was reported on 6/9 and what was appearing in the current file.  

We drilled down on the Aug P&L for one of the COS codes and noticed that all invoices for a particular product had a different inventory value on the current report to what is showing on the file from 6/9. Please see attached file.  We can find no reason for this to have happened.  The build for the product is exactly the same in both AR files.

Prices of raw materials and our sale items do change, but these are always updated before processing starts for a new financial month, and we have never had this problem before.  We are worried this is a software issue as it has backdated through previous months. 

Any assistance, insight or ideas are appreciated.  I have had to start processing for October, but will checking the IVR constantly for any changes.

Thanks Ann

[Mike_James]

Hi @annbrinck , a printout of the "item register detail report" for this item for the period from both files might shed more light on this.

From your other posts I note that you use purchase orders and receive items, which if not accurately costed from the start can result in back-dated purchase transaction adjustments, and you may already be aware that editing/changing even non-financial info on prior item sales transactions will lead to changes, because the COGS amount gets updated to the current average cost. 

PS did you intend to publish the whole excel file to this (public) forum?

[annbrinck]

Hi Mike

Thanks for your response.

Attached is an extract of the Item Register Detail as you suggested.

As mentioned in my post, our buy and sell prices do change, sometimes monthly, especially at the moment.  Where possible these are updated at the beginning of every month. However we have had prices change mid-month before and while there have been changes to the inventory reconciliation report in the past ($00's max) they have never impacted on previous months reports and have never caused our Inv Rec to be out by $000's.

We did have a major price increase to a large part of our sales items effective 1st Sept. But surely that couldn't have caused the problem?

All businesses must put their prices up at some stage. Why should that affect previous reports?

For the record, we also had an increase on similar items in Feb this year and there were no problems.

Please advise how it might be possible to change the buy/sell prices of materials without this problem occuring.

Thanks

Ann

(P.S. No, I did not mean to upload the whole file, thank you for letting me know).

[Mike_James]

Hi @annbrinck , thanks for the item register details. Changing sell prices will definitely NOT change prior cost of sales. Your autobuild cost changed from 0.9340 to 0.7860 across August. The order of processing transactions can affect the end result, but doesn't explain why prior transactions changed. 

The simplest reason for the change in cost of sales is that the invoices for this item were edited, for example if someone wanted to edit then print each invoice, they would open it, edit and print, then close by selecting OK, which would update the cost of sales. A way to prevent that is to lock the prior period once all processing and reporting is done - do you normally do that?

[annbrinck]

Hi Mike

Thanks for your response.

We do lock a period once processing finished processing.

However - during September I did unlock and go through all invoices for 2021 and 2020 to add a referral source (for additional reporting reasons).  No changes were made to any figures though.  Why would this have caused the changes to COS? 

I have a couple of additional questions my manager would like confirmation on: 

When a raw material price goes up, does it change the historical cost of the material and any builds it used in?

The average cost of an item is generated by the invoices entered?

Other than editing an invoice, would there be any other things that could change historical balances?

Thanks for your help

[Mike_James · Accepted solution]

Hi @annbrinck , changing the referral source is probably enough to trigger a re-recording of the invoice when you click OK; this will then use the present-moment average cost of stock, which may differ from the cost when the invoice was first recorded. (I haven't tested this type of change myself, but I do know that changing a (non-financial) line description does the same). 

When raw material prices change, the historical amounts used in autobuilds will not change; I note that the autobuilds in the item registers did not change between file versions.

Prior purchase transaction journals can be changed, in cases where:

- purchase orders are used, and

- receive items are used, and

- the price on the final purchase bill needs to be changed, and

- sales of that purchased stock have taken place before the bill is recorded.

AccountRight regards the cost of sale on such invoices as provisional until then, and will automatically make cost of sales adjustments. These adjustments used to appear as separate inventory journals in v19, but in the current program they are accumulated with the original purchase journal, which is tidy but not auditable.

The average cost of an item is recalculated after each recorded transaction (purchase, inventory journal); every sales invoice uses the average cost of stock onhand when that invoice is recorded.  

[VickiC2012]

Hi @Mike_James 

Sorry i know this is an old thread but i'm having similar issues with auto-build throwing out the inventory reconciliation.

Am i understanding this correctly  - the unit cost values in the auto build do not update automatically when the average cost of an item is changed ?  If this is the case, is there a way to refresh the unit cost ?

Many thanks

Vicki

[Mike_James]

Hi @VickiC2012 , welcome to the forum. Sorry I missed the notification of your post.

I would expect that when an autobuilt item is built, AccountRight will use the current unit cost of the component item. 

The autobuld report should display the latest build cost for the autobuilt item. 

I'm not aware of autobuilds causing the reconciliation to go out of balance; can you provide a worked example, with a screenshot of the relevant inventory adjustment?