Calculating Inventory Outs

[Gene Haines]

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

 

[Sean Timmons]

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]

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

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