Use of Standard Deviation in Business – How to calculate Standard Deviation using Microsoft Excel

Introduction

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.

Students' marks

Source

So who is better Mary, John or Nike?
We can get the answer by using Standard deviation.

Students' marks & Standard Deviation

Source

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

Source

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:

Source

While for standard deviation of the entire population, just the denominator will be (n) instead of (n-1).

Source

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

  1. Get the figures in the first column.
  2. Calculate the average of these figures in the second column.
  3. Deduct the every actual figure from the average (Note: sure the total of differences is Zero.
  4. Square every difference (multiply it by itself) in column 3 & write the value in the forth column.
  5. Sum the Figures in column 4.
  6. 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.
  7. 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:

Source

Below is same but showing results.

Source

Excel Formulas

Here we can speak about three maybe new formulas for you.

SQRT
Syntax:
SQRT(number) where Number is the number for which you want the square root.

STDEV - Estimates standard deviation based on a sample.
Syntax:
STDEV(number1,number2,...)
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.
Syntax:
STDEVP(number1,number2,...)

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.

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.
How?

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.
Thanks.

More by this Author


The feedback is highly appreciated. Thanks 3 comments

ReneeDC1979 profile image

ReneeDC1979 3 years ago from Gaithersburg, Maryland

Where O where were you in February when I was taking statistics. I could have used your help.. Luckily my mom a retired statistician was able to help. Love the variety in your hubs. Keep hubbing!


Taleb80 profile image

Taleb80 3 years ago Author

Sure, your mom was better.

Thanks for your encouraging words.


Ashok 22 months ago

HI, I did my MBA. In my MBA program we had statistics for Managers subjects, believe me the person who taught me statistics was so bullshiting, he never explained it correctly the concepts. I am really glad to learn it from you. Please explain the same way about statistical testings and there real life business applications. !!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working