Extended stock control functionality using extensions to MS Excel

Excellent as Microsoft Excel undoubtably is, there are limits to the applications it can address efficiently. In particular, there are often circumstances where some numerically intensive functionality is required, but the requirement is not covered by Microsoft's own built-in functions.

Now that Excel 97 is able to write and call VBA (Visual Basic for Applications) functions, it should be possible to build customised functions which meet pretty well any need.

The routines that follow were written when the XLM macro provided the only built-in function-writing capability. Unfortunately, when numerically-intensive calculations were required, the old Excel macro was unacceptably slow. Fortunately Excel provides a very flexible method of extending the scope of its own built-in functions. Provided argument addressing conforms to Excel standards, it is possible to use efficient external compliers to write DLL's which are able to take input from, and return results to, Excel spreadsheets via conventional argument lists. Even vector arguments can be used if needed

Whether these externally compiled DLL's still provide the same (large) speed advantage over VBA as they did over XLM macros isn't, and won't be, known until a new requirement emerges.

Random Number Generators:

These functions generate random number generated according to the exponential, normal and lognormal distributions respectively

Register(STOKUTILS, REXP, PP, RanExp, Avg_Gap)
Register(STOKUTILS, RNORM, PPP, RanNorm, Mu, Sigma)
Register(STOKUTILS, RLNORM, PPP, RanLogNorm, Mu, C_of_V)

"Fill Rate" Service Level Measures:

These functions calculate the stock level required to meet given service levels assuming demand distributed according to the normal, gamma, lognormal and negative binomial distributions respectively. They assume that "service level" defines the first-time probability of a pick failure. They are different from the more conventional (and simpler formulae) for which "service level" is interpretted as the probability of a stock-out at some point within the replenishment cycle.

Register(STOKUTILS, PARTIALNORM, PPPP, PartialNorm, Mu, Sigma, Serv_Level)
Register(STOKUTILS, PARTIALGAMMA, PPPP, PartialGamma, Mu, Sigma, Serv_Level)
Register(STOKUTILS, PARTIALLOGNORMAL,PPPP, PartialLogNormal, Mu, Sigma, Serv_Level)
Register(STOKUTILS, PARTIALPOISSON, PPPP, PartialPois, Mu, Sigma, Serv_Level)

Queuing Theory-related:

These formulae concern the size of a pool of rotables (repairables) required to provide a given availability for a population of equipments subject to given rates of (Poisson) failure and repair at a given number of repairers.

Register(STOKUTILS, ROTABLES, PPPPPP, Rotables, Utilisation, In_Serv_Pop, Tot_Pop, No_Repairers, No_Rot_Types)
Register(STOKUTILS, PZERO, PPP, PZero, Utilisation, No_Servers)

Stock Control

This function calculates the optimum Economic Order Quantity and Re-order Point (as a vector) for Gamma distributed demand. The parameters of the Gamma distributed demand are calculated on the basis of mean and variance of demand within mean leadtime, and leadtime variance.

Register(STOKUTILS, EOQ, PPPPPPPP, EOQ, MuDem, SigDem, MuLT, SigLT, SetUp, HoldCost, Serv_Level)

Multinomial

This function concerns the Multinomial Distribution. It both inputs, and returns, a vector.

Register(STOKUTILS, MULTINOMIAL, PPP, Multinomial, Arg1..N, ServLev/Ordinate)