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

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 use in supplier selection and in 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 1 of a 2 article series that will provide the reader with tips and best practices for the creation of supplier selection and supplier evaluation balanced scorecards.

 

Technology

 

You will want to create your balanced scorecard in Excel. While you could print a basic table in Word or even draw one out by hand and calculate the scores manually, you will be missing out on the analytical opportunities outlined in these two articles,and the digital sharing functionality with your colleagues, while increasing the possibility of human error. Your Excel scorecard will essentially be a dashboard, with colour notifications by way of conditional formatting and historical tracking with automated graphs if you want to get fancy. If creating dashboards in Excel is not for you and this all sounds intimidating, you’re in luck! I have created the following Purchasing Coordinator evaluation scorecard for your use in hiring someone to build one for you. See how useful balanced scorecards can be?

adamkwitko.com

 


Creating a Supplier Selection Balanced Scorecard

 

To build a scorecard to help in the selection of a supplier, you will want to begin by identifying the metrics considered to be of importance to your organization relating to the good or service the potential supplier is to provide. This is often associated with the business problem they are to solve. A more complex product or service will likely require more performance matrices than one that is less complex, but don’t get carried away. You do not want to waste your time creating and evaluating metrics, so 5 to 8 should do. List them vertically, assigning each metric to its designated cell in the performance metric column. You will input your potential suppliers’ names in the top row of your table. This should be a relatively short list as well, as the scorecard is designed to identify the most qualified provider according to the needs of your organization.

 

Each metric now has a designated row. The cells of that row under each candidate name is where the score that particular candidate achieves for that metric will be input. This will generally be in the form of a range or a binary Yes/No input. It is important to remain consistent in the range you use to evaluate each row throughout your scorecard. A score of 1-10 is a good idea. For a Yes/No selection, you have a few options:

  • Input 10 to equal Yes and 0 to equal No
  • Indicate “Yes” to equal Yes and “No” to equal No
  • Insert a check box or radio button.

 

Consider incorporating conditional formatting to bring attention to a particular score range. For example: make the score cell turn green if the cell’s score >= 8, yellow if >=4 and <8, red if <4. For Yes/No or checkbox/radio button cells you can use conditional formatting to make the cell turn green if Yes or if the box is ticked and red if No or if the box is not ticked.

 

The bottom row is where the total score is calculated for each candidate good or service provider. This is where your SUM formula will go. Sum each candidate’s column, while assigning a percentage or decimal point to each performance metric, giving the more important ones more weight. Note that if you ranked your metrics using a scale of 1 to 10 as recommended in this article, you will want to divide it by 10 before multiplying the cell by the percentage weight. For example:

=SUM(B2/10)*whatever weight you would like it to be worth)+your next equation

 

If you opted to use “Yes”/”No” text, checkboxes or radio button for your binary metrics, you will have to incorporate an IF Statement into your formula for each one. For example:

 

=if(E5=”Yes”,whatever weight you would like it to be worth,0) for the “Yes”/”No” text

=if(E5=TRUE,whatever weight you would like it to be worth,0) for a Checkbox or radio button




Each of your column’s SUM formulas should look something like this:

=SUM(B3/10*0.05,B4/10*0.2,B5/10*0.1,IF(B12=TRUE,0.15,0),IF(B13=TRUE,0.125,0),IF(B14=TRUE,0.125,0),IF(B15=TRUE,0.125,0),IF(B16=TRUE,0.125,0))

 

As the supplier selection scorecard is an internal document, the final perfect score does not have to equal a clean 100%, however it will be more comprehensive if it is equal to a common sense rounded number. Now that you have the tools to evaluate your suppliers, the next step in your supplier evaluation adventure is to crete Supplier Performance Evaluation Scorecards.

0

Purchasing

The purchasing department is in a strong position to leverage cost savings into profit. The profit leverage effect dictates that reducing operating expenses is more efficient than increasing sales. Situated at the beginning of the production process of a product or service, the procurement stage is in an excellent position to reduce overall costs, especially in the short term. This is why companies often resort to reducing headcount when they run into financial difficulties. Reducing operating costs is the fastest way to produce a short-term impact on the bottom line.

 

With this in mind, let’s talk about purchasing’s profit leverage effect. The following example will display how every dollar saved in purchasing goes directly to the bottom line, and it does so in a way that is more efficient that it would be through increased sales.

 

Your sales are $120,000 and your cost of goods sold (COGS) are $60,000. Within your COGS is your cost of purchased goods, which is 75% of your COGS ($45,000). Let’s say you reduce your cost of purchased goods through a combination of supplier relations and negotiations by 10%, you would save $4,500. Your cost of purchased goods is now $40,500. Your COGS are now $55,500.


Reducing costs of goods sold decreases your COGS from 50% to 46.25% of sales. Your operating income (net profit) therefor increases by the same amount. Let’s say operating income was $25,000 or 21% of sales, after the 10% of purchasing cost savings, net profit increased by 18% to $$29,736. That’s pretty good!

 

Now let’s look at what the sales department would have to do to achieve a comparable increase in net profit. To calculate how many more sales dollars would have to be generated we divide the needed additional profits ($4,500) by the operating profit margin (21%). The sales department will therefore have to sell an additional $21,428.57 worth of your product or service, which is the equivalent of increasing sales by 15%. And that does not factor in the marketing costs associated with increasing sales.

Which is easier? Decreasing the cost of purchasing by 10% or increasing sales by 15%? For most companies, that large of an increase in sales with no increase in advertising spend would be an incredible challenge, especially in the short run. On the other hand, reducing the cost of purchases by 10% is very attainable for organizations who have not traditionally managed purchasing as a strategic function.

 

Key Takeaway: every dollar saved through purchasing goes straight to the bottom line. By contrast, only 21% of sales goes to the bottom line, the remainder is consumed by the costs associated with doing business.

 

0

Purchasing, Supply Chain Management

Purchasing supply chain management software is challenging as just like the supply chain, it includes a number of moving parts, departments and changing regions. When you migrate to a new software platform, all your stakeholders are affected. Why do we buy software in the first place? The purchasing decision is not really about the software itself. It’s about the issue that the software will solve, or at least, you hope it will solve.  In the context of a complex supply chain, here are some common goals which those in the position to purchase software wish to achieve:

 

  • Increased efficiency through the automation of a pre-existing manual business process
  • Offer new functionality, helping you do more or increase your organization’s quality of service
  • Compliment current software platform, so that well-functioning pre-existing systems can live-on
  • Futureproofing, ensuring the software spend down the line is minimized

 

Traditional supply chain ERP software is corporate organization outward focussed with a high emphasis on stakeholder integration and collaboration. You need to consider a product that will not only integrate into your organization’s business processes, but also those of your suppliers, vendors and other partners which you interact with on a regular basis. Some important considerations for a potential ERP implementation include:

 

  • How employees within your organization will use the software
  • How they do those activities and processes today
  • How your partners including vendors and logistics service providers interact with you today and if it will change their process. Will they have the desire to / do they possess the ability to interact with the software you are looking to procure.

 

Some organizations look at purchasing software from a procurement perspective. Their procurement teams might create an RFP and will have particular requirements. They will research potential providers and consult with different departments in a cross functional approach. Ultimately, a document will be created, outlining what they are looking for in a vendor, potentially in the form of a balanced scorecard. From there they will shop around for that software.

 

Another option is for the supply chain organization within the company to own the software purchasing decision. The organization is familiar with how they get products from purchasing to logistics to customer service to planning: all of those departments will be considered in some way. This organizational focus is in a better position to represent the specific needs of the various business functions. While the procurement approach is often more concerned with making the most feasible financial decision. Of course, the best approach to a major supply chain software procurement decision would be a combination of the two methodologies.

0

Inventory Management, Purchasing

          Bankruptcy forced the DeLorean Motor Company (DMC) to shut down its Belfast, Northern Ireland production facility in 1982, with an inventory of over 1,700 brand new cars and millions of parts. During liquidation, Consolidated International acquired all of DMC’s remaining inventory. Meanwhile, Steven Wynne, a British mechanic specializing in DeLorean maintenance and restoration since 1982, opened a 40,000 square foot warehouse in Houston, Texas, meant to act as a centralized distribution centre for used DMC parts. In 1997, Wynne acquired all DMC inventory from Consolidated International, in addition to the DeLorean Motor Company name and logo.

          Initially, the acquisition was aimed to support Wayne’s maintenance, restoration and parts sales operations. He and his team have serviced a consistent stock of 35-45 DeLoreans belonging to owners from all around the world since 1987. They also sell parts to DeLorean owners and restorers. As those operations were still not considerably cutting into the new DMC’s parts stock, they began assembling brand new DeLoreans themselves.


         A DeLorean requires roughly 2,700 individual parts of which DMC has over 99%, with no opportunities for traditional inventory replenishment. To fill the holes in their inventory, the remaining less than 1% are rather easily reproduced, rebuilt or procured as used parts. As all original DMC technical specifications and drawings were also acquired, they are often able to reproduce parts using the original specs with CAD/CAM and 3D modeling. This, in combination with their current inventory, allows the modern DeLorean Motor Company to produce a maximum of 500 cars, while continuing its additional pursuit to be the most prominent facility for DeLorean service, parts and restoration.


     

        Since 2016, the new DMC has employed Acctivate as its inventory management software. Acctivate is utilized for inventory adjustments when parts are received in the distribution centre, whether reproduced or acquired as used parts. Those adjustments are then automatically integrated into DMC’s web store. Acctivate supports and is used to create assemblies (one part containing multiple parts) in addition to sales order management including open and closed sales order monitoring, the creation of pick tickets and sales order printing. Some service orders, such as full frame-off restorations, require 200-300 line items for labor codes and part codes.

We’re able to build a service order pretty quickly with Acctivate, especially with some of these big restorations– Sarah Heasty, Service Manager, DeLorean Motor Company.

         DMC uses Acctivate’s Business Activity Service Billing module to create service order quotes, where separate subtotals can be created for a customer’s engine, transmission, suspension, etc., providing increased transparency. The Business Activity Scheduling module is employed to track labour hours and parts used for each service order. Labour hour tracking helps with DMC’s capacity planning as parts are pulled prior to service, increasing efficiency.

0

Excel, Inventory Management, Purchasing, Supply Chain Management

Quantitative forecasts are as in-demand as ever. This post provides four solid forecasting options in Microsoft Excel that can be used to predict sales, operating costs, performance and more. The forecasting methods explained include: moving average, the Excel FORECAST function, trendline and regression using the Analysis ToolPak.


Moving Average

A moving average predicts the future value of a time period through averaging past time period values. That forecast can then be used in further forecasting down the line, averaging it with other time period values. The risk in Moving Average forecasting is that it can lag behind a trend.

  1. Select the Data tab, then Data Analysis command button. In the Data Analysis dialog box, select the Moving Average item from the list and then click OK.
  2. Identify the data you want to use to calculate the moving average. Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by selecting the worksheet range. Your range reference should use absolute cell addresses ($A$1:$A$5 as opposed to A1:A5).
  3. Indicate how many values are to be included in the moving average calculation in the Interval text box. By default, Excel uses the most recent three values to calculate the moving average (i.e 3 month moving average, 3 year moving average, etc.). To specify that another number of values are to be used to calculate the moving average, enter that value into the Interval text box.
  4. Use the Output Range text box to establish the worksheet range into which you want to place the moving average data.

The FORECAST Function

The FORECAST Function predicts a future value using existing values. The predicted value is a y value for a given x value. The known values are existing x values and y values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

FORECAST (x, known_y’s, known_x’s)

example =FORECAST(100,A2:A10,B2:B10)

  • X is the data point for which you want to predict a value.

  • Known Ys are the dependent range of data.

  • Known Xs are the independent range of data.


Trendline


A Trendline is simple way to analyse the trend within a collection of data points within a graph, smoothing out the data oscillations in the process. The trend within the data is then used to forecast future performance.
  1. Create a graph with your existing data.
  2. Right click on any of the data oints within the graph and select Add Trendline.
  3. In the Format Trendline window select whether you would like to forecast/analyse using: exponential, linear, logarithmic, polynomial, power or moving average. Linear is most common. If the data appears to trend towards compounding, try exponential
  4. Click on close.

Regression Using the Analysis Toolpak (ATP)


  1. Ensure Analysis Toolpack is installed (Tools tab, select Excel Add-ins, select Analysis Toolpak)
  2. Select the Tools tab
  3. Select Data Analysis
  4. In the Data Analysis window select regression.
  5. The Input Y range represents what you want to estimate (likely sales)
  6. The Input X range represents the data that can explain your Y (likely unit cost or price)
  7. Click OK


What does all this mean?

In Regression Statistics
Multiple R represents to correlation coefficient between Y and X
R Square represents the percentage of Y you can explain from X. A number closer to 1 indicates low variability and a number closer to 0 indicates a random correlation between your X and Y.

In ANOVA
Regression represents the number of independent variables
Risidual represents Total – Regression
Total represents the number of values – 1 (minus 1) (likely the number of active rows – 1)
SS represents Sum of Square
MS represents Mean Sum of Square
Lower % and Upper % means that 95% of the time, your coefficient will be between the lower value and the upper value


P-Value – The lower the P-Value, the less variability you have. The result of 1 – P-Value provides you with the percentage that the intercept will be correct


Cover photo credit: Finance Monthly
0

Inventory Management, Logistics, Supply Chain Management
Barcodes and their associated scanning guns have been a staple in inventory management. Whether implemented in a library for signing books in and out, or within a large-scale online retailer’s distribution centre (DC), the use of both wired and wireless barcode systems is widespread throughout the supply chain. Tech companies including the Vitech Business Group are currently shaking up the inventory management industry through their integration of voice-directed order picking. Their product allows for order pickers to receive picking lists audibly, then confirm the pick by reading the product name or code out loud , rather than scan it with a traditional barcode scanning gun. The technology, originally developed by Honeywell as Vocollect Voice Total Solutions, has been around for quite some time. Vitech has integrated it into the SAP Extended Warehouse Management (SAP EWM), a component of the SAP’s supply chain management suite of solutions. The recent adoption of Vocollect Voice Total Solutions by Patterson Companies Inc.: a distributer of dental and animal health technology, products and equipment, will be used to demonstrate the effects of voice-directed order picking on the supply chain. This article will provide the reader with an understanding of the subsequent advantages in addition to some risks that may arise in inventory management.




Benefits


Increased picking speed and efficiency

With voice-directed order selection at Patterson, order fillers operate 25% faster than with the traditional barcode scanning system. In fact, pickers were even faster on their initial attempts with the new system than they were on the legacy barcode system to which they were accustomed.

Paperless


Vocollect displays the pick list audibly at the push of a button, with the ability to repeat the audiable list. This alleviates the need for printed picking lists. A reduction of paperwork generally contributes to greater efficiency.

Less Mechanical Error


Damaged barcodes tags lead to errors in the scanning process causing order pickers to input the code manually. This not only causes a delay, it also can lead to input errors. A literate order picker will possess the ability to discern what a marked-up tag represents, where a barcode scanner cannot.

Reduction of training time


Patterson claimed a greater ease and speed for training using the hands-free voice system compared to training employees on a traditional barcode scanning system. This is especially beneficial for temporary order picker hires during the holiday season.

Employee Preference


Paul Courchene, Logistics Core Team Leader at Patterson claims that “No one wants to use the radio frequency (RF) guns…They all want to go hands-free and use voice”. The BBC’s Amazon The Truth Behind the Click Panorama documentary highlights how the constant countdown beeping coming from scanner guns leads to emotional distress, nightmares and has provided evidence of increased risk of mental illness.
 

Employee Pride


Hands-free voice systems are found to produce greater employee cognitive engagement in a repetitive task, leading to increased employee job satisfaction. There is also an institutional benefit derived from the pride associated with the feeling of doing things differently than other organizations.

Risks

Human Error


While Vitech boasts of superb accuracy, voice-directed picking requires validation by the order picker at the pick and at the put in, therefor requiring a second visual confirmation of their voice confirmation. The risks of human error are clearly a possibility within this process. It is up to the inventory analyst to determine whether the increased efficiencies derived from voice-directed picking outweigh the risks of human error.

Barcode Still Required


It should be noted that the implementation of a voice-directed order selection solution will not completely do-away with barcode scanners. As was the case in the Patterson case study, barcode scanning continued to be utilized for receiving and put away functions.

0

Inventory Management, Logistics, Transportation

NOTE: This article was written prior to the recent US corporate tax rate decrease to 21% and increase in the Ontario minimum wage to $14/hour.

Executive Summary

 

            Opening a distribution centre staffed by 501 full time minimum wage employees in Richmond, Virginia would be $2,462,771 per year less expensive than in Cornwall, Ontario. Due in large part to a lower minimum wage rate difference of $1.76 (CAD) and a mandatory employer contribution requirement differential of $625,045 per year, which aid it to surpass its unfavourable current exchange rate difference of 33%. Employees in Richmond generally experience lower quality of life, stemming from shorter, unsubsidized parental leave and un-mandated minimum paid vacation compared to their Canadian counterparts. Richmond’s favorable 5,806 unemployed citizens will provide a more advantageous pool of potential employees should high workplace turnover occur. The one critical unknown relevant factor lies in the lack of a profit forecast, which impedes this report from applying the considerably higher American corporate tax rate of 38.9% compared to the Canadian rate of 15.5%.

 

Introduction

 

The following information was gathered to aid in the site selection determination for a distribution centre comprised of 501 minimum wage employees. Various site selection factors were considered in establishing whether Cornwall, Ontario or Richmond, Virginia would be the most appropriate location given their respected provincial/state and federal staffing constraints. The predominant factor taken into consideration was staffing, which includes wage rates and employee benefit entitlements. Cash flow is another influential factor as it relates to the significant exchange rate in converting CAD to USD. Lastly, employment taxes rounded out the decision making process, as the province of Ontario, the state of Virginia and their respective federal counterparts require differing tax contributions from employers. This report will conclude by evaluating the above mentioned factors and figures to give recommendations as to the advantages and disadvantages of opening a distribution centre in both locations.

 

Background and Discussion

 

            As the predominant motive for the location of this distribution centre is based on overall cost reduction to the organization, it is evident how this situation relates to both an offshore factory and a source factory site selection process. As the facility is to employ 501 full time staff at minimum wage, it relates to both strategies in that regard. Another site selection consideration similarity lies in how the state of Virginia has a low minimum wage rate of $7.25 USD per hour ($9.64 CAD) (“2017 Minimum Wage By State”). The lower Canadian exchange rate balances out the higher Ontario minimum wage rate of $11.40 per hour, to some extent. Whether or not there is to be development of the local population can be argued either way. As 501 employees are to be paid at the minimum wage rate, that would not significantly develop the lives of the local population financially to a large extent. That situation would relate to an Offshore site selection strategy. However, it could be argued that if employees are to be taken from a segment of the population that is currently unemployed, that would in fact contribute to the development of the local population. The latter would put a small dent in either Richmond’s 4.3% unemployment rate (“Richmond, VA Economy At A Glance”) or Cornwall’s rate of 8.1% (Baker, Lois Ann), constituting the site selection decision making process to relate to the Source strategy. The one piece of information that is lacking is whether or not management is to be taken from the selected location’s local population, or whether they are to be expatriates.

 

            Another factor to be considered is the federal corporate tax rate of the country in which the proposed new distribution centre will reside. Canada’s corporate tax rate is relatively low at 15.5% while the rate within the United States is 38.9%. These figures cannot be applied to this particular situation at this time as profit forecasts have yet to be calculated, however they should certainly be taken into consideration. In addition, the WSIB rate of $2.43 per $100 insurable dollars (“2017 Premium Rates”) would likely be required for a distribution centre in Ontario, which provides workplace liability insurance coverage. Amounting to $288,677, this figure was omitted from calculations as it was not requested for analysis.

 

Analysis

 

Staffing

 

            Staffing and human resource factors are critical when determining the site for a distribution centre. As it is after low skilled, minimum wage employees, an analysis of the current minimum wage rates in Ontario and Virginia is crucial. Ontario currently stands at $11.40 per hour (CAD) (“Minimum Wage”) while Virginia has a rate of $7.25 per hour (USD) (“2017 Minimum Wage By State”) or $9.64 CAD. For simplicity, all figures moving forward in this text and its corresponding appendix will be in CAD. Using trendline in Microsoft Excel, we can forecast the Ontario minimum wage rate to climb approximately $0.20 per hour each year, to $12.40 per hour by the year 2022. In contrast, Virginia’s minimum wage unemployment rate has not changed in the past 7 years and is expected to remain constant at $9.64.










With a present rate difference of $1.76 per hour in favour of Richmond, VA and a projected difference of $2.76 in five years, Richmond is clearly less expensive when considering the minimum wage by itself. Gross payroll for a full time employee at minimum wage in Cornwall is $23,712 and $11,879,712 for 501 employees while the equivalent employee in Richmond would cost $20,044 and 501 employees would cost $10,041,986.

 

Employment Insurance

 

            To determine the end dollar amount for employing 501 minimum wage workers in Cornwall and Richmond, taxes and expenses must be considered.  An employer’s contribution requirements include employment insurance (EI), health care costs, minimum vacation requirements and compulsory government pension plan contributions based on the gross employee payroll. In Canada, an employer is to pay EI contributions of 2.632% for each employee’s salary which amounts to $624 per employee or $271,095 for all 501 employees in 2017. The American equivalent of EI is The State Unemployment Tax Act (SUTA) and The Federal Unemployment Tax Act (FUTA). The SUTA rate for new employers in the state of Virginia is 2.53% (Virginia State Tax Information”), which equals $202 per employee and $101,402 for all 501 workers. FUTA is calculated at a rate of 0.6%, equaling $42 per employee and $21,042 for all 501. Combining SUTA and FUTA amounts to $166,651, which will cost the distribution centre $104,444 less than its Canadian counterpart.

 

Healthcare Contribution

 

Opening the distribution centre in Cornwall will require payment of Ontario’s Employer Health Tax at a rate of 1.95% (“Employer Health Tax”). This will amount to $462 per individual and $231,654 for all 501 workers. The state of Virginia charges a Medicare rate of 1.45% (“Employer’s Tax Guide”) amounting to $291 per individual and $145,609 for all 501 employees. Virginia is once again less expensive than Ontario at a differential of $86,045.

 

Vacation Requirements

 

            Vacation requirements are a one sided financial expense, however it requires overall productivity and organizational moral to be taken into account. As the state of Virginia classifies vacation as a fringe benefit, it does not require employers to offer paid vacation time or vacation contributions (“Labor and Employment Law”). However, in Canada, employers are obligated to provide two weeks of paid vacation every year plus vacation pay, which “must be at least four per cent of the “gross” wages (excluding any vacation pay)” (“Vacation”). This expense not only amounts to $948 for the individual and $475,188 for all staff, it will result in decreased overall productivity due to an average of 19.3 employees always being on vacation. This therefor decreases the 501 staff down to 482, while the distribution centre will continue to pay for 501 employees. On the other hand, mandatory vacation time and pay will lead to higher workplace moral and likely increased productivity in the long run.

 

Government Pension Plan

 

            Both locations require tax contributions in the form of government pension plans. The Canada Pension Plan requires a 4.9% contribution by both the employer and the employee on all income between the first $3,500 year and up to $53,000 (“T4032-ON – Payroll Deductions Tables – CPP, EI, And Income Tax Deductions – Ontario – Effective January 1, 2016”). This will amount to $990 per employee and $496,184 for all 501 employees. The United States offers a pension plan in the form of Social Security, where a rate of 6.2% is taxed for each employee (“Employer’s Tax Guide”). Social Security will cost the distribution centre $1,243 per individual and $622,603 for all 501 staff. Social Security is a rare example of an American tax program costing companies more than their Canadian counterpart, accounting for a $126,419 difference in favor of the Canada Pension Plan.

 

Parental Leave

 

            While parental leave does not necessarily cost the organization direct out-of-pocket expenses, there are some less upfront ways in which it affects staffing. The Federal Government of Canada offers 55% of the employee’s salary over a 12-month period, for which one parent is entitled to maternity leave. It was recently announced the individual can extend the same income rate over 18 months, bringing it to 33% of their initial salary (Kohut, Tania). While Canadian employers are not required to contribute any funds to parental leave, it is customary for many organizations to top off that amount to the employee’s regular salary. On the other hand, the state of Virginia requires employers to provide their employees with only 12 weeks of parental leave, which is not subsidized by state or federal governments. The downside of Canada’s 12 to 18-month maternity leave entitlement for employers is they have to maintain that position or an equivalent position for the return of the employee, while benefits of paternal leave include a higher quality of life for the employee and less employee disenchantment. Virginia’s shorter and unsubsidised parental leave forces parents of newborns back to work much sooner, causing a smaller HR burden in the short run, but decreases employee quality of life and workplace moral.

 

Unemployment Rate

 

            A region’s unemployment is an indicator of the availability of labour. The unemployment rate of both Cornwall and Richmond are encouraging for the opening of a sizeable distribution centre. Cornwall’s current unemployment levels sits “at or slightly below national and provincial levels” at 8.1% (June 2015) (Baker, Lois Ann). Virginia’s unemployment rate is nearly half of Cornwall’s, at 4.3% as of January 2017 (“Richmond, VA Economy At A Glance”). One could interpret these figures to show that Cornwall has a larger available labour force, however, when taking their respective total populations into account (Cornwall 46,000 as of 2016) (“Cornwall Demographics & Population Information”) (Richmond 221,679

as of 2016) (“Demographics”) Richmond has roughly 2.6 times the number of unemployed individuals as Cornwall at 9,532 to 3,726 respectively.

 

Region

Population

Unemployment Rate

Number of unemployed

Difference

Cornwall

46,000

8.1%

 3,726

-5,806

Richmond

221,679

4.3%

 9,532

 5,806

 

Exchange Rate

 

         While this report focusses in large part to the staffing factors which affect site selection, as the two proposed locations are located within different countries, the cash flow factor of exchange rates is to be considered. The CAD to USD exchange rate has fluctuated considerable over the past decade ranging from below par to 135.52% (“XE Currency Charts: USD to CAD”). This forecast was derived using a trendline which takes into account each year’s average exchange rate from 2007 to 2016 (“Annual Average Exchange Rates”).

            It determines the recent increase to be rather drastic when compared to less recent years and suggests a less steep climb in the coming 5 years. An above par exchange rate considerably favours setting up the distribution centre in Richmond as it has a direct impact on net payroll expenses.

 

Conclusion

 

            Taking into consideration all required contributions including employment insurance, health care, vacation and government pension plans, the city of Richmond, Virginia is clearly the less expensive option. As setting up a distribution centre, staffed with 501 full time minimum wage employees in Richmond will result in a net payroll of $10,932,642 per year vs $13,395,413 per year in Cornwall, and considering how lower operating costs is the primary objective in this site selection process, the decision is clear. Richmond, Virginia should be selected due to its total net payroll cost savings of $2,462,771 per year when compared to Cornwall, aided by a lower minimum wage rate and a net benefits cost savings advantage of $625,045 per year.

 

Differences

Cornwall, On

Richmond, VA

Benefits and Taxes per Employee Difference

 $1,248

-$1,248

Net Benefits and Taxes Difference

 $625,045

-$625,045

Net Payroll per Employee Difference

 $4,916

-$4,916

Total Net Payroll Difference

 $2,462,771

-$2,462,771

 

            Unsubsidized parental leave and un-mandated minimum vacation requirements run the risk of contributing to decreased workplace moral and overall productivity. However, in Virginia, management will have the option of employing these two benefits should they choose to, as opposed to having them forced upon the organization by the Federal Government of Canada.

0

Logistics, Supply Chain Management, Transportation
The Tesla Semi-trailer truck press launch on November 17th, 2017 wowed a hanger full of tech enthusiasts who likely have never stepped foot inside a logistics office or an actual semi themselves. I mean come on, when was the last time you heard a logistician cheer and scream in that manner? The first of its kind electric semi boasts the following impressive claims:
  • 500 miles of range on a full charge at maximum weight
  • 30 minute charge time to 80% capacity
  • 0-60mph in five seconds with an empty trailer
  • 60mph in 20 seconds with a max load of 80,000lbs
  • A one million mile warranty
  • No brake pads to replace
  • No transmission to replace
  • Nuclear explosion-proof glass
  • Zero emissions – the big one!
All resulting in Tesla claiming for their semi to have a true cost-of-trucking at least 20% lower than a traditional diesel truck. However, things get interesting when platooning, or convoying as Elon Musk calls it, is taken into account. What is Platooning? Put simply: platooning is how semi-autonomous vehicles will disrupt the logistics industry, driving down prices all the way down the supply chain to the end user, laying off many truck drivers in the process. Platooning trucks and trailers is similar to rail transport. The driver-equipped semi in the front acts as a locomotive, while autonomous trucks are pulled behind using radio frequencies acting as rail cars. This leads to the cost savings associated with not requiring a driver, their subsequent salary and benefits, to operate every truck. That said, while Musk claims Tesla has already developed this technology and is confident it will result in 10x higher highway safety for truckers and commuters, there are many unclear details as to how platooning will operate. My questions include:
  • Will the platooning semis have to be equipped with drivers? That would defeat the purpose of autonomous vehicles
  • If not, what will the cabins look like? Will there even be a cabin? You wouldn’t really need one…
  • What is the maximum number of trailers that can platoon behind one driver equipped semi?
  • Will Megacharge stations have the ability to handle the semi numbers associated with multiple platoons? Will this cause excessive wait times at Megacharge stations?
In platooning, or “convoying”, the Tesla Semi is said to poses half the true cost-of-trucking of a diesel truck. Musk claims a platoon of Tesla electric Semis can beat the price of rail, while the convoy would remain far more flexible, not being tied to a network of railways. This is not particularly fair to diesel trucks, as it is only a matter of time until they too will gain the ability to platoon. In reality, many limitations regarding platoon trailer size will likely be caused by government restrictions, as we all know that technology moves exceptionally faster than government regulation. Also, don’t expect to see a convoy of any number of semi-autonomous trailers blocking up your city’s downtown core any time soon. Platoons will only be utilized for long-haul trips on major highways and possibly in remote areas, at least in its primary stages. Drivers will certainly be required for shunting purposes, in a way acting as tug boat operators within the distribution centre lot and for last mile delivery if the convoy is to split up. Some have proposed integrated offices in the cabin, so that drivers who are being platooned (and are therefore not driving) can perform logistics documentation and paperwork. However, I have a hard time believing 4PL logistic companies will pay to train all their drivers to perform work already performed by their centralized logistics coordinators.
0

SAP, Supply Chain Management
This ASUG Roadshow provided SAP customers with a place to meet and learn about the road to SAP S/4HANA from experienced partners, customers, and SAP experts.




The December Toronto event featured:

  • The Art of the Possible with SAP S/4HANA presented by Kim Cybulski S/4HANA Solution Director from SAP who described Recent innovations, new business processes and S/4HANA advantages over ECC
  • Followed by: Charting the Course of Deployment Options: Which provided insight as to which conversion direction could be best for you, and what key considerations should be made: a collaboration between Curtis Gaska, Solutions Architect at Symmetry and Alan Rudolph EVP and General Manager at smartShift
  • Lorraine Howell, VP of Research and Development at Illumiti then explained the advantages of an S/4HANA implementation from both greenfield and system conversion perspectives
  • A Customer Story where Sharon Kaiser, CIO of New England Biolabs described her implementation process and a few words of advice: start gathering your data 1.5 years prior to implementation or conversion and don’t be afraid!
  • Kim Cybulski then returned to the stage to explain how to Chart our SAP S/4HANA Adventure: Considerations, prep work, dos and don’ts, along with methods, tools and services available from S/4HANA 1709


This has been SAP Certified Associate Adam Kwitko signing off from the ASUG roadshow: Your Journey to SAP S/4HANA in downtown Toronto. Visit my blog, adamkwitko.com/blog where I write and post videos on topics pertaining to supply chain and connect with me on LinkedIn.
0

PREVIOUS POSTSPage 1 of 2NO NEW POSTS