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