Hi,
You should not need to calculate the GRNI amount manually in Excel.
The Purchase Line table already contains the following standard fields:
- Qty. Rcd. Not Invoiced
- A. Rcd. Not Inv. Ex. VAT (LCY)
Use A. Rcd. Not Inv. Ex. VAT (LCY) instead of Amt. Rcd. Not Invoiced (LCY). The first field represents the received-but-not-invoiced amount in local currency excluding VAT.
On the Purchase Lines page, create an analysis with filters such as:
- Document Type = Order
- Type = Item
- Qty. Rcd. Not Invoiced <> 0
Then group the results by Vendor No., Document No. and Item No.
If the excluding-VAT field is not available through Personalization or Analysis Mode, it can be exposed with a very small page extension or used in a custom query/report. No additional calculation field should be necessary.
Also, the suggested formula:
Quantity - Outstanding Quantity
gives the total quantity received, not necessarily the quantity received and still not invoiced. For example, after a partial invoice it would also include the quantity that has already been invoiced. The correct standard field is Qty. Rcd. Not Invoiced.
For financial month-end reconciliation, you should also review Expected Cost Posting to G/L. When enabled, Business Central posts received-not-invoiced expected costs to the Inventory Account (Interim) and Inventory Accrual Account (Interim).
The Inventory Valuation report can include expected costs and can be used to reconcile the inventory subledger with the interim G/L accounts. However, it is primarily a financial reconciliation report rather than a detailed vendor-by-purchase-order GRNI listing.
Therefore:
For the current detailed GRNI list, use Purchase Lines with A. Rcd. Not Inv. Ex. VAT (LCY).
For financial reconciliation, use the interim G/L accounts and Inventory Valuation with expected costs.
If Finance requires a historical GRNI report as of a specific closing date, including Vendor and Purchase Receipt details, a custom report based on Value Entries and Purchase Receipt Lines may be required.
Kind regards
Tarik Jerković
