Quote:
Originally Posted by 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" 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: 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