Posted by: IS | March 7, 2010

Implementing Dynamics GP Landed Costs module in mid stream


Please be careful with this one. I’ve tested it thoroughly and can’t find an issue…but it does involve editing tables through SQL. It is possible that custom reports etc. that are taking data from different tables may not like these changes…so test thoroughly in a test enviornment. But…it worked for me and saved a lot of hassle.

Landed Costs Module for Microsoft Dynamics GP really is a superb piece of software – one of those modules that does exactly what it says on the tin, and fills a definite need for anyone incurring significant additional costs in getting their stock all the way from their supplier to their customer. When you implement it though, you can only add landed costs for new purchases – makes sense, the old transactions are posted through, may be part sold, and are recorded at the actual item cost.

In the transition period (where you’re selling items at the old cost and the new cost which includes the landed costs elements) this might cause any Gross Margin reports to be skewed a bit…and if an item has a small turnover, it might be in stock for a while!  Hard to make decisions about which stock items to stick with and which to drop from your portfolio if you aren’t comparing like with like on the cost front.

Luckily whenever you transact in a Stock item in Microsoft Dynamics GP, the key table is the IV10200 table – Inventory Goods Received Work. This tracks every receipt, the quantity received, the unit cost and the quantity from this receipt that is sold. This table keeps track of what stock is sold based on your FIFO / LIFO etc choices.

You could manually update your on hand stock quantities using the Adjust Costs Inventory Utility. However with large numbers of transactions, this is going to be a marathon exercise. However you decide to uplift your item unit costs to include a portion of landed costs…you will have to adjust your GL afterwards – basically Debit Inventory…and Credit whatever account(s) the landed cost invoices are currently posted to in your Profit and Loss account. Really just a re-analysis exercise. You’re taking costs from your P&L and adding them back on to the Inventory Items so that they move to your P&L into COGS when the items are sold…not when the shipping invoice or the import duty advice note is received.

There is a way however that you can do this through SQL. By identifying those Receipts in the IV10200 table that have stock remaining, you can write a query to change the unit cost directly in the table. The cost used is your issue…I exported these receipts out to XL and added columns for each of the landed costs elements and then entered values in each…a total column then gave me the new Item Unit Cost (old cost plus the landed costs elements). The totals of the landed cost columns also formed the basis of my adjusting GL journal. This XL sheet also provided me with a series of MSSQL queries (one per row – or put another way, one per individual stock receipt) that I could run against the IV10200 table to update the costs

If you have Sales Orders or Sales Invoices sitting unposted – these will have the current cost of the item recorded. No real issue here, as GP recalculates the Item cost when you post these documents – going back to the IV10200 table to ‘grab’ stock from sucessive receipts until its required quota is filled…and taking the Unit Cost that exists in the table at the time. The Stock Status report and the Inventory Item Enquiry all reflect this new cost.

My update query ended up as something like:

 

 

UPDATE IV10200 SET UNITCOST = ‘32.856’ WHERE RCPTNMBR = ‘00034952’ AND ITEMNMBR = ‘51722 ‘

Repeated for each receipt that had quantities remaining and where the unit cost was being uplifted.Really can’t stress enough that you need to test this for your particular circumstances. Work it all out in advance and create a test enviornment where you can process your normal reports / enquiries etc. This is a workaround that in my case had to be done as the alternative was impossible to achieve both in terms of time available and the investment required.

Advertisements

Responses

  1. […] Stewart has a new post up looking at issues when Implementing Landed Costs in mid stream. Published: Monday, March 08, 2010, 02:00 […]

  2. If you want to use the Historical Inventory Trial Balance report, the costs are also updated in SEE30303 at a minimum (not sure if there are other tables involved).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: