Use of Standard Deviation in Business – How to calculate Standard Deviation using Microsoft Excel
Some of us studied Standard Deviation & some not, and some has not used it so he forgot it.
So let us start from the base.
Note: If you now how to use the formula go directly to the bottom paragraph "How to use for improving business"
The definition: The standard deviation is a measure of how widely values are dispersed from the average value (the mean) (from MS Excel Help).
What does it mean?
Let us say that we have three students have the below marks in four subjects, as we can see all of them have same total 288 of 400, so they have same average as you can see.
So who is better Mary, John or Nike?
We can get the answer by using Standard deviation.
Students' marks & Standard Deviation
As we can see Marry is the best because she always get same mark (I know it is difficult to get that, but I wanted to show when standard deviation value could be Zero), John is the next because he is getting close marks to the average while Nike’s marks is very risky maybe he gets high mark or a very low mark.
The below chart show how much Mary’s marks are stable (just a straight line) John has a little fluctuate line while Nike’s line is a disaster.
Chart showing students' marks
What is the formula of standard Deviation?
Before we speak about the formula we should understand two terms, Population & Sample.
Population: Robert A. Donnelly Jr., Ph.D. says, “The term "population" is used in statistics to represent all possible measurements or outcomes that are of interest to us in a particular study."
Examples: Weight of every unit produced of a specific product like sugar or rice, All daily sales of Business.
Sample: Robert A. Donnelly Jr., Ph.D. Says, “The term "sample" refers to a portion of the population that is representative of the population from which it was selected.”
Examples: 48 units chosen randomly (Sugar or rice bags or 15 daily Sales randomly selected from all Business Sales).
Now what is the formula?
For standard deviation of a Sample use this one:
While for standard deviation of the entire population, just the denominator will be (n) instead of (n-1).
Where x̅ is the population/sample mean.
And n is the number of figures (it 4 in our previous example).
Steps of calculating standard Deviation
- Get the figures in the first column.
- Calculate the average of these figures in the second column.
- Deduct the every actual figure from the average (Note: sure the total of differences is Zero.
- Square every difference (multiply it by itself) in column 3 & write the value in the forth column.
- Sum the Figures in column 4.
- Divide the result in step 5 by n if you want SD for Entire population or by n-1 if you want ST for Sample.
- Now take the square root for the figure you get in step 6, this is Standard deviation.
Below is an example of how to calculate standard deviation, manually or by using direct formulas.
Here I show the Formulas:
Below is same but showing results.
Here we can speak about three maybe new formulas for you.
Syntax: SQRT(number) where Number is the number for which you want the square root.
STDEV - Estimates standard deviation based on a sample.
Number1, number2, ... are 1 to 255 number arguments corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.
STDEVP - Calculates standard deviation based on the entire population given as arguments.
Note: Here, I am using same data to calculate standard deviation based on a sample or a population for teaching purpose, in real life you chose one othe them depending on the data which you have.
Sure, calculating standard deviation based on a population is more accurate because it includes all the data.
Learn Microsoft Excel
How to use for improving Business
I know that you start feeling bored & tired but now we’ll speack about how to use Standard Deviation in business.
Mostly some risk lay behind fluctuations, fluctuations in Sales, Income, Cost & Quality.etc
So in business world, it is a preferable to reduce fluctuations.
For Example: If we have sales figures item wise, month wise, year wise, salesperson wise, branch wise, group wise etc.
1- We highlight which item, month, year, salesperson, etc has big standard deviation.
2- We analyze the data related properly to find out the real reasons behind these fluctuations.
3- Put well studied procedures for these reason to reduce it to the minimum.
Sometimes we cannot help when fluctuations is related to seasonality of sales, like Ice cream sales, which increase in summer & reduce sharply in winter, but we should work on reducing controllable ones.
Another example: if we are packing rice/sugar, we can take accurate weight for 25 bags randomly, and then calculate the standard deviation for the sample, as much as the SD is less as much the machine is packing well.
Sharing the Knowledge
you could ask any question about Excel in comment, I will do my best to answer it.
If you find this hub useful, share it with others, maybe someone gets some benefit.