The Inventory 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 As Range)

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

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

= Inv_WIP(Del1_Inst,Del1)

An alternative entry would enter the ranges explicitly.

= Inv_WIP(B8:B10,B1:B7)

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 As Range)

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

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 As Range)

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.