Excel, Purchasing, Supply Chain Management

In all likelihood, your first encounter with a balanced scorecard would have been your report card, where individual school projects, tests and exams were given a certain weight in accordance to their level of importance. In purchasing, this tool is put to practice in supplier selection and supplier evaluation. It is used to avoid risk, reduce costs, mitigate rogue or maverick purchasing and ultimately aid in the selection of the most qualified good or service provider. Performance metrics are listed in columns and are then scored using a standard numerical value range often being 0% to 100% or 1 through 10. Each individual score is then multiplied by the weight determined by their level of importance and are summed at the bottom, often converted into a percentage format. This is part 2 of a 2 article series that will provide the reader with tips and best practices for the creation of supplier evaluation balanced scorecards.

 

Now that you have selected your supplier(s) using the points outlined in part 1 of this article, titled Creating a Supplier Selection Balanced Scorecard in Excel, your supplier evaluation responsibilities are far from complete. Suppliers must be regularly tracked to ensure that KPIs including price, quality and reliability, or whatever metrics are important to your organization, are available for evaluation. Your Supplier Evaluation Scorecard will be broken down into two separate tables: your Supplier Evaluation Scorecard and your Historical Tracking Scorecard.

 

Supplier Evaluation Scorecard

 

The Supplier evaluation table will include columns possessing the following titles:

  • KPI Groups (optional)
  • Key Performance Indicators (KPIs)
  • Performance Target (optional)
  • Measurement
  • Acceptable Score
  • Score This Month
  • Variance from Acceptable Score


 

KPI Groups (optional) and Key Performance Indicators (KPIs)

KPIs include a description of the metrics used to evaluate the success of the good or service provider. The KPIs can be grouped into a KPI Area (optional) column that proceeds it using KPI groups such as Customer Service, Cost, Quality, etc.

 

Performance Target (optional)

The optional Performance Target column includes the quantifiable goal you wish for the KPI row to achieve. While it is nice to be specific in producing your supplier evaluation balanced scorecard, the point is to spend your time evaluating, as opposed to producing the most detailed document. So use the Performance Target column with caution.

 

Measurement

The Measurement column describes precisely how you will quantify the score associated with each KPI.

 

Acceptable Score

The following column titles are self-explanatory. The Acceptable Score is the score that you would accept. This is the passing score, not necessarily a perfect score.

 

Score This Month

The Score This Month column is where you do the actual scoring. The time interval does not have to be monthly. Use the evaluation time interval that is appropriate for your situation. It is important to remain constant in your scoring for the purpose of incorporating this data with the Historical Tracking Scorecard that we will get to in a minute.

 

Variance from Acceptable Score

A simple =Score This Month cell – Acceptable Score cell formula will deliver your Variance from Acceptable Score for each KPI. The resulting value is what will be used in your Historical Tracking Scorecard.

 

Historical Tracking Scorecard

 

The Historical Tracking Scorecard is where your historical scores are recorded and evaluated. This table can either be placed on the same tab, or on its own separate tab. The table is a stripped-down version of the Supplier Evaluation Scorecard including the optional KPI Area column if you added one, an identical KPI column, and your time interval columns. As this example uses months, we will add 12-time intervals to evaluate. Of course, you can evaluate weekly, quarterly, yearly, etc. Each time interval, copy the score from the Variance from Acceptable Score column in the Supplier Evaluation Balanced Scorecard and paste those figures into the time interval column that corresponds with it in the Historical Tracking Scorecard.




To make the Historical Tracking Scorecard more visual: turn it into a  line chart by selecting the entire table’s containments and clicking on the Insert Tab ==> Chart ==> Line




Right click inside the chart and click Select Data. In the Select Data Source menu, for each Legend entry, highlight the name field and click the KPI cell (start with the first KPI). Then highlight the Y values field and select the entire time interval ROW that are associated with that KPI, all 12 months in our example. Do not include your KPI name. Repeat this step for every KPI. For the Horizontal (Category) Axis Label field, highlight the row cells that indicate your time intervals (month numbers/names, quarter names, etc.) You now have a beautiful chart that will help visualize your historical scores for each KPI. Customize the chart according to your preferences.




Now that you have completed your Supplier Evaluation and Historical Tracking Scorecards, it is vital to spend the time to fill them out within the specified time interval (weekly, monthly, quarterly, etc.). If for whatever reason you miss a time period or neglect to analyze the evaluation data you spent time making clear and easy to interpret, you will have wasted the time you spent producing the scorecards. It is also crucial that you remain constant in your scoring standards. Otherwise your supplier’s performance data will be distorted over time.

 

0

Inventory Management, Supply Chain Management

In pursuing my Post-Graduate Certificate in Supply Chain Management – Global Logistics, I could not help to draw parallels between the components of SCM and my previous career in mass-participant sports event operations. This indirect background in Supply Chain greatly facilitated the program as I had a constant supply of past professional examples to which I could relate many concepts. The biggest take-away was that all three segments of Supply Chain Management, which include: purchasing, inventory management and logistics are required for a well-managed event, whether on a large or small scale. This article focusses on how mass participant event operations relates to the supply chain management component of inventory management and is the third in a series of three articles on the topic. The first on purchasing can be found here and the second on logistics can be accessed here.

 

Warehousing

 

In event operations, both Just-In-Time (JIT) and traditional warehouse inventory (AKA Just-In-Case) is employed. Re-usable equipment and other objects are stockpiled in storage locations that can include a warehouse, shipping container, trailer or within the event’s office space. Trailers and shipping containers are particularly handy as they are both modular and mobile. One or all containers can be sent to the event venue or moved to an alternate lot and as they are already packed, there is no need to transfer the equipment from storage to a transportation device. Trailers are especially useful for events that tour multiple locations throughout the season and can incorporate highway with rail transportation in the form for intermodal piggybacking. Operations managers will want to perform the light integrity test to ensure the container’s contents will be safe from the elements. However, trailers and shipping containers are not perfect: items stored inside are difficult to access and their outdoor placement puts them at risk of theft, overheating and freezing.












On-demand warehousing is often an attractive option for events who desire the reliability of traditional warehousing, with the scalability of an on-demand service. Storing items inside a traditional warehouse allows for greater access to items that are required by event staff on a moderate basis, regular to intermittent item picking and offers protection from the elements. On-demand warehouse space also allows for organizations to take advantage of potential economies of scale offered in the form of lot size discounts and transportation discounts. Here an event can spread out the procurement of a lot size of items across multiple events, acquiring time and cost savings. Warehouses are an ideal storage location for safety stock.




Just-In-Time

 

For items that are required for the specific event, it often makes no sense to have them delivered to your warehouse, just to have them loaded up and transported to the event venue at a later date. As many medium to large scale events have access to their venue in the days or week leading up to the event, inventory holding savings, transportation savings and time savings can be achieved by having those items shipped directly to venue. This works particularly well for high volume items that you will receive in the days leading up to the event, including food, water, new signing, sponsor banners and other last minute items. The Just-In-Time benefit of eliminating inventory carrying costs is countered by the high risk of stock-out (in this case, not receiving your items on time), due to unexpectedly long delivery lead times. JIT should therefor only be employed with trusted suppliers, with reliable carriers, and for items that make sense to only arrive in the days before.

 

 

A combination storage location types

 

On-demand warehouse space can be useful in alleviating the downsides of trailer and shipping container storage, yet warehousing overhead costs need to be taken into account. It is not uncommon for large scale events to diversify their inventory across office space, warehouse space and trailer/ shipping containers. This was you can take advantage of the mobility of shipping containers and trailers while taking advantage of the convenience of in-office storage locations for items that require regular access. In addition, items that require temperature control and moderate access, whether in the form of intermittent picking or otherwise will benefit from in-demand warehousing.

0

Job Search, Supply Chain Management
The 2017 SCMA Alberta Conference in Red Deer, Alberta saw The Canadian Supply Chain Sector Council launch new videos profiling People in Supply Chain.

These video profiles focus on professionals working in Canada’s supply chain sector in a variety of industries. They provide a sample of the supply chain management career options. All english videos feature french subtitles and french videos include english subtitles.

View this post which includes 7 more career profile videos.

Logistics

Brad Beerling
Logistics Manager
Meridian Manufacturing Inc.



“Be willing to accept problems are going to happen, be willing to adapt to them, learn from them and grow with them.” —Brad Beerling

Takeaways:
  • Getting into logistics requires finding someone to learn from, finding a company that is willing to spend their time on you

  • Be prepared and willing to react to problems that arise

 

Warehousing and Logistics

Brent Ellis

Director, Commercial Warehouses

Wills Transfer Limited


 
Takeaways:
  • Vendor Managed Inventory (VMI) requires continuous coordination, lots of reporting

  • Understanding the needs of your customers

  • Opportunities for advancement within the warehouse industry


Supply Chain Management

Patrick Etokudo
Director of Supply Chain Management
Enbridge



“The skills that you need generically, to perform well as a supply chain manager would be things like interpersonal skills, great communication skills, an ability to negotiate, an ability to build networks and maintain relationships.” —Patrick Etokudo

Takeaways:
  • Soft skills include: Interpersonal skills, communication, negotiating, build networks and maintain relationships.


Transportation

Serge Faucher
Executive Director
Les entreprises Dupont 1972 inc.
 



Takeaways:
  • Communication is key

  • Diesel procurement contract negotiating is a vital role of trucking companies

  • Precise scheduling


Logistics

Christina Forth
Co-Owner
FFAF Cargo



“The types of skills you need in this industry are to be very organized, very time sensitive… reliable, responsible; those things you always look for in employees.” —Christina Forth

Takeaways:
  • A high degree of coordination is vital for freight forwarder portions (obvious)

  • Ethnic diversity is useful in understanding the cultural norms at play within geographic locations being served

  • Fast-paced and stressful environment


Manufacturing

Jim Gillespie
Director of Programs
Peraton



“If you’re interested in a full career, starting in supply chain, pick that part of the job that you love, get into that part of the job, work with others, but expect to stay at that level unless you’re willing to learn what all the other elements do. That’s the only way that you can grow into the leadership and turn it into a full career. Because it is very rewarding, very challenging and there’s going to be lots of room in this industry coming up.” —Jim Gillespie

Takeaways:
  • Maintenance is the core of the supply chain

  • Cross-functional team management is paramount to this position

  • A military background is not necessary to work on military grade products

  • To grow into a leader, you must master your area of expertise, then learn about all of your colleagues’ areas of expertise



Supply Chain Management

Pierre Massicotte
Senior Vice President, Operations
L’Oréal Canada



Takeaways:
  • Supply Chain Management plays a strategic role in the distribution of consumer cosmetics

  • Productivity drives competitiveness (obvious)

  • Many ingredients are imported, providing opportunities for those with logistics and customs backgrounds

0