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