My first boss called me. “I need you to report on expedites by letter code,” he said, “For all facilities. Can you do that?”

“Yeah, I should be able to do that. Our facility will be the hardest, of course, but you just add a query in there to reconcile the codes… yeah I can get that pretty easily.” For some perverse reason I cannot explain, our facility did not use the letter code field to assign letter codes, but an extra text field. The letter codes represent how important it is to keep the product in stock. It is a simple matter to use a query to select between two different fields depending on the value of another field, although I think I am still the only one on our entire team who has gotten a handle on this trick. (LetterCode:IIf([Facility]=”Us”,[CustomCode],[NormalCode]) — there you go.)

It was such an easy addition that while I was at it I added that would help anyone digging deep into the report cross reference the facts. Then I reorganized it. Then I cleaned it up some and sent it to my boss–a little overtime, but not too bad and a nice straightforward report. I told him if he liked it I would send it to everyone else.

The next day he called me. “When are you going to get that report done?” It would not be the first time he did not see something I sent him. “Uhh…. I am pretty sure I sent it to you, let me check…”

“I saw the one for this month. What about the last six months?”

“Oh sorry I did not make a note that it was supposed to be for six months, I can get that.”

“Okay, good, do that.”

“So, you are happy with how it looks, then?”

“Well, let me look at it…” I tried to deduce how he had seen the one month version without looking at it but I failed. “What is this column with the symbol on it?” he asked.

“That is for item numbers that don’t have a letter code,” I said.

“There’s a lot of them. Can you get them into buckets with the letter codes?”

“I’m not sure I can. I’m just pulling out the data that is there. I don’t know where else to get the data from.”

“Yeah but can you just break them up into groups by the numbers?”

“You mean by volume?” Letter codes are generally calculated from the volume of business done on the item. “I guess I could probably do that. Yeah, I can just get the volume from the shipments history. Letter code is by site, though, that could take a little while to run. Not that I can’t do it, it will just take some time.”

“How about if you just took the numbers here and just split them up proportionately?”

“You mean the volume of expedites? Wait, clarify this for me. Are we trying to get the number of expedites that were filed for each class of item, or are we trying to classify the items by how frequently they get expedited?”

“No, we just want to use the letter codes that are already there, but you should be able to correlate from the letter code and just break out the number of unknowns by percent.”

“Okay, I’m not sure that would work. Because we could get a lot of expedites on the highest volume items just because people order them a lot so there’s more traffic, or we could get a lot of expedites on the lowest volume items because it takes us longer to ship them so people get more impatient. So I don’t know we can say there’s a correlation between the shipment volume and the expedite volume.”

“So what do we need to do?”

“Well, if you want to know by the expedite volume I can ignore the letter code and just bucket the part numbers by number of expedites. You just tell me what buckets you want me in and I can do that. Or if you want it by volume of shipments I will have to calculate the yearly values and then work from there.”

“What if we just take the number that is already there? I think it would be pretty close. Just work them into the other buckets.”

“No, that’s two completely different things. Either we can go by volume of shipment or by volume of expedites. Tell me what you want to know, what you are going to use it for, and I can figure out how to do it.”

“Well… can we do both?”

“No.”

“I just don’t want you to have to spend a lot of time on this. I don’t want it to be taking all of your time. So we just need to figure out what we can do.”

“Let’s start by clarifying what you want and I can figure out my options from there. Do you want them bucketed by letter code or by expedite volume?”

“Which is easier?”

At this point I about choked. Would you want to know the capital of France or the capital of Belize, whichever was easier? They are two different pieces of information. You want to know one or you want to know the other. Maybe even you decide that you need to know both. But if you just run with whichever is easier–what useful conclusions are you going to draw once I supply the information?

“I am pretty sure I can do it either way, I just need to know what you want so I can figure out how to do it.”

“That’s fine, we can do it whichever way, I just don’t want it to take all of your time and be a huge project. And I need it to be repeatable. Wouldn’t it just be easier to break these numbers up into buckets?”

“I would rather not be messing around with numbers on the spreadsheet, especially if you want it to be repeatable. I hate the other report that I have to work out on a spreadsheet.”

“Yeah, I’m not sure all of the time spent on that other report is value-added.” Nevermind that on the other report I at least have a very clear idea of what data I am deriving, even if it takes me longer than I’d like.

“So what do you want to do for this report?”

“Can you just take these ones with no letter code and add them into the other buckets?”

“I can do that. I am just… not comfortable with the accuracy of the data that would result.” Meaning I will do that if you order me to, but you are going to have to be explicit.

“I think it will be pretty good. Good enough for what we need.”

“Okay. Tell me what buckets you want. What percent for each bucket, I mean.”

“Well, let’s see, if you take this number… that’s about… okay and add that… hmm….”

“How about this? Want me to come up with a solution and then check it with you?”

Pause.

“Okay that’s fine.”

I decided I would just do the work to dig up the letter codes for all facilities. I started refreshing my tables to make sure I didn’t have blanks from missing data. That didn’t go quickly, and I didn’t have a connection to all facilities. So I investigated how to connect to one of the other facilities. Meanwhile I was letting the biggest dataset, for the distribution warehouse, refresh. I messed up the new connection a couple of times but finally got it running. However the warehouse still hadn’t refreshed.

I let it go for hours, thinking sometime it would finally pop up done. It didn’t. Then I remember that at headquarters they got a nightly export of all the items lists. I asked for that to be mailed to me, but even as a zipped plain text file it was too big to pass the size limits on our e-mail servers. I asked my contact to trim it down to just the items I needed, but the rather vague assent made me fairly certain I would not see the file.

A little later I realized I could just connect remotely to the headquarters computer and pass myself the file. This network “live” connection (rather than by e-mail relay) is always much slower than e-mailing, but I figured it wouldn’t absolutely refuse to transfer the file. And it didn’t. It just asked for 90 minutes to do it, which was more time than I had left in the day.

So I also opened a help-desk ticket to ask that the file be sent by FTP to my local servers as well as the headquarters servers on the nightly batch.

The next morning I had the file. I thought I would just be able to drop it into my existing table but it turned out to be excessively troublesome, and it was a while before I got it loaded. Also my slave system that I was going to use to do the analysis had borked and the other work it was supposed to have done was undone. And actually it had failed for several days. So I had to go and patch that all up. In the meantime I talked to my boss and told him I would work on the report as soon as I got the problems on the system sorted out.

At last I got everything in its place and I ran my report. But now my own facility came up completely blank. I had no clue why so I ran everything again. Still blank. Then I remembered that we had our own special field and I had just loaded in the standard table. So I copied all the data from our custom field into the standard field, even though I don’t like playing with my base data (you never know when a built-in assumption will become obsolete). I ran everything again. Still blank. Then I realized that I copied the blank (because not even pulled) custom field from the standard data into the letter code field. So I pulled down a new copy of my sites data and then pulled that into the data set for all sites (nearly blowing the two gig limit on Access databases in the meantime, so compacting all the databases to clean up on my sloppy work). I ran everything again. Still blank. Then I realized that I was using a query that automatically looked at the custom field for my facility, which was precisely what was missing from the standard data.

So I took out that one little query, which was the only thing I’d done so far that I liked, and ran it again, and it worked. Only, even with all the data showing I still had too many blanks. So it was right back to square one for all practical purposes.

I ran out simple query to get the total last-year volume by item by facility, which actually did not take very long after all. Especially compared to some of the other things I had been doing (or attempting) lately. Then I ran off that into another little table for the total volume for each facility so I could get the the percentage of each item out of the total, which I did next.

Then I realized that was dumb, because no single item was going to be 80% of the total volume. I needed cumulative or running totals, from the highest movers to the lowest. But I knew from past experience that trying to run a calculation against a sorted dataset was probably going to be a failure in Access, because the sorting is not applied until all the data is calculated. You get results but they aren’t what you thought you asked for.

Then I tried to hack together a nested SQL query that would cleverly force the data to sort, then accumulate the total record by record. I couldn’t quite work it out, though.

Then I realized I was a moron because I was only trying to order by a number, and all I had to do was tell the query to add up the total quantity for all items where the quantity was equal or greater than the current quantity. And I knew how to do that. (DSum(“Qty”,”Items”,”Facility='” & ThisFacility & “‘ And Qty<=" & ThisQty)–for sure nobody else on the team of data analyzers at this large multinational company knows that one.)

Then I started asking people what percentage I should actually break the buckets at. Not my first boss; no. I asked some people in the facility. They weren't sure so I asked the expert from the distribution warehouse. He gave me the percentages–of dollar value of shipments. I know for sure that all our letter codes were calculated primarily on unit shipments.

Then I called my second boss and asked him if he really wanted a funky amalgamation of everybody's different definition of a letter code plus whatever I made up for the rest. He said the expert's definition was wrong and the proper definition was primarily by units, but also accounted for number of shipments (not just number of pieces) as well as the value. And he said I should not worry about any of that but just get an approximate calculation by units shipped, and if people wanted a more sophisticated number they should fix their own letter codes. It took us about five minutes to reach an understanding, not half an hour to reach a truce.

Today I developed some new reports on how accurate our ship promise dates are. I sorted the accuracy into ranges and within the ranges I created we got all the way into five days late or ten days early before 80% of the lines were accounted for.

Most of these were original system-generated promises assigned when the order was booked. But when we start manually adjusting the promise dates things don’t get too much better. Our promise dates are not very stable. Some of this is due to unforseen events such as equipment breakdown. Some of it is vendors missing their promise dates to us, which is partly the hidden cost of low cost vendors.

Some of the problems are a little less obvious. We often hear that while we were waiting for component Y to become available, customer orders depleted component X, forcing us to repromise the product even if component Y came in on the original schedule. This sounds like a moron’s problem; why not lock up all the components needed so they can’t disappear? But we only hear about the problems. We don’t hear about all the times when a customer wanted some of part X and we shipped their order and replaced the inventory before it was ever missed. So how far out do you lock up your components?

Most sensibly, within the component lead time. But our software only supports a single time window for allocation of sales orders. We could also just immediately lock up any inventory that will be needed for a planned build, but if we don’t start hearing from our customers within a week of doing that, we will be hearing from our leadership by the end of the month when they want us to turn every scrap of inventory into invoicable shipments, and worry about the damage next month.

Further degrading our promise accuracy is that we don’t really know what we can build in the first place. That is, our system doesn’t tell us that, given the supply that is past due and the supply that is on schedule, n of unit A can be built today and p of unit B can be built tomorrow. It will tell us when we owe A; separately, it will tell us what we need to build A; separately, we can look up what is going on with those parts needed; but the system does not bring all of this information together. So even assuming we had accurate dates for when our vendors would deliver loaded into the system, that information is not taken into account to delivery, systematically, a straightforward build plan.

Throw in the factor that many components are required across a number of different models, and what often happens is just simple human oversight of some complicating factor that delays the production of the item to be promised.

On this last point I just started last week researching what kind of logic the system would have to apply to bring the whole picture together. But I am already gearing up to be told by our skeleton crew of IT that such a program is too resource-intensive. I mean, looking at all the interrelated layers of supply and demand–that’s a lot to ask of a production control system, isn’t it?

Hey! My company stock price doubled since year-end 2008! It’s not too far off from where it was year-end 2007!

SELL, BABY, SELL!

Orders are still going strong. I give it till Q3 before the music stops again.

I am working on something sneaky.

We have this thing called the hockey-stick effect where we sell most of our volume at the end of the month. The accumulation of orders is mild in the first three weeks of the month relative to the last month, hence the image of a hockey stick (laid on its side). Since the capacity of the factory to produce is more or less a straight line, this means that we get over our capacity at the end of the month (and especially, the end of the quarter), and we remain behind for a week or two. So even when orders are coming in at a lower level in the first weeks of the month, we are working our way through backlog.

It occurred to me that if we could give our customers a financial incentive to place their orders before they wanted them shipped, by about a month, we could more successfully build ahead and keep shipments moving. The problem turned out to be that the sales force has no incentive to get the customers to book orders in advance. That is, the sales force is not measured on whether the booked order ships on time (that’s the factory’s problem); they are actually paid on margin. Of course they are still pushed by their supervisors to meet sales targets by the end of the month, and especially the end of the quarter. So this means the sales teams hold out to keep their paychecks fat, then fire sell and the end of the month.

The discounting is mostly done on terms of payment, which I think still lets the salesmen keep their bonus (while hurting the company’s cash flow). To a considerable extent, it is just convenient for customers to order at the end of the month when they know our prices and terms will be as good as they get, and they know how much money they have left in their budgets. But whatever leverage the sales force has, it will not and cannot reserve that only for scheduled orders because they must make their sales targets. If we say you get discount X or terms Y for future-scheduling your orders, we will wind up giving the same terms with no strings attached except “book this month” when a financial period comes to a close behind target.

So how can I work an incentive in for future scheduled orders without rebuilding our entire sales strategy and corporate culture?

My idea is this. Do not allow customers to make any modifications whatsoever to their future-scheduled order once booked. No tweaking the quantities. No adding a few more items. No removing items. No changes. Then, any modifications to the order that are requested have to be booked under the more favorable (for our company) terms and prices.

You see, if our customers were able to predict a month out exactly what they need, they would already be telling us. Our forecast accuracy is lousy. Therefore, our customers don’t know exactly what they need.

So they would then have a choice: sit tight and take exactly what they ordered (reducing the sudden changes to production schedules) or pay more to change their mind at the last minute–at a higher rate to the salesperson.

It’s a hook. Offer the discount on the gamble they will need to change their order later. But how likely are they to change their minds later?

It’s hard to say for sure. Behavior would change once you bias the choice as described. But what I need before I even pitch my idea to sales is some baseline figure for how often it happens now. I ran some numbers. I think the number is high enough.

I have begun thinking of ways to stick it to the customer while offering a deal. I may go far in the business world.

The basic premise of my job is to help customers get their orders sooner. Sometimes this is just a matter of getting up from behind your desk and touring the office until you find the right person who can make the snafu in the system that is holding up your order go away. One day recently I walked around the building with what amounted to a plastic handle needing two stickers. The order was months old and I had already tried sending e-mails and making phone calls, so I just resorted to walking around with the physical item until everybody agreed I could ship it.

Other problems aren’t so simple. Even before I got this job I was selected to represent our plant for an internal customer turnaround project. I have kept this duty through its ups and downs of urgency, now on its third spike in as many years, and my responsibility is to identify constraints on late orders, advise when they will ship, and make the same review of upcoming orders owed. And I have never done this in three years.

I don’t have any reliable way to look up what is holding up an order in production without asking someone. If I can get someone to tell me what is holding up an order, I probably cannot also get them to tell me when it will ship, and vice versa. And if I can drub or cajole both bits of information, I certainly cannot get them to seriously look at upcoming orders and tell me anything accurate about them.

This is the third time we have been red-flagged by this critical customer. Their chief complaint is our inconsistency. Taking care of this is one of my foremost job responsibilities. My boss’ boss has alerted my plant manager’s boss that we have a serious problem with this customer.

I need help, lots of help, to even understand what is going on, let alone to improve on it. I need buyers and planners and manager to tell me how everything is going to come together to ship these orders. Earlier in the week I tried getting the help by bringing up the orders in the morning meeting with everyone present. The plant manager said “We don’t all need to be here for this, do we?” which resulted in nobody being there for it. Today I tried offering to e-mail a report (since nobody wanted to review it in person) and I was kind of indirectly laughed off the stage. There were some looks going around the room and the plant manager interjected to note how he had talked with a higher-ranking figure in the company who had not raised any issues. He did not say this in direct contradiction to anything I said, he basically just changed the subject, but also indirectly undermined that there was anything to be much worried about.

Some of the biggest trouble I have ever been in was the last time I got fed up and told the customer turnaround group that my plant was not engaged in the effort. I did not get a formal reprimand or anything but there were scorch marks on my bridges. If I just want to make myself look like a victim I can go whining that nobody is helping me, but that will have no good effect on the long term willingness of people to help me out when not forced at gunpoint.

But I don’t know what I can do to make the importance of this problem to my role, my superiors, and my department felt and acted on. And I don’t know how to let my superiors know that my wheels are spinning without running over all my necessary allies at the same time.

And this has been part of my illustrious, lauded, shining-star career for three years now.

I had a meeting about meetings today. Pretty much. It was the later half of the weekly team meeting, and our illustrious leader decided that since the weekly meetings had been such a flop in the year past, we needed to revamp them. So he asked us for our input. Fair enough.

I, always ready to jump out in traffic if asked, suggested that the first thing we wanted to hear from him is what is important today. He liked that idea and proceeded to affirm it by saying that everyone would have to report metrics on three most important things we always have to deal with.

Which is not what I said at all.

Those three things, they are always supposed to be important; but the truth is that the daily and weekly winds that blow often fly other flags. To understand where our boss is coming from we have to understand what is giving him heartburn, and it won’t always be those three “key” metrics. Further more, in real life, none of us is going to know those metrics by heart, and we will all madly scramble to get them before the appointed hour. In the five minutes before the appointed hour.

So rather than being a reality check on where things really lay this week, it will be an artificial and panic-inducing mad dash to pretend we are all sedately working on The Important Things, rather than fighting fires as usual. And that applies to dear old boss as much as any of us; he made some lofty commitment to update all of us on Major Projects every week, but we all know he is going to stumble into the meeting five minutes late, having come from two consecutive prior meetings, and not really have the foggiest idea what is going on with the big projects.

Resolved: Next year, real life will look more like what we put on paper!

One of the managers–one of those types who likes to monologue–was telling me that when the plant manager comes to the daily meeting, everyone is posturing to make themselves look good, by making other people look worse if necessary. He said it becomes a useless meeting where everyone is tearing each other apart, and that he and other managers had tried to explain this to the plant manager.

I had to think of the time for a few months when it seemed like the plant manager had quit coming to the meetings and was no longer interested. The other managers were rarely seen, and the meeting devolved into little more than a handshake on the day’s work, with some friendly (or not so friendly) put-downs thrown in.

This is not the first time I have heard that the plant manager has no business in a meeting about the day to day operations. But I think he can only afford not to be there when someone else is going to take care of the business for him. Somehow it still seems to require the plant manager emphasizing that their are customers who want their orders shipped before people become really eager to solve problems. People may point fingers more when he is around, but they volunteer a lot more, too.

So this is when we say that the plant manager needs to work on his management team. But we say this after a year of multiple general layoffs that included the elimination of two management positions and the firing–sorry, sudden resigning–of another. And even aside from the volatility of these changes, we are speaking of the great gamble: whether you can get someone better than you have now. When ordinary budgetary approvals are going two and three levels up, and you are located far away from attractions for the business elite, it is no small thing to speak of improving the caliber of your management team.