Backorder brainstorming
I feel that
backorder tracking is one of the most important part of my job.
In fact, if you go
and look at indeed, procurement is basically one of the hottest job
there. Some job titles includes purchasing manager/coordinator,
Material Planner, Logistic assistance – Procurement...etc.
A lot of time, it
requires some planning, data analyzing, even some vendor negotiating.
I am not saying that it’s an exciting job. I am simply saying
that it’s an important job in any organization.
My company has made
streamlined the process to a point that, nobody talks about it.
Which is really perplexing to me.
I consider my main
job as data collector for the company. The reason being that my team
manages at least three google sheet everyone is relying upon. The
first is Daily Summary, basically our record of shipment. The second
is Product Sort, basically a receiving record of shipments from
Vendor. The third one is Requested List for BJ, basically a
Backorder record. There are other sheets we are collecting and use.
But those three are the most essential. We cannot operate with them,
especially for the Order team and Customer service. But as you can
see, for the shipping and receiving, we should be able to get from
QB. And for a lot of times, we’ll get S&R info from QB. But
QB does not have lot numbers. QB does not really say why something
was not shipped. We would note why something was not shipped in
Daily Summary. There are notes from vendor about the product we
received that were not noted in QB, especially when QB really does
not have an effective way to receive products.
The Requested List
for BJ, our third sheet is so vital to our operation that, if we
forgot to put an out of stock item in the list, we would forgot about
it for 3-4 weeks until customer inquired about ETA of their items.
Then, we would sometimes made up excuses and let them wait another
2-3 weeks (hopefully), and put a rush order to vendor. The other
thing is the RL is not linked to Product Sort, our receiving record.
So we have no idea if the items we requested was fulfilled by the
vendor. It was last week that David inquired about 2 items, which we
requested, but vendor never sent to us. That order was almost 2
months ago.
I know that Chandara
made a sheet to tracking some of the orders, at Kevin’s request.
But it was a very tedious job to compare the receiving list and
backorder list. And I feel it’s a highly ineffective way of doing
things.
One of the hurdles
is that those 2 so called “lists” are not really lists, at least
not a “whole: list. Their setup reminded me of “Forms”. At
Mascon, Annie used to give us those forms with receiving information
on them, printed on papers. They usually contains shipment
information, like tracking, how many boxes, how many items to
receive. It also contains a date of shipping and a date of
receiving. So in the google sheet, each shipment is a range of it’s
own. They are visually separated, very easy to read which shipment
contain which item. But it’s not a database, or a whole list. The
RL is a very similar setup. But it separates mostly by date we put
the request in.
I am wondering if we
could setup a form in the first page, then setup a database in the
second page by collecting the first page. This is the whole purpose
of the whole writing. LOL.
I tried to compare
the receiving list to the backorder for a while per Kevin’s
request. I used countif function to compare. If the result is more
than 0, then I know a backorder is fulfilled. If the value is 0,
then we never requested the item. I do not know why vendor sent us
then.
That seemed easy
enough. However, I can not find a way to do it efficiently. It is
dumb. There must be a way to streamlined the process. And I feel
that it is so dumb. I stopped doing it. It’s dumb that throughout
a whole year, we might request one items a few times, and I have to
mark the item everything I did the comparison. Otherwise, I am not
sure which requests get fulfilled. Sure, if I am persistent enough,
this might work. But I am not. Because I keep thinking there must
be a better way. A better way to compare, a better way to mark which
item is fulfilled, a better way to track if vendor sent us something
we did not requested, and a better way to find which item vendor
forgot to ship.
The other thing
about Request List is that I need to know the standard size or format
for the item before I could request. There are couple times where,
the item is in lyophilized form, and the size was a micron gram or
mg, but I did not know. I simply used a standard requested size,
1ml, which implied a liquid format with buffer. But I think I can
resolve it by extracting info from product information sheet.
The most important
thing is how to link the Receiving list and Backorder. No, the most
important thing is to make the two lists into 2 complete datasets. I
do have a list of compiled Receiving list, I used for drop down menu
for lots in my pick list, which I used for picking.