Excel FIFO Style Inventory Advice

[Monkeyman50]

Hi There,

I have a bit of a dilemma with an excel sheet I'm working on and was hoping for a bit of advice from the experts on here.

I am trying to create an excel workbook that can manage my inventory, record sales and stock purchases and display some figures in a nice little dashboard.

Where I'm getting stuck is by trying to create a First In First Out table for my products. So for example I have bought 10 chairs at £2 and then 2 weeks later I've bought 15 chairs at £5 as the price has gone up, now if someone has bought 12 chairs from me in one sale, I need the sheet to minus 10 at £2 and then realising that "pool" of stock is finished minus 2 at £5 and return the total cost of £30 in the sales sheet so I can accurately see profit. I almost had a solution but using only formulas (lots of IFS and INDIRECTS), as i'm a novice with VBA, but it doesn't quite work and the sheet is very slow.

I've been trying to create a stock table for each product I have and in the near future I could have over 1000 products. Should I be using access for my product database or would excel cope with this amount of data without slowing to a crawl? If the proper method / VBA etc was used as a solution.

Any advice is appreciated.

Thank you,

 

[James006]

Hello,

You can take a look at following solution :

https://www.thesmallman.com/fifo-calculator/

Hope this will help

 

[MickG]

Try this:-
https://app.box.com/s/o8yyckqapmnbq3zbdm1hkohuh8jxv3jm

 

[Monkeyman50]

Thanks I'm currently evaluating that first example from thesmallman.com and trying to see if i can make it work. MickG thanks for the link but I couldn't get it to do anything - it says click to run but nothing happens, is there a way to download it?

Thanks again,

 

[MickG]

You need to copy "G1:K3" to "A1" then run the code, Have you done that ???
What you have at the moment is the RESULT of running the code

 

[Monkeyman50]

MickG thanks for that I ran the code and it works well however I'm looking for a more sheet independent approach - for example I'd have all the sales in one sheet, and adding sales day by day, another sheet for stock purchase and then a separate sheet for each product which would do the FIFO calculation - pulling data from the stock purchase and sales sheet to the product sheet and showing a table of stock in and out along with remaining stock. The sales sheet would also pull data from the FIFO calculation to display the cost of the sale that day.

The first example does work well however it is only really set up for one date record of purchases per month, if I add a new date in the cell below in the same month and say I've bought the same product - the code runs through the sales from scratch and gives a remaining quantity based on this, whereas I need it to look at the previous cell's remaining quantity and then work off that. Any idea how I would adapt it to do this?

 

[MickG]

What you are asking for sounds quite complicated to do, if you can send a example file (DropBox.com or Box.com) showing the some basic layout/Data with expected results, I will have another look ,but I can't promise anything.!!

 

[Monkeyman50]

Hi There,

Sorry for the extremely late reply! I've just been caught up trying a few inventory solutions but none of which are as useful or do what I need really so I'm back at this trying to make it work .

I've included a link to dropbox (https://www.dropbox.com/s/ym897bn9vxwgszk/Example Help Sheet.xlsx?dl=0) where I've uploaded my example excel file along with notes on each page to show what I am trying to do. I've got a separate sheet for each product that shows which "pot" of stock is the current one (FIFO style) after accounting for sales but the problem lies in feeding a true product cost to the sales sheet so I can accurately calculate profit.

I would be very grateful if you could take a look

Thank you,

 

[MickG]

This is some code for the basic FIFO bit but relating to your sheets "Sales" and "Stock Purchase".
I hope you will see the reason for the extra columns, perhaps you have a better way of recording the passed Sales/Stock Purchase.

See attached Example file:-
https://app.box.com/s/p95csydvbvweurdmom67e5wy6mkpkb2x

 

[Monkeyman50]

Thank you MickG for this, I'm working through it in my spare time and will hopefully be able to use the code and button you've created to play well with the sheet.

I'll report back soon on how I get on. Thanks for your help again!