Forum Discussion
Inventory Forecast Formula.
How open are you to rethinking altogether how you approach this? In order to identify usage (outflow) as a distinct part of the flow of product, you need to account for those moves--IN and OUT--separately. In my experience, the ideal way to do that is to use a database in which, day-by-day, you record stock movement IN or OUT of the inventory. As you're experiencing here, the end-of-week stock count by itself is of no use, maybe misleading, in projecting future use (outflow).
If I were doing it, I'd create a transactional database, as I suggested above. You must be keeping records anyway, I would think. So keeping them in Excel and then using Excel to summarize the in and out and calculate the inventory at the end of each week (which could also be verified periodically): that would be ideal.
Short of that, you could just modify your spreadsheet as shown below (I've attached the modified spreadsheet) to account separately for Out and In, and then use the "Out" numbers alone as the basis for your projections. This gives you far more useful information in general, since you can also see patterns for both In and Out.....You can see also the formula in cell G6, which calculates the week-end inventory based on last week's numbers, minus the Out, plus the In. Those calculations could be verified by on-site counts. It would be a way to improve your procedures overall, to make sure you are counting at each stage.
As I said, though, the idea from my point of view would be to have a database where each transaction--shipping out or receiving in--is noted. If you were to start doing that, then the numbers in each Out and In column could be derived from the database and this whole sheet automated. The work would be "at the front end" and "real-time."
mathetes thank you for your reply,
I have attached a copy for you. The data is in Rows and the dates show the stock count at those dates for the item in the row. As stock replenishment orders are received, they are added into the total for the stock item.
We are looking to have a formula to calculate the projected stock levels based on the usage from previous weeks. My concern is the injection of received stock replenishment will cause inaccurate forecasting, due to the 'spike'. If there is a way to not include this spike in the forecast it would be ideal, but we can work around this.
The outlined cells are the weeks where stock has arrived in the warehouse and the order qty has been added to the total qty. This data comes from a daily sheet we get from the warehouse, rather than an in-and-out report. I then use a VLOOKUP to pull the data into this sheet and paste as values under the respective dates.
This data is to be used on the master stock sheet, to help predict reorder levels that match lead times and sales.
I hope I have answered your questions
How open are you to rethinking altogether how you approach this? In order to identify usage (outflow) as a distinct part of the flow of product, you need to account for those moves--IN and OUT--separately. In my experience, the ideal way to do that is to use a database in which, day-by-day, you record stock movement IN or OUT of the inventory. As you're experiencing here, the end-of-week stock count by itself is of no use, maybe misleading, in projecting future use (outflow).
If I were doing it, I'd create a transactional database, as I suggested above. You must be keeping records anyway, I would think. So keeping them in Excel and then using Excel to summarize the in and out and calculate the inventory at the end of each week (which could also be verified periodically): that would be ideal.
Short of that, you could just modify your spreadsheet as shown below (I've attached the modified spreadsheet) to account separately for Out and In, and then use the "Out" numbers alone as the basis for your projections. This gives you far more useful information in general, since you can also see patterns for both In and Out.....You can see also the formula in cell G6, which calculates the week-end inventory based on last week's numbers, minus the Out, plus the In. Those calculations could be verified by on-site counts. It would be a way to improve your procedures overall, to make sure you are counting at each stage.
As I said, though, the idea from my point of view would be to have a database where each transaction--shipping out or receiving in--is noted. If you were to start doing that, then the numbers in each Out and In column could be derived from the database and this whole sheet automated. The work would be "at the front end" and "real-time."