Using Excel Spreadsheet for Inventory Analysis Drago Pupavac, Marija Baburi Polytehnic of Rijeka, Croatia University of Applied Sciences Nikola Tesla in Gospi drago.pupavac@veleri.hr; mbaburic@net.hr Abstract - This paper discusses how inventory items can be classified in different classes (called ABC analysis) using Excel spreadsheet. The working hypothesis connects efficacy of estimated spreadsheet to how students understand difference between classes of inventory proved on a practical example. In this way the given model can be applied to all ABC inventory analysis of similar problem capacity. Applied scientific research for recognition of the set hypothesis is based on analysis and synthesis method, abstraction method and information modeling method. I. INTRODUCTION Classifying inventory has been taught at Polytechnic department of entrepreneurship in Logistics for entrepreneurship course during the second year. Students like this topic and enjoy solving practical problems. Nevertheless students made a lot of mistakes while doing paper version exercises or exam and a number of students doesn’t quite understand what the difference between the classes is. It feels appropriate to transfer some exercises from courses to computers. In this way students will learn how important the some of the item is and recognize that inventory classification is usually a first step toward efficient inventory management. II. ABC ANALYSIS The need to rank inventory items in terms of importance was first recognized in 1951 by H. Ford Dicky of General Electric He suggested that GE classify items according to relative sales volume, cash flows, lead time, or stockout costs. He used what we now refer to as ABC analysis for this particular classification scheme. Analysis of a range of items which have different levels of significance and should be handled or controlled differently. It is a form of Pareto analysis in which the items (such as activities, customers, documents, inventory items, sales territories) are grouped into three categories (A, B, and C) in order of their estimated importance. 'A' items are very important, 'B' items are important, 'C' items are marginally important.. ABC analysis divides on-hand inventory into three classifications on the basis of greatest impact or value. For example, the best customers (typically 20 percent of the total number of customers) who yield highest revenue (typically 80 percent of the total revenue) are given the 'A' rating, are usually serviced by the sales manager, and receive most attention. 'B' and 'C' customers warrant progressively less attention and are serviced accordingly. In inventory terms, this suggests that a relatively small number of items or stock-keeping units (SKUs) may account for a considerable impact or value. It is not realistic to monitor inexpensive items with the same intensity as very expensive items. The ABC tool is used to identify the “vital few” from the “trivial many”, according to a defined set of criteria (e.g. annual expenditure, number of orders, number of claims, occupied space in the inventory, etc.). ABC classification is relatively simple. The first step is to select some criterion, such as sales revenue, for developing the ranking. Second step is to collect data about the analyzed items. The next step is to rank items in descending order of importance according to this criterion and to calculate actual and cumulative total sales revenue percent-ages for each item. This calculation should help to group the items into ABC categories. Class A items are those on which the annual dollar volume is high. Although such items may represent only about 20% of the total inventory items, they represent 80% of the total dollar usage. Class B items are those inventory items of medium annual dollar volume. These items may represent about 30% of inventory items and 15% of the total value. Those with low annual dollar volume are Class C, which may represent only 5% of the annual dollar volume but about 55% of the total inventory items. Graphically, the inventory of many organizations would appear as presented in Figure 1. Figure 1: Graphic Representation of ABC Analysis