nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Discrepancy between the balance sheet and Inventory Valuation Summary report

ReginaB

Hello again, I need some assistance with the discrepancy between the balance sheet and "Inventory Valuation Summary" report.

I have found the which transaction is making the discrepancy by looking through the transaction detail report from balance sheet. It seems that the amount or ie. the opening balance have been entered in the balance sheet however, this is not recognised or not showing within the "Inventory Valuation Summary" report.

What may be the reason this has happened? And is there a way to fix this?

Accepted answers

Acctd4

Hi @ReginaB

Stock values are continually & automatically updated as you buy & sell your Inventory Items, based on the quantities & average cost.

Example:

You originally bought 100 T-shirts at $ 5 each. When you have 10 shirts left in stock, you order 100 more shirts, but the purchase price has now gone up to $ 6 each.

Here's how Reckon Accounts/Hosted calculates the average cost:

The cost of your old stock is $ 50 (10 shirts x $ 5 each)
The cost of the new inventory is $ 600 (100 shirts x $ 6)
The combined cost of the old & new inventory together, is $ 650 ($ 50 + $ 600)
Therefore, the average cost of your entire Inventory is $ 5.91 ($ 650/110 shirts)

Therefore, the system is unable to identify a stand-alone Journal as it relies on the movement of the Items themselves, in order to perform these calculations. Instead, use the Adjust Quantity/Value on Hand feature (under the Suppliers dropdown menu > Inventory Activities) to adjust Item quantities &/or values.

Hope that helps 😊

All comments

Acctd4

Hi @ReginaB

This report is specifically generated from (Inventory) Items movements only.

" … It seems that the amount or ie. the opening balance have been entered in the balance sheet …"

Has a manual amount been entered to the Inventory Asset account (eg a General Journal, for example) ? 🤔

ReginaB

Yes, a general journal has been entered to the inventory asset account. What are you thinking might be happening?

Acctd4

Hi @ReginaB

Stock values are continually & automatically updated as you buy & sell your Inventory Items, based on the quantities & average cost.

Example:

You originally bought 100 T-shirts at $ 5 each. When you have 10 shirts left in stock, you order 100 more shirts, but the purchase price has now gone up to $ 6 each.

Here's how Reckon Accounts/Hosted calculates the average cost:

The cost of your old stock is $ 50 (10 shirts x $ 5 each)
The cost of the new inventory is $ 600 (100 shirts x $ 6)
The combined cost of the old & new inventory together, is $ 650 ($ 50 + $ 600)
Therefore, the average cost of your entire Inventory is $ 5.91 ($ 650/110 shirts)

Hope that helps 😊

MCD2

I have a similar problem. My client's inventory valuation, trial balance and balance sheet were all out of balance. I followed the instructions on here and rebuilt the file 3 times. This brought the balance sheet and trial balance into balance. I chose to accept the valuation report figure and journalled a correction to the balance sheet to agree with the inventory valuation. The balance sheet and trial balance remain in balance but the inventory valuation is creeping out of balance from the day after the journal. I do not understand from the previous comments how the balance sheet and trial balance cannot agree with the inventory valuation when I am journalling to correct the control account and accepting the valuation value. Please ignore the Prepaid Inventory account, that is not linked. Comments please.

Inventory valuation 30-01-26 copy.docx

Acctd4

Hi @MCD2

They will continue to be out of balance if you keep posting GJs 😩 As per my previous explanation, the inventory valuation running balance is an automated calculation & entirely dependent on Item transactions.

The system can't calculate correctly when you post a manual GJ as its calculation process is:

Total cost of an Inventory Item (eg it adds up how much you paid for each one you in stock) & divides that amount by the total quantity of that item held at the time.

MCD2

Sorry, I do not understand that answer. I understand how inventory is valued and, as mentioned, I have accepted the value of inventory in the inventory valuation report. The general ledger / control account has to be corrected to agree with what is essentially thousands of inventory sub accounts, i.e. each item, that makes up the inventory valuation report, if we assume that the valuation report is correct. The balance sheet / control account should move with any change to the inventory valuation report and stay in balance with it. Surely when it does not, there is something wrong other than a GL posting? An accounting entry in the GL does not link back to the inventory valuation report, it simply aligns it. How are you suggesting getting the Balance Sheet to agree with the inventory valuation report without entering a journal?

Acctd4

The valuation report reflects an averaging of calculated cost for items on hand, while the GL records the financial value of every individual transaction. Whilst in theory these should balance, because of human error - eg dates/timing, manual entries, incorrect Item mapping/account allocations, report filters, inventory received but not yet shipped, cash v accrual basis comparison, back/post-dated entries, separate WIP account tracking & negative inventory quantities - these will all have an impact.

" … An accounting entry in the GL does not link back to the inventory valuation report, it simply aligns it …"

It depends on the particular "accounting entry" - The Inventory Valuation report is solely driven by the use/movement of Inventory Items (eg the use of Inventory Items on Bills, Cheques, POs, SOs, Tax Invs, Sales Receipts, Inventory Item Adjs etc) … but posting just to the accounts - without using an Item - doesn't link to the valuation report in any way, hence the discrepancies.

GJs are the biggest & most common cause. It's a double problem if you're needing to continually adjust subledger accounts, as that suggests that not only are there inventory items that are mapped incorrectly (eg not to their relevant sub-ledger account) but every time you've entered a GJ, you've changed the balance sheet amount, but not the inventory valuation report at the same time, so you will forever be in that adjusting cycle 😩

Many people don't understand the difference between Items & Accounts & will simply duplicate the same list for both. This increases file size, impacts stability & commonly leads to ongoing, systematic errors due to unnecessary over-complexity.

Whilst having some sub-ledger breakdown can be beneficial (depending on the business/circumstance), the majority of Items don't need to also have their own separate, subledger account. The more that do, the increased likelihood of some being incorrectly mapped.

If you've ever been a manual bookkeeper, I explain it this way .. Each account in the Chart of Accounts represents a column in the ledger book - We didn't create a new column for each Item or expense description back then, because the columns represented collective grouping/categorising.

MCD2

I DO understand how inventory and software works.

The long and short is that I have to get the balance sheet to agree with the inventory valuation report otherwise it's just nonsense.

There must be a way to correct this unless the file is corrupt and how would I know? I can backup, verify, and rebuild the file time and time again and nothing changes.

I probably need to get the client to submit their file to support for checking.

Datarec

The Verify and Rebuild operation will not correct issues or any differences in reports that are the result of general journals being manually entered.

MCD2

The rebuild had nothing to do with the journals, the balance sheet and trial balance were out of balance as well. The rebuild fixed that side of the problem. What is the procedure for the client to raise a ticket to have the data file checked? We are aware that there is a cost.

Datarec

Hi @MCD2

You've indicated that your data file has been put through the built-in Verify Data and Rebuild Data operation.

Is the issue that you want looked at, the difference between the balance sheet and an inventory valuation report?

As indicated previously by @Acctd4

"The Inventory Valuation report is solely driven by the use/movement of Inventory Items

(eg the use of Inventory Items on Bills, Cheques, POs, SOs, Tax Invs, Sales Receipts, Inventory Item Adjs etc) … but posting just to the accounts - without using an Item - doesn't link to the valuation report in any way, hence the discrepancies."

"They will continue to be out of balance if you keep posting GJs 😩 As per my previous explanation, the inventory valuation running balance is an automated calculation & entirely dependent on Item transactions.

The system can't calculate correctly when you post a manual GJ as its calculation process is:

Total cost of an Inventory Item (eg it adds up how much you paid for each one you in stock) & divides that amount by the total quantity of that item held at the time."

If the Balance Sheet report is generated, filtered to exclude transactions of type "General Journal" and that resulting report matches the Inventory Valuation report, then that would explain the discrepancy.

You can raise a support ticket for a data service here and we can take a quick look at the file.

Data Services Request Form | Support | Reckon AU

When you submit the request, please ensure that you provide:

the specific period you are generating the report for
the specific transaction which you have already identified as being the cause of the discrepancy

MCD2

Thank you. I will filter out general journal entries first and see what happens. I think I did that last time but I will try again. I have only recently taken over support of this file so anything could have happened in the past.

Reckon Accounts Hosted Announcements & Important Info

Don't miss announcements and important info for Reckon Accounts Hosted

Button

Reckon Accounts Hosted Ideas & Feature Requests 💡

Do you have an idea for a feature in Reckon Accounts Hosted? Let us know here!

Button