An Accounting Seed recommended best practice for month-end close is to reconcile all sub-ledgers to the general ledger. (see Best Practices for Month End for more detail). When utilizing inventory accounts, this includes a periodic reconciliation between the inventory sub-ledger and the Trial Balance. An inventory sub-ledger is essentially the detail of your inventory that isn’t captured in your general ledger. In this case, the inventory quantities are captured in two different objects:
- Inventory Quantity Available - Captures the inventory quantities available and inventory movements as of the current point in time in the system. (see Checking Inventory Quantity Available for more detail).
- Inventory Quantity Available Histories - Captures the inventory quantities available and inventory movements as of a specific past point in time in the system. Typically, you will generate these history records at month-end but it can be for any date.
A key requirement for performing inventory reconciliations at month-end is to create a snapshot of the inventory quantities at that date. The method used to create this historical Inventory Quantity Available detail is the Create Inventory History process available on the Inventory Quantity Available list view.
Create an Inventory Quantity Available History Record(s)
Perform these steps to create an Inventory Quantity Available History record(s):
- Navigate to Accounting Home and click the Create Entries tab. Then, under the Orders menu, click Inventory Quantity Available.
- Select the “All” list view to ensure that all Inventory Quantity Available records are available to the Create Inventory Histories process. If only a subset of the Inventory Quantity Available records are required, a custom list view can be created.
- For this example, a custom list view was selected.
- Select the specific Inventory Quantity Available records, by checking the checkbox beside them, that Inventory Histories need to be created for.
- If Inventory Histories are required for all Inventory Quantity Available records shown in the selected list view, select the checkbox in the column heading section will automatically select all records.
- Due to limits on the number of records you can select in a list view, it is important to note that you can only select a maximum of 200 records at one time. If you have more than 200 records, you will need to process in batches of 200 at a time.
- If you have a substantially large number of Inventory Quantity Available records whereby doing them in batches of 200 would be inefficient, you can create an automated job to run through this process in batch. A Salesforce developer or an Accounting Seed certified service partner can assist with this. Please contact Support for more information.
- Once the Inventory Quantity Available records required have been selected click Create Inventory History to start the process.
- Select the date you would like to create Inventory History records for. This is the period end date and should align with the end date of the period that is being reconciled. Once set, click Create History to create the Inventory History records.
Note: Click here to view the above image in full screen.
The Inventory History job, based on the number of Inventory Quantity Available records selected, could take a bit of time to complete. As such, the notification displayed is only that the job was successfully submitted.
Note: Click here to view the above image in full screen.
- The job’s status can be ascertained by either:
- If the number of records is small, directly checking the Inventory Quantity Available records.
- Wait for the email notification.
- Goto the Apex Jobs Status list and verify the status. This list can be viewed by going into Setup, entering “job” in the Quick Find search box and then select Apex Jobs in the side menu.
- When the job has successfully completed, the Inventory History record created for a specific Inventory Quantity Available record can be found on that record’s related list.
- The Inventory Quantity Available History record detail can be seen by either clicking the Inventory Quantity Available History name or by clicking on View All.
- The Inventory History record is cumulative up to the date set on the request. Below is an example showing the 8/31 record, an interim record for 9/10 which includes all transactions through 9/10, and the 9/30 record which now includes all transactions through 9/30.
- Once the Inventory History records have been created, they can now be used in reports and to perform reconciliations. See the Report on and Reconcile Inventory article for more detail.
Feature Request: Automatic Creation of Inventory Balance History Records on Period close: https://accountingseed.zendesk.com/hc/en-us/community/posts/115001183887-Automatic-Inventory-History-Records-on-Period-Close
We have noticed that we are unable to make positive inventory adjustments for a product that has a Zero quantity on it. Sometimes when doing a fiscal count, an inventory item is found that maybe was written off or miscounted in the past, now taking the quantity value from 0 to 1. I do understand that your shouldn't be able to make negative inventory adjustments to zero balance products, but positive adjustments are needed.
Try taking a look at this article about inventory adjustments and let me know if you have any other questions. Please direct these questions to our support desk.
Issue: The formula field on inventory balance history records that calculates available quantity as of the date of the IBHistory records is set to 2 decimal places - but inventory is stored at 6 decimal places, and indeed, all the fields on the IBHistory record are also stored at 6 decimal places. It seems like the standard field on this object should also be set to 6 decimal places, and if some customer needs it set to 2 instead they could add a custom formula field to limit it.
Hi Rebecca, thank you for the insight on this. I will submit a ticket to our product improvement team.
I am trying to create history balances. We have over 11,000 parts. The list view of inventory balance items only shows 200 items at a time. I'll never be able to accomplish this task/keep track of which 200 of 11,000 I've already selected.... How can I create the inventory more efficiently and accurately?
Hi Jeanae - the best method I have come up with is to create a list view of all the inventory balances I need inventory balance history records for, set it to show 200 records at a time, select all 200 on a page, create the balance history, and go to the next page create the balance history, etc until I'm done.
I agree it is not efficient. Especially if you get distracted midway through and can't remember if you've already made the request for the page you're on. The clover release at least makes sure that duplicate history records for a particular date are not created, so you should be safe to resubmit the page ... regardless. It's a fairly manual process and not much fun - particularly if you have a LOT of products.
Up at the top of this thread, I posted a link to a feature request asking Accounting Seed to automatically create the history balances as part of the period close process like they do for AR and AP aging histories.
Please upvote it if you also think it would be useful. https://accountingseed.zendesk.com/hc/en-us/community/posts/115001183887-Automatic-Inventory-History-Records-on-Period-Close
Please sign in to leave a comment.