Add-Ins command, checking the SimulationTools box. Microsoft Excel provides two built-in functions that are of great importance in simulation modeling: rand and norminv



Yüklə 33,4 Kb.
tarix18.08.2018
ölçüsü33,4 Kb.
#72365

SIMTOOLS.XLA can be downloaded from http://www.kellogg.nwu.edu/faculty/myerson/ftp/addins.htm It should be saved into your MSOffice\Excel\Library directory or MSOffice\Office\Library directory (for Office97) or to the Excel MacroLibrary folder (in a Macintosh). Then, in Excel, you can add Simtools by the Tools>Add-Ins command, checking the SimulationTools box. Microsoft Excel provides two built-in functions that are of great importance in simulation modeling: RAND and NORMINV. Every RAND() that is used in a formula in an Excel workbook is takes a random value that is drawn from a uniform distribution on the interval between 0 and 1, independently of all other RANDs in the workbook. New random values of the RANDs are drawn every time the the spreadsheet is recalculated. The NORMINV(probability,mean,standard_dev) function can be used to make random variables that have a bell-shaped normal distribution, by putting a RAND() value in the place of the first "probability" parameter. Such a normal random variable has an approximately two-thirds probability of being between the values (mean-standard_dev) and (mean+standard_dev). So for example, if we enter the formula =NORMINV(RAND(),10,5) into any cell, then its value becomes a normal random variable with mean 10 and standard deviation 5, and it should be between 5 and 15 about two-thirds of the time. SIMTOOLS.XLA (version 3.3) adds 32 statistical functions to Excel. Ten of these Simtools functions are (like NORMINV) used with the RAND() function to make individual random variables that are drawn from other well-known families of probability distributions: BETINV, BINOMINV, DISCRINV, EXPOINV, GAMINV, GENLINV, LNORMINV, POISINV, TRIANINV, and XTREMINV. Six Simtools functions are used to work with correlations among random variables: CORAND, MCORRELS, MIDRAND MSQRT (an advanced function included to help sophisticated users compute correlated random variables like CORANDs faster), NORMIZE, and PRODS. Five Simtools functions are used for randomly generating discrete probability distributions: DIRICH, DIRALPHA, LGT, LGTINV, and SHUFFLE. Six Simtools functions are designed to facilitate decision analysis in Excel: CE, RISKTOL, UTIL, UINV, ARGMAX, and CEPR. Three Simtools functions are designed for use in a basic probability classes to analyze discrete random variables: STDEVPR, COVARPR, and CORRELPR. Two Simtools functions are designed to extend the basic regression-analysis capabilities of Excel: REGRESSN and YHATSTE. Short descriptions of these 32 Simtools functions, in alphabetical order, are as follows: ARGMAX(labels, values, testCells, criterion) returns a label from a cell in the labels-range that corresponds to a maximal value cell, among all the cells in the values-range that correspond to test cells (if any) that match the criterion. The labels-range and the values-range must have the same number of rows and the same number of columns. The testCells and criterion are optional. If they are included, then the testCells must be a range that has the same number of rows and columns as the labels-range and values-range. Sophisticated criteria with "*" as a wild card may be used, as in Excel's COUNTIF function. BINOMINV(probability, n, p) returns the inverse cumulative distribution for a binomial random variable. So the formula BINOMINV(RAND(),10,.7) yields a binomial random variable with n=10 and p=.7. The number of "successes" in n independent trials, when the probability of success is p in each trial, is a binomial random variable with parameters n and p. The mean of such a binomial random variable is n*p, and its standard deviation is (n*p*(1-p))^.5. BETINV(probability, mean, stdevn, lowerbound, upperbound) returns the inverse cumulative distribution for a Beta random variable on the interval from the lowerbound to the upperbound. This function differs from Excel's built-in BETAINV only in that the parameters are the mean and the standard deviation, rather than the mathematically traditional parameters "alpha" and "beta". If omitted, the default values for the lowerbound and upperbound are 0 and 1. The value of a Beta random variable is always in the finite interval between its lowerbound and its upper bound. Thus, the Beta distribution is often used to describe beliefs about an unknown proportion, which must always be in the interval between 0 and 1 (that is, between 0% and 100%). For example the formula BETINV(RAND(),.6,.1) yields a Beta random variable with mean 0.6 and standard deviation 0.1, which always takes values between (the default bounds) 0 and 1. CE(incomes, RiskTolConst, RiskTolSlope) returns the certainty equivalent of a random draw from the incomes range, for a decision-maker with constant risk tolerance (or with linear risk tolerance when the optional RiskTolSlope parameter is used). The incomes parameter must be a rectangular range. CE applies the UTIL function (with the given risk-tolerance parameters) to each number in this incomes range, computes the average of these UTILity values, applies the UINV function (with the same risk tolerance parameters) to this average utility, and returns the resulting certainty equivalent. If the risk tolerance constant is 0 and the RiskTolSlope parameter is omitted, then CE simply returns the average of the numbers in the incomes range. Nonnumerical cells in the incomes range are ignored. (If the RiskTolSlope parameter is used then a zero or negative risk tolerance generates an error.) CEPR(values, probabilities, RiskTolConst, testCells, criterion) returns the certainty equivalent, for a decision-maker with constant risk tolerance, of a random income drawn from the specified values according to the corresponding probabilities, conditional on the event where the corresponding test cells (if any) match the criterion. When the RiskTolConst parameter is 0, the CEPR function returns the conditional expected value. The values-range and the probabilities-range must have the same number of rows and the same number of columns. The testCells and criterion are optional. If included, the testCells range must have the same number of rows and the same number of columns as the values and probabilities ranges. Sophisticated criteria with "*" as a wild card may be used, as in Excel's COUNTIF function. CORAND(correlarray, randsource) returns a vector of uniform 0-to-1 random variables (individually like RANDs) that are correlated appropriately to serve as seeds for constructing random variables with the given correlations. CORAND is an array function, designed to return values simultaneously to a selected range of cells in a row, when entered with CTRL-SHIFT-ENTER. (If you have never used an array function, you should look at Excel's Help on "array formulas.") For any whole number n greater than 1, the correlarray parameter may be a square n-by-n array of correlations (as produced by MCORRELS), and then CORAND returns n correspondingly correlated values. Such an n-by-n correlation array should be symmetric and must have ones on the diagonal from top-left to bottom-right. (To handle a nonsymmetric array, CORAND only looks at the portion on and below the diagonal.) The correlarray parameter can also be a single number, in which case CORAND functions as if the correlarray parameter were a 2-by-2 array, returning two random values with the given correlation. For example, suppose that the value 1 is entered into cells A1 and B2, some value between -1 and 1 is entered into cell B1, and the formula =B1 is entered into the cell A2. Then suppose we select the selected range A4:B4 and we ctrl-shift-enter the array formula =CORAND(A1:B2). The values of cells A4 and B4 will then be random variables, each drawn from a uniform distribution on the interval from 0 to 1, but correlated according to the value of cell B1. If the value in cell B1 is 0.95, for example, then the random values of cells A4 and B4 will tend to be close to each other. But if the value in cell B1 is -0.95, then the random values of cells A4 and B4 will be negatively correlated and will tend to keep a sum close to 1. Because CORAND is only generating two random values in this case, the numerical correlarray syntax can also be equivalently used; that is, the array formula =CORAND(B1) would be functionally equivalent to =CORAND(A1:B2) in this example. With an n-by-n correlation array, CORAND's optional randsource parameter can be a reference either to a single cell or to n cells in a row, which should contain independent RAND or CORAND values. When randsource is a single cell, this cell's value is returned by CORAND as its last value on the right. When randsource is a range of n cells, CORAND looks in these cells for the random inputs that it needs to generate its n correlated outputs, preserving the rightmost value. When the optional randsource parameter is omitted, CORAND automatically generates its n random values by transforming the results of n implicit calls to Excel's random number generator. Specifying a single-cell randsource range is useful for making autocorrelated series. For example, suppose that cell A1 contains the value 0.8, cell B1 contains the formula =RAND(), and cell B2 contains the formula =CORAND($A$1,B1). Then the value of cell B2 will contain a uniform 0-to-1 random variable that has a correlation approximately 0.8 with the value of cell B1. Copying cell B2 down to B3:B20 will then yield a series of 20 autocorrelated random variables, each having correlation approximately 0.8 with the value above it. (Specifying a range of n cells as the randsource can be useful for making CORAND work with uniform random inputs that are generated by another simulation add-in for Excel.) CORAND is designed so that, when the CORAND variables are used to construct normal random variables (through the NORMINV function), the probabilistic correlations among these normal random variables will match the numbers in the given array. When CORANDs are used to make random variables with distributions other than the normal, the resulting correlations may be slightly different. (In fact, CORAND generates its random array by first creating an array of standard-normal random variables that have the given matrix of correlation coefficients, and then converting these normal random variables to uniform random variables by the NORMSDIST function.) When the correlation array is large, the CORAND function can be quite slow, because it recomputes a matrix square root every time it is called. If this is a problem, the MSQRT function (see below) can be used to generate the correlated random variables much more quickly, but using MSQRT requires a few more keystrokes and a bit more thinking to set up the model. CORRELPR(values1, values2, probabilities). For a probability distribution with corresponding values of two random variables, CORRELPR returns the correlation coefficient of the random variables. The first two parameters (values1, values2) are ranges which contain one cell for each possible combination of values of the two random variables, listing possible values of the first variable in the values1 range and the second variable in the values2 range. The third parameter (probabilities) is a range containing the corresponding probabilities of these value pairs. The cells in the probabilities range must contain nonnegative numbers that sum to 1. The values1 and values2 ranges must each have the same number of rows and the same number of columns as the probabilities range. COVARPR(values1, values2, probabilities). For a probability distribution with corresponding values of two random variables, COVARPR returns the covariance of the random variables. The first two parameters (values1, values2) are ranges which contain one cell for each possible combination of values of the two random variables, listing possible values of the first variable in the values1 range and the second variable in the values2 range. The third parameter (probabilities) is a range containing the corresponding probabilities of these value pairs. The cells in the probabilities range must contain nonnegative numbers that sum to 1. The values1 and values2 ranges must each have the same number of rows and the same number of columns as the probabilities range. DIRALPHA(dataArray) is an array function. To a selected range that has one row and the same number of columns as the data array, DIRALPHA returns estimated (maximum-likelihood) alpha parameters for a Dirichlet distribution, under the assumption that each row of the data array is an independent sample from a fixed Dirichlet distribution. Each row of the data array must contain nonnegative numbers that sum to one. DIRICH(alphaArray, randsource) is an array function. To a selected range that has the same numbers of rows and columns as the alpha array, DIRICH returns Dirichlet random fractions that are nonnegative and have a sum equal to 1. The means of these random fractions are proportional to the values in the alpha array. Higher alpha-array values yield lower variance. The values in the alpha array should be positive numbers, usually greater than 1. Random seeds for these Dirichlet fractions may be provided externally by RAND or CORAND formulas in a range specified by the optional randsource parameter. If included, this randsource must be a range that has the same numbers of rows and columns as the alpha array, and it must contain random numbers between 0 and 1 that are generated by independent RAND or CORAND formulas. With CORAND formulas, this optional randsource could be used to correlate the corresponding DIRICH fractions with other random variables in the spreadsheet. If the randsource parameter is omitted, then DIRICH generates its own random numbers. To describe the statistical properties of Dirichlet random fractions more precisely, let a(i) denote the value of the i'th cell in the alpha array, and let A denote the sum of all the cells in the alpha array. Then the i'th cell in the DIRICH array function has a random value with mean a(i)/A and with standard deviation (a(i)*(A-a(i))/(A*A*(A+1)))^0.5. For example, suppose that the values of cells A1, B1, and C1 are 20, 30, and 50 respectively, and suppose that the array formula =DIRICH(A1:C1) is ctrl-shift-entered into the selected range A3:B3. Then cell A3 has a random value with mean 0.2 and standard deviation 0.0398, cell B3 has a random value with mean 0.3 and standard deviation 0.0456, and cell C3 has a random value with mean 0.5 and standard deviation 0.0498. These Dirichlet random values in the range A3:C3 will always be nonnegative, and their sum will always equal 1 If we have several types of potential customers, Dirichlet random variables can be used to describe our uncertainty about what fractions of our customers will be of each type. If our beliefs about these unknown fractions are derived from data about the types that were observed in a sample of past customers then, in the alpha array, the value of the alpha cell that corresponds to each type of customer should be one more than the number of customers of this type that were observed in the sample. DISCRINV(randprob, values, probabilities). When the first parameter (randprob) is set equal to RAND(), then DISCRINV returns a discrete random variable with values and corresponding probabilities in the given ranges. EXPOINV(probability, mean) returns the inverse cumulative distribution for an exponential distribution. So =EXPOINV(RAND(),10) is an exponential random variable with mean 10. The exponential distribution is often used to describe waiting times, such as the unknown time that we will have to wait until the next customer arrives into a shop. The standard deviation of an exponential random variable is the same as its mean. The exponential random variables are actually a subset of the more general gamma family of random variables. GAMINV(probability, mean, stdevn) returns the inverse cumulative distribution for a gamma random variable. So =GAMINV(RAND(),10,5) is a gamma random variable with mean 10 and standard deviation 5. This GAMINV function differs from Excel's built-in GAMMAINV function only in that GAMINV is parameterized by the mean and standard deviation, rather than the mathematically traditional parameters "alpha" and "beta," which are harder to intuitively assess. The value of a gamma random variable can be any nonnegative number. Gamma random variables are sometimes used to describe our uncertainty about the length of time that some project will take. Gamma distributions are also used to describe our beliefs about the unknown mean of a Poisson random variable (For example, if we are learning about the rate at which accidents are likely to occur in a new factory, our uncertainty about the long-run rate of accidents might be described by a gamma distribution.) GENLINV(probability, quartile1, quartile2, quartile3, lowest, highest) returns the inverse cumulative distribution for a "generalized lognormal" random variable that has 25% probability below quartile1, 50% probability below quartile2, and 75% probability below quartile3. (A generalized lognormal random variable is a constant plus or minus a lognormal random variable.) So if a manager has assessed that his new product's first-year sales are equally likely to be above or below 3000 units, and have a probability 1/4 of being below 1000, and a probability 1/4 of being above 7000, then GENLINV(RAND(),1000,3000,7000) is a random variable that fits the manager's assessed quartiles. Thus, the GENLINV function is useful for simulating random variables for which a decision-maker has subjectively assessed quartile boundary points. In the case where quartile3-quartile2 = quartile2-quartile1, the generalized lognormal becomes simply a normal random variable, with mean equal to quartile2 and with standard deviation equal to (quartile3-quartile2)/0.675. So GENLINV(RAND(),1000,4000,7000) is a normal random variable with mean 4000 and standard deviation 3000/0.675 = 4444. In the case where quartile3/quartile2 = quartile2/quartile1, the generalized lognormal becomes simply a lognormal random variable (but its mean and standard deviation are not so simple to compute). The lowest and highest parameters are optional. If they are included, then values of GENLINV are adjusted as necessary to stay within these bounds. So the formula GENLINV(RAND(),quartile1,quartile2,quartile3,lowest,highest) is equivalent to the formula MIN(highest,MAX(lowest,GENLINV(RAND(),quartile1,quartile2,quartile3))) when the parameters satisfy the required inequalities lowest <= quartile1 < quartile2 < quartile3 <= highest. So to take account of the impossibility of negative sales, with the assessed quartile points 1000, 3000, and 7000, we can use the formula GENLINV(RAND(),1000,3000,7000,0), which is never negative (but may equal 0, with a probability close to 9%). If capacity constraints imply that sales also could never be higher than 20000, then we could use the formula GENLINV(RAND(),1000,3000,7000,0,20000) to simulate first-year sales. LGT(X) transforms random variables from a logit model to fractions between 0 and 1 or (as an array function) to discrete probability distributions proportional to EXP(x(i)). The parameter X may be a number, or a range of cells in a row. When X is a number or a single cell, LGT returns the value EXP(X)/(EXP(X)+1). When X is a range of n cells in a row, where n>=2, LGT returns an array of fractions that sum to 1, where the i'th fraction is EXP(X(i)) divided by the sum of all EXP(X(j)) for j=1 to n. (In a logit model, the last cell in the X range should equal 0 and the other cells in the X range should be multivariate normal random variables.) LGTINV(P) is the inverse of the logistic or logit function LGT. The parameter P may be a single number or a range of cells in a row. When P is a number or a single cell, LGTINV returns the value LN(P/(1-P)). When P is a range of n cells, where n>=2, LGTINV returns an array of log-odds ratios, where the i'th value is LN(P(i)/P(n)). (So the last value in aLGTINV array should equal 0.) When P contains distributional data, statisticians commonly assume that LGTINV(P) is a multivariate normal random vector. LNORMINV(probability, mean, stdevn) returns the inverse cumulative distribution for a lognormal random variable, parameterized by its mean and standard deviation. So the formula =LNORMINV(RAND(),10,5) generates a lognormal random variable that has mean 10 and standard deviation 5. Excel's LOGINV function might seem to do the same thing, but the "mean" and "standard deviation" parameters of LOGINV are actually the mean and standard deviations of the logarithm of the random variable that is generated by the function, not the random variable itself. The value of a lognormal random variable can be any nonnegative number. The product of two independent lognormal random variables is also lognormal. Because of this multiplicative property, lognormal random variables are often used to model the growth of demand for a product over some period of time, or the appreciation of a financial asset over some period of time. MCORRELS(dataRange) returns the matrix of correlation coefficients among the columns of the data range. MCORRELS is array function returning values to a square range in which the numbers of rows and the columns must be equal to the number of columns in the data range. The output of MCORRELS can be used as the correlation-array parameter of the CORAND function. MIDRAND(correlation, givenCoValue) returns the conditional median of a CORAND given the value of another CORAND and their correlation. Used for subjective assessment of correlations. MSQRT(squareArray) returns a lower-triangular matrix square root of a given square array. The formula =MSQRT(array) returns a matrix that has the same number of rows and columns as the given array. MSQRT is an array function, designed to return values to a selected range all at once (when entered with CTRL+SHIFT+ENTER). (If you have never used an array function, you should look at Excel's Help on "array formulas.") In Excel's array-formula notation, MSQRT is designed to satisfy the mathematical equation MMULT(TRANSPOSE(MSQRT(squareArray)),MSQRT(squareArray)) = (squareArray) when the given square array is symmetric. (To handle a nonsymmetric array, MSQRT only looks at the portion on and below the diagonal.) This MSQRT function can be used to build a row of normal random variables that have covariances and variances as in the given array. To do so, make a column-array of standard normal random variables (with NORMINV(RAND(),0,1) in each cell) and take the SUMPRODUCT of this column-array with each of the columns in the MSQRT(array) output. These sumproducts will jointly have a multivariate normal probability distribution, with mean 0 and covariances as in the given array. NORMIZE(datacolumn) returns values (fractile medians) from a standard normal distribution with the same rank-order as the data column. An array function. When CORANDs are used to make continuous random variables that are not Normal, the correlation parameters of CORAND should be normalized rank correlations, which can be estimated from data by applying NORMIZE to each data series and then computing the correlations among these normalized arrays. (Having large array formulas can make a spreadsheet recalculate slowly. To avoid this problem, a range of NORMIZEd data can be copied and pasted-special values onto itself.) POISINV(probability, mean) returns the inverse cumulative distribution for a Poisson random variable. So =POISINV(RAND(),10) is a Poisson random variable with mean equal to 10. The value of a Poisson random variable is always an nonnegative integer, but it can be arbitrarily large (unlike the binomial random variable which cannot be larger than its parameter n). Thus, Poisson random variables are often used for the unknown number of customers who will come into a shop during a fixed time interval, or the number of accidents that will occur in a factory during some fixed time interval. PRODS(values) multiplies each pair of values in the given range and returns the products as a square array. The values must be given in one row or one column. To illustrate the use of this function, suppose that a range named "correls" lists the correlations of the random returns per share of various stocks, a range named "stdevns" lists the standard deviations of these stock returns, and a range named "shares" lists the numbers of shares of these stocks in some investment portfolio; then the standard deviation of the total returns of the portfolio is SUMPRODUCT(PRODS(shares),PRODS(stdevns),correls)^0.5 RISKTOL(HighIncome, LowIncome, CertainEquiv) returns the constant risk tolerance such that a lottery paying either the high or low income, each with probability 1/2, has this certainty equivalent value. The parameters must be numbers satisfying the inequalities HighIncome > CertainEquive > LowIncome. (RISKTOL returns a #DIV/0! error when the CertainEquiv is exactly halfway between the HighIncome and the LowIncome, because this is the case of risk neutrality, which corresponds to infinite risk tolerance, and the AVERAGE function should be used instead of the CE function in this case.) REGRESSN(XDataRange, YDataRange) returns multiple regression output to a selected range, which must have 7 rows and as many columns as the XDataRange. This is an array function, and must be entered with the keystrokes CTRL+SHIFT+Enter. The XDataRange must be a rectangular range of cells, and the number of rows in this range must be at least two more than the number of columns. The YDataRange must be a range of cells in one column with as many rows as the X data range. The advantage of using REGRESSN instead of Excel's DataAnalysis regression package is that the output of REGRESSN actively changes whenever the data changes. (See also YHATSTE.) SHUFFLE(n, RandSource), entered as an array formula in a range of n cells in one row, returns a random ordering of the numbers from 1 to n. When entered into a row range of fewer than n cells, this function generates random samples from {1,...,n} without replacement. For small n (n<8), a list of all permutations of {1,...,n} can be generated by letting the optional RandSource parameter range from 0 to (n!-1)/n! in steps of size 1/n!. (The quantity n! is FACT(n) in Excel.) Given a range of n cells in a row, the array formula {=INDEX(range,1,SHUFFLE(n))}, entered into another n cells in a row, shuffles the values of the given range. STDEVPR(values, probabilities). For a probability distribution with corresponding values of a random variable, STDEVPR returns the standard deviation of the random variable. The first parameter (values) is a range that contains one cell for each possible value of the random variable. The second parameter (probabilities) is a range that contains the respective probabilities of these values. The cells in the probabilities range must contain nonnegative numbers that sum to 1. The values range must have the same number of rows and the same number of columns as the probabilities range. TRIANINV(probability,lowerbound,mostlikely,upperbound) returns the inverse cumulative for a triangular probability density on the interval from the lowerbound to the upperbound, with mode at the mostlikely value. So =TRIANINV(RAND(),5,10,20) yields a random variable that takes values between 5 and 20, with a probability density that is highest at 10. UINV(utility, RiskTolConst, RiskTolSlope) returns the monetary certainty equivalent that corresponds to an expected utility value computed with the UTIL function using the same risk-tolerance parameters. If omitted, the RiskTolSlope parameter is assumed to be zero (constant risk tolerance). UTIL(income, RiskTolConst, RiskTolSlope) returns the utility value of monetary income, for a decision maker with constant or linear risk tolerance. When the RiskTolSlope parameter is omitted or set equal to zero, then the formula used is UTIL(income,RiskTolConst) = -EXP(-income/RiskTolConst) When the risk-tolerance-slope parameter equals 1, then the formula is UTIL(income,RiskTolConst,1) = LN(RiskTolConst+income) When the RiskTolSlope parameter is neither zero nor one then the formula is UTIL(income,RiskTolConst,RiskTolSlope) = ((RiskTolConst+RiskTolSlope*income)^(1-1/RiskTolSlope))/(RiskTolSlope-1) XTREMINV(probability, mean, stdevn) returns inverse cumulative values for an extreme-value (or Gumbel) random variable, parameterized by its mean and standard deviation. When the first parameter is a RAND, XTREMINV yields a random variable that may be positive or negative. If W is a Weibull random variable then -LN(W) has this extreme-value distribution. As conventionally defined, the extreme-value distribution is positively skewed, with a longer tail in the positive direction. But negative value of the stdevn parameter can be used in XTREMINV to yield a negatively-skewed random variable that has a distribution which is the mirror image of a conventional extreme-value distribution. The standard deviation for such a random variable would then be the absolute value of the stdevn parameter. YHATSTE(XDataRange, NewXRow, RegressnStdErr) returns the standard error of the estimated conditional mean of Y (often called Y-hat) at the given new X row, for a multiple regression with the X data range and the regression standard error as specified in the parameters of YHATSTE. The XDataRange must be a rectangular range of cells. The new X row must be a range of cells in one row with as many columns as the X data range. The regression standard error is a number (specified by REGRESSN or by Excel in its regression output). (Function names that have changed in version 2 of Simtools: The version-1 functions BINV, GINV, TRNGLINV, and EXPLINV have been renamed BETINV, GAMINV, TRIANINV, and EXPOINV. The version-1 function EXPLUTIL function has been replaced by a new function UTIL which has better computational properties. The inverse of the UTIL function is UINV.) SIMTOOLS.XLA also adds three macro procedures to the Excel Tools menu: SIMULATION TABLE tabulates output from repeated MonteCarlo simulations of a spreadsheet model with random variables. To use SimulationTable, Excel's Calculation property must be set to Automatic (see Tools>Options>Calculation). Before using the SimulationTable macro, a range must be selected in which the output to be tabulated is in the top row, but not in the top-left cell. The output from repeated recalculations of the model then fills the lower rows of the selected range below these output cells. The leftmost column of the selected range is filled with fractile numbers, indicating (in each row of the simulation table) what fraction of the simulation data is above this row. SimulationTable is similar to a column-input DataTable, but SimulationTable stores the output data as values that are not recalculated whenever the spreadsheet changes. ITERATIVE PROCESS iteratively updates a State Range with values copied from an Update Range, while tabulating output into an Output Table. The State Range and the Update Range must have the same size. The Output Table must have outputs from the model in the top row, but leaving the top-left cell unused; iteration numbers will fill the left column. During iteration, Excel's Calculation option will be temporarily set to Manual, and then will be reset to Automatic when the simulation is done. COMBINE ROWS combines copies of the rows in selected input ranges, to make an output range whose rows are all the possible combinations of the rows in the input ranges. Using CombineRows (and the LOOKUP or VLOOKUP function), a column-input data table can be made to show functional dependence on any number of variables. CombineRows can also be useful in making tables to calculate conditional probabilities. In addition to the functions that are added by Simtools, the following regular Excel functions should be noted for their importance in making simulation models and for doing statistical analysis: RAND, NORMINV, BETAINV, GAMMAINV, CHIINV, LOGINV, NORMSDIST, TDIST, TINV, FINV, FDIST, LOOKUP, VLOOKUP, INDEX, EXP, LN, SUM, SUMPRODUCT, IF, AND, OR, SUMIF, COUNTIF, INT, MAX, MIN, AVERAGE, STDEV, STDEVP, FREQUENCY, QUARTILE, PERCENTILE, VAR, COVAR, CORREL, DCOUNT, DSUM, DAVERAGE, OFFSET, INDIRECT, TRANSPOSE, NPV. Anyone who does advanced modeling in Excel should also become familiar with the column-input DataTable procedure, the DataSort procedure, the Solver procedure, and Chart making. From more basic courses, students should be familiar with the Cut, Copy, Paste, and PasteSpecial>Values commands, and the use of absolute($) references. I also recommend using my FORMLIST.XLA add-in (also available from http://www.kellogg.nwu.edu/faculty/myerson/ftp/addins.htm) to display the hidden formulas that are used in any analytical spreadsheet. Formlist adds one macro procedure to the Excel Tools menu, and it adds one Lookup&Reference function called FORMULAS.
Yüklə 33,4 Kb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2022
rəhbərliyinə müraciət

    Ana səhifə