First in first out (FIFO) subtraction

Nick G

New Member
Joined
Feb 11, 2019
Messages
7
Hello there,
I have an excel sheet which contains an inventory purchase & sell details of a stock.

In first column I have date in ascending order
In second column I have purchased stock quantity.
In last column I have sold stock quantity.

So what I want is that whenever there is a sell it should get deducted from the first purchase amount.
1-if the sale makes the purchase zero then the remaining sell amount should reduce the next purchase amount.
2- if the sell amount is not enough to reduce the purchase down to zero then the next sale should be deducted from that purchase untill it's zero and then move on to next purchase deduction from the remaining sell amounts

3. I want the formula added to should show how much of each purchase is remaining in stock.

Note:- 1)The data sheet I have got is static, i.e. there will be no more addition in purchase or sale in that data. Whatever I have got is final sheet.

2) before the first buy, the opening Balance is always zero.

Sample data

Date. buy sell Balance
3/4/19 1500 200 0
3/5/19 2400 1400 0
4/17/19 400 2300 0
5/14/19 480 120 0
6/20/19 0 100 0
7/30/19 1003 1000 663
9/30/19 20 0 20
10/4/19 100 0 100
Total 5903 5120 783

Here the balance column is manually calculated as-
1500(first buy)-200(first sale) so still 1300 remains then out of 1400(next sale) that 1300 gets deducted and the balance column for the first purchase becomes zero.
The 100 amount of sell which is remaining from the 1400 sale then gets deducted from 2400 which is next purchase and then 2300 remains of that purchese so then 2300 of next sales gets deducted from that remaining purchase and second row of balance column becomes zero. And so on.

So what I want is that Balance column to be calculated by a formula or a vba or any other method.

I know this sounds complex. But kindly help me this as tha data that I get are in thounds of rows daily.

Thanks in advance
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
Re: Help in First in first out (FIFO) subtraction

See if this does what you want.

Excel Workbook
ABCD
1Date.buysellBalance
24/03/201915002000
35/03/2019240014000
417/04/201940023000
514/05/20194801200
620/06/201901000
730/07/201910031000663
830/09/201920020
94/10/20191000100
10Total59035120783
FIFO
 

Nick G

New Member
Joined
Feb 11, 2019
Messages
7
Re: Help in First in first out (FIFO) subtraction

It works like a charm. Thank you very much
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
Re: Help in First in first out (FIFO) subtraction

It works like a charm. Thank you very much
You're very welcome. Thanks for the follow-up. :)
 

supladaiii

New Member
Joined
Mar 15, 2008
Messages
30
I'm not sure if I can ask a follow-up question relating to above scenario but here goes. Instead of or possibly on top of the balance calculation, how do I populate (on another sheet or range) the following:
Date.sell
4/3/2019200
4/3/20191300
5/3/2019100
5/3/20192300
17/04/2019120
17/04/2019100
17/04/2019180
14/05/2019480
30/07/2019340
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
I'm not sure if I can ask a follow-up question relating to above scenario but here goes. Instead of or possibly on top of the balance calculation, how do I populate (on another sheet or range) the following
How (manually) do you arrive at those numbers?
 

Forum statistics

Threads
1,141,060
Messages
5,704,039
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top