Forecasting for any small business involves guesswork. You know your business and its past performance, but you may not be comfortable predicting the future. Using Excel is a great way to perform what-if analysis, and formulas generating random values that make sense for your forecasting help present a variety of outcomes for analysis.
RAND() Function
-
Excel uses the function RAND() to return a random and uniformly distributed number between 0 and 1, every time the worksheet is calculated. If you are analyzing how fluctuations in sales will affect your business, for example, this number has little use on its own, since all random outcomes follow uniform distribution, while sales results usually follow a normal distribution.
See AlsoDeFi Saver Knowledge Base
Normal Distribution
-
Normal, or Gaussian, distribution describes a pattern where values in your defined range occur more frequently around a center point, or mean. Using the sales projection example, you observe that extreme high and low sales happen infrequently, that is, they are not uniformly distributed. If your average weekly sales are $2000, for instance, two adjacent weeks showing sales of $1,900 and $2,100 are more probable than weeks of $0 and $4,000.
NORMINV Function
-
The NORMINV function in Excel returns a number that is normally distributed around a mean, altered by a standard deviation and by a probability factor. In Excel, syntax for this function is NORMINV(probability,mean,standard deviation). Using the sales example, with a weekly sales average of $2,000 that fluctuates about $500 up or down, you know the mean and standard deviation. You can use the RAND() function to establish probability and create a random variable with normal distribution.
Formula Syntax
-
Use the formula "=NORMINV(RAND(),B2,C2)", where the RAND() function creates your probability, B2 provides your mean and C2 references your standard deviation. You can change B2 and C2 to reference different cells or enter the values into the formula itself. The combination of the two functions ensures generation of a random variable with normal distribution.
