Computation Section
Subunit Inventory Analysis

When several items are stored in inventory, there may be restrictions on features of the inventory system that involve all the items. For example, all inventories involve investment, and it may be that the total capital available for investment in inventories is limited. In this case we want to maximize total profit or minimize total inventory costs subject to a constraint on total inventory investment. This is an optimization problem. The add-in constructs models for three different optimization problems that have been discussed in the literature. All the models allow several different constraints to be placed on the system.

Optimization models are defined by selecting the Optimize command from the menu.

The optimization problems considered include the following.

  • Multiple Item: This option refers to the situation in which two or more items are stored in the inventory system and there are constraints on the system.
  • Common Cycle Time: For this option, the system is operated so that replenishment orders are placed on a regular time cycle. The several items in the inventory must all be replenished with the same interval between replenishments or with integer multiples of the system cycle time.
  • Machine Scheduling: Here we consider several items produced on the same machine. The items usually have finite production rates and non-zero setup times. The machine is scheduled on a regular cycle. In each cycle all or some of the items are produced. The replenishment of a specific item occurs in each cycle or in some multiple of the machine cycle time. In addition to the system constraints allowed for the other models, a constraint on total machine availability is introduced. A variable is added to allow more than one machine to be used for the system.

The add-in builds the optimization models using the Math Programming add-in, so this add-in must be installed for the optimization option. Models are solved with the Excel Solver add-in. In order to use this add-in it must be installed. In addition, before building a model, the user must open the Solver dialog, reached from the Tools menu. This establishes a link to the Solver add-in, and this step must be done or the model building process will not work.

The models constructed are nonlinear and in some cases involve integer variables. Optimization models involving nonlinear functions and integer variables are hard to solve. After solving a problem once using the Solve button on the worksheet, solve the problem again several times using Solver directly. Experiment with different Solver parameters such as: Precision, Tolerance and Convergence. Use several starting values for the solution variables and select the best of several Solver solutions.

When the Optimize command is selected, a dialog is presented that allows features of the system to be described. The dialog is shown below.

By clicking the buttons on the left, the features of the several inventories are specified. All items have the same features. The optimization options only consider deterministic systems. The Params button allows the user to specify default parameters for the items. When first placed on the worksheet all the items have the same parameters, but they may be changed directly on the worksheet.

The buttons in the center, determine the kind of optimization problem to be modeled. The boxes on the right determine the constraints that will be included. At least one constraint must be selected for a meaningful analysis. The Shortage constraint is available only for those systems that allow shortages. The Utilization constraint is available for the Common Cycle problem. It is required for the Machine Scheduling problem. We will explain the constraints on the following pages that show specific examples of each type.

Return to Top

tree roots

Operations Management / Industrial Engineering
by Paul A. Jensen
Copyright 2004 - All rights reserved