Microsoft Excel Add-in

my professional homepage





Intro Bibliography Book Shop The Bullwhip Explorer Management Games Excel Add-ins The Super-ellipse Explorer Links Bullwhip Help / Contact Info



Excel Add-In.  Many of functions required in Operations and Supply Chain Management  scenarios are not available native in Microsoft Excel.  I have created an Excel "Add-In" that you can install to add functionality to Excel to solve many common industrial problems. The Add-In can be downloaded here.  You should save this file in your Mircosoft\AddIn folder.  The location of this folder changes depending on your computer set-up, but if you use your Windows search tool to find the "AddIns" folder, you will find it.  Once the Add-In is saved in your Add-In folder, start Excel, goto the Add-In Manager (again this changes depending on your system, please refer to the Excel help if you can't find it) and you will see the "Operations Analysis" Addin.  Check the tickbox and you are all set.   You will now have access to the following Excel Functions.

=InvLossFun(x) Gives the inverse of the standard normal distribution function evaluated at x. This is a useful function when determining safety stock levels when net stock levels are normally distributed.
=LambertW(mode,z) Gives the real solutions to the Lambert W function on the principle branch (mode = 1) and the alternative branch (mode = -1), evaluated at z. This function is useful for identifying stability boundaries and bullwhip expressions in continuous time problems and also for identifying the Net Present Value of the cash flows in the EOQ problem.
=ExpSmo(range,alpha) Provides a simple exponential smoothing forecast using the data in range with an exponential smoothing constant of alpha, 0<=alpha<2.  The forecast in the first period is a naive forecast.
=CBk(phi_range,theta_range,k) Calculates a critical bullwhip condition for ARMA(p,q) demand in the Order-Up-To policy with a lead-time of k periods.  If CBk is positive bullwhip is generated.  If CBk is negative bullwhip is avoided.  This criteria even works with non-stationary demand.  The maximum allowable k is set to 100 as otherwise it slows up the computer.
=DampedTrend(range, alpha, beta, phi, Tp, WIPQuery) Calculates the Damped Trend forecast with a smoothing constant for the level of alpha, a smoothing constant for the trend of beta, a damping parameter of phi.  If WIPQuery is False then the Tp+1 period ahead forecast is calculated.  If  WIPQuery is True then the forecast calculated the sum of the forecasts over the next Tp periods. When phi = 1 the Holts Method forecasts are generated. When beta = 0, exponential smoothing forecasts are generated.
=Fillrate(mu1,sigma1,mu2,sigma2,rho) Calculates the proper fill rate in the linear Order-Up-To policy when demand is normally distributed.  Demand can also be correlated and possibly negative in some periods.  Mu1 is the mean of the (net stock + demand), Sigma1 is the standard deviation of the (net stock + demand), mu2 is the mean demand, sigma2 is the standard deviation of demand and rho is the Pearson Correlation Coefficient between (net stock + demand) and demand.


If there are any problems / questions please Email me