Forum Discussion
Inventory Forecast Formula.
Hi all,
I am trying to find the right formula to forecast inventory movement.
I get a daily update of stock units via email from our warehouse. I have collected the data from every Friday date into a single sheet. However when I use the Forecast tool under the Data tab, it does not reflect our use as the stock count includes any inventory replenishments in the total, which causes an upwards curve when forecasting.
Is there a formula I can use that can show projected inventory usage without being affected by the spikes in Inventory count.
Here is an image for example
I have been searching for the right formula but I have not been able to find the right one.
Thank you in advance!
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."
- An image, sadly, doesn't really show much more than your words told us. What part of those numbers, for example, is the replenishment that you don't want to include in your forecast? That's just one question that still needs to be answered.
Also helpful would be if you could attach a copy of the actual spreadsheet, rather than just an image, along with a more complete description. If some of the data are confidential or proprietary, give us a reasonable facsimile (still in Excel) of what the crucial numbers look like, and what's what in them.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."
