Inventory problem

Schermafbeelding 2013-03-14 om 22.24.50.png

Hello everyone,

I am having problems with setting an excel formula together.

Suppose we have following (relevant) data in an excel file:

- 1000 products
- stock (amount of products in inventory) data on date 01/01/2012
- the time to deliver a product when you ordered new products when you run out of stock (stock on 1/1/2012 minus sales)
- sales for each month of the year


Of course, stock, sales and time to deliver differs for each product. What the formula has to do is tell when there has to be a purchase of products because running low on stock. An example: Stock on 01/01/2012 is 500 units. In January and february we sell 250 units/month. This means at the end of february there is no stock left. The delivery time is 30 days, which means products need to be ordered at the end of January or at the beginning of February.

My question is how to set up the formula so that when I want to check this on a regular base, I do not have to change a lot.

Many thanks!

Can you please upload the sample workbook, instead of an image?

My job uses a spreadsheet I created to do this but it extracts data from our system and performs calculations on that.

You need to figure out you sales rate @ 1 day say and then multiply this by your required cover.

So if Product A sells 100 in 10 days (=10 a day) and it takes 30 days from ordering to delivery you want to order when you are down to 400 in stock to give you a few days scope for delivery.
Therefore you need to need to have a stock level, rate of sale (R.O.S.) and cover (number of days you need to order to). a simple formula can then show you what to order.

If you can upload an example sheet I may be able to help but this could become complicated if you start to include minimum drop quantites/ordering in pallets or layers etc.

Inventory.xlsx

Pleased see attached. The current stock column is calculated from what your staring stock was minus sales shown each month however if you can just type in your current stock level into here it would tell you accuratly when an order is needed.

Or more simply if you stock level is less than your min stock level then you will need an order for that product. As you track your monthly sales the average will update automatically.

Note calculations for min stock level assume 30 days to a month.

Hope this helps.

Calculator.xlsx

Attached how far you could go with ordering system.

Started to confuse myslef doing this so it may not work quite right, if not let me know.

Note the red cell is a drop down list for which you select the month when you are checking this sheet.

Inventory2.xlsx