BUSINESS STAT HOMEWORK

Data Analysis Project #2 – Module 5

In this project we will revisit your friend who owns the fitness center. Assume that 1.5 years have passed

and in that time your friend has done a lot to upgrade the quality of his staff, his facilities, and the

fitness plans offered. He has asked for your assistance again as he is not sure how to do the analysis

himself. This project will use the skills you learned in Modules 3 and 4 on probability to help him better

staff his facilities. Here is what you’ll need to do:

Download the project data file. On the first worksheet (raw data), you will see a orange set of boxes.

Enter the last four digits of your student ID number into the boxes as shown in the example in the

worksheet. This will generate a fresh data set for you. You will be using the numbers in the blue boxes to

do your analyses. Each worksheet will explain which set of values you are to use. To ensure the numbers

stay constant, you will need to copy the correct set of numbers, then paste the VALUES into each

worksheet as directed.

The project requires you to use Excel to do a number of things to give you practice in doing basic data

analysis using probability. Specific questions to answer are posted in the discussion board for Module 5.

In answering those questions, you will be using the following required elements from your Excel data file

including:

1. Your own set of data, generated as described above (you cannot use anyone else’s)

2. At least one contingency table created with Excel’s pivot table function. You will use these

frequencies to answer specific questions in the Excel worksheet.

3. Use of Excel’s binomial probability functions to answer specific questions.

4. Use of Excel’s normal probability functions to answer specific questions about your data.

5. Use of Excel’s Poisson probability functions to answer specific questions about your data.

When you have completed your analysis, save the Excel file and add your name to the title, then upload

the completed file into the Assignment area in Module 5. Do not upload your Excel file to the

discussion board! I need you to upload it into the other area so I can collect them.

In the discussion board for Module 5, you will be briefly discussing your results and posting answers to

specific questions using your data analysis. Questions to discuss are posted in that board. Remember

this is not a multi-page essay you are creating. It is meant as a short project to get you used to the

power of Excel and how it can be used in analyzing data in a business setting.

NOTE: I am expecting that everyone in the course has already taken IS2241 or has basic familiarity with

using Excel. If that’s not the case, or if it has been a while since you’ve used these functions, then You

Tube is your friend.  There are tons of short videos out there that explain how to create charts, graphs,

etc. in Excel. I’ve got one posted that talks about pivot tables in Blackboard, but there are others you can

view if you get stuck or need ideas to create the required elements. Have fun!

The next page provides a brief tutorial on how to use Excel’s normal probability functions. Excel uses a

cumulative probability so it differs from our book a little, so this information should help you to

understand what Excel is doing. I have also posted several videos in Blackboard to assist you with using

the various probability functions.

How to use Excel’s NORM.DIST and NORM.INV functions

50 63 Scale of X

0 z Scale of Z

Excel’s NORM.DIST function always calculates cumulative probabilities, or the sum of the probability area to the left of X.

 If the value of X is above the mean, then entering the X value gives you a probability greater than 0.5 as it includes the area to the left of the mean and the area between the mean and the value of X.

 If the value of X is below the mean, then entering the X value gives you a probability less than 0.5 as it only includes the area to the left of X (in the lower tail, essentially).

To calculate probabilities in other areas, you’ll need to get creative with the formula. Suppose that we want to know the probability of lying between the mean and a value of X above the mean. In that case, you would be looking for the probability in the region of the green arrow above. Since Excel only calculates probabilities in cumulative format, you would need to follow a two-step procedure to find that probability: 1. Find P(X < 63) which would be =NORM.DIST(63,50,10,TRUE). This returns the cumulative probability of being to the left of 63

as shown by the orange arrow above. 2. Find P(X < 50), which would be =NORM.DIST(50,50,10,TRUE). You could use the formula, but we also know that the

probability of being to the left of the mean is 0.5 given the symmetric curve. 3. Consequently, the P(50 < x < 63) = NORM.DIST(63,50,10,TRUE) – 0.5. This gives you the value shown by the green arrow. 4. NOTE: If you are asked to find the probability between a number below the mean and above the mean, you’d simply use two

sets of calculations: For example, P(40 < x < 63) = NORM.DIST(63,50,10,TRUE) – NORM.DIST(40,50,10,TRUE). This gives you the value between those two values. Verify you understand by drawing a picture.

What if you want the probability of having an X value in the lower or upper tails? This also takes some thinking (and a drawing helps greatly!).

 Let’s suppose we want to find the P(X > 63). How do we find this? Well, we already know that =NORM.DIST(63,50,10,TRUE) will give us the cumulative probability P(X < 63). How would you then find the probability of being in the upper tail? We know that the total area under the curve is 1, so P(X > 63) = 1 – P(X < 63). Enter =1 - NORM.DIST(63,50,10,TRUE) in Excel.

 What if you want to find the P(X < 40), so you are in the lower tail now? Just as we did above, since Excel tells us the probability of being less than our X value, we can just use the formula “as is”, or =NORM.DIST(40,50,10,TRUE). We only need to adjust the formula for the upper tail given the way Excel calculates its probabilities.

What if we have a problem where we need to use the inverse normal to find the X value that corresponds with a certain percentile? Here, we use a different Excel function.

 Suppose that we are asked to find the value of X where the lower 10% of values lie (this is the 10th percentile). In this case, we use the NORM.INV function where NORM.INV(probability,mean,standard_dev). In this example, the probability would be 0.10, as we are looking for a lower tail probability (the probability in this function represents the percentile value). To find the number, we would enter =NORM.INV(.10,50,10). Again, Excel focuses on the probability that lies to the left of the number you are looking for. The answer Excel gives is the X value that lies at the 10th percentile.

 What if we are asked instead to find the value of the 68th percentile? In that case, we are now above the mean. To find the answer, we would enter =NORM.INV(.68,50,10) to get the value of X that lies at the 68th percentile.

 What if we are asked for the value of X that represents the top 5%? Here, we are looking for .05 in the upper tail. What percentile is this? That would be the 95th percentile, as 95% of values lie below that value of X and 5% lie above it. To use Excel to find the answer, type in =NORM.INV(.95,50,10). Your answer will be above the mean because the 95th percentile is above the mean. Make sure that the answers you get make sense. If they don’t, check your formula for errors!

Suppose mean = 50 and stnd dev = 10, and

the value of X we are interested in is 63.