Statistical Functions in Excel: Financial Modeling using MS Excel Training
MS Excel allows one to perform various advanced statistical functions which serve great help in the purpose of financial modeling. Anyone looking forward towards attaining a Financial Modeling certification needs to realize the advanced statistical functions using MS Excel. Let’s get introduced to statistical functions using MS Excel.
There are various types of statistical functions that can be used for calculations in MS Excel using the excel functions of the respective function. Mean, Standard Deviation, Skewness and Kurtosis are some of the most important ones which are discussed as below.
Mean: Mean is the most important concept in statistics. In mathematics and statistics, mean is the another name for average. The mean is calculated by adding all of the values together and then dividing by the number of original values. In excel the average function will automatically calculate a mean for a given data set. If we look at our excel worksheet we have a list of assets in a fund and their return to date.
- The excel function of Average is = Average (range or range_name)
Standard Deviation: Standard deviation is another major calculation of statistics. It is the square root of the variance, which measures the difference between the mean of the data set and the individual values. The parameter for this function is also just the range of data for which the standard deviation is calculated. Standard deviation is applied to the annual rate of return of an investment to measure the investment’s volatility. The higher is the standard deviation, the more volatile is the investment.
- The excel function of Standard Deviation is = STDEV (range or range_name)
Skewness: Skewness is another statistical function. It is a measure of symmetry. A normal distribution has skewness equal to 0; i.e. perfect symmetry. This symmetry does not exist much in the real life. A skewness value greater than +1 is the degree to which the data is skewed in the positive direction; likewise, a value less than –1 is the degree to which the data is skewed in the negative direction. A skewness value between –1 and +1 implies symmetry.
- The excel function of Skewness is =SKEW (range or range_name)
Kurtosis: Kurtosis is a measure of the flatness of the distribution. It compares the data peak to that of a Normal curve. Positive kurtosis indicates too few cases in the tails or a tall distribution, negative kurtosis on the other hand indicates too many cases in the tails or a flat distribution.
- The excel function of Kurtosis is =KURT (range or range_name)
To know more about statistical functions in excel, you can explore our training courses on Financial Modeling. Simplilearn offers both online and classroom training on Financial Modeling using MS Excel.