add-in also has a function to estimate average Work in Progress
(WIP) for components of a system. The general call to this
function is written as below with two arguments.
Inv_WIP(instance As Range, inventory
Both arguments are ranges. The instance is a range
that specifies the variables controlling the component. The
range inventory holds the parameters associated with
the component that are necessary to compute the WIP and related
Several different types of components are
analyzed by the same function. The worksheet below shows the
results a delay, one of the simplest of the components. The inventory range
is B1:B7 and has the name Del1. The type of the component is
in B3, Delay. The WIP functions interpret the remainder
of the range based on this type. The instance range
is B8:B10 and has the name Del1_Inst. The controlling variables
are the input lot size (1), the delay time (0.1 week) and the
output lot size (2).
The WIP for this component is computed by the function is
An alternative entry would enter the ranges explicitly.
Although the data is arranged by column here,
the function also accepts a row orientation. The value, 10.50,
is the average level of WIP inventory caused by this delay.
Two other WIP related quantities are computed in the example
with two additional functions. The first computes the average
residence time, which is typically called cycle time in WIP
analysis. The second computes the holding cost associated with
the WIP. General call statements are below.
Inv_WIPCycle(Inv_L, inventory As Range)
Inv_WIPCost(instance As Range, inventory
The first argument of the Inv_WIPCycle function
is a pointer to the cell holding the average WIP.
The figure below illustrates another component
type, Process, which represents a processing operation
in a manufacturing system. Although the call statements the
WIP functions are the same, the inventory range is
larger for this component to accommodate parameters relevant
to a processing operation. The instance range still
has three cells, but the middle cell, B29, now shows the processing
The results of the WIP function appear in cells
B31 through B35. Two new functions are introduced here to compute
the processor utilization and the integer number of processors
required to accommodate the flow. The general call statements
show that have the two range arguments.
Inv_Utilization(instance As Range,
inventory As Range)
Inv_Mach_Used(instance As Range, inventory
Although Excel formulas or functions can implement
special cases of the results, we use functions to provide generality.
Because the functions use all the data and variables associated
the system, they can be used without the data forms constructed
by the add-in.
The WIP functions recognize seven different types
of components as illustrated in the figure below. We show with
x's data that is irrelevant for certain of the components.
The middle cell of the instance variables has different
meanings for the different components. The Change component
does not use the middle cell.