LOGIC

[H0MELY]

Thank you for looking, I am stuck. Basically I have been asked to provide
some information regarding how well we are logistically supporting our
stores. Here is a brief synapsis...

Imagine there are 1000 stores that receive any number of 8 items from a
wharehouse. The thing is that these items are scheduled for delivery through
out the week based upon an "estimated" need. What that means is that store 1
may be receiving any number of the 8 products but they get them delivered on
different days. To compound the issue, store 2 may also get any number of
the 8 products delieverd...but their delivery days may be completely
different than store 1.

Delivery Fees work this way...there is a $30 trip fee charged for making a
trip to the store (no matter how many items are being delivered) and then
each item has its own delivery fee of $5. So if 1 item is delivered it costs
$35 for the trip (30+5). If 4 items are delivered it would cost $50
(30+(4*5)). As you can see the biggest expense is just having the delivery
person go out to the store.

I have been asked to take a look at the data and locate delivery days that
can potentially be adjusted t o bring costs down. Basically we are looking
for delivery locations that are having 1 (potentially 2) items delivered on a
given day that could have that item delivered as part of another trip the day
before or the day after. Obviously the goal here is to avoid the costly and
hopefully avoidable $30 trip charges. So here is where I am needing help
with my logic...

I only want to highlight these days...maybe prepare alist that tells me days
and items to take a look at. Certain locations need the ability to specify
certain frequency and day specific deliveries which is why I am wary of using
solver.

I am actually pretty easy when it comes to the application used..I just am
having difficulty geting at the data the way I would like. I have included
some data in .CSV format to model what I may see. Take a look at Store2 Item
5 & 7...these are two dates that I would be interested in because they are
teh only item being delivered on a particular day and store is not receiving
those items the day before or after. Tore3 Item 5 is another example.
Store 3 Item 7 is not a good example because even though item 7 is the only
item being delivered on Saturday...there is a scheduled delivery for Fridays
as well. This is an example of where a store needs to have the ability to
control their deliveries.

Again thank you for looking...any suggestions are welcome. Do not limit the
logic to needing to be done in excel...as long as I have a grasp on a logical
way to accomplish this I should be fine to use whatever application will
facillitate it.

Store,Item,Mon,Tue,Wed,Thu,Fri,Sat,Sun
Store1,Item1,Y,Y,Y,Y,Y,N,N
Store1,Item2,N,N,N,Y,N,N,N
Store1,Item3,N,N,Y,N,N,N,N
Store1,Item5,N,Y,N,N,Y,Y,N
Store1,Item6,N,N,N,N,Y,N,N
Store1,Item8,N,N,Y,N,N,N,N
Store2,Item1,Y,N,N,Y,N,N,N
Store2,Item2,Y,N,Y,N,N,N,N
Store2,Item3,N,N,N,Y,N,N,N
Store2,Item4,N,N,Y,N,N,N,N
Store2,Item5,N,Y,N,N,Y,N,N
Store2,Item6,N,N,N,N,Y,N,N
Store2,Item7,N,N,N,N,N,N,Y
Store3,Item1,Y,N,N,Y,N,N,N
Store3,Item3,Y,Y,N,Y,Y,N,N
Store3,Item4,N,N,N,Y,N,N,N
Store3,Item5,N,N,Y,N,N,N,N
Store3,Item7,N,Y,N,N,Y,Y,N
Store4,Item2,Y,N,N,N,Y,N,N
Store4,Item3,N,N,N,Y,N,N,N
Store4,Item6,N,N,Y,N,N,N,N
Store4,Item7,N,Y,N,N,Y,Y,N
Store4,Item8,N,N,N,N,Y,N,N

 

[Jim Rech]

Well, it's been a hundred years or so since I had a "Quant" class but I'd
say you don't have sufficient information. Since there is no "revenue lost"
data and all you want to do is minimize costs, clearly you will do that by
delaying deliveries as long as possible. The longer you delay the more you
save. In fact, going out of business would maximize your savings! Tell
your boss that that's your recommendation<g>.

--
Jim
| Thank you for looking, I am stuck. Basically I have been asked to provide
| some information regarding how well we are logistically supporting our
| stores. Here is a brief synapsis...
|
| Imagine there are 1000 stores that receive any number of 8 items from a
| wharehouse. The thing is that these items are scheduled for delivery
through
| out the week based upon an "estimated" need. What that means is that
store 1
| may be receiving any number of the 8 products but they get them delivered
on
| different days. To compound the issue, store 2 may also get any number of
| the 8 products delieverd...but their delivery days may be completely
| different than store 1.
|
| Delivery Fees work this way...there is a $30 trip fee charged for making a
| trip to the store (no matter how many items are being delivered) and then
| each item has its own delivery fee of $5. So if 1 item is delivered it
costs
| $35 for the trip (30+5). If 4 items are delivered it would cost $50
| (30+(4*5)). As you can see the biggest expense is just having the
delivery
| person go out to the store.
|
| I have been asked to take a look at the data and locate delivery days that
| can potentially be adjusted t o bring costs down. Basically we are
looking
| for delivery locations that are having 1 (potentially 2) items delivered
on a
| given day that could have that item delivered as part of another trip the
day
| before or the day after. Obviously the goal here is to avoid the costly
and
| hopefully avoidable $30 trip charges. So here is where I am needing help
| with my logic...
|
| I only want to highlight these days...maybe prepare alist that tells me
days
| and items to take a look at. Certain locations need the ability to
specify
| certain frequency and day specific deliveries which is why I am wary of
using
| solver.
|
| I am actually pretty easy when it comes to the application used..I just am
| having difficulty geting at the data the way I would like. I have
included
| some data in .CSV format to model what I may see. Take a look at Store2
Item
| 5 & 7...these are two dates that I would be interested in because they are
| teh only item being delivered on a particular day and store is not
receiving
| those items the day before or after. Tore3 Item 5 is another example.
| Store 3 Item 7 is not a good example because even though item 7 is the
only
| item being delivered on Saturday...there is a scheduled delivery for
Fridays
| as well. This is an example of where a store needs to have the ability to
| control their deliveries.
|
| Again thank you for looking...any suggestions are welcome. Do not limit
the
| logic to needing to be done in excel...as long as I have a grasp on a
logical
| way to accomplish this I should be fine to use whatever application will
| facillitate it.
|
| Store,Item,Mon,Tue,Wed,Thu,Fri,Sat,Sun
| Store1,Item1,Y,Y,Y,Y,Y,N,N
| Store1,Item2,N,N,N,Y,N,N,N
| Store1,Item3,N,N,Y,N,N,N,N
| Store1,Item5,N,Y,N,N,Y,Y,N
| Store1,Item6,N,N,N,N,Y,N,N
| Store1,Item8,N,N,Y,N,N,N,N
| Store2,Item1,Y,N,N,Y,N,N,N
| Store2,Item2,Y,N,Y,N,N,N,N
| Store2,Item3,N,N,N,Y,N,N,N
| Store2,Item4,N,N,Y,N,N,N,N
| Store2,Item5,N,Y,N,N,Y,N,N
| Store2,Item6,N,N,N,N,Y,N,N
| Store2,Item7,N,N,N,N,N,N,Y
| Store3,Item1,Y,N,N,Y,N,N,N
| Store3,Item3,Y,Y,N,Y,Y,N,N
| Store3,Item4,N,N,N,Y,N,N,N
| Store3,Item5,N,N,Y,N,N,N,N
| Store3,Item7,N,Y,N,N,Y,Y,N
| Store4,Item2,Y,N,N,N,Y,N,N
| Store4,Item3,N,N,N,Y,N,N,N
| Store4,Item6,N,N,Y,N,N,N,N
| Store4,Item7,N,Y,N,N,Y,Y,N
| Store4,Item8,N,N,N,N,Y,N,N
|

 

[H0MELY]

That wasn't helpful...or funny.

Well, it's been a hundred years or so since I had a "Quant" class but I'd
say you don't have sufficient information. Since there is no "revenue lost"
data and all you want to do is minimize costs, clearly you will do that by
delaying deliveries as long as possible. The longer you delay the more you
save. In fact, going out of business would maximize your savings! Tell
your boss that that's your recommendation<g>.

--
Jim
| Thank you for looking, I am stuck. Basically I have been asked to provide
| some information regarding how well we are logistically supporting our
| stores. Here is a brief synapsis...
|
| Imagine there are 1000 stores that receive any number of 8 items from a
| wharehouse. The thing is that these items are scheduled for delivery
through
| out the week based upon an "estimated" need. What that means is that
store 1
| may be receiving any number of the 8 products but they get them delivered
on
| different days. To compound the issue, store 2 may also get any number of
| the 8 products delieverd...but their delivery days may be completely
| different than store 1.
|
| Delivery Fees work this way...there is a $30 trip fee charged for making a
| trip to the store (no matter how many items are being delivered) and then
| each item has its own delivery fee of $5. So if 1 item is delivered it
costs
| $35 for the trip (30+5). If 4 items are delivered it would cost $50
| (30+(4*5)). As you can see the biggest expense is just having the
delivery
| person go out to the store.
|
| I have been asked to take a look at the data and locate delivery days that
| can potentially be adjusted t o bring costs down. Basically we are
looking
| for delivery locations that are having 1 (potentially 2) items delivered
on a
| given day that could have that item delivered as part of another trip the
day
| before or the day after. Obviously the goal here is to avoid the costly
and
| hopefully avoidable $30 trip charges. So here is where I am needing help
| with my logic...
|
| I only want to highlight these days...maybe prepare alist that tells me
days
| and items to take a look at. Certain locations need the ability to
specify
| certain frequency and day specific deliveries which is why I am wary of
using
| solver.
|
| I am actually pretty easy when it comes to the application used..I just am
| having difficulty geting at the data the way I would like. I have
included
| some data in .CSV format to model what I may see. Take a look at Store2
Item
| 5 & 7...these are two dates that I would be interested in because they are
| teh only item being delivered on a particular day and store is not
receiving
| those items the day before or after. Tore3 Item 5 is another example.
| Store 3 Item 7 is not a good example because even though item 7 is the
only
| item being delivered on Saturday...there is a scheduled delivery for
Fridays
| as well. This is an example of where a store needs to have the ability to
| control their deliveries.
|
| Again thank you for looking...any suggestions are welcome. Do not limit
the
| logic to needing to be done in excel...as long as I have a grasp on a
logical
| way to accomplish this I should be fine to use whatever application will
| facillitate it.
|
| Store,Item,Mon,Tue,Wed,Thu,Fri,Sat,Sun
| Store1,Item1,Y,Y,Y,Y,Y,N,N
| Store1,Item2,N,N,N,Y,N,N,N
| Store1,Item3,N,N,Y,N,N,N,N
| Store1,Item5,N,Y,N,N,Y,Y,N
| Store1,Item6,N,N,N,N,Y,N,N
| Store1,Item8,N,N,Y,N,N,N,N
| Store2,Item1,Y,N,N,Y,N,N,N
| Store2,Item2,Y,N,Y,N,N,N,N
| Store2,Item3,N,N,N,Y,N,N,N
| Store2,Item4,N,N,Y,N,N,N,N
| Store2,Item5,N,Y,N,N,Y,N,N
| Store2,Item6,N,N,N,N,Y,N,N
| Store2,Item7,N,N,N,N,N,N,Y
| Store3,Item1,Y,N,N,Y,N,N,N
| Store3,Item3,Y,Y,N,Y,Y,N,N
| Store3,Item4,N,N,N,Y,N,N,N
| Store3,Item5,N,N,Y,N,N,N,N
| Store3,Item7,N,Y,N,N,Y,Y,N
| Store4,Item2,Y,N,N,N,Y,N,N
| Store4,Item3,N,N,N,Y,N,N,N
| Store4,Item6,N,N,Y,N,N,N,N
| Store4,Item7,N,Y,N,N,Y,Y,N
| Store4,Item8,N,N,N,N,Y,N,N
|

 

[Bob I]

Consider setting a Mon, Wed, Fri delivery schedule and if that doesn't
work for a particular store, adjust that one individually.

 

[Jim Rech]

Well, forget the last sentence but the rest of it makes sense. Why don't
you think about it?

--
Jim
| That wasn't helpful...or funny.
|
| "Jim Rech" wrote:
|
| > Well, it's been a hundred years or so since I had a "Quant" class but
I'd
| > say you don't have sufficient information. Since there is no "revenue
lost"
| > data and all you want to do is minimize costs, clearly you will do that
by
| > delaying deliveries as long as possible. The longer you delay the more
you
| > save. In fact, going out of business would maximize your savings! Tell
| > your boss that that's your recommendation<g>.
| >
| > --
| > Jim
| > | > | Thank you for looking, I am stuck. Basically I have been asked to
provide
| > | some information regarding how well we are logistically supporting our
| > | stores. Here is a brief synapsis...
| > |
| > | Imagine there are 1000 stores that receive any number of 8 items from
a
| > | wharehouse. The thing is that these items are scheduled for delivery
| > through
| > | out the week based upon an "estimated" need. What that means is that
| > store 1
| > | may be receiving any number of the 8 products but they get them
delivered
| > on
| > | different days. To compound the issue, store 2 may also get any
number of
| > | the 8 products delieverd...but their delivery days may be completely
| > | different than store 1.
| > |
| > | Delivery Fees work this way...there is a $30 trip fee charged for
making a
| > | trip to the store (no matter how many items are being delivered) and
then
| > | each item has its own delivery fee of $5. So if 1 item is delivered
it
| > costs
| > | $35 for the trip (30+5). If 4 items are delivered it would cost $50
| > | (30+(4*5)). As you can see the biggest expense is just having the
| > delivery
| > | person go out to the store.
| > |
| > | I have been asked to take a look at the data and locate delivery days
that
| > | can potentially be adjusted t o bring costs down. Basically we are
| > looking
| > | for delivery locations that are having 1 (potentially 2) items
delivered
| > on a
| > | given day that could have that item delivered as part of another trip
the
| > day
| > | before or the day after. Obviously the goal here is to avoid the
costly
| > and
| > | hopefully avoidable $30 trip charges. So here is where I am needing
help
| > | with my logic...
| > |
| > | I only want to highlight these days...maybe prepare alist that tells
me
| > days
| > | and items to take a look at. Certain locations need the ability to
| > specify
| > | certain frequency and day specific deliveries which is why I am wary
of
| > using
| > | solver.
| > |
| > | I am actually pretty easy when it comes to the application used..I
just am
| > | having difficulty geting at the data the way I would like. I have
| > included
| > | some data in .CSV format to model what I may see. Take a look at
Store2
| > Item
| > | 5 & 7...these are two dates that I would be interested in because they
are
| > | teh only item being delivered on a particular day and store is not
| > receiving
| > | those items the day before or after. Tore3 Item 5 is another example.
| > | Store 3 Item 7 is not a good example because even though item 7 is the
| > only
| > | item being delivered on Saturday...there is a scheduled delivery for
| > Fridays
| > | as well. This is an example of where a store needs to have the
ability to
| > | control their deliveries.
| > |
| > | Again thank you for looking...any suggestions are welcome. Do not
limit
| > the
| > | logic to needing to be done in excel...as long as I have a grasp on a
| > logical
| > | way to accomplish this I should be fine to use whatever application
will
| > | facillitate it.
| > |
| > | Store,Item,Mon,Tue,Wed,Thu,Fri,Sat,Sun
| > | Store1,Item1,Y,Y,Y,Y,Y,N,N
| > | Store1,Item2,N,N,N,Y,N,N,N
| > | Store1,Item3,N,N,Y,N,N,N,N
| > | Store1,Item5,N,Y,N,N,Y,Y,N
| > | Store1,Item6,N,N,N,N,Y,N,N
| > | Store1,Item8,N,N,Y,N,N,N,N
| > | Store2,Item1,Y,N,N,Y,N,N,N
| > | Store2,Item2,Y,N,Y,N,N,N,N
| > | Store2,Item3,N,N,N,Y,N,N,N
| > | Store2,Item4,N,N,Y,N,N,N,N
| > | Store2,Item5,N,Y,N,N,Y,N,N
| > | Store2,Item6,N,N,N,N,Y,N,N
| > | Store2,Item7,N,N,N,N,N,N,Y
| > | Store3,Item1,Y,N,N,Y,N,N,N
| > | Store3,Item3,Y,Y,N,Y,Y,N,N
| > | Store3,Item4,N,N,N,Y,N,N,N
| > | Store3,Item5,N,N,Y,N,N,N,N
| > | Store3,Item7,N,Y,N,N,Y,Y,N
| > | Store4,Item2,Y,N,N,N,Y,N,N
| > | Store4,Item3,N,N,N,Y,N,N,N
| > | Store4,Item6,N,N,Y,N,N,N,N
| > | Store4,Item7,N,Y,N,N,Y,Y,N
| > | Store4,Item8,N,N,N,N,Y,N,N
| > |
| >
| >
| >

 

[H0MELY]

Bob, thank you for the response. Unfortunately I do not actually have the
ability to make or even recommend any changes...mainly because many people
have invested a great deal of time and effort in to setting up a logistics
schedule for these items.

I suppose it may also help you to know that the example I gave relates to
what I am trying to accomplish but it is not exactly real world data. I tend
to scare people when I give them real information. The number of stores is
actually closer to 29K in over 20 states. There are only 8 items, but the 8
items deal with monetary items (deposits and cash deliveries). That should
hopefully explain why a Monday Wednesday Friday schedule wouldn't work.

Now during spot reviews it has been recognized that a store may have a cash
delivery on say M,W,F and a cash pickup on Tusday only. What I would like to
do is isolate these types of instances where a particular event is happening
on a day where it is the only reason for the trip and could potentially be
moved to a corresponding date when the store is having another event taking
place. Hence wanting the ability to compare the day before and teh day after
to see if they are making a trip for the same reason.

I went into this thinking Solver could take care of it for me...the problem
is that I am not thinking that I can group the 29K stores and take into
account the contraint of trying to avoid a store specific trip fee. Oh, and
the $30 trip fee and $5 item fee are not exactly accurate either.

Thanks again for taking a look at this. I am generally pretty good at
getting at my data, I am just falling down and getting the logic right for
this one.

-John

 

[Bob I]

the variables keep increasing ;-) I think in this case you will need to
"select" patterns first and then test those patterns against the data.