Detail Balancing of INV to Ledger Trial Balance

Overview of Month End Processing for Ledger:

Data entry completed for SJI Period.
Set your earliest date allowed to the next month.
Everyone out of the ACR-ACP-INV packages. Inventory is unique in that the Inventory Ledger Accounts will be updated by 3-different packages.
Select your SJI period. SJI > Select SJI Period > Select period to be default. All data keyed or edited between these date ranges will be pulled into the SJI and summarized into ledger entries by location code, transaction Code, then source codes.

Each package pulls it's transactions into ledger and posts to the INV ledger accounts. Each package interfaces to the INV code assignment in INV by keying in an account code of BANK.

Run the Create SJI process to create the SJI entries.
1. SJI > Create System Entries > Select Accounts Receivable. Sent reports to the spool.
2. SJI > Create System Entries > Select Accounts Payable. Send reports to the spool.
3. SJI > Create System Entries > Select Inventory Accounting. Send reports to the spool. DO NOT DELETE until Fiscal Year is closed.

Select the Ledger Period. LDG > Select Ledger Period > Select Default Period.
Post the System Journal entries. LDG > Journal Entries > System Journal Entries. Answer YES to Account Payable (note: AGRIS only allows you to transfer the entries once. So even if you answer yes to a package that has already posted, don't worry. It can only be pulled once. You will not double the ledger balances by just leaving all questions at yes.)
Run the Ledger trial Balance report. LDG > Reports Manager > Journal Entry Reports > Standard reports > Trial Balance Report.
1. This is the report that you use to balance to the subsidiary Inventory Accounts.
Run the INV Activity Summary Report for SJI Period Ending Date
1. No data entry should be allowed when this report is run. Run after a cost adjustment function has been run.
2. INV > Reports Manager > INV Status Reports > Standard > Inv Activity Summary Report > Answer NO to print Detail on the Subtotal Screen > Additional Options Screen > Answer YES to "Print Activity Summary Information?" > Key in the beginning and ending date that matches the ledger period ending date > Answer YES to "Exclude Active Delivery Tickets?”
3. This report does the following: Take current inventory balance+ active delivery tickets from the time of the cost adjustment- transactions that have occurred since the ending date range = Balance of Inventory on Last Date of Ledger Period
The ending balance of the Ledger INV accounts should match the ending balance of the Inventory Activity Summary Report.

What is happening during this process?

The SJI process gathers all the transactions that were entered for the SJI Date Range Period:

Stock Addition Vouchers via Accounts Payable
Invoices via Accounts Receivable
Inventory transactions via Inventory

It looks at each transaction code (assigned in SJI System Accounts) and assigns the dollars and quantities of these documents to a ledger accounts. So if you know what transaction code should be hitting your ledger account, then you know what document to be looking for when balancing.
Stock Addition Vouchers: Debit Inventory Asset for the amount of the Line Items (IV-transaction code)Debit Inventory Asset for the amount of the service items allocated to them (IV-transaction code)Debit Expense Account for the amount of Service Items that Stand Alone (SE-Transaction code)Credit Accounts Payable for the amount of the voucher (AP-transaction code)
Item Invoices: Debit Accounts Receivable for the amount of the invoice (AR-transaction code)Credit Inventory Sales for the amount of the Lines Items (SL-Transaction code)Credit the Sale Tax Payable for the amount of tax (S1-S4 Transaction code)Debit the Inventory Cost of Sales for the cost of the product (CS-Transaction code)Credit the Inventory Asset for the cost of the product (IV-Transaction Code)
Negative Inventory Adjustment – taking away inventory quantity or value Debit Cost of Goods Sold (AJ-Transaction code) Credit Inventory Asset (IV - Transaction code)

Positive Inventory Adjustment – adding inventory quantity or value Debit Inventory Asset (IV - Transaction code)Credit Cost of Goods Sold (AJ-Transaction code)

Negative Cost Adjustment – taking away inventory value Debit Cost of Goods Sold (CA-Transaction code)Credit Inventory Asset (IV - Transaction code)

Positive Cost Adjustment – adding inventory value Debit Inventory Asset (IV - Transaction code)Credit Cost of Goods Sold (CA-Transaction code)
Physical Count - Loss Debit Cost of Goods Sold (PC-Transaction code)Credit Inventory Asset of product (IV - Transaction code)
Physical Count - Gain Debit Inventory Asset of product (IV - Transaction code) Credit Cost of Goods Sold (PC-Transaction code)
Manufacturing – Making a new product from other products Debit Inventory Asset of Product being Made (IV - Transaction code)Credit Inventory Asset of product being used (IV - Transaction code)
Transferring Inventory – Making a new product from other products Debit Inventory Transfer Clearing Account at Loc of Original product (TC – Transfer Clearing)Credit Asset of Original Item product category (IV - Transaction code)Debit Asset of Receiving Item Product category at their location (IV- Transaction Code)Credit Inventory Transfer Clearing Account at their location (TC – Transfer Clearing)

When things go wrong

Begin to compare the INV Status Summary Report to the ledger trial balance and they do not match. They do not, so now narrow it down by Product category to Detail Code.

Look for un-posted SJI entries. SJI > Report Manager > SJI Entry Reports > Standard > Un-posted SJI Entries
Look for manual Ledger Posting that has a description of something other than INVENTORY. LDG > Inquiry options > Account Inquiry > view the detail of the account.
Verify that your subsidiary reports were run after aging and with everyone out.
Focus on what we have learned from the document types.

Document Type Focus

When the subsidiary does not balance to ledger, it has to be a document that is affecting it.

Voucher was posted or edited after SJI was run (example, add freight to a stock addition AFTER SJI's to get the physical count correct)
Invoice was reversed or keyed after SJI was run (like keying in one more invoice AFTER SJI's were run)
An inventory transaction could be keyed or reversed AFTER the SJI's were run. (Example, entering a stock adjustment or running the cost adjustment for the SJI period AFTER SJI's were run.)

Balancing Inventory Asset Account – 1340-0HDifference of $82,000

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Example: balancing Inventory Asset, the transaction code that should be hitting it is IV-Inventory. And if only IV transaction codes should be hitting the Inventory Asset, then it can only be an AP Stock Addition Voucher, or an invoice, or one of the five inventory transactions (adjustment, cost adjustment, physical count, transfer, or a manufacturing. You have 7-document types that should affect this account.
Step Two:
Build a current SJI Exec Work file that includes the current month you are trying to balance. It can include more time than the current month.
Step Three:
Run the standard Ledger Account detail report for the AP Ledger Account. Compare the ending Transaction Total with the Trial Balance Change Amount to see if they are the same. Or the change amount from a Ledger Account Inquiry. If they are NOT, then data has been changed AFTER the SJI was created. This could be new data added or reversed, but something has been done to documents with IV transaction codes in the month that you have already gathered the data for the ledger.

Here, the amounts are $82,000.00 different. This is stating that if we re-ran the SJI's again for this time period, it would post a larger debit to the ledger. There was a document that was entered/edited/reversed using a back date, or date into a month that the SJI had already been run. What were these documents?
Step Four:
If INV is out of balance then OTHER accounts are out of balance as well. If we can find the other accounts then it will give us a heads up of the type of voucher or disbursement record that we are looking for. Now in our example above, it's pretty obvious why it's off. But if you had your normal range of data, then just eyeballing the document would not work.Run the SJI Execution ID report called MIMIC SYSTEM JOURNAL REPORT using the parameter settings to limit this report to just entries from the IV package. SJI > Report manager > Execution ID reports > Standard Reports > MIMIC SYSTEM JOURNAL REPORT >
Key in date range to match ledger period > go to Additional Options > Answer NO to everything but INV.This will give you only the entries that posted with the INV SJI Creation Process
Compare this report to the standard SJI Entry report MIMIC EXECUTION ID REPORT. SJI > Report manager > Journal Entry Reports > Standard Reports > MIMIC EXECUTION ID REPORT >Select IV to IV for Source parameter and the current period
This gives you all the entries that did post.

Now you can see that it is not just IV Asset for 1340-0H that is out of balance, but also other accounts. Just looking at the INV comparison, we are off by $82,000.00 and if you look down the list, the amounts for the 5340-0H Feed, Beef cost of sales is also off by $82,00. If we were to rerun the SJI's today it would post $82,000 more than it did the first time the SJI's were run. So think about the documents that hit the Inventory Asset and the offsetting SJI Code. That would be an adjustment document. It would debit INV and Credit cost of goods sold. There was an inventory adjustment keyed AFTER the SJI for the month was run.

Get the exact date the SJI's were created from the SJI reports. You can read this from the upper-left hand corner of the reports or from the saved documents in the report spool. 4/13/14 at 7:23 pm or 19:23 hours
Run an inventory audit report looking for any transaction that had a ledger date up to the ending SJI period but was actually keyed in after the SJI creation date. INV > Report Manager > Inventory Audit Reports > Standard > Inventory Audit Report – By Date Entered
This report will show everything that was keyed and when. So we are looking for anything with a ledger date of 9/30 or earlier that has a physical entry date the SJI Creation date at 7:23 PM.

This finds that I had two transactions keyed AFTER SJI creation. Reverse Inv count for 310 units at zero dollars 999-QZ0085
Stock Adjustment for 410.00 units at $200 average cost for $82,000.
Step Five:
Make a manual ledger entry to post the backdated entries into the ledger. Debit Inventory Asset for $82,000.00Credit Cost of Goods Sold for $82,000.00

Balancing Inventory Asset Account – 1340-6EDifference of $78,500

Step One:
Think about how you are balancing a transaction code on a document to the ledger. Seed Corn Inventory (account 1340-6E) is out of balance by $78,500. If the asset is out of balance, then another account is out of balance too.
Step Two:
Build a current SJI Exec Work file, if one is not already built.
Step Three:
Run the standard Ledger Account detail report for the Inventory asset account.

Compare the ending Transaction Total with the Trial Balance Change Amount to see if they are the same. Or the change amount from a Ledger Account Inquiry.

So in this case, there was no change amount in the ledger for 1340-6E and there are no documents for 1340-6E. So nothing was backdated. So there is nothing that the Exec Work Files can give us. They are zero and our change status is zero, so nothing was backdated.

Step Four:
Now we go to see what happened to the items in this product category by running an inventory activity report for all items in 6E for the month.
Now we can see that there was a stock addition for $78,500 keyed on 9/25.So why is this not in the ledger? To further research, now go back and run the inventory audit report for this product category.INV > Report Manager > inventory Audit reports > Inventory Audit – on hand > Limit to this product category of 6E for the ledger month.
Here we find that one transaction was definitely keyed PRIOR to the SJI run of 4/13 at 19:23 pm.The second transaction, a stock addition adjustment, was keyed AFTER the SJI's were created.This means that at least some of this should have been in the SJI's.
Step Five:
So now we must look at the Accounts Payable SJI Creation reports, looking for document #999-VA0392. This is one of the reasons that we request you keep these documents.
In this case, we can see that voucher 999-VA0392 was in the SJI Creation Process.Our problem is that AGRIS did not know where to post the IV code for 6E, thus the series of asterisks on the report.1. Setup the SJI code for future Posting.
2. Move the dollars from the system clearing into the 1340-6E account.
Debit Inventory Seed for $80,000Credit System Clearing for $80,000

Snowball Effect of Balancing

Step One:
As you make the entries in your research we will see that the difference in the Inventory Asset is changing with each entry. Now we are only off by $1500 in Account 1340-6E. We are using each entry to find the overall issue with Accounts Payable.

Step Two:
Now we know that we are only $1500 off and it was NOT a backdating issue per the SJI Ledger Account detail Report vs the Trial Balance change Amount.
However, we also know but they Inventory Audit report that we did have a backdated document on a product category that was not defined in the SJI System account setup. Since it was not defined in the SJI system Setup, the backdated document will not show in the work file reports. The SJI Exec Work file does not show dollars for:

Missing SJI Codes that would post to system clearing if an entry was created.
Out-of-balance entries that would post to system clearing if an entry was created.
Zero Dollar Grain Sales

Step Three:
So the only option is to make a manual ledger entry posting the "Adjust Stock Addition Entry" manually to the ledger.
Debit Accounts Payable for $1500Credit Inventory Asset 1340-6E for $1500
Step Five: Run your trial balance one more time to compare to the Inventory Activity Summary Report.
You are now in Balance!

Wrap up

Control your earliest and latest dates. This will prevent backdating into a month you have already run SJI and make your life much easier.
Keep your SJI Creation Reports.
Run your balancing reports with all users out of the system.
Run your balancing reports after a cost adjustment.
Note that once we got an INV balancing report, we assumed it was correct and worked to make the ledger balance to the Inventory Activity Summary
Understand documents types and what accounts they are programmed to post to.
Do not rebuild your work file after each manual entry or the manual entry combines with the subsidiary entry to appeared double in the work file.

~Documentation Written By: Alisa Mueller 04/2014