First in - first out

[sbf]

Hi Everyone

I want to calculate the store value, based on the “first in - first out principle” for each item. When items are issued the earliest(oldest) items are taken from the store.

lot issue date quant price curbal value
A RECEIPT 01-12 7 10 7 7*10=70
B RECEIPT 02-12 4 20 11 7*10+4*20=150
ISSUE 03-12 -6 5 7*10+4*20–(6*10)=90
C RECEIPT 04-12 2 30 7 7*10+4*20-(6*10)+2*30 =150
ISSUE 05-12 -2 5 7*10 +4*20–(6*10)+2*30 –(1*10 +1*20)=120

line 3,
6 items are issued. The 6 items are taken from lot A (6*10)

line 5,
2 items are issued. The first item is taken from lot A and the second from lot B (1*10+1*20)

curbal is the running total of quant

I use am using CRW 8.5 / 9.
Thanks in advance, sbf-kla

 

[dgillz]

So do you have a specific question?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Madawc]

If you have several records for an item and want the oldest, then group by item and sort by date within group. With date ascending, the Group Header can show the oldest.

If you want a definite number that come from one or more oldest item, that's much more tricky in Crystal.

Madawc Williams
East Anglia, Great Britain

 

[sbf]

Thanks for your reply dgillz and Madawc

My problem is to make a formular to calculate the actual store value on the latest record date (newest)In my example, 5. Dec, where the field Issue = ISSUE.

The formula should be something like:

If curbal (line 5) <= quant (line 4 ) then curbal (line 5)*price(line 4)
Else if curbal (line 5) <= quant (line 4 ) + quant (line 2)
Then quant (line 4)* price(line 4) +
(curbal (line 5) - quant (line 4 ) * price(line 2)
Else if curbal (line 5) <= quant (line 4 ) + quant (line 2) + quant (line 1)
Then quant (line 4 )*price(line 4) +
quant (line 2)* price(line 2) +
((curbal (line 5) - quant (line 4 ) - quant (line 2))* price (line 1)
........................... And so on backwards in time until curbal is less than sum of quant where Issue = RECEIPT

Calculation of values are performed on records where Issue = RECEIPT (line 4 , 2 and 1)

If the last record was the record in line 4, curbal (line 4) should be put into the formula instead of curbal (line 5).

I think I need some sort of counter to be able to go back to the previous record where Issue = RECEIPT, but perhaps this is too tricky?

Thanks again, sbf-kla

 

[Ngolem]

lot issue date qty price curbal value
A RECEIPT 01-12 7 10 7 70
B RECEIPT 02-12 4 20 11 150
ISSUE 03-12 -6 5 90
C RECEIPT 04-12 2 30 7 150
ISSUE 05-12 -2 5 120

******************************************

So essentially your problem is to calculate and display Curbal and Value...yes?

First of all how is this report grouped?? Is Date more important than Lot Number??

I sort of think so since you are wanting to evaluate how much of a certain product you have at any given time.

Your data example gives successively increasing date and form a RECEIPT point of view I can see this being true (ie. Batch Lot A should be made before Batch Lot C) but when ISSUES of Batch B begin are all ISSUES of Lot A complete??

To me this must be addressed since you are looking for on-hand / current value numbers....at least that is what the SALES MANAGER in me thinks.

So the Grouping of this report is probably

Group 1 - Product ID (not included in your example)
Group 2 - date (Transaction Date)
Group 3 - Lot Number
with sorting by Issue (descending)

At least that is what I will assume they will be for this example.

a tricky part here in your calculations is that your value depends on what has been issued....there doesn't seem to be an indicator as to what Lot has been issued so it must be on the basis of FIFO as your title indicates.

Now to keep track of this I would use arrays . The way to do this to reduce maintainance is to set the size 50% higher than you expect...so if your max number of LOTs for a given report is 10 in your estimation ... then size the arrays for 15

To Calculate your CurBal and Value

1. Create an initialization formula

//@Initialization (placed suppressed in Group 1 header)
WhilePrintingRecords;

if Not inRepeatingGroupHeader then
(
stringVar Array Lot := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;];
numberVar array Quantity := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar array Price := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar Curbal := 0;
numberVar Value := 0;
StringVar Warning := &quot;&quot;;
);

2. Place the following formulas in the Detail section in their appropriate places

//@CalcNumbers (Suppressed in detail section)

WhilePrintingRecords;
numberVar Curbal;
numberVar Value;
stringVar array Lot;
numberVar array Quantity;
numberVar array Price;
numberVar pointer;
BooleanVar flag := False;
NumberVar temp := 0;

if {Table.issue} = &quot;RECEIPT&quot; then
(
for pointer := 1 to ubound(Lot) do
(
if Lot[pointer] = {Table.Lot} or Lot[pointer] = &quot;&quot; then
flag := True;
if flag then exit do;
);

If pointer = ubound(Lot) and not flag then
(
Warning := &quot;Maintenance on arrays required...data missing&quot;;
temp := 0; //does nothing, just makes If-Then legal
)
else
(
Lot[pointer] := {Table.Lot};
Price[pointer] := {Table.price};
Quantity[pointer] := Quantity[pointer]+ {Table.Quantity};
CurBal := CurBal + {Table.Quantity};
value := value + ({Table.price} * {Table.Quantity});
);
temp := 0; //does nothing, just makes If-Then legal
)

else // this is for ISSUES
(
temp := {Table.Quantity};
CurBal := CurBal + temp;
for pointer := 1 to ubound(Quantity) do
(
if Quantity[pointer] <> 0 then
(
if Quantity[pointer] > temp then
(
Quantity[pointer] := Quantity[pointer]+ temp;
value := value + (Price[pointer] * temp);
flag := True;
)
else
(
temp := temp - Quantity[pointer] ;
value := value + (Price[pointer] * Quantity[pointer]);
flag := False;
);
);
if flag then exit do;
);
temp := 0; //does nothing, just makes If-Then legal
);

now you can assign display values to the calculated results

//@DisplayCurBal (in detail section)

EvaluateAfter(@CalcNumbers)
numberVar Curbal;
Curbal;

//@DisplayValue (in detail section)

EvaluateAfter(@CalcNumbers)
numberVar Value;
Value;

In the Group 1 footer place this maintenance Alert

//@Alert (this can have a yellow background to standout)

WhilePrintingRecords;
stringVar Warning;

Warning; //will not appear unless tere is a problem with the array sizes.

I think this will do the trick....may require a tweak or two though.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[sbf]

Thanks a lot Ngolem, I regret my late response, I just got back to the office today.

Yes, my problem is to calculate the Value and curbal of products in the storeroom.
Yes, date is more important than lot.
We will receive not more than one shipment (Lot) per day of each Product ID, so I probably don’t have to use the Lot number, but only the date - I am sorry misleading you on that.
//////
Your data example gives successively increasing date and form a RECEIPT point of view I can see this being true (ie. Batch Lot A should be made before Batch Lot C) but when ISSUES of Batch B begin are all ISSUES of Lot A complete??
///////
I am not sure if I quite understand your question here. But all items of LOT A are used before items are taken from LOT B.
On the 3rd of December six pieces from LOT A are ISSUED and one piece remains in the storeroom.
On the 5th December, the remaining one from LOT A and one from LOT B are ISSUED.

The report is grouped by Product ID and sorted by date.
The report should show the value of each Product ID and the total value.
I only need to show the most recent value of each Product ID:
Example:
Product ID (trans) Date Curbal value
1000 05-12 5 120
1001 30-11 10 750
1003 01-12 33 660
------
Total on 5 dec 2003= 1530

I have tried your formulas and made a minor modification (in bold) to @CalcNumbers:

WhilePrintingRecords;
numberVar Curbal;
numberVar Value;
stringVar array Lot;
numberVar array Quantity;
numberVar array Price;
numberVar pointer;
BooleanVar flag := False;
NumberVar temp := 0;
StringVar Warning; ////// I inserted this line

if {Table.issue} = &quot;RECEIPT&quot; then
(
for pointer := 1 to ubound(Lot) do
(
if Lot[pointer] = {Table.Lot} or Lot[pointer] = &quot;&quot; then
flag := True;
if flag then exit for; //I changed from exit do to exit for
);

If pointer = ubound(Lot) and not flag then
(
Warning := &quot;Maintenance on arrays required...data missing&quot;;
temp := 0; //does nothing, just makes If-Then legal
)
else
(
Lot[pointer] := {Table.Lot};
Price[pointer] := {Table.price};
Quantity[pointer] := Quantity[pointer]+ {Table.Quantity};
CurBal := CurBal + {Table.Quantity};
value := value + ({Table.price} * {Table.Quantity});
);
temp := 0; //does nothing, just makes If-Then legal
)

else // this is for ISSUES
(
temp := {Table.Quantity};
CurBal := CurBal + temp;
for pointer := 1 to ubound(Quantity) do
(
if Quantity[pointer] <> 0 then
(
if Quantity[pointer] > temp then
(
Quantity[pointer] := Quantity[pointer]+ temp;
value := value + (Price[pointer] * temp);
flag := True;
)
else
(
temp := temp - Quantity[pointer] ;
value := value + (Price[pointer] * Quantity[pointer]);
flag := False;
);
);
if flag then exit for ; // I changed from exit do to exit for
);
temp := 0; //does nothing, just makes If-Then legal
);

I got the following output, curbal is correct but the value should be 120 instead of 130 as the remaining 5 pieces is 3 from 02-12, with unit price 20 plus 2 from 04-12 with a unit price of 30, total 120.

Lot issue date qty price curbal value
A RECEIPT 01-12 7 10 7 70
B RECEIPT 02-12 4 20 11 150
ISSUE 03-12 -6 5 90
C RECEIPT 04-12 2 30 7 150
ISSUE 05-12 -2 5 130

If qty is -7 instead of -6 in line3, calculations in all lines are correct.
Thanks again, I really appreciate your help!!
sbf-kla

 

[Ngolem]

Glad it worked....sorry about the Exit For mistake....thinking VB at the time I guess

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[sbf]

Hi Ngolem

The RECIEIPT works ok, but I still have a problem with the ISSUE part.

With qty <=7 in line 3, ISSUE part, gives the right result :
Lot issue date qty price curbal value
A RECEIPT 01-12 7 10 7 70
B RECEIPT 02-12 4 20 11 150
ISSUE 03-12 -6 5 90 ok!

But if qty > 7 in line 3 the formula gives the wrong result:
Lot issue date qty price curbal value
A RECEIPT 01-12 7 10 7 70
B RECEIPT 02-12 4 20 11 150
ISSUE 03-12 -8 3 70 but should be 60!

I have not worked with arrays before but I think that the problem is in the first part of the ISSUE part of the formula ? Any suggestions ?


Thanks in advance

sbf-kla