Boğaziçi University

Department of Management Information Systems

MIS 463 Decision Support Systems for Business

PROJECT FINAL REPORT

INVENTORY PLANNING SYSTEM FOR DURABLE GOODS COMPANIES

Project Team No: 6

Yasin Mat

Ali Öztemür

Kemal Sütçü

Burak Türk

Instructor : Aslı Sencer Erdem

İstanbul - December, 2009

I. INTRODUCTION

Durable goods sector has one of the most complex decisions making environments; especially when we consider inventory management and planning issue. There are several factors causing that. For instance, there are numerous types of products which require numerous types of components to be produced. In current real economy, companies want and need to avoid uncertainty as much as they can to be able to get one step ahead of their competitors. Therefore, for durable goods companies, it is very crucial to plan their future and prepare themselves accordingly. To achieve this, planning must be done at department’s level in companies. Since it is very important to plan yearly basis production amounts to gain success in market, purchasing departments must be able to satisfy the demand(number of products to produce) coming from strategic planning, sales or marketing departments by making accurate number of component purchases and keeping inventory levels adequate and optimal levels to gain cost efficiencies.

I.1 The Decision Environment

The decision problem is for purchasing departments in durable goods companies to optimize purchase amounts of components required for production and inventory levels on a yearly basis (month-by-month) in order to minimize cost.

In this our system the main decision is to forecast amount of purchases of components on month-by-month basis in a 6-month period to satisfy the demand of end-products which comes from sales, marketing or strategic planning departments. Naturally, associated decisions involve keeping optimal inventory levels of components and minimizing overall cost of purchasing and inventory holding.

The decision makers are purchasing departments in companies which operates in durable goods sector.

Our system’s scope will be limited to decision environment of purchasing departments only. Decision will involve a 6-month period on a month-by-month basis. That is, the input parameters will be entered to the system at the beginning of a 6-month period and our system will produce a plan for component purchases and inventory levels for each month, which of course, minimizes the cost associated with purchasing and inventory holding processes. The plan may be revised in any period by changing input parameters.

By its nature it is very complex problem since the data involved is extremely huge and number of related variables is high. A single refrigerator has approximately hundred kinds of components in it. Considering number of product types and models produced by a durable goods company and components used in each product, we can see the how huge the data is. Moreover there are many parameters which affect the price of each component. These parameters may involve macroeconomic conditions, current sector conditions etc. We have to know and use the information about which parameters affect price of which components and to what extent in order to estimate monthly basis prices of each component in a 6-month period.

In addition to complexity of this real life problem, the constraints and bottlenecks make it a harder challenge. Capacity of inventory, for instance, is one constraint. Demand coming from sales, marketing or strategic planning department might be so high that inventory capacity may become inadequate to store components required for production. To cope with this these departments must work in coordination or other solutions like renting inventories or reducing forecasted production demand must be considered.

Currently, most of the companies in this sector does not use computerized decision support systems which reduces their efficiency and effectiveness of their processes. This causes inefficient use of labor and time.

If companies cannot plan their future production, purchasing and inventory amounts accurately they will lose their competitive advantage in the market. They have to be cost efficient in their operational processes to gain competitive advantage. If input parameters are entered wrongly or, which is most likely without computerized DSS; calculations, estimations or forecasts based on these parameters made wrongly and inaccurately, losses would be very high.

Considering all factors stated above, like complexity of problem, volume of data, cost of erroneous decision etc., it is very crucial and necessary for durable goods companies to use computerized decision support systems in their purchasing and inventory planning processes.

I.2 Mission of Project

The mission of project is to design a computerized decision support system which will help durable goods companies to plan their future component purchases and inventory volumes accurately.

To fulfill this mission, we have the following goals:

»  To minimize costs associated with purchasing and inventory holding

»  Satisfy the demand coming from other departments in a best possible way

»  To keep inventory in balance

»  Design the system in a user friendly and sensitive way

I.3 Scope of Project

The scope of the system is limited with decisions associated with purchasing department. These decisions mainly are:

»  Purchase timings of specific component in a 6-month period(Monthly basis)

»  Purchase amount of specific component in a 6-month period(Monthly Basis)

»  Inventory balances in monthly basis

We are not coping with profitability of end-products. That is the aim is not, for instance, maximizing profits but it is minimizing overall cost of purchasing and inventory holding. We do not deal with about supplier or contract selection in this system, as well.

I.4 Methodology

We are going to use operations research techniques(specifically, inventory balance model) to minimize cost associated with purchasing and inventory holding, and keep inventory in balance. You will find more details in development of DSS section

II. LITERATURE SURVEY

The integration and cooperation between marketing, sales and purchasing departments is crucial to success of companies. Besides purchasing departments, marketing departments shift from a focus on the exchange of goods to the provision of capabilities. The relationship between marketing and purchasing is being structured as a result of shift from product- to capability-focused commerce (Sheth, Sharma, & Iyer, 2009). Thus, the capabilities and performance of purchasing department influence the success of marketing department; which in turn affect the overall performance of firm in the market.

Interactions between marketing and purchasing occur in more operational dictates such as implementation of just-in-time systems, development of forecasting models, and inventory management. Traditionally, primary concern of marketing is demand generation and fulfillment of customer needs, while purchasing has focused attention on suppliers to ensure manufacturing and capacity utilization.

Figure 2.1: Distinct foci of marketing and purchasing (Sheth, Sharma, & Iyer, 2009)

As seen on figure 2.1, dyadic relations for marketing and purchasing are different and therefore, their priorities and strategies are often independent and contradictory to one another. For a manufacturer, the most important thing is to decide the optimal replenishment schedule for raw components and the optimal production plan for finished products (He, Wang, & Lai, 2009). However it is very important to coordinate marketing and purchasing intentions because purchasing performance significantly affects overall performance of organization. For instance; the classic organizational problem of “make-versus-buy” to meet customer demand was determined by the evaluation of production and contracting costs which is significantly affected by purchasing performance.

Performance and effectiveness of purchasing departments and components management systems are measured by variables such as inventory turnover, fill rates, average stock levels etc (Yuthas, & Young, 1998). Therefore we deducted that it is very important for computerized decision support systems to calculate and measure these variables.

In our decision support system, we aim to plan annual purchasing amounts and inventory balances on a month-by-month basis. In result we will calculate minimized cost of purchasing, inventory carrying and inventory holding costs and make sure that company will have the required adequate inventory level for the next period of production. Inventory balance equation is crucial understanding and modeling our system.

I i,t = Ii,t-1 + Xi,t – Di,t
Figure2.2: Inventory Balance Equation (Denizel, Altekin, Süral, & Stadtler, 2007)

Where ;

I i,t : Inventory level for period t

Ii,t-1 : Inventory level transferred from period t-1

Xi,t : Component amount purchased in period t

Di,t : Demand for components for production in period t

Moreover, if the item received earlier from the production period of end item, an inventory holding cost h will be incurred for each time unit the item is held in inventory after arrival and until t*. Thus we plan to include monthly holding cost of each component in component table in database. We will use this to calculate total cost of component for period t (production period) (Ronen, 1986).

Besides inventory holding, the system must consider opportunity effects of buying component earlier than production period such as interest rate (Sana, 2009) because if interest rate is high enough, it may become more reasonable to buy component closer to production period rather than buying it earlier and incurring more inventory holding and carrying costs.

To conclude, our modal will aim to achieve:

»  Calculation of component costs for 6-month period

»  Calculation of monthly minimized purchasing costs

»  Calculation of monthly optimal inventory levels

IV. DEVELOPMENT OF THE DSS

IV.1. DSS Architecture

Figure 4.1: Context Diagram

In this context diagram, you see 3 external entities that interact with the system. There are several data flows that each entity sends to system. Marketing Department sends the production demand file for each product type on monthly basis which is in MS Excel format. Management sends the parameters file which includes monthly values of each parameter. This file also is required to be in MS Excel format. System uses this information to forecast the cost of each component for a 6- month on monthly basis. Management also sends monthly budget file which shows monthly available budget for purchasing. Purchasing Department sends the product info and component info such as space required to store each component, which component is used in which product in what amount, components’ cost in current term(month), parameter-component match(which parameter affects which component) etc. Purchasing Department also sends capacity constraint which is available warehouse space to store products. System uses these data flows and produces a purchase plan including monthly inventory levels which minimizes the combined cost of purchasing and inventory holding.

IV.2. Technical Issues

The inventory planning system for durable good companies is going to consist of three main components to do its job properly. They are:

»  Database,

» MS Excel 2007,

»  Application(C#).

We want our system to be flexible, so that both user and we can move the system from one computer system to another, and run it in various systems. So, we decided to use the most common tools to develop this system. As database, we are using Microsoft Access which is easy to create, manage and move. The database contains product definitions, current inventory data and monthly parameters of the components to calculate future prices of them.

Here is the Entity-Relationship Diagram (ERD):

Figure 4.2: ERD

The ERD of our database includes 9 entities, 2 of which are associative ones,

Product entity holds some basic information of each product. Similarly, Component entity stores required data for each component including component definition, storage cost (monthly holding cost of an item), storage requirement (the space required to store that item in the warehouse). Each component can be used for more than one product, and each product requires more than one component to get produced. This situation leaves us to the associative entity named Product Tree. This entity holds which component is required for which product and what amount. By using Product Tree entity we explode the production demand for each product and obtain how much required for each component for each month.

Budget of each month gets defined in the entity of Budget which has no relations with other entities. The monthly budget info comes as a batch file which is an .xls format, our system process it and store it to the database for further usage in our model base. Demand entity includes the demand of each product for each month. This entity also works in similar way with Budget entity

One other entity of the database is Parameter. These parameters are the ones which are going to be used to estimate future prices of the components. Each parameter has a different value for different months. So, here there is another associative entity called Estimated Change that holds parameter values for each month. This entity also is filled by processing an input batch file which is sent by management of company.

Moreover, in User table we hold the information of users who use our system which holds user name and password of each user.

Finally, the last entity, which is also an associative one, is Component Cost Structure. This entity stores that which parameter affects which component and at which proportion.

The second part of the project is Excel Solver which is used to solve our optimization formula. The solver uses the data stored in the database, and then calculates an annual inventory plan.

The application is a desktop application, and interacts with both the database and the solver. C# programming language, which is one of the strongest and most flexible one in the market, is used to develop the application. The application has two main tasks. One is that it enables users to define products and their components, and enter monthly parameters of each component. The other task is that the application triggers excel solver to produce optimum results and print reports from the solutions.

IV.3. Data Source and Flow Mechanisms

Our entity relationship diagram is applicable in the previous section (Figure 4.2); namely technical issues. As you will see, there are 9 entities. The data in each of these entities is editable by the user of our system. For instance, using our graphical user interface, user can define a new product, define a new component, define component costs structures etc. Thus, database will be editable by the user via interface. Besides, user will enter monthly product demands and parameter changes, storage and budget constraints etc. which will be used by our system to execute model base behind it and create optimal solution.