Building the Efficient Supply Chain

Anything that saves time is perceived as good. Anything that wastes time is perceived as not good. The efficiency of any supply chain and its traverse time are inextricably tied together. The longer the chain, with all other things being equal, the longer it will take to get from one end to the other.

Any action that can speed up any portion of the supply chain will increase the efficiency of the entire chain if subsequent links are prepared for the accelerated previous link. So, when a supply chain is being controlled by only one person, the person can move from beginning to end with a minimum of unwanted delays. When the supply chain control is extended beyond a single person, unknown variables are then factored into the efficiency equation and can be represented as "risks."

Every supply chain can be segmented into discrete tasks and authorities. The resolution of those tasks can be further defined by the specialty skills required to perform them. A distributor has a unique set of tasks relating to distribution inventory management and order fulfillment. A freight forwarder also deals with inventory movement and order fulfillment, but with a unique set of delineated skills requiring specialty knowledge.

Let me start somewhere closer to the beginning of the chain and try to gain some efficiency. In an electronics manufacturing environment with an in-house R&D, purchasing first kicks in when a design requirement has created a demand for parts. The unreleased primitive bill of materials is located in an engineering parts database. Usually, the parts list has been derived from a schematic that has been created by, or loaded into, a CAD system where each discrete component has been assigned a reference designator (C1, C2, R1, R2, U13, U14, etc.), indicating where the part is mounted on the printed circuit board.

At a minimum, in order for purchasing to buy the correct part, the manufacturer's name, part number, quantity used in the bill of materials (BOM), and perhaps a company internal part number is required. This basic information lends itself very neatly into spreadsheet column headings. In fact, almost all parts management software will export the various database fields and records into an Excel (XLS or XLSX), or CSV (comma delineated file) format.

Once the parts list is exported into the spreadsheet, additional work is required to prepare for the purchasing RFQ so that a supplier can use the spreadsheet to complete the important missing information concerning cost and availability. This is where the use of a MACRO can save the person responsible for creating and processing an RFQ hours of effort.

Macros provide the convenience of allowing you to automate many repetitive tasks that you already do each day, or even to do things you can't normally do in Excel (in a reasonable amount of time). Even better, once a macro is created, you can summon its convenience with the quick click of a button or a few keystrokes. So, let's step through creating a macro for an RFQ.

Everyone is familiar with pushing the record button on a voice recorder, speaking, and pausing or stopping the record operation. Playback is a matter of pushing the play button and listening. Creating a macro is just as simple. The difference is that instead of recording a voice signal, the macro records computer key strokes and mouse actions step-by-step.

So, to prepare an RFQ formatted spreadsheet from a raw database import, just do what you normally do, but be sure to use the macro record function before you begin formatting.

Now, column width, cell formatting, font selection, formulas, headers and footers, row and column deletions and insertions -- which may require hundreds of mouse clicks -- resizing efforts, and formula insertions are captured and can be replayed or re-performed in a single "Run macro" command.

Name your macro such that it indicates what type of spreadsheet you are creating. You might want to just call this one "RFQ," so the next time you import from your database with the intention of preparing an RFQ, you just have to perform the export to xls file from the database and then open your Excel to import the raw file. Once the raw file is on your spreadsheet, run the macro and sit back and watch your macro create a picture perfect RFQ, which is ready to send to a supplier just seconds later.

Bills of material and parts lists vary in length, so when you are selecting a range for a spreadsheet activity, be sure to consider adding extra blank rows and columns in the formatting macro. When you import those extra-long lists, the macro will think it is dealing with blank cells, but in reality, you will have populated them with the extra data. If you forget to do this on your initial macro, Excel gives you the option to step through the macro one step at a time, and when you get to row selection, just increase the number of rows and run your macro again.

Over the course of a year, you will save hundreds of hours of repetitive effort when you use a macro. Experiment with other standard spreadsheets like kit picking or cycle counting forms that you now have to format one at a time. You'll be glad you did. When you're done, reward yourself with a cookie. May I suggest a coconut macaroon?