I asked this before and perhaps there was not enough information, so I will
try again.

We are a wholesale distributor with 8 branches across Canada. We also have
a large quantity of product sitting in the wrong locations. I need to know
how to take that product and distribute to locations that are selling the
product.

The example that I gave is as follows, if more information is required, or
further explanation is needed I would like to know. This situation is
critical, and I would like as much input as I can get. So anyone out there
that can assist me would be wonderful. Thank you.

Example:

Whse 1 Product: XYZ 30 each This is all in overstock for this
warehouse.

Whse 2 Same Product has 10 each in inventory and Usage is 5 over the
past 3 months.

Whse 5 Same Product has 2 each in inventory and usage is 10 over the past
3 months.

I would like to move the 30 from warehouse 1 to warehouse 2 and 5 with the
appropriate quantities according to their on hand and usage.

I also have on the same spreadsheet:

Whse 1 Product ABC, with 200 in overstock.

Whse 3 Same Product has 300 in stock and usage of 100 over the past 3
months.

Whse 6 Same Product has 100 in stock and usage of 100 over the past 3
months.

and so on.

I need help on creating a formula that would do the bulk of the distribution
for me, taking into account the usage over the past few months, and the
quantity already on hand.

I am working with Excel 2003, and if anyone has any tips I would appreciate
it.

Thank you again.

What signifies that the product is "in overstock," or is that purely a
function of no/low sales for that product?

It would also help to know how your data is structure within the worksheet.
If it's in an external datasource, knowing about that would help.

How many months of supply does each warehouse need to have? In the second
example you state Whse 3 has 300 in stock and usage of 100 over 3 months, so
a 9 month supply. How much SHOULD they have and how much should be relocated?


"Barbara" wrote:

I asked this before and perhaps there was not enough information, so I will
try again.

We are a wholesale distributor with 8 branches across Canada. We also have
a large quantity of product sitting in the wrong locations. I need to know
how to take that product and distribute to locations that are selling the
product.

The example that I gave is as follows, if more information is required, or
further explanation is needed I would like to know. This situation is
critical, and I would like as much input as I can get. So anyone out there
that can assist me would be wonderful. Thank you.

Example:

Whse 1 Product: XYZ 30 each This is all in overstock for this
warehouse.

Whse 2 Same Product has 10 each in inventory and Usage is 5 over the
past 3 months.

Whse 5 Same Product has 2 each in inventory and usage is 10 over the past
3 months.

I would like to move the 30 from warehouse 1 to warehouse 2 and 5 with the
appropriate quantities according to their on hand and usage.

I also have on the same spreadsheet:

Whse 1 Product ABC, with 200 in overstock.

Whse 3 Same Product has 300 in stock and usage of 100 over the past 3
months.

Whse 6 Same Product has 100 in stock and usage of 100 over the past 3
months.

and so on.

I need help on creating a formula that would do the bulk of the distribution
for me, taking into account the usage over the past few months, and the
quantity already on hand.

I am working with Excel 2003, and if anyone has any tips I would appreciate
it.

Thank you again.

The product is "dead" in that warehouse because it has been designated as an
"E" item. No hits for the past 12 months.

The information that I have is downloaded to excel by way of Hyperion from
our ERP system.

I would generally have a report generated that tells me what all of my "E"
ranked items are. Another report is generated for all of the "A to D" ranked
items. This is then cross referenced to the first report. I can leave them
in two reports or I can merge the information together.

Right now we are not too concerned about the months suppy because our goal
is to move the stock to the area that is actually selling it.

The first time that I did this, it took everything on a first come, first
serve basis, so my warehouse 2 of course was stuck with most of the product.
So I would like to distribute the product a little more evenly.

My first thought was to add up all of the Usage rates and find a percentage
and use that percentage to distribute the "E" ranked items. But I was having
a difficult time figuring out how the formula would read to first find all of
the corresponding items, and then do the calculation for the percentage and
then do the distribution.

I had thought of using Macros, but that is my weak area of Excel.


"Duke Carey" wrote:

What signifies that the product is "in overstock," or is that purely a
function of no/low sales for that product?

It would also help to know how your data is structure within the worksheet.
If it's in an external datasource, knowing about that would help.

How many months of supply does each warehouse need to have? In the second
example you state Whse 3 has 300 in stock and usage of 100 over 3 months, so
a 9 month supply. How much SHOULD they have and how much should be relocated?


"Barbara" wrote:

I asked this before and perhaps there was not enough information, so I will
try again.

We are a wholesale distributor with 8 branches across Canada. We also have
a large quantity of product sitting in the wrong locations. I need to know
how to take that product and distribute to locations that are selling the
product.

The example that I gave is as follows, if more information is required, or
further explanation is needed I would like to know. This situation is
critical, and I would like as much input as I can get. So anyone out there
that can assist me would be wonderful. Thank you.

Example:

Whse 1 Product: XYZ 30 each This is all in overstock for this
warehouse.

Whse 2 Same Product has 10 each in inventory and Usage is 5 over the
past 3 months.

Whse 5 Same Product has 2 each in inventory and usage is 10 over the past
3 months.

I would like to move the 30 from warehouse 1 to warehouse 2 and 5 with the
appropriate quantities according to their on hand and usage.

I also have on the same spreadsheet:

Whse 1 Product ABC, with 200 in overstock.

Whse 3 Same Product has 300 in stock and usage of 100 over the past 3
months.

Whse 6 Same Product has 100 in stock and usage of 100 over the past 3
months.

and so on.

I need help on creating a formula that would do the bulk of the distribution
for me, taking into account the usage over the past few months, and the
quantity already on hand.

I am working with Excel 2003, and if anyone has any tips I would appreciate
it.

Thank you again.

Barbara -

While you know what is meant by A through D itmes and how they relate or
don't relate to E items, none of that means anything to me, or to others here.

I'd like to help you but, given the information you've offered, all I can do
is offer general suggestions that are more business-related than
Excel-related.

First you need to have some rules for determining what the target inventory
for a warehouse ought to be, by product. Without that step, the rest is
going to be pure guesswork. You may find that these rules vary by individual
product.

Next, on a product by product basis, for each of the 8 warehouses show the
current inventory and the target inventory. In an ideal case, you'll have
just a few simple moves from overstocked whses to understocked whses. But,
in real life, you'll find it is much more complicated, taking 10 from #1 and
5 from #2, then sending 7 to #3, 2 to #4 and 6 to #5.

That sort of apportionment is a very thorny logisitics problem.

Hope this helps at least a little

"Barbara" wrote:

The product is "dead" in that warehouse because it has been designated as an
"E" item. No hits for the past 12 months.

The information that I have is downloaded to excel by way of Hyperion from
our ERP system.

I would generally have a report generated that tells me what all of my "E"
ranked items are. Another report is generated for all of the "A to D" ranked
items. This is then cross referenced to the first report. I can leave them
in two reports or I can merge the information together.

Right now we are not too concerned about the months suppy because our goal
is to move the stock to the area that is actually selling it.

The first time that I did this, it took everything on a first come, first
serve basis, so my warehouse 2 of course was stuck with most of the product.
So I would like to distribute the product a little more evenly.

My first thought was to add up all of the Usage rates and find a percentage
and use that percentage to distribute the "E" ranked items. But I was having
a difficult time figuring out how the formula would read to first find all of
the corresponding items, and then do the calculation for the percentage and
then do the distribution.

I had thought of using Macros, but that is my weak area of Excel.