Gross Margin to Cost of Goods Reconciliation

I have been asked in the past to reconcile the Gross Margin to the Cost of Goods, or reconcile Gross Margin to the Ledger.
See below for steps is achieving reconciliation of Gross Margin to Cost of Goods or Ledger

1. Generate Gross Margin by Item Report: Accounts Receivables - Reports - Statistics - Customer - Gross Margin by item Report. (Run report by date range)
2. Review Gross Margin Report against Inventory Transactions to ensure that the Gross Margin report is OK: Inventory management - Reports - Transactions - Inventory Transactions. Ensure the Reference field in the Inventory Transactions table is set to "Sales Order" and that any Projects related transactions are not included (By removing financial vouchers in the projects numbering sequence)
3. Add up the Financial Cost Amount Column to the Adjustment Column and match to the value from the Gross Margin by item report. This should be zero
4. Next review the ledger or Cost of Goods to reconcile to the Gross Margin. The Cost of Goods sold can be found in the Ledger Transactions List with posting type "Sales order consumption'. General Ledger - Reports - Transaction - Ledger Transaction List filter by date range and posting type "Sales order Consumption"
5. This value of the Cost of Goods Sold should be reconciled to the Gross margin by item value.
6. If there is any difference, this would be attributed to adjustments from inventory closing as at month-end. We need to run two separate inventory adjustment reports to determine which sales orders have been corrected per the end of the month and after the end of the month
7. Inventory management - Reports - Transactions - Adjustments

(a) Ensure Date on the Inventory Settlements table is for the month in question (e.g 8/1/2010..8/31/2010) the P&L Posting of the Inventory Settlements table is set to "Sales Order Consumption" and that the Financial date of Inventory Transactions is less (<) than the first day of the month in question (e.g <8/1/2010)>

(b)Ensure Date on Inventory Settlements table is greater (>) than the last day of the month in question (e.g >8/31/2010), the P&L Posting of the Inventory Settlements table is set to "Sales Order Consumption" and that the Financial date of the Inventory Transactions is the month in question (e.g 8/1/2010..8/31..2010). Obtain the adjustment amount from this report

This analysis should be reconciled if the steps outlined are followed.

I also have an Excel Spreadsheet template which can be used to assist with the reconciliation. Feel free to reach out to me for assistance.

Good luck as you reconcile!

Inventory Reconciliation in Dynamics AX

Reconciling Inventory is a big pain issue for most organizations on Dynamics AX. Usually, improper set up of Item Groups, Posting of manual journals into inventory accounts, Service items posting to the ledger, and financial dates are some of the reasons why the inventory sub-ledger does not reconcile to the General ledger.
Follow these steps to determine the cause of the variance and enable you take remedial action and ensure Inventory balances:

1. Ensure Inventory has been closed for the period you are reconciling
2. Obtain Trial Balance figures for inventory: General Ledger - Reports - Transactions - Periodic - Trial Balance
3. Review the posting types by account to determine the transaction types posted to the inventory ledgers: General Ledger - Reports - Reconciliation - Inventory - Item Posting by Account
4. Run the ledger transactions list to determine postings not captured by the Item Posting by Account report: General Ledger - Reports - Transactions - Ledger Transaction list
5. Download this report to Excel and auto-filter by posting to ensure that each posting type is captured
6. Postings not captured could include manual journals, service transactions, blank posting type (probably a bug)
7. Obtain the inventory sub-ledger figures from the inventory value by item group report. Tie the financial value of each item group to the Trial Balance: Inventory Management - Reports - Status - Inventory Value - Inventory Value by item group
8. Research the variances
9. Take remedial actions on a case by case basis
10. If you identify manual journal entries into the inventory account, create a reversing journal entry to correct this. In addition, ensure controls around posting to the Inventory account are in place such as using the "locked in journal" functionality in the chart of accounts or setting up validations for posting type also in the chart of accounts.
11. If you identify service transactions in ledger, then the service item is not set up appropriately. You need to finalize all transactions with the service item, create a new service item that is set up with the right item type, inventory model group, item group and dimension group and create a journal entry to reverse the service item posting.
12. Maintain back up of your work for reference to your auditors.
13. You can also run the Inventory Reconciliation Report to assist in your analysis: General Ledger - Reports - Reconciliation - Inventory - Inventory. Click show differences to determine reasons for variance

I have an Excel template which I have designed to properly capture all the details of the inventory reconciliation and would be glad to provide that. Feel free to contact me.

Good luck as you reconcile inventory and let me know if this post has been useful to you!

Alternate Vendor Payment

Microsoft Dynamics AX provides functionality to support the payment of a vendor in lieu of another vendor. The invoice account field on the vendor master and the purchase order can be set to allow one vendor to make the purchase and another vendor to receive the payment for the purchase.
However in the event that the invoice account was not set properly and the payment is on the open transactions of the purchase vendor, let me outline the steps needed to handle the alternate vendor payment.
Assume in this scenario we have two vendors - Vendor A whose invoice has been processed and Vendor B whom we have been instructed to pay in lieu of Vendor A

• Create new method of payment called VEND

1. Accounts Payable - Set up - Payments - Methods of Payment
2. Click the new icon or CTRL +N
3. Enter the following:
4. Method of Payment - VEND
5. Period - Invoice
6. Description - Vendor Payment
7. Account Type - Vendor

• Process payment for Vendor A

1. Accounts Payable - Journals - Payments - Payment Journal
2. Create Payment Journal Header
3. Click Lines
4. Create new Line
5. Enter Vendor Account (in this case Vendor A)
6. Ensure that the method of payment is "VEND" (the new method of payment that was earlier created)
7. Ensure that the offset account on the payment journal is of type vendor
8. Select Vendor B in the offset account
9. Click on Functions - Settlement and mark the invoice for payment
10. NOTE: Do not generate payments as no check will print
11. Validate and Post Journal

• Perform review of Vendor B

1. Accounts Payable - Vendor details
2. Search for Vendor B
3. Click Functions - Open Transaction Editing
4. The outstanding payable from Vendor A now exists for Vendor B

• Process payment for Vendor B as you normally would

1. Accounts Payable - Journals - Payments - Payment journal
2. Create payment journal header
3. Click Lines
4. Create new line
5. Enter Vendor B in the vendor account field
6. Ensure the method of payment is Check or ACH etc.
7. Ensure that the offset account type and offset account on the payment journal is Bank
8. Click on functions - Settlement and mark invoice for payment
9. Click on functions - Generate payments to generate check
10. Validate and Post journal

Introducing....

This is the firt time I am blogging in my life... and I would like my first blog to be on a topic I have been passionate about since 2005 - Microsoft Dynamics AX.
I hope this blog would provide new insights to the software for those who come across it.
I would be providing tips and tricks as well as processes to achieve tasks in Dynamics AX.
As the software evolves, this blog would too.. so look out for discussions on what's new in Dynamics AX6.