The Brief
The brief of this project was to create a Prototype Benchmarking Tool for the Supply Chain of a business with Microsoft Excel VBA. The tool should use Porter’s Value Chain as a framework to evaluate the supply chain network. The focus of the project was developing the model of the analysis tool, however the accompanying report also featured a review of literature on supply chain quantification and benchmarking models, flowcharts of data input and output and a case study using the developed Excel VBA model.
Overview
The supply chain is a vital component in increasing profitability, achieving organizational goals and gaining the competitive advantage, in any business. The supply chain covers a vast number of activities involved in the process of producing and selling a product. It includes a network of organizations, information, people and resources that participate in the creation of a product to its delivery to the end customer. The supply chain is defined as a system composed of elements such as “material suppliers, production facilities, distribution services and customers linked together via the feed forward flow of materials and the feedback flow of information” (Gunasekaran, Patel, Tirtiroglu, 2001).
In modern global business, companies are striving towards creating an efficient supply chain to increase productivity and profitability. Supply Chain Management (SCM), is an essential aspect to competitive strategy. SCM has developed from the globalization of markets and an increased trend in outsourcing as businesses aim to become lean.
Michael Porter devised the Value Chain framework as a method to examine the supply chain through specific activities which firms create value and reinforce competitive advantage through. Porter divided these activities into primary activities, which are directly connected to the manufacturing and sale of a product, and support activities which support each primary activity (Porter, 1985).
The report focused on the supply chain and the development of a Microsoft Excel benchmarking tool. The model used a number of metrics to evaluate the supply chain, and its generic structure allowed for the assessment of businesses of different scales. In order to create the model to allow for effective quantification of the supply chain, current benchmarking models were first analysed.
Porter's Value Chain, 1985
Applications for an Excel Model
Applying the literature findings to an Excel Model will yield a useful tool for benchmarking the supply chain performance. The Excel tool will be based off the structure of Porter’s Value Chain. Most notably, the utilisation of the metrics outlined in the accompanying literature study in the report are important. The measurement metrics identified can be used to rank the overall performance of the supply chain activity, which can then be used to calculate the supply chain’s overall performance score.
Model Structure
The Excel VBA tool is an interface designed to quantify the supply chain performance of a business, using Porter’s Value Chain as a model. The user is taken through a series of questions, grouped under the headings of the activities in Porter’s value chain. The user inputs are then translated into a score for each activity, which are then used to produce an overall score for the supply chain.
The tool has 72 questions based on the metrics of Porter’s Value Chain. The first page of the tool, asks a number of introductory questions, establishing the size of the business, the turnover and the industry sector. The user is guided through the questionnaire through the use of Excel VBA buttons, at the bottom of each page.
The questions used in the program were based on the performance metrics identified in the literature study. For example, for the supply chain activity of Inbound Logistics, this included measurements such as number of suppliers, cost of transportation, storage costs, purchase order lead time, the quality of the inbound materials and how the materials are transported to the firm.
For each question there are a number of set answers the user can choose from. Depending on the question these answers may be numerical. Each set answer for each question had a numerical value assigned to it. When the user input their answer, the value corresponding to their answer was then input to the table. Upon completion of the section, the user would then receive a percentage score.
The best possible answer of each question was assigned a value of one. The table would produce an overall score. The lower the score the better the performance of the activity. Some questions asked in the Excel program are of greater importance than others. Due to this, some questions have a weighting attached. A number of the input answer values have a weighting applied before calculating the final overall score.
The total score for each section is translated to a percentage score before being displayed to the user. This is displayed at the bottom of each activity page upon completion, and on the final results page.
Upon completion of the program the user is taken to the results page. The results page displays the percentage score of each activity, alongside a graph. The graph is a visual representation of the scores, however it has an immediate impact as straight away the user can identify where the weakness, or strength is in the supply chain.Model Limitations
There a number of limitations associated with developing a system to benchmark the supply chain process. There is a vast range of systems and methods employed in the supply chain of which many require particular measurement systems for optimal results which contributes to the difficulty of creating a general supply chain performance measurement process (Beamon, 1999). For optimal results, the supply chain cannot be measured purely on quantitative performance measures, however qualitative evaluations can be difficult to define when analysing the performance of a system.
When adapting the programme for universal use, a number of improvements can be made. For example, this model was created for the use of an Irish based manufacturing business. When adapting this for general use, on the opening page there would also be a location question, as this would influence factors such as the location of the supplier, or wholesalers. Ideally, factors such as firm size, location and turnover, would be taken into account and would alter the weightings for the answers to questions. For instance, for a small firm, it is not practical to have hundreds of suppliers, while a large firm may have thousands of suppliers around the globe.
In every company, the value chain activities play a role in the effectiveness of the supply chain, however depending on the industry sector a particular activity may be critical. Greater accuracy in the supply chain evaluation score would be achieved by applying a weighting for the activities within the value chain, as depending on the industrial sector, different activities have varying importance. For example, for a manufacturing business, Human Resource is not as crucial as Technology Development. For a distributor, inbound and outbound logistics are clearly most important. The weighting given to the various activities is relevant to the sector the business is in. When adjusting the Excel Program for general use the weighting applied to activities would be adjusted when the user identifies the firm’s industry.
The questions asked in the model could be developed and expanded. Industry experts and individuals knowledgeable in supply chain management could be consulted in order to improve the questions and answers in order to produce more accurate and detailed results.
Conclusion
It is evident that a proactive strategy towards supply chain management will result in increased competitiveness and increased financial output. The activities within the supply chain are interdependent and have an impact on the overall performance of the supply chain. Developing and improving one activity or task within the supply chain will have a ripple effect and have a positive effect on another task. For example, by implementing a JIT strategy, the need for storage before manufacturing is reduced, which also decreases cost and lead-times.
In order to maximise competitive advantage, performance measurement must be carried out regularly to identify any improvements necessary. Common goals, such as customer satisfaction, should be a priority throughout the supply chain to enhance competitiveness (Gunasekaran, Patel, McGaughey, 2003).
Developing the model Excel Programme discussed in this report will enable firms to measure and monitor the performance of the supply chain through the activities defined by Porter’s Value Chain. With this proposed evaluation method, the entire system will be analysed and individual activities can be examined in order to diagnose potential weaknesses or strengths.
References
Beamon, B. (1999). Measuring Supply Chain Performance. International Journal of Operations & Production Management, 19(3).
Gunasekaran, A., Patel, C. and Tirtiroglu, E. (2001). Performance measures and metrics in a supply chain environment. International Journal of Operations & Production Management, 21.
Gunasekaran, A., Patel, C. and McGaughey, R. (2003). A framework for supply chain performance measurement. International Journal of Production Economics
Porter, M. (1985). Competitive Advantage: Creating and Sustaining Superior Performance New York: Free Press.