Trying to streamline a work task involving shipping product from the closest warehouse to the address of the customer

Question

At my job, I work with an Excel spreadsheet that has a few columns with customer information, most importantly their state, and then another 10+ columns representing each warehouse we have, with the current available inventory of the product the customer is trying to purchase. There is one column between these two groupings that contains an index/match formula (I think, I'd have to check again tomorrow) that effectively goes through the totals of the warehouses and then returns the warehouse with the highest inventory.

This process has worked without fail for a long time, but it still requires that someone manually look over each line to see if there are better options, like if the formula chooses the Florida warehouse for the customer that lives in Oregon, but there is plenty of stock in the California warehouse, so while the formula chooses Florida, common sense chooses California.

My question is if there is a way to, for each state, designate a sort of priority to go through with the warehouses? For example, if a customer orders something from California, is there a way to make excel look through our warehouses in a certain order: first the California warehouse, then the Texas warehouse, then the Georgia warehouse, and so on and so forth, and then return the inventory of the first viable warehouse? Or perhaps is there another method I'm not thinking of with which to approach this issue?

What It Does vs What I Would Like

I'm just trying to make my job a little easier and quicker. Any help greatly appreciated.

Answer 1

You have two problems to solve for this (at least, but two major ones):

1. Deciding shipping time from warehouses to states
2. Limiting evaluated choices to only those warehouses with sufficient inventory to ship the order

The first can be solved in several ways, two of which stand out. The second seems at first glance an easy one but actually is far harder if one's major goal is a more automatic solution that thereby requires less intervention.

The second one first. The first pass would be to create a sublist of warehouses that can satisfy the order inside your formula and this would be very simple. You would then apply the first problem's solution to decide from the sublist which warehouse to ship from. You might actually consider this to be a fully adequate improvement and worry about further considerations if they become a problem (or put another way, "of interest" the way your stated problem has become).

If so, I would suggest not only creating the direct solution of creating the sublist and applying the first problem's solution to it, but also add in an easy way to manually influence/override the formula's result. For example, have a column in which simply typing something triggers an IF() in the sublist creation to either choose, or to ignore, one warehouse or another. So if it has an entry, perhaps 1-10 (if there are exactly 10 warehouses), the formula chooses the designated warehouse, but if blank, it evaluates as planned. This way, manual override is easy and can be easily reset by clearing the entry, or the whole column.

However, you might begin to notice, say, that 12 orders choose the CA warehouse and while it can satisfy any particular order, or even 11 of them, it hasn't enough stock to satisfy ALL of the orders being evaluated at that time. Solving this would take a great deal more work and the devil would be in the details: your, or your supervisor's proclivities and common sense, and whatever constraints one wished to work with to solve the "min/max" problem one would be seeing. Without an actual set of information and interaction with you answering questions as they arose, this would not be something anyone would spend time on as there's no chance of just happening to create a desirable, to you, solution.

But one can definitely do the first pass, then see how much further work is needed to make it achieve one's goals, then and only then, spending the effort to further improve it.

For the first problem, there would be quite a few ways to approach the problem of defining shipping times from each warehouse and therefore the fastest one to choose. (One assumes THAT, shipping time not distance, and certainly not the next level up: warehouse inventory levels and shifting between them to accommodate experienced shipment patterns), is the goal of this spreadsheet.)

The simplest would be to go to UPS or FedEx and find one of their "Shipping Days" charts. (Remember: shipping TIME, not distance. Distance is surely not the concern, rather time to arrive and therefore customer satisfaction.) They have a pretty simple concept. They are set up by entering a Zip Code and the map generates. Just go to https://www.ups.com/maps/results?loc=en_US# and enter the Zip Codes for each warehouse, one by one, getting their maps, and creating a small database for how many days to ship to each state from each warehouse. Looks to me like about five minutes of work for each. This little Table in your spreadsheet would then be the source of shipping time from each warehouse to any state.

You would use the sublist of warehouses that can satisfy each order created by the formula to lookup the shipping times. The formula would then pick the MIN() time from the sublist and that would be the warehouse that ships the order.

To be sure, there would be a small amount of decision-making in creating the Table in that some states are a wee bit complicated. For instance, the highly populated tropical-ish east Texas region (Dallas, Houston, San Antonio, etc.) is faster to deliver to than the more sparsely populated western desert. You would have to weigh that imperfection against the ease of doing this approach and the value of much reduced manual effort and make choices. See what I mean about you and your supervisor's proclivities and common sense along with business details only you know, not us?

If such a NOT-finely grained solution only satisfies for a time, UPS and so on, along with folks like Google Maps and a thousand companies that deal in Zip Code information and use daily, surely offer interaction, certainly for a fee for most, that would let you read their data for the two Zip Codes involved, via websites or online portals, and come back with a distance. But that would not just cost money, likely, and take research, but it would get back a distance, not a shipping time.

My bet is the first approach above would end up being the most "satisfying." A walk before run approach that could later be modified. A modification one might make, one day, could be to create a Table that has "faux states" in it, not just legal entities. So you'd have East Texas and West Texas. That kind of thing. You would need a further lookup and supporting Table (though the two could be combined, either immediately or as a further "start running instead of walking" improvement someday) to generate the "faux states" from Zip Codes (actually, from just the first three digits of the Zip Codes).

Keeping that in mind, it WOULD be worth laying the formula used to accommodate future changes going along that path so edits are needed, not wholesale revamping. NOT to do all the work for the "run not walk" solution of someday, just to think how you'd apply it someday and choose the form of the formula to suit adding that rather than having to someday rethink the whole thing.

As stated in current Comments, we simply can't anticipate enough to even write formulas for you as we flat don't know at all how you work, are willing to work, want to work, and the upgrade path that suits your circumstances. So the best we can offer is an approach with hints at the larger concerns you need to keep in mind.

It seems appropriate to next mention that in my personal opinion, once one has made and successfully used an approach via Excel, worked out the bugs, so to speak, and made it a smooth part of the operation, the next step, for those able to get the resources (time and money) to do it, is to present it to the software company that currently provides you your basic business handling software. With 10+ warehouses, you SURELY have one. Ask it to code your need into their software, or to provide dedicated data service from it to Excel, so that you get out of using a spreadsheet for a very important aspect of your business. Not every business can do this, I know, my boss has a limit for it he can stomach (you know, that "money" thing). But with a national scope and 10+ warehouses, you have a real need to be "there" someday so keep that in mind.

By the way, many of these issues would be far more easily, faster, and more robustly solved via formal programming, even if it were a standalone program that worked from reports your regular business software provides in, say, Excel or CSV format. (Though I'd avoid "standalone developers" for something involving your scale of business.)