Microsoft Dynamics GP (Archived)

FIFO and pulling in cost for inventory

(0) Share

Report

Posted on by Katie Pieczynski

3,225

Our system is set up on FIFO. Right now we run an inventory report every month that has:

ITEM NUMBER

QTY on HAND

Current COST (which comes off of the item card)

Since we run on a FIFO method we need to have the cost that we bought it at displayed also so we may have two line items of XXX one at $1 and 5 of the XXX at $3.

Is there a way for GP or Excel report builder to pull this in?

Thank you

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

Dear Ms. Katie

Regarding the "Current Cost"issue raised above, please be informed that in FIFO valuation method, the cost is updated after every increase transaction for an item, which means that the current cost it is the last cost in which an inventory replenishment occurs.

I assume what you are looking for is the cost layers for an item per purchase receipt. To retrieve this data;

GO To Dynamics GP > Smart List > Inventory > Inventory Purchase Receipt

For further details on the Inventory Valuation Method Current Cost, refer to the reference below;

Inventory Valuation Methods

For any further details, please never hesitate to ask me.

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

Hi Katie,

I think Victoria Yudin has just what you're looking for. Run this SQL statement to create a view, that you can easily use in Excel or SmartList Builder

Kind regards,

Leslie

CREATE VIEW view_On_Hand_Inventory

AS

/********************************************************************

view_On_Hand_Inventory

Created on June 18, 2009 by Victoria Yudin - Flexible Solutions, Inc.

For updates see victoriayudin.com/gp-reports

Inventory on hand quantities by site

Only tested with FIFO Perpetual inventory valuation method

I - IV00101 - Item Master

T - IV10200 - Purchase Receipts

Updated on June 24, 2009 to add Item Class

********************************************************************/

SELECT T.ITEMNMBR Item,

I.ITEMDESC Description,

I.ITMCLSCD Item_Class,

T.TRXLOCTN Site_ID,

sum(T.QTYRECVD-T.QTYSOLD) Quantity,

sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD)) Total_Cost,

sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))

/sum(T.QTYRECVD-T.QTYSOLD) Avg_Unit_Cost

FROM IV10200 T

INNER JOIN

IV00101 I

ON I.ITEMNMBR = T.ITEMNMBR

WHERE T.QTYRECVD <> T.QTYSOLD

GROUP BY T.ITEMNMBR, T.TRXLOCTN, I.ITEMDESC, I.ITMCLSCD

/** the following will grant permissions to this view to DYNGRP,

leave this section off if you do not want to grant permissions **/

GO

GRANT SELECT ON view_On_Hand_Inventory TO DYNGRP

Was this reply helpful? Yes No
Suggested answer

Olaf Laos 1,890 on at

Like (0)

Report

There is an add-on for Dynamics called "Distribution Elementz" from www.gpelementz.com.

This has a collection of useful windows and reports - and the latest version has an Inventory Valuation View window and report which shows how a FIFO item has its valuation made up of different receipts and different prices. They will send you a version to have for free limited period trial if you are interested.

Was this reply helpful? Yes No