First in First Out (Fifo) Stock balance Calaculation File.

[Nick321]

Hello there,

I actually need help in a simple task of stock balance calculation but due to this task being a daily task and my input files having least 200 entries to work on for this task I am having many difficulties. Kindly help to kind of Automate this task (or at least reducing manual calculation) with the help of anything like formulas, VBA code or any other method.

Task - I have a stock balance file of previous day (Input 1) and transaction file of current day (Input 2) and I have to create and Stock balance file of current day (Output) with FIFO method by adding new purchases of current day to the stock and deducting the sales of current day from the stock with FIFO method.

So let me tell you how my files for this task are.

In below attached sample file -

The Input 1 is stock balance report of previous day (in this example it is of 04/07/2020) which consist of-

• product name showing all the products held at the end of day of 04/07/2020
• unique code for each purchase made. This code is given at the time of purchase. It will always be in ascending order in this report.
• buy date shows when this product was purchased.
• balance stock (qty) shows quantity of of that product at the end of day of 04/07/2020
• holding period- this is the time period for which we are holding this security in our stock. It is calculated by subtracting the 'buy date' from the report date (in this case it is like 04/07/2020 - the each buy date). Each purchase has an holding period limit of 90 days i.e after a product is purchased at x date it has to be sold within x+90 days. Hence we have to track holding period for each purchase made and not product wise so for eaxmple, as we see in sample input 1 that product A is repeating in the report but the holding period for it is differing based on the 'buy date' of that product. Holding period is calculated for each date i.e even if sunday is holiday that day will still be counted in holding period so no heed is to paid to any holidays.

Note:-Input 1 is always pre-arranged in the ascending order of Unique code.

In Input 2 we have transactions of the current day (in this case 05/07/2020) i.e products bought and sold on current day. This data is not live we receive this data on the end of the current date so this data is static there will not be any new buy and sell after this report is generated.

• The product name here is showing product bought and sold on that day. The important thing here is that there is no such rule that the transactions will take place in only in or all the products held by us in previous day. For example, as seen in sample input 2 below there is no transcation for Product C. Also we can see that two new products (E and F) are bought on 05/07/2020.
• Unique code as said earlier is given to each purchase when it is made. Here sale will also have an unique code. basically each transaction is given unique code which will never repeat.
• Buy/Sell date will show when the security was bought and sold as this transaction report is of 05/07/2020, this column will have only 05/07/2020 in each cell of this coloumn. As I Generate this report daily this column will have only one date.
• Quantity shows how much quantity is bought or sold in each transaction.

-This Input 2 is the main culprit. as each day on an average 200 transactions take place so the work to generate the output file takes very long and is prone to several errors.

Note:- Input 2 is always pre-arranged in the ascending order of Unique code.


In Ouput we have our working. This is the file I want to somehow automate or reduce the working in. This output is nothing but an stock balance report of Current day (in this example it is of 05/07/2020). so basically like Input 1. This ouput will become input 1 for the next day's working. This report is created at the very end of the day after the transactions for the current day are stopped.

• Here again the product code shows products held by us at the end of the current day. In the sample output an important thing to notice is that we have the new purchases of the current day added to the report as we can see each buy transaction of 05/07/2020 shown in input 2 is added at the end of the report one below other.
• We already know what unique code is. The purpose that it serves here is to guide the addition and subtraction of the current day transactions as this output will always has to be in ascending order of unique code, so in the sample output file you can see that Product E comes before Product F because the unique code of E being lesser than the Unique code of F. By the way these Unique codes for the new purchases are picked up from the transaction report i.e (Input 2)
• Buy date as explained shows when the product was purchased. This date for the new purchases is taken from Input 2
• Balance Stock (qty)- now this is where all problems come. This column as we know shows the balance stock held on the current day (in this case 05/07/2020). As we are using the FIFO method the sales that took place on 05/07/2020 will be deducted on FIFO basis from each product. So to find out which purchase was first we use unique code, so in our sample for 'Product A' the purchase of unique code 1 was earlier than the purchase of unique code of 4 so when we deduct the sales we will first deduct from unique code 1 and then from unique code 4 and so on. Similar for sales the sales of unique code 22 will be deducted first for Product A and then sales of unique code 32 will be deducted.
• Holding Period here again shows for how many days we are holding this product from the 'buy date'. This calculation is very easy as this is done on daily basis, ignoring any holidays. We just have to do +1 for each purchase as the holding period will increase by 1 day every day.
• Helping columnis just to show you my working it does not form part of the actual output. Here let me explain you what have I done.
  • So as per FIFO method the first purchased product needs to be sold first andmas explained earlier unique code is used to find out which product was purchased first. So let's see what has happened in case of 'Product A'.
    • So in case of Product A as we can derive from Input 1 that the purchases are of unique code 1, 4 and 5 . So as per unique codes the first purchase is of unique code 1 then of code 4 and the of code 5. The same is for sales as per Input 2 the sales for Product A are of unique code 22,32 and 47. So the first sale is of 22 then 32 and then 47.
    • So what I do is I copy and paste Input 1 and then add the new buy deals as per Input 2 one below other in ascending order of unique code then start deducting sales of each product one at a time as per FIFO method.
    • Thus For Product A I took first sale from Input 2 of 1500 and deducted it from first purchase of A which is of 2000. Then as the balance of 500 is remaining of first Purchase I took the second sale deal of Product A from Input 2 which is of 2000. Now as the sale is greater than what has remained of the first purchase of I took only part of that sale i.e 500 and knocked out the first purchase to zero. Then I moved to 2nd Purchase of A which is of 2500 and started deduction sales from it. As there is 1500 remaining of the 2nd sale deal we first deduct that from 2500.so 1000 quantity is still in balance of 2nd purchase so I look for more sales of A as I found third sale of A in Input 2 of 800 I deduct that from that 1000. Now 200 more is remaining in that purchase but there is no new sale for A in Input 2 so the balance of 2nd purchase of Product A becomes 200 and the remaining all Purchases of Product A remain the same as there is no more sale to affect them. Then after this I move to next product and do the same working for all the other products. The balance of the products who does not have sale in Input 2 to affect them stay the same. This is the part that I want to automate. If you can kindly provide any formula or VBA code for this part only will also do a great favor. other things I can do manually. Like I can manually add the new buy deals below in the output. I can do +1 in holding period of output manually. I just cannot do this manual work daily as not only the volume is more but there is also chance to miss an sale transaction which can ruin the full report.
    • After that I add + 1 day to holding period in output file. and then save the workbook.
    • For next day I Copy paste this output file and convert it into Input 1 by deleting the entries which have zero balance. This is an important step as I don't want the Input 1 showing any zero balance entries.

So basically I wan and VBA code to do this all in few clicks or an formula or VBA that at-least does that deduction step automatically.

Note:- I have Input 1 and Input 2 in two different workbooks. and I create the Output in an different Workbook. But it is very flexible from my side. I just wan the Output. Other things can be changed. If you want me to put both inputs in 2 worksheets of the same workbook or in single worksheet one below other or any other way that can be arranged. If the output thrown is in different workbook or on different worksheet of the same workbook or below the both inputs or any other way does not matter. I just need the Output thrown to be somehow automated and error free. Any method that gets the work done will be appreciated.

Thanks again for sparing time for this. Any kind of help will be appreciated..

Sample File ----->

Book1
	A	B	C	D	E	F	G
1					Input 1 ---->	Day end stock and holding period as on	04-07-2020
2	Product name	Unique code	Buy date	Balance Stock (Qty)	Holding Period
3	A	1	16-04-2020	2000	79
4	B	2	20-04-2020	1500	75
5	C	3	29-04-2020	3000	66
6	A	4	08-05-2020	2500	57
7	A	5	22-05-2020	2500	43
8	B	6	22-05-2020	1000	43
9	C	7	05-06-2020	200	29
10	D	8	23-06-2020	350	11
11
12					Input 2 ----->	Transaction report on date	05-07-2020
13
14	Product Name	Unique Code	Buy/Sell Date	Buy/Sell (B/S)	Quantity
15	A	22	05-07-2020	S	1500
16	B	25	05-07-2020	S	1500
17	A	32	05-07-2020	S	2000
18	A	36	05-07-2020	B	200
19	A	47	05-07-2020	S	800
20	D	48	05-07-2020	B	100
21	B	52	05-07-2020	S	200
22	E	59	05-07-2020	B	1000
23	F	64	05-07-2020	B	1000
24
25					Output---->	Day end stock and holding period as on	05-07-2020
26
27	Product name	Unique code	Buy date	Balance Stock (Qty)	Holding Period	Helping Coloumn (for balance stock)
28	A	1	16-04-2020	0	80	2000-1500(22)-500(32) = 0
29	B	2	20-04-2020	0	76	1500-1500(25) = 0
30	C	3	29-04-2020	3000	67	no sale
31	A	4	08-05-2020	200	58	2500-1500(32)-800(47) = 200
32	A	5	22-05-2020	2500	44	no sale
33	B	6	22-05-2020	800	44	1000-200(52) = 800
34	C	7	05-06-2020	200	30	no sale
35	D	8	23-06-2020	350	12	no sale
36	A	36	05-07-2020	200	0	buy -36
37	D	48	05-07-2020	100	0	buy - 48
38	E	59	05-07-2020	1000	0	buy-59
39	F	64	05-07-2020	1000	0	buy-64
Sheet1

.

 

[RayFrye]

Hello, It appears you have thought thru your requirements. I am sure you have noticed that no one has offered to help. I am offering friendly advice, I doubt anyone will decide to write your rather complex VBA application. If you chop it up into tiny pieces and try something yourself you might get a nibble.

 

[TotallyConfused]

Hello Nick

You have presented us with an interesting, though I'm sure it will also be a challenging problem.

I want to CONGRATULATE you on doing such a wonderful job of presenting your problem. What you gave us is much better than we usually receive from people. You did a great job of giving, in great detail, a written description of what you have, what you do and the end results. Your sample data matches your written description in a very easy way for us to follow, which allows us to see how the numbers connect to each other. This is exactly the kind of information a programmer needs. Give yourself a pat on your back for a great job!

After reading over your description and data, a couple of questions came to my mind that I think anyone working on this problem will need answers to.

1) Your Input #2 file shows all the transactions for the day of 05-07-2020. Now lets move ahead one day until the close of 06-07-2020 and you put that day's transactions into Input #2. My question is, do you first save Input #2 data for 05-07-2020 someplace BEFORE pasting in 06-07-2020, or is Input #2 overwritten?

2) This is similar to question #1 except this applies to your OUTPUT file that you would have created using the closing transactions (File #2) for 05-07-2020. Now lets move ahead one day until the close of 06-07-2020. My question is, do you first save the OUTPUT file for 05-07-2020 BEFORE processing the transaction data for 06-07-2020, or is this OUTPUT file overwritten?

TotallyConfused