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. |