Calculating Inventory Outs
 

Need a little help: I have projected sales by item listed in 52 columns for the 52 weeks of the year. I also have a total available Finished Goods inventory column along with a column of Finished Goods on order and the date these Finished Goods will be coming in to our facility for each item. How can I create a formula that will sum the projected sales of each item starting with the current business week that I am in and then add each subsequent projected sales week and at the same time subtracting the inventory available against those summed projected sales to show me what month I may or may not run out before the next order of Finished Goods inventory comes into our facility. It would seem I need to create a formula that sums the sales and at the same time reduce the inventory by that summation in addtion to looking at the due dates of the Finished Goods coming in to our facility to see exactly when I will run out. This seems rather complicated. Any ideas would be greatly appreciated.

Regards

Gene

Calculating Inventory Outs
 

I would think you'd want to do something like this...

in row 2, you have your sales forecasts by month
in row 3 you have the expected additional inventory places in the dates you
expect them to be available
In row 4, you would calculate your total inventory

In column A, you would have your starting Inventory
In cell B4, you would type =A4-B2+B3
Copy this cell across the rst of the columns.

If your expected additonal inventory is in a format of dates down column A
and amonts along column B, highlight these fields, copy - go to a blank part
of the sheet, right click - paste special - Transpose. This will put the
inventory along a row instead of down a column.

HTH!

"Gene Haines" wrote:


Need a little help: I have projected sales by item listed in 52 columns
for the 52 weeks of the year. I also have a total available Finished
Goods inventory column along with a column of Finished Goods on order
and the date these Finished Goods will be coming in to our facility for
each item. How can I create a formula that will sum the projected sales
of each item starting with the current business week that I am in and
then add each subsequent projected sales week and at the same time
subtracting the inventory available against those summed projected
sales to show me what month I may or may not run out before the next
order of Finished Goods inventory comes into our facility. It would
seem I need to create a formula that sums the sales and at the same
time reduce the inventory by that summation in addtion to looking at
the due dates of the Finished Goods coming in to our facility to see
exactly when I will run out. This seems rather complicated. Any ideas
would be greatly appreciated.

Regards

Gene




--
Gene Haines

Sean: Thanks for your help. I will give this a try

Sean: This is what I am trying to do with approx 125 items.
Item # Mar-08 Apr-08 May-08 Current Inv Due Date Qty Due
1234 50 43 25 96 6/5/2008 50

Column A is item #, Column B thru D is Monthly projected Sales of each item, Column E is current inv of that item and F and G are due dates of inv coming in and the qty for that item. How can I create a formula in the column next to Qty Due that will calculate the follwing: Take the 96pcs of inventory and start subtracting out the 50,43,25 and let me know if I will run out before the next Due date. Meaing, can a formula be created that will display the month I might run out before I get in more inventory. Not sure if a formula could be created. Thanks

Gene