I was not able to find an answer on this also so I did this work around to meet inventory reporting requirement to show purchase price and landed cost separately in the value of an item - We use Landed costs for third party freight and customs. We use moving average valuation but this should work for FiFo as well. for this example Dec 31, 2020 is the report end date.
Run the GL report for inventory GL with these selections:
- Inventory GL
- Click expanded button, make sure all documents are selected EXCEPT Landed Cost.
- from Dec 31, 2020
- to Dec 31,2020.
- Check box for Opening balance from start of company activity
Run the report. This will give you your inventory balance at Dec 31, 2020 without landed cost documents.
Run an inventory Audit report with selections:
- Posting Date Dec 31,2020
- All warehouses
- By items
- Display OB for items with no transactions
- Show items with zero balance
This report should match your balance sheet - if not, double check your selections. Copy the collapsed version it into excel. This is your item listing of item cost with landed costs.
Now you know the balance of your inventory in-stock at Dec 31, 2020 with and without landed cost. Subtract the two and you have the landed cost value that is still in inventory at Dec 31.2020.
Run a query of the Landed cost documents just to flag which items have been revalued over the fiscal year (or longer or shorter depending on how fast your inventory turns), spread the landed cost value by weighted amount among the items that were in revaluation documents. This is not prefect by no means, but you could better calculate your weighted values based on % of the Landed Cost query. Ie. you have $100 in landed cost documents. 80% was from supplier A, 15% from Supplier b, and the rest is a mix. Use this %'s to disperse the landed cost value you in inventory.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.