I am working on a spreadsheet to determine when an item will be sold by, based on historical sales data. With access to sales history I am able to find how many units are sold on average per month and I want to be able to take this average sales information to project when individual lots of items will be sold.
For instance, we might receive a shipment of light bulbs on January 1st, March 15th & May 3rd. The goal is to estimate, based on the average monthly sales, when all of the light bulbs received on Jan. 1 will be completely sold by. Then when the March shipment will be depleted, then the May shipment. We are selling these items in order of when they arrived, so the March 15th shipment will not be put on the stock shelf until all of the January 1st bulbs are gone.
We have many items so we will be able to utilize part numbers which can roll down the forecasted sell through.
Since this is so dynamic I believe this will have to incorporate some sort of macro and/or array to facilitate rolling the forecast into the items.
Below is an example of how I would like to calculate column D, based on another tab calculating the average sales of a given item.
Today is 10/11/2016
On average 50 light bulbs are sold each month
<tbody>
</tbody>
Note how on within the equation, it would need to be estimated based on sales, that LOT #2 could not start having sales happen until LOT #1 is completely sold.
This one has me stumped on how to make this work. Any help is greatly appreciated.
For instance, we might receive a shipment of light bulbs on January 1st, March 15th & May 3rd. The goal is to estimate, based on the average monthly sales, when all of the light bulbs received on Jan. 1 will be completely sold by. Then when the March shipment will be depleted, then the May shipment. We are selling these items in order of when they arrived, so the March 15th shipment will not be put on the stock shelf until all of the January 1st bulbs are gone.
We have many items so we will be able to utilize part numbers which can roll down the forecasted sell through.
Since this is so dynamic I believe this will have to incorporate some sort of macro and/or array to facilitate rolling the forecast into the items.
Below is an example of how I would like to calculate column D, based on another tab calculating the average sales of a given item.
Today is 10/11/2016
On average 50 light bulbs are sold each month
| Part # | LOT Received Date | # of items on hand | =f(x) Est. Sold by Date |
| Light bulbs LOT 1 | 1/1/2016 | 50 | 11/11/2016 |
| Light bulbs LOT 2 | 3/15/2016 | 75 | 12/26/2016 |
| Light bulbs LOT 3 | 5/3/2016 | 100 | 2/24/2017 |
<tbody>
</tbody>
Note how on within the equation, it would need to be estimated based on sales, that LOT #2 could not start having sales happen until LOT #1 is completely sold.
This one has me stumped on how to make this work. Any help is greatly appreciated.
