Generating a FIFO formula to re-cost Inventory

[helenaapw]

I have a report that identifies all on-hand Inventory and values the inventory at the Current Standard Cost. I also have all of the Purchase Receipts for each part with the value that was applicable at the time of the receipt. I need to be able to look at my qty of on-hand stock (eg 100 pcs @ standard cost $1.00 ea), the costs from each receipt (qty 50 received 17/01/05 @ 1.00, qty 25 received 14/01/05 @ 1.50, qty 100 received 11/01/05 @ 0.90 and qty 1000 received 04/01/05 @ 0.50) The formula needs to look back through the receipts until it has found a sufficient qty of receipts to cover the stock on hand and revalue according to the receipts it has found. Please bear in mind there could be hundreds of receipt entries for each part, the formula needs to pull only enough of the receipt entries to cover the stock on hand qty. In this instance the revalued cost of my stock will be 50*1.00 + 25*1.50 + 25*0.90 revaluation = 110. Please note that the recipt with the per unit value = 0.50 is not used. How can I generate this type of formula in Crystal?

 

[synapsevampire]

That's a fairly complex formula and requires a better understanding of the table structures to clearly describe it, however the following theory should prove useful.

Since you know that you have 100 OH, then you would either loop through the receipts via a join or a subreport, and count the Qtys until the 100 are covered. It's also unclear to me how you determine the individual costs when they are aggregated over different price points.

An inner grouping using the Receipts will allow for this (again, either via a join or a subreport) and a formula to count the qty and the individual costs of each receipt, making sure that the Receipt rows are sorted Ascending.

Group Header:

whileprintingrecords;
OHQty := {table.OHQty};

Details:

whileprintingrecords;
numbervar CurrCost;
numbervar OHQty; //if subreport use a shared variable
numbervar RcptQty;:=RcptQty+{table.Receipt};
If RcptQty >= OHQty
<???formula to determine individual costs???>
else
RcptQty:=RcptQty+{table.Receipt};

Group Footer
whileprintingrecords;
numbervar CurrCost

-k