System Design For Inventory Control In Tertiary Institution Using Distributed Database Model

System Design For Inventory Control In Tertiary Institution Using Distributed Database Model

System Design For Inventory Control In Tertiary Institution Using Distributed Database Model

Dr. Ejiofor V E*, Dr. Boniface Ekechukwu*, David Sunday Madaki**

*Department of Computer Science, Nnamdi Azikiwe University, PMB5025 Awka,

Anambra State, Nigeria .

**Department of Computer Science, Fed. Collage of Education(Tech) Asaba, Delta State, Nigeria.

Abstract

An inventory control system is a process for managing and locating objects or materials. In common usage, the term may also refer to just the system solution. Inventory objects could include any kind of physical asset: merchandise, consumables, fixed assets, circulating tools, library books, or capital equipment. The management of an organization using these models is complex and further complicated by the vast number of items in the organisation. In fact, more than 300,000 active items needed to be controlled in the organisation. This research has developed inventory software for control and tracking of warehouse, consumables, fixed assets, circulating tools, library books, or capital equipment for tertiary institutions. The management of these items can only be done with the aid of a computer; hence the complex items have a computerized item inventory database. Each of the items that is supplied or replenished is continuously keyed into the computer and the inventory stock parameters are updated automatically. The company can now use a software package for its inventory control. The system can be used to identify the index number of the item. From the index number, the location of the item in the stock room is identified. Inventory control model is a tool to help you manage your inventory and operate your pricing strategy, for an organization. This is a model designed to Study the market, strategize a price, and beat the competition. Inventory control model is used to develop and enact a

complex, automated pricing strategy, based on real-time analysis of your industry. It is used to respond to market changes immediately with unlimited lightning-fast updates.

Introduction

Many things can be achieved in modern information systems using distributed database model. Inventory control system is one of the modern aspects of information systems. In consequence, distributed database model has been discovered in this research as suitable tool for the design and development of inventory control system in tertiary institutions. Within manufacturing, distribution and retail environments, inventory can be the most valuable asset. For that reason, companies employ the use of inventory control systems. Inventory control systems serve the primary purpose of keeping an accurate inventory. To understand an inventory control system, you should learn what defines an inventory as “accurate,” the most common transactions that affect inventory, and physical- and cycle-counting inventory audits. In modern time, standards have risen to such levels that it is difficult, if not impossible to control inventory by manual form of optimization. In most cases, the structure of an organisation inhibits service differentiation of demands thereby creating an environment where rationing can be applied. Thus, the approach in this research is to incorporate rationing to the current practice of the company with three demand classes differentiated by their demand lead-times. The motivation in taking this approach is that the researcher believes it will result in better system performance given certain service level requirements. Consideration was made to orders from the transport firm as the highest priority class, failure and maintenance orders from the maintenance section as the medium priority class while orders from external customer as the lowest priority class. This research also established two static threshold levels in order to

model a single location system facing an assumed Poisson arrival rates for the classes. The Gold class has zero lead time while the Silver has a shorter but positive lead time than the bronze counterpart. However, the simulation of a Model-Driven Decision Support system would incorporate the continuous review, one to one lot, service differentiation, backordering, demand lead time, threshold rationing and clearing mechanism) items inventory policies with replenishment lead times inclusive so as to find to find the fill rates as well as the average number of backorders for the demand classes. This is because the knowledge of the fill rates (probability of no stockout) and the rate at which demands are backordered can help the company predict optimal parameters for inventory. Note that the magnificent power of information technology is once again used to provide succour to this ailing but important sector of the society. The study employs the availability of the computing power and speed to build a hypothetical system which can impact positively inventory control. In specific terms, a robust programming languages (VB. Net) is used to model events and to provide optimal heuristics for proposed inventory policies. In other words this study depicts the details of a Discrete Event Simulation of stochastic demands for items Inventory Control. Distributed database model has been applied in this system to enable independent manipulation of data from various locations(institutions).

Materials and Methods

This section contains the analysis of some models developed for inventory control and management and why they are unsuitable for solving the problems that motivated this study. First, is the distributed database model for various institutions. Here, the management of the inventory is done with the aid of a computer; hence the inventory complexity has a computerized inventory database in a distributed model to facilitate various institutional manipulation of data. Each

of the inventory that is supplied or replenished is continuously keyed into the computer and the inventory stock parameters are updated automatically. The institutions use a software package for their inventory control. This is used to identify the index number of the item. The software will search and pop up the index number of the item. From the index number, the location of the item in the stock room is identified. This is how the institution performs its inventory management and control.

The allocation of inventory to demand classes has become increasingly dynamic, complex and lies at the heart of almost every management problem. This is due to limited capacity and perishable inventory. Several industries have innovated ways to deal with demands from customers in view of the service level agreements and price as the case maybe.

According to Isotupa (2005), to show that some of the equipments/parts are very critical for the smooth running of the operations and needs to be serviced on a priority basis, while other equipment is less critical and will have lower priority. The two extremes are Ordinary/Priority as was used by Isotupa (2005), Critical/Non-critical as was used by Rosetti and Thomas, Gold and silver as was used by Vicil and Jackson (2006) and High/Low Priority Demand as was used by Okonkwo (2010) and so many other authors. Different class of customers may represent different importance to the supplier in a like manner or different customer may have differing stockout costs, penalty costs or different minimum service level. That is why it is overly necessary to distinguish between classes of customers (Customer Differentiation) i.e. the consequence of multiple demand classes, hence the handling of different customers in a non-uniform way. In this way, it would be very easy to assign a different service level to each class. Also the practical implementation of this policy would be relatively easy and will require

less mathematical analysis. But the drawback of this policy is that there is no advantage from the so-called portfolio effect. In other words, the advantage of pooling demand from different demand sources together would no longer be utilized. Therefore, as a result of the increasing variability of demand, more safety stock would be needed to ensure a minimum required service level which in turn means more inventory.

On the other side, one could simply use the same pool of inventory to satisfy demand from various customer classes without differentiating them. In this case, the highest required service level would determine the total inventory needed and thus the inventory cost. The drawback of this policy is that higher service level will be offered to the rest of the demand classes, a deficiency that would lead to increased inventory costs. Critical level/threshold level/rationing level policy essentially lies between these two extremes. It requires complex mathematical analysis, but the gains outweigh the task involved.

Systems Design Using Distributed Database Model

Distributed database is a database in which the data is contained within a number of separate subsystems, usually in different physical locations. If the constituent subsystems are essentially similar, the system is said to be homogeneous, otherwise it is said to be heterogeneous. Distributed database systems may vary considerably. At one extreme is the type where the complete system was conceived, designed, and implemented as a single entity; such systems exist within large commercial organizations and are usually homogeneous. At the other extreme is the case where a number of existing systems originally planned as isolated systems continue in their normal operation but in addition are loosely linked to provide a larger distributed system; in this instance the system is often

heterogeneous. Data is stored at several sites, several tertiary institutions, each managed by a Database Management System that can run independently.

GATEWAY

GATEWAY

GATEWAY

GATEWAY

GATEWAY

GATEWAY

DBMS1

DBMS2

DBMS3

DBMS1

DBMS2

DBMS3

Distributed Database Architecture

One of the principle advantages of a DBMS is that the same information can be made available to different users(Tertiary Institutions). The data in a DBMS is more concise because, as a general rule, the information in it appears just once. This reduces data redundancy, or in other words, the need to repeat the same data over and over again. Minimizing redundancy can therefore significantly reduce the cost of storing information on hard drives and other storage devices. In contrast, data fields are commonly repeated in multiple files when a file management system is used.

ARRIVAL RATE SENSITIVITY ANALYSIS

Dataset [MST = 100, S = 10, K2 = 5, K1 = 2, 2 = 3.25, = 2.56, SLd = BLd

= 0.12, L = 0.5]

Fill Rates results on Arrival Rate Sensitivity Analysis

FIRST THRESHOLD LEVEL SENSITIVITY ANALYSIS

Fill Rates on First Threshold Level Sensitivity Analysis

Dataset [MST = 100, S = 15, K1 = 2, 1 = 1.45, 2 = 3.25, = 2.56, SLd = L

= 0.5, BLd = 0.12]

Fill Rates Results on First Threshold Level Sensitivity Analysis SECOND THRESHOLD LEVEL SENSITIVITY ANALYSIS

Fill Rates on Second Threshold Level Sensitivity Analysis

Dataset [MST = 100, K2 = 8, 1 = 2.56, 2 = 3.25, = 2.56, SLd = L = 0.5,

BLd = 0.12]

Input Dialogue Box for the Model

DISCUSSION OF FINDINGS

Even though the system is a stochastic one, that is unpredictable and has the ability to give different results, some consistent behaviour has be observed. This consistency goes to match the intended policies with which this research coded the application and the expected behavioural output which was to some extent made evident in the concept modelling. This research notes the observations and findings of the simulation experiments done in this study. They include:

The fill rate was increased while the three classes deteriorated. This sensitivity was obvious in the demand class at = 5.56, where none of its demands were filled because the value for the demand lead time is small. It was also seen that their values were almost converging as the arrival rate was increased. This implies that if the arrival rate is moving up steadily, it will reach a point were no demand class will be filled. Though not perfectly so, the average number of backorders increased with increases in the arrival rate. The first threshold level was increased to meet the base stock level and the fill rates began to suffer. At point 8 and 10 it hit zero, meaning that no demand was attended to. This is in line with the threshold rationing policy of the simulation model that allows the filling of three classes above the K2. But as the threshold level was increased, the demands were filled less. The lines followed the same pattern. This is possible because of the close proximity between their inter-arrival rates (i.e. = 1/0.14=7.14 and = 1/0.12 = 8.33. This also shows that if the demand lead times of demand are far apart, a remarkable difference is highly inevitable.

References

Acharya, D., Nagbhushanam, G., and Alam, S.S. (1986), Jointly Optimal Block Replacement and Spare Provisioning Policy, IEEE Transactions on Reliability, Vol. R-35, No. 4, pp447-451.

Arslan, H., Ayhan, H., Olsen T. V. (2001). Analytic models for when and how to expedite in make-to-order systems, IIE Transactions, 33, 10191029.

Bradley, J. (2003) A Brownian Approximation of a Production-Inventory System with a Manufacturer that Subcontracts. Working paper, Jonhnson Graduate School of Business, Cornell University.

Bonczek R.H., Holsapple C.W., Whinston A.B. (1981) Foundatios of Decision Support Systems, Academic Press, New York.

Cohen, M. A., P. R. Kleindorfer, and H. L Lee. 1988. Service Constrained (s, S) Inventory Systems with Priority Demand Classes and Lost Sales, Management Science, 34(4), 482-499.

Deshpande, V. Cohen, M.A., Donohue K. (2003) A threshold inventory rationing policy for service-differentiated demand classes.Management Science 49, 683 703.

Dekker, R., Kleijn M. J., Rooij P. J. (1998) A spare parts stocking system based on equipment criticality. International Journal of Production Economics, 56:69- 77,

Dooley, K. (2002), Simulation Research Methods, Companion to Organizations,Joel Baum (ed.), London: Blackwell, p. 829-848.

Frank, K. C., Zhang R. Q., Duenyas I. (2003) Optimal policies for inventory systems with priority demand classes. Operations Research, 51:993-1002.

Gachet, A. (2004). Building Model-Driven Decision Support Systems with Dicodess. Zurich, VDF.

Ha, A. Y. 1997 Inventory Rationing in a Make-to-Stock Production System with Several Demand Classes and Lost Sales. Management Science, 43(8), 1093- 1103.

Ha, A. Y. 1997 Stock Rationing Policy for a make-to-stock production system with two priority classes and backordering, Naval Research Logistics, , 43, 458- 472.

Ha, A. Y. 2000. Stock Rationing in an M|Ek|1 Make-to-Stock Queue, Management Science. 46(1), 77-87.

Haettenschwiler, P. (1999). Neues anwenderfreundliches Konzept der Entscheidungsunterstützung.Gutes Entscheiden in Wirtschaft, Politik und Gesellschaft. Zurich, vdf Hochschulverlag AG: 189-208.

Harvey, M. 1989 Principles of Operations Research: with Applications to Management Decisions, 2nd Ed. Prentice-Hall Inc., USA.

Henk G. Sol et al. (1987). Expert systems and artificial intelligence in decision support systems Proceedings of the Second Mini Euroconference, Lunteren, The Netherlands, Springer, pp1-2.

Heuts, R.M. et al. (1999) A combined forecast-inventory control procedure for spare partsConference on Flexible Automation & Intelligent Manufacturing.

Holsapple, C.W., and A. B. Whinston. (1996). Decision Support Systems: A Knowledge-Based Approach. St. Paul: West Publishing. ISBN 0-324-03578-0

Ilgin, M. A. and Gupta, S. M., (2008) “Using Simulation Optimization for Parts Recovery and Spare Parts Inventory Management in Post Product Life Cycle”, Proceedings of the2008 Northeast Decision Sciences Institute Conference, Brooklyn, New York.