Simulation models programmed in Excel can be simulated using Simetar©. Simetar©, an acronym for Simulation & Econometrics to Analyze Risk is an Excel add-in. Simetar© requires little additional memory and operates efficiently with Windows 7, 8 and 10 computers running Excel 2013 or 2016. Instructions for installing Simetar© are provided in the next section.  Simetar Only Works on the Downloaded Version of Office 365. Do Not Download Simetar if You Are Using the Web Version of Office 365.

Simetar© consists of both Menu Driven Functions and User Defined Functions that run in Excel. A common theme with Simetar©, is that all functions are dynamic; so if changes are made to the original data most all parameters, hypothesis tests, regression models, and risk ranking strategies are automatically updated. This feature of having Excel dynamically recalculate parameters offers significant efficiencies during the development, validation and verification steps for developing stochastic simulation models.

The more than 270 User Defined functions in Simetar© can be categorized into six groups: (1) simulating random variables, (2) statistical analyses and tests, (3) graphical analysis, (4) ranking risky alternatives, (5) data manipulation and analysis, (6) econometric modeling, and (7) forecasting. Simetar© can be used to perform all of the steps for developing, simulating, and applying a stochastic model, namely: estimate parameters for random variables, simulate stochastic variables, test the validity of the random variables, present the results graphically, and rank risky alternatives.

  SIMULATING RANDOM VARIABLES
  • Basic Univariate Probability Distributions
• =UNIFORM (min, max); USD=UNIFORM( )
• =NORM (mean, std dev); SND=NORM( )
• =EMPIRICAL (sorted data)
• =BETAINV (USD, alpha, beta, min, max)
• =GAMMINV (USD, alpha, beta)
• =BERNOULLI (p)
  • Multivariate Probability Distributions
• =MVNORM (mean vector, covariance matrix)
• =MVEMPIRICAL (matrix of variables)
• =CSND (correlation matrix)
• =CUSD (correlation matrix)
Simulation options for simulating an Excel workbook   Univariate parameter estimator for 16 probability distributions
  Set all random variables to expected value   Develop line chart with and without labels on data points
  Calculate summary statistics   Fan graph of alternative stochastic scenarios
  Multiple regression (OLS, GLS, Logit, Probit, Ridge, 2SLS, WLS)   Histogram of alternative stochastic scenarios
  Simple regression   Cumulative distribution function chart
  Calculate correlation and covariance matrices   Probability density function chart
  Data manipulation and matrix operation functions   Probability plot charts (NP, QQ and PP plots)
  Stochastic dominance with respect to a function   Box plot chart
  Stochastic efficiency with respect to a function   Scatter matrix plot
  Develop a stoplight chart for comparing risky alternatives   Estimate parameters for an empirical probability distribution
  Statistical tests for validating simulated random variables   Personal settings for Simetar©
  AR and VAR time series model estimates   Help for 250+ user defined functions in Simetar©
  Forecasting with exponential smoothing and seasonal indices
FUNCTION NAME   DESCRIPTION
ANOVA One way analysis of variance
ARLAG Recommends the number of lags in an autoregressive model
ARSCHWARZ Schwarz criterion associated with recommended number of lags
AUTOCORR Autocorrelation function for a univariate time series
BANDWIDTH Bandwidth function in kernel density estimation
BERNOULLI Bernoulli random variable
BERNOULLIDIST Bernoulli distribution function
BINOMINV Binomial random variable
BLOCKIT Column Vector to a Matrix
BOOTSTRAPPER Bootstrap re-sampling of a univariate or multivariate series
BOXCOX Box-Cox transformation of a data series for normalization
BOXCOXEXP Estimate of the Box-Cox exponent in a Box-Cox transformation
BOXM Box’s M statistic for testing multivariate variances
CAUCHY Cauchy random variable
CAUCHYDIST Cauchy distribution function
CDFDEV Indicate goodness of fit between sample data & known distribution data
CELLSUB Replace an item or items in a block of data
CERTEQ Certainty equivalent of a data series assuming a utility function
CMOVAVG Centered moving average
CONCAT Concatenate two or more matrices
COSDIST Cosine distribution function
COSINV Cosine random variable
CSND Correlated standard normal deviates
CUSD Correlated uniform standard deviates
DELNUM Remove the numbers from a string of text and numbers
DELTEXT Remove the text from a string of text and numbers
DEMPIRICAL Discrete empirical distribution random variable
DEXPONDIST Double exponential distribution function
DEXPONINV Double exponential random variable
DF Dickey-Fuller test statistic
DIRICHINV Dirichlet random variable
EDF Empirical distribution function
EMP Empirical random variable
EMPCOPULA Empirical copula function
EMPIRICAL Empirical random variable
EPANDIST Epanechnikov distribution function
EWMA Exponentially weighted moving average
EXPONINV Exponential random variable
EXTVALDIST Extreme value distribution function
EXTVALINV Extreme value random variable
GEOMDIST Geometric distribution function
GEOMINV Geometric random variable
GMDIF Gini’s mean difference
GRK GRK random variable
GRKS GRKS random variable
GRKSDIST GRKS distribution function
HOTELLTDIST Hotelling T-squared distribution function
HOTELLTINV Hotelling T-squared random variable
HYPERGEOMINV Hypergeometric random variable
IMPULSE Impulse response function in a vector autoregresion
INVGAUS Inverse Gaussian random variable
INVGAUSDIST Inverse Gaussian distribution function
IQR Inner quartile range of a sample
ITERSUM Sum a value across iterations during a simulation
JACKKNIFE Jackknife estimate of statistic, bias, and variance
KDEINV Random variable based on a kernel density estimate
KTAU Kendall’s Tau measure of concordance
LOGISTICDIST Logistic distribution function
LOGISTICINV Logistic random variable
LOGIT Logit binary response regression
LOGLOGDIST Log-log distribution function
LOGLOGINV Log-log random variable
LOGLOGISTICDIST Log-logistic distribution function
LOGLOGISTICINV Log-logistic random variable
LR Linear regression (OLS)
LRAIC Akaike information criterion for a regression
LRBIG Linear regression (OLS) for large data sets
LRDFBETA Observational diagnostics for a regression
LRDHATMAT Diagonal of the hat matrix
LRDW Durbin-Watson test statistic in a regression
LREGLS Estimated generalized least squares (EGLS)
LRGLS Generalized least squares (GLS)
LRGQ Goldfeld-Quandt test statistic for a regression
LROBS Regression observation count and degrees of freedom
LRPARTCORR Partial correlation function in a regression
LRRESID Residuals and predicted values in a regression
LRRHO Autocorrelation coefficient in the errors of a regression
LRRIDGE Ridge regression
LRSEMICORR Semi-partial correlation function in a regression
LRSIC Schwarz information criterion for a regression
LRT Likelihood ratio test in univariate or multivariate autoregression estimation
LRVIF Variance inflation factor for a regression
LRWLS Weighted least squares (WLS)
MAE Mean absolute error
MAHANGLE Mahalanobis angle of a data matrix
MAPE Mean absolute percent error
MCENTER Centering matrix of a specified dimension
MCHOL Choleski factorization of an nx(n+p) matrix, ()
MCOFACTOR Cofactor of a square matrix
MCOR Correlation matrix
MCOV Covariance matrix
MDET Determinant of a square matrix
MDIAG Diagonalize a vector or matrix
MDIST Squared Mahalanobis distance of two data matrices
MEQCORR Equicorrelation matrix of a specified dimension
MEVAL Eigenvalues of a square matrix
MEXP Exponential power of a matrix
MGINVERSE Generalized inverse of a matrix
MIDEN Identity matrix
MINV Inverse of a square matrix
MIP Inner product of two matrices
MJ Matrix of 1s
MKRON Kronecker multiply two matrices
MLEBETA Beta MLE of parameters
MLEBINOM Binomial MLE of parameters
MLEDEXPON Double Exponential MLE of parameters
MLEEXPON Exponential MLE of parameter
MLEGAMMA Gamma MLE of parameters
MLEGEOM Geometric MLE of parameter
MLELOGISTIC Logistic MLE of parameters
MLELOGLOG Log-Log MLE of parameters
MLELOGLOGISTIC Log-Logistic MLE of parameters
MLELOGNORM Lognormal MLE of parameters
MLENEGBIN Negative Binomial MLE of parameters
MLENORM Normal MLE of parameters
MLEPARETO Pareto MLE of parameters
MLEPOISSON Poisson MLE of parameter
MLEUNIFORM Uniform MLE of parameters
MLEWEIB Weibull MLE of parameters
MNORM Norm of a matrix
MOMBETA Beta Method Of Moments of parameters
MOMBINOM Binomial Method Of Moments of parameters
MOMDEXPON Double Exponential Method Of Moments of parameters
MOMEXPON Exponential Method Of Moments of parameter
MOMGAMMA Gamma METHOD OF MOMENTS of parameters
MOMGEOM Geometric METHOD OF MOMENTS of parameter
MOMLOGISTIC Logistic METHOD OF MOMENTS of parameters
MOMLOGLOG Log-Log METHOD OF MOMENTS of parameters
MOMLOGLOGISTIC Log-Logistic METHOD OF MOMENTS of parameters
MOMLOGNORM Lognormal METHOD OF MOMENTS of parameters
MOMNEGBIN Negative Binomial METHOD OF MOMENTS of parameters
MOMNORM Normal METHOD OF MOMENTS of parameters
MOMPARETO Pareto METHOD OF MOMENTS of parameters
MOMPOISSON Poisson METHOD OF MOMENTS of parameter
MOMUNIFORM Uniform METHOD OF MOMENTS of parameters
MOMWEIB Weibull METHOD OF MOMENTS of parameters
MORTH Orthoganalize a matrix
MOVAVG Moving average
MPROD Multiply two or more conformable matrices
MRANK Rank of a matrix
MRECH Row Echelon Form of a matrix
MRRECH Reduced row echelon form of a matrix
MSE Mean squared error
MSQRT Factor a square, symmetric matrix
MSTACK Stack two or more matrices
MSVD Singular value decomposition of a matrix
MSWEEP Sweep a square matrix on a diagonal element
MTOEP Column vector to a Toeplitz matrix
MTPNORM Modified two-piece normal random variable
MTPNORMDIST Modified two-piece normal distribution function
MTRACE Trace of a square matrix
MULTINOMDIST Multinomial distribution function
MULTINOMINV Multinomial random vector
MULTSORT Sort a matrix by a specified column
MVCHT LRT for complete homogeneity of multiple data matrices
MVCV Multivariate coefficient of variation
MVEMP Multivariate empirical random vector
MVEMPIRICAL Multivariate empirical random vector
MVEPANDIST Multivariate Epanechnikov distribution function
MVLOGNORM Multivariate lognormal random vector
MVNORM Multivariate normal random vector
MVNORMDIST Multivariate normal distribution function
MVPDENSITY Percentile based on a multivariate kernel density estimator
MVTINV Multivariate student’s t random variable
NEGBINOMINV Negative binomial random variable
NORM Normal random variable
NORMAD Anderson Darling statistic for test of normality
NORMCHI Chi-squared statistic for a test of normality
NORMCVM Cramer von Mises statistic for test of normality
NORMKS Kolmogorov Smirnov statistic for test of normality
NORMSW Shapiro-Wilks statistic for test of normality
OPT Find an iterative optimum solution
PARETO Pareto random variable
PARETODIST Pareto distribution function
PAUTOCORR Partial autocorrelation function for a univariate time series
PDENSITY Percentile based on a Kernel density estimator
PERTDIST Project evaluation and review technique (PERT) distribution function
PERTINV Project evaluation and review technique (PERT) random variable
PNORM Power normal random variable
PNORMDIST Power normal distribution function
POISSONINV Poisson random variable
PROBIT Probit binary response regression
QUANTILE Find the quantile of an empirical CDF given the probability
RANDSORT Randomly sort a vector
RANDWALK Generate a random walk series
RANKCORREL Rank correlation of two data series
REVERSE Reverse the order of a vector
RINTEGRAL Riemann integral of a bounded function
RMSE Root mean squared error
SCENARIO Return a value associated with different scenarios in a simulation
SEMICIRCDIST Semicircle distribution function
SEMICIRCINV Semicircle random variable
SEQ Sequence of numbers
SIMETARCR Returns copyright information for Simetar
STRETCHIT Matrix to a vector
TEMPIRICAL Truncated empirical random variable
TGAMMADIST Truncated gamma distribution function
TGAMMAINV Truncated gamma random variable
THEILU2 Theil’s U2 statistic for forecasts
TNORM Truncated normal random variable
TNORMDIST Truncated normal distribution function
TPNORM Two-piece normal random variable
TPNORMDIST Two-piece normal distribution function
TRANS Transpose a matrix
TRIANGLE Triangle random variable
TRIANGLEDIST Triangle distribution function
TWEIBDIST Truncated Weibull distribution function
TWEIBINV Truncated Weibull random variable
TWOSLS Two stage least squares (2SLS)
UNBOXCOX Convert a Box-Cox transformed value back to the original level
UNIFORM Uniform random variable
UNIFORMDIST Uniform distribution function
USND Uncorrelated standard normal deviate
UUSD Uncorrelated uniform standard deviate
VARAIC Akaike information criterion in univariate or multivariate autoregression models
VAREST Univariate or multivariate autoregression estimation function
VARLRT Likelihood ratio test in univariate or multivariate autoregression estimation
VARRESID Predictions & residuals in univariate or multivariate autoregression models
VFORMULA View the formula in the referenced cell
WAPE Weighted absolute percent error
WBNAME Return the name of the workbook
WEIBDIST Weibull distribution function
WEIBINV Weibull random variable
WILKSLDIST Approximate cdf of the Wilks’ Lambda random variable
WILKSLINV Wilks Lambda random variable
WISHDIST Wishart distribution function
WISHINV Wishart random matrix
WSNAME Return the name of the worksheet