Functions Deterministic Stochastic

 Inventory Analysis Functions for Deterministic Systems

The Inventory add-in provides a variety of functions to compute information about a deterministic inventory. An example is the function that computes the average inventory level.

Inv_Level(instance As Range, inventory As Range)

This function and most of the others.have two arguments that are both ranges. The instance is a range that specifies the variables controlling the inventory, usually lot size and fill rate. The range inventory holds the parameters of the inventory model. Although the computation of the inventory level for the some systems is very simple (Q/2 for the simplest case), the function is general for all deterministic systems including those with shortages.

The figure below shows computations for a model with the data name Inv1. The function calls and computations are in column B and column D shows how the functions are entered. This is a deterministic model with an infinite replenishment rate and no shortages. In this case instance is the single cell B13 that specifies the lot size. Since no shortages are allowed, the fill rate is 100% and is not required as a variable. The add-in provides an Excel name for the cells comprising the instance variables. The name is Inv1_Inst for this example.

The inventory for this model is the range B1:B12. The add-in has given this range the Excel name Inv1. As illustrated the range holds all the parameters necessary for the model. Particularly important is the Type specified in B3. The value Inf identifies the model as a system with infinite replenishments and no shortages. Other types are illustrated throughout these pages. The entries included in this range depend on the type of system. The function call for the example is in B16 and is an equality sign followed by the function name. The arguments are in the parentheses.

= Inv_Level(Inv1_Inst, Inv1)

An equally valid reference would be

= Inv_Level(B13, B1:B12)

Since the arguments are ranges, they must be references to worksheet cells or named ranges rather than specific numbers.

Cell B21 illustrates a second kind of function that has only one range argument. This function returns the optimum order level, so only the inventory is necessary to determine its value.

= Inv_EOQ(Inv1)

We have assigned the name Inv_Opt as the name of cell B21. In cells B25:B31 we use this range as the instance range in the function calls. Thus, these cells return the characteristics of the optimum solution.

Below the figure, there is a complete list of the functions for deterministic systems. Some of the functions are relevant only to systems with finite replenishment or that allow shortages. Particularly relevant is the function,

Inv_OptFR(inventory As Range)

This function returns the optimum fill rate for systems that allow shortages.

 Inv_Level(instance As Range, inventory As Range) Computes the average on-hand inventory level Inv_Short(instance As Range, inventory As Range) Computes the average backorder level. Inv_ResTime(instance As Range, inventory As Range) Computes the average residence time in inventory Inv_BOTime(instance As Range, inventory As Range) Computes average time an item is in backorder Inv_CycleTime(instance As Range, inventory As Range) Computes the time between orders Inv_InvCost(instance As Range, inventory As Range) Computes the average cost per unit time of costs related to the inventory Inv_TotProfit(instance As Range, inventory As Range) Computes the average (per unit time) revenue from sales less the average costs related to the inventory Inv_reorder(instance As Range, inventory As Range) Computes the reorder point Inv_MaxBO(instance As Range, inventory As Range) Computes the maximum backorder level reached. When Inv_Min is numeric this is the negative of that value. When it is not numeric the function returns ***. Inv_Min(instance As Range, inventory As Range) Computes the minimum inventory level Inv_Max(instance As Range, inventory As Range) Computes the maximum inventory level Inv_EOQ(inventory As Range) Returns optimum economic order quantity Inv_OptFR(inventory As Range) Returns the optimum fill rate for models allowing shortages
The figure below shows the functions used to analyze as system with shortages. The instance range now consists of two cells, one holds the lot size and the other the fill rate. The optimum solution specifies the lot size and fill rate to maximize the total profit. Since this model allows no lost sales, the solution also minimizes the total inventory cost.

The figure below shows an example with the data arranged horizontally in a row of cells. Functions can deal with either row or column orientation for both the instance and inventory ranges.

Operations Management / Industrial Engineering
Internet
by Paul A. Jensen