Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
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 "H0MELY" wrote in message ... | 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 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
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 "H0MELY" wrote in message ... | 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 | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
Consider setting a Mon, Wed, Fri delivery schedule and if that doesn't
work for a particular store, adjust that one individually. H0MELY wrote: 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 "H0MELY" wrote in message ... | 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 | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
Well, forget the last sentence but the rest of it makes sense. Why don't
you think about it? -- Jim "H0MELY" wrote in message ... | 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 | "H0MELY" wrote in message | ... | | 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 | | | | | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
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" wrote: Consider setting a Mon, Wed, Fri delivery schedule and if that doesn't work for a particular store, adjust that one individually. H0MELY wrote: 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 "H0MELY" wrote in message ... | 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 | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOGIC
the variables keep increasing ;-) I think in this case you will need to
"select" patterns first and then test those patterns against the data. H0MELY wrote: 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" wrote: Consider setting a Mon, Wed, Fri delivery schedule and if that doesn't work for a particular store, adjust that one individually. H0MELY wrote: 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 "H0MELY" wrote in message ... | 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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I Then logic help | Excel Worksheet Functions | |||
Logic please | Excel Discussion (Misc queries) | |||
If Then logic not enough | Excel Discussion (Misc queries) | |||
IRR Logic | Excel Worksheet Functions | |||
IF THEN LOGIC | Excel Discussion (Misc queries) |