Advertisement

Blog

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?

10 comments on “Building the Efficient Supply Chain

  1. Ashu001
    October 26, 2012

    Rich,

    True.

    It would however,be very unlikely that a SMB sized firm would spend Time and Resources for this initiative;unless they were either forced [By Competitive Circumstances] or encouraged by Government Subsidies/Taxbreaks.

    Improvement is all Well and Good but too many Firms are just struggling to stay above Water today ,primarily because Economic Confidence has fallen of a cliff to suggest that they can find Scarce Resources for this initiative is easier said than Done .

    Regards

    Ashish.

  2. Anand
    October 26, 2012

    Improvement is all Well and Good but too many Firms are just struggling to stay above Water today ,primarily because Economic Confidence has fallen

    @Ashish, no doubt economic confidence has fallen buts the economy is recovering fast. Look at UK, it has declared that it is out of double dip recession. We can expect economic confidence going up in other countries too.

  3. Ashu001
    October 26, 2012

    Anand,

    I Honestly hope you are right.

    But a part of me feels like this is the Lull before the Storm with the coming Fiscal Cliff in 2013,2013 is gonna look very-very different from 2012.

    Already most of Asia(including China,India) are experiencing a massive-massive slowdown caused by Inflation or by vanishing of Demand.

    If Asia loses steam I don't think rest of the Globe will be able to manage things.

    Regards

    Ashish.

  4. Anand
    October 26, 2012

    Already most of Asia(including China,India) are experiencing a massive-massive slowdown caused by Inflation or by vanishing of Demand.

    @Ashish, I agree with you on this. Its strange that developing nations like China and India which were supposed to grow much faster are showing weakness. Inflation and Vanishing of Demand are some geninue reasons but I think one more thing common between these two countries is ramptant corruption. I think corruption is taking serious toll on the growth of these two nations.

  5. Anand
    October 26, 2012

    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).

    @Douglas, I totally agree with you. I am big fan of Macros. I always use Macros in Excel/Nedit and the improvement in work efficiency is tremendous. Infact it wouldn't be bad idea to use coding programmes like VBA along with excel because this gives even more flexibility compared to MACROS.

  6. Ashu001
    October 26, 2012

    Anand,

    Quite true. I don't dispute the fact that India and China are waaay more Corrupt than America or the leading Countries in Europe;but I feel it has more to do with the lack of effective Governance systems in place.

    I mean,you could Legislate from the Top that I want people to do this and that and that…But if the Enforcement mechanism is weak not much we can do.

    Can we?

    I also feel its unfair to compare India(which is vibrant Multi-Party Democracy) and China(which is a One-party State where if you challenge the existing status Quo you get knocked off…)

    Regards

    Ashish.

  7. dalexander
    October 26, 2012

    @Rich, Macros are exportable and importable, so one person, (Purchasing Manager) could gift the macro file to Junior and Senior Buyers. The training to use just couldn't be simpler. “Run Macro.”

  8. Ashu001
    October 28, 2012

    Rich,

    Yeah The Government is just like the Captain of the Titanic today.

    Inspite of the fact that we are sinking,They keep saying everying is A-ok!!!

    Don't worry a bit!!

     

  9. hash.era
    October 31, 2012

    @ Rich : 🙂 … I think thats because most of us are used for advanced programming tools rather than worrying about macros anymore 🙂

  10. hash.era
    January 30, 2013

    Rich: Going for advanced tools is something which we all should do. All the other simple stuff are outdated. You cannot expect things out of them to work forever.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.